时间戳

首页 > 解决方案 > 带有时间窗口的sql唯一约束

sql - 带有时间窗口的sql唯一约束

问题描述

我有一个表，其中记录有一个（开始，结束）存在时间窗口（例如就业时间，出生和死亡，租金持续时间，......）

如果没有界限，则开始 IS NULL 或结束 IS NULL。

CREATE TABLE mytable(
id int primary key,
value int, --UNIQUE at any point in time
begin datetime  NULL,
end datetime  NULL
);

我希望列值在任何时间点都是唯一的。

INSERT INTO mytable VALUES(1, 1, '2021-07-23', '2021-07-24'),(2, 1, '2021-07-25', NULL);

没问题

然而

INSERT INTO mytable VALUES(1, 1, '2021-07-23', '2021-07-30'),(2, 1, '2021-07-25', NULL);

不行，因为两条记录的 value=1 和重叠的时间窗口。

有没有办法在 SQL 中强制执行这样的约束？

标签： sqlsql-serverunique-constraintreferential-integrity

解决方案

你不能在桌子上这样做，不，因为没有什么可做UNIQUE的。但是，您可以做的是使用 aVIEW来强制执行它。

首先，让我们创建您的表。我假设列datetime，实际上应该是begin和end；我建议不要使用这些名称，因为它们是保留关键字。因此，我打电话给他们DateBegin和DateEnd. 我还假设它们只是日期（没有时间部分）值，因此将它们定义为 a datenot a datetime：

CREATE TABLE dbo.mytable(ID int primary key,
                         Value int, 
                         [BeginDate] date NULL,
                         [EndEnd] date NULL);

我们将INSERT您的前 2 行，因为它们是“好的”：

INSERT INTO dbo.mytable (ID, Value, BeginDate, EndDate)
VALUES(1, 1, '20210723', '20210724'),
      (2, 1, '20210725', NULL);

现在我们需要制作一个VIEW，但我们需要每个日期一行。因此，您需要创建一个日历表。我不打算在这里介绍如何创建一个，但实际上有 100 多篇文章，例如 SQL Server Central 上的文章：SQL 的骨头 - 日历表，T-SQL 中的日历表。

拥有日历表后，您可以创建VIEW以下内容，JOIN将表中的数据添加到日历表中。我们将使它只VIEW返回列value和日期。我们还将对其进行模式绑定；这意味着我们将能够添加一个UNIQUE INDEX：

CREATE VIEW dbo.MyView
WITH SCHEMABINDING
AS
    SELECT MT.[Value],
           CT.CalendarDate
    FROM dbo.MyTable MT
         JOIN dbo.CalendarTable CT ON MT.BeginDate <= CT.CalendarDate --I assume, despite your schema, MT.BeginDate can't be NULL
                                  AND (MT.EndDate >= CT.CalendarDate OR MT.EndDate IS NULL);

现在我们有一个VIEW每个日期和每个值都有一行的。这意味着我们现在可以创建我们的UNIQUE INDEX：

CREATE UNIQUE CLUSTERED INDEX MyIndex ON dbo.MyView ([Value], CalendarDate);

现在，如果我们尝试INSERT在同一日期和值上的行，我们会得到一个错误：

INSERT INTO dbo.MyTable (ID, Value, BeginDate, EndDate)
VALUES(3, 1, '20210720', '20210723');

无法在具有唯一索引“MyIndex”的对象“dbo.MyView”中插入重复的键行。重复键值为 (1, 2021-07-23)。

推荐阅读