TSQL检查约束与现有行相关

Question

我有以下表格：

CREATE TABLE dbo.myTable] 
(
    ID1 [int] NOT NULL,
    ID2 [int] NOT NULL,
    StartDate smalldatetime NOT NULL,
    EndDate smalldatetime NULL,
    CONSTRAINT [PK_myTable1] PRIMARY KEY CLUSTERED (ID1 ASC, ID2 ASC, StartDate ASC)
) ON [PRIMARY]

我希望确保每个 ID1 和 ID2 的 StartDate 到 EndDate 期间是唯一的，没有重叠。

如何创建这样一个检查约束：

(
    ID1 <> existingRow.ID1
    or ID2 <> existingRow.ID2
)
or (
    ID1 = existingRow.ID1
    and ID2 = existingRow.ID2
    and (
        StartDate >= isnull(existingRow.EndDate, Startdate + 1)
        or isnull(EndDate, existingRow.StartDate + 1) <= existingRow.StartDate
    )
)

…或者一个带有如下条件的约束：

IF ID1 = existingRow.ID1 and ID2 = existingRow.ID2
CHECK (
    StartDate >= isnull(existingRow.EndDate, Startdate + 1)
    or isnull(EndDate, existingRow.StartDate + 1) <= existingRow.StartDate
)

提前感谢…

- MichaSch

---

你需要使用触发器来进行这样的验证。 - ughai

我无法完全理解你的伪代码，但你是否在寻找一个时间表？也就是说，在任何时刻，对于给定的ID1、ID2组合，恰好有一行“有效”数据？ - Damien_The_Unbeliever

如果是这样，您可能会发现这个答案有用。 - Damien_The_Unbeliever

我会尝试ughai提出的“before insert/update触发器”的想法。谢谢... ;) - MichaSch

我链接的答案可能看起来比“仅仅”使用触发器更复杂，但它的优点在于表中的实际正确性已经建模到约束条件中。那个答案中的触发器只是隐藏了一些细节。 - Damien_The_Unbeliever

你可以使用调用UDF的检查约束来实现。 - Tab Alleman

Answer 1

你可以有两个表：一个只存储ID1和ID2列，另一个复制你现在拥有的表。然后，你可以添加一系列触发器到第一个表中，在插入、更新或删除后，将插入或更新第二个表。大致如下所示：

CREATE TABLE dbo.TableOne
(
    ID1 [int] NOT NULL,
    ID2 [int] NOT NULL,
    CONSTRAINT [PK_myTable1] PRIMARY KEY CLUSTERED (ID1 ASC, ID2 ASC)
) ON [PRIMARY]

GO
create table TableTwo(
    ID1 int not null,
    ID2 int not null,
    StartDate smalldatetime not null default(getdate()),
    EndDate smalldatetime not null
)
GO
create trigger tTableOneAfterInsert on TableOne after insert
as begin
    update TableTwo 
        set EndDate = getdate()
    from TableTwo 
        join inserted on TableTwo.ID1 = inserted.ID1 and TableTwo.ID2 = inserted.ID2
    where TableTwo.EndDate is NULL

    insert into TableTwo(ID1, ID2)
    select ID1, ID2 from inserted
end
GO
create trigger tTableOneAfterUpdate on TableOne after update
as begin
    update TableTwo 
        set EndDate = getdate()
    from TableTwo 
        join inserted on TableTwo.ID1 = inserted.ID1 and TableTwo.ID2 = inserted.ID2
    where TableTwo.EndDate is NULL

    insert into TableTwo(ID1, ID2)
    select ID1, ID2 from inserted
end
GO
create trigger tTableOneAfterDelete on TableOne after delete
as begin
    update TableTwo 
        set EndDate = getdate()
    from TableTwo 
        join deleted on TableTwo.ID1 = deleted.ID1 and TableTwo.ID2 = deleted.ID2
    where TableTwo.EndDate is NULL
end

- Gamic

Answer 2

你试过类似这样的东西吗？

CREATE FUNCTION dbo.fn_check_unique_value 
(
    @ID1 INT,
    @ID2 INT,
    @StartDate SMALLDATETIME,
    @EndDate SMALLDATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT

    SET @Result = (SELECT COUNT(*) 
                   FROM   dbo.myTable existingRow
                   WHERE  (   @ID1 <> existingRow.ID1
                             or @ID2 <> existingRow.ID2
                            )
                            or (
                                @ID1 = existingRow.ID1
                                and @ID2 = existingRow.ID2
                                and (
                                     @StartDate >= isnull(existingRow.EndDate, @Startdate + 1)
                                     or isnull(@EndDate, existingRow.StartDate + 1) <= existingRow.StartDate
                                    )
                               ))

    RETURN @Result
END
GO

ALTER TABLE dbo.myTable ADD CONSTRAINT CK_No_Overlap
    CHECK ( dbo.fn_check_unique_value(ID1, ID2, StartDate, EndDate) <= 1
          )

- Issac Peña