CODEWITHSUNDEEP
sql-serversql-server-2014
kyletme
kyletme

Reputation: 471

SQL Server Unique Key Constraint Date/Time Span

How can I set a unique key constraint for the following table to ensure the date/time span between the Date/BeginTime and Date/EndTime do not overlap with another record? If I need to add a computed column, what data type and calculation?

Column Name   Data Type
Date          date
BeginTime     time(7)
EndTime       time(7)

Thanks.

Upvotes: 2

Views: 2476

Answers (3)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Alexander Kuznetsov shows one possible way. Storing intervals of time with no overlaps.

See also article by Joe Celko: Contiguous Time Periods

Here is the table and the first interval:

CREATE TABLE dbo.IntegerSettings(SettingID INT NOT NULL,
  IntValue INT NOT NULL,
  StartedAt DATETIME NOT NULL,
  FinishedAt DATETIME NOT NULL,
  PreviousFinishedAt DATETIME NULL,

  CONSTRAINT PK_IntegerSettings_SettingID_FinishedAt 
      PRIMARY KEY(SettingID, FinishedAt),

  CONSTRAINT UNQ_IntegerSettings_SettingID_PreviousFinishedAt
      UNIQUE(SettingID, PreviousFinishedAt),

  CONSTRAINT FK_IntegerSettings_SettingID_PreviousFinishedAt
    FOREIGN KEY(SettingID, PreviousFinishedAt)
    REFERENCES dbo.IntegerSettings(SettingID, FinishedAt),

  CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt
      CHECK(PreviousFinishedAt <= StartedAt),

  CONSTRAINT CHK_IntegerSettings_StartedAt_Before_FinishedAt 
      CHECK(StartedAt < FinishedAt)
);

INSERT INTO dbo.IntegerSettings
    (SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 1, '20070101', '20070103', NULL);

Constraints enforce these rules:

  • There can be only one first interval for a setting
  • Next window must begin after the end of the previous one
  • Two different windows cannot refer to one and the same window as their previous one

Upvotes: 5

Sql Surfer
Sql Surfer

Reputation: 1422

-- this is a unique key that allows for null in EndTime field
--  This Unique Index could be clusteres optionally instead of the traditional primary key being clustered 
CREATE UNIQUE NONCLUSTERED INDEX  
[UNQ_IDX_Date_BeginTm_EndTm_UniqueIndex_With_Null_EndTime] ON [MyTableName]
(
    [Date] ASC,
    [BeginTime] ASC,
    [EndTime] ASC
) 

GO


-- this is a traditional PK Constraint that is clustered but EndTime is
--- Not Null
-- it is possible that this table would not have a traditional Primary Key
ALTER TABLE dbo.MyTable ADD CONSTRAINT
    PK_Date_BeginTm_EndTm_EndTimeIsNotNull PRIMARY KEY CLUSTERED 
    (
    Date,
    BeginTime,
    EndTime
    ) 

GO

-- HINT - Control your BeginTime and EndTime secconds and milliseconds at 
-- all insert and read points 
-- you want 13:01:42.000  and 13:01:42.333  to evaluate and compare the
-- exact way you expect from a KEY perspective

Upvotes: 0

cliffordheath
cliffordheath

Reputation: 2606

I don't believe that you can do that using a UNIQUE constraint in SQL Server. Postgres has this capability, but to implement it in SQL Server you must use a trigger. Since your question was "how can I do this using a unique key constraint", the correct answer is "you can't". If you had asked "how can I enforce this non-overlapping constraint", there is an answer.

Upvotes: 4

Related Questions