sql unique constraint with time window

Question

I have a table where records have a (begin, end) time window of existence (for things like employement duration, birth and death, rent duration, ...)

begin IS NULL or end IS NULL if there is no bound.

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

I want column value to be unique at any point in time.

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

Is OK

Whereas

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

Is not OK, because both records have value=1 and overlapping time windows.

Is there a way to enforce such a constraint in SQL ?

Thom A · Accepted Answer · 2021-07-23 15:50:11Z

You can't do this on the table, no, as there's nothing to make UNIQUE on. What you could do, however, is use a VIEW to enforce it.

Firstly, let's create your table. I assume the columns datetime, should actually be begin and end; I recommend against these names as they are reserved keywords. As such I am calling them DateBegin and DateEnd. I am also assuming that they are date only (no time portion) values and so define them as a date not a datetime:

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

And we'll INSERT your first 2 rows, as they are "ok":

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

Now we need to make a VIEW, but we need one row per date. As such you'll want to create a Calendar Table. I'm not going to cover how to create one here, but there are literally 100's of articles, such as there on SQL Server Central: Bones of SQL - The Calendar Table, Calendar Tables in T-SQL.

Once you have your Calendar table, you can create the VIEW below, which JOINs the data in your table to the calendar table. We're going to make it so that the VIEW just returns the columns value and the date. WE're also going to schemabind it; this means we'll be able to add an UNIQUE INDEX to it:

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

Now we have a VIEW that has a row for each date, and for each value. This means we can now create our UNIQUE INDEX:

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

Now if we try to INSERT a row that is on the same date and value, we'll get an error:

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

Cannot insert duplicate key row in object 'dbo.MyView' with unique index 'MyIndex'. The duplicate key value is (1, 2021-07-23).

Collectives™ on Stack Overflow

sql unique constraint with time window

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related