TSQL check constraint related to existing rows

Question

I have the following table:

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]

I want to ensure that the StartDate to EndDate period for each ID1 and ID2 is unique, and that there is no overlap.

How to create a check constraint like this:

(
    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
    )
)

...or a constraint with a condition like this:

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

Thanks in advance...

I can't quite follow your pseudo-code, but are you essentially looking for a [temporal table](https://msdn.microsoft.com/en-us/library/dn935015(v=sql.130).aspx)? That is, at any given moment in time, there's exactly one "valid" row for a given `ID1`, `ID2` combination? — Damien_The_Unbeliever, Jun 19 '15 at 06:29
If so, you might find [this answer](http://stackoverflow.com/a/6035413/15498) useful — Damien_The_Unbeliever, Jun 19 '15 at 06:41
I will try ughai's idea of a 'before insert/update trigger'. thx... ;) — MichaSch, Jun 19 '15 at 06:55
The answer I linked to may seem to be more work than "just" using a trigger but has the advantage that the actual correctness has been modelled in the constraints on the table. The triggers in that answer just hide some of the moving parts. — Damien_The_Unbeliever, Jun 19 '15 at 07:06

Issac Peña · Answer 1 · 2015-07-21T16:05:26.310

Did you try something like this?

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
          )

score 0 · Answer 2 · answered Sep 30 '17 at 10:39

You can have two tables: One that stores just the ID1 and ID2 columns, and one that copies the table you have now. You can then add a series of triggers to the first table that, after inserting, updating or deleting, will insert or update the second table. Something along the lines of:

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

TSQL check constraint related to existing rows

2 Answers2