CODEWITHSUNDEEP
sql-serverdatabasesql-server-2005database-designcheck-constraints
Antonio Manello
Antonio Manello

Reputation: 474

Date range overlapping check constraint

I've a simple table in sql server 2005 with 3 columns: DateStart, DateEnd and Value. I tried to set a "table check constraint" to avoid inserting overlapping records. For instance if in such table there is a record with DateStart = 2012-01-01 (first January) and DateEnd 2012-01-15 (15th January) than Check constraint must avoid inserting a record with DateStart=2012-01-10 (no care DateEnd), a record with DateEnd=2012-01-10 (no care DateStart) or a record with DateStart 2011-12-10 and DateEnd 2012-02-01.

I defined a UDF in such way:

CREATE FUNCTION [dbo].[ufn_checkOverlappingDateRange]
(
    @DateStart AS DATETIME
    ,@DateEnd AS DATETIME
)
RETURNS BIT 
AS
BEGIN
  DECLARE @retval BIT
  /* date range at least one day */
  IF (DATEDIFF(day,@DateStart,@DateEnd) < 1)
    BEGIN
      SET @retval=0
    END
  ELSE
    BEGIN
      IF EXISTS
        (
          SELECT
              *
            FROM [dbo].[myTable]
            WHERE
            ((DateStart <= @DateStart) AND (DateEnd > @DateStart))
            OR
            ((@DateStart <= DateStart) AND (@DateEnd > DateStart))
        )
        BEGIN
          SET @retval=0
        END
    ELSE
      BEGIN
            SET @retval=1
          END
        END
  RETURN @retval
END

Then thought check could be this:

ALTER TABLE [dbo].[myTable]  WITH CHECK ADD  CONSTRAINT [CK_OverlappingDateRange] CHECK  ([dbo].[ufn_checkOverlappingDateRange]([DateStart],[DateEnd])<>(0))

But even with [myTable] empty EXISTS Operator returns true when i insert first record. Where i'm wrog ? Is it possible to set a constraint like this ?

BTW I consider DateStart includes in range and DateEnd excludes from range.

Upvotes: 30

Views: 20361

Answers (3)

user1785960
user1785960

Reputation: 636


CREATE TABLE [dbo].[TEMPLATE] (
    [ID]             BIGINT       IDENTITY (1, 1) NOT NULL,
    [DATE_START]     DATETIME     NOT NULL,
    [DATE_END]       DATETIME     NOT NULL,
    [TEMPLATE_NAME]  VARCHAR (50) NOT NULL,
    CONSTRAINT [PK_TEMPLATE] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_current_start_and_end_dates_in_sequence] CHECK ([DATE_START]<=[DATE_END])
);

go


CREATE FUNCTION [dbo].[Check_Period]
(
    @start DateTime,
    @end DateTime
)
RETURNS INT
AS
BEGIN
    declare @result INT = 0 ;
    set @result = (Select count(*) from [dbo].[TEMPLATE] F where F.DATE_START <= @start and F.DATE_END >= @start );

    set @result = @result +  
    (
         Select count(*) from [dbo].[TEMPLATE] F where F.DATE_START <= @end and F.DATE_END >= @end 
    ) 
    RETURN @result
END

go

ALTER TABLE [dbo].[TEMPLATE]
    ADD CONSTRAINT [FK_overlap_period_t]
    CHECK ([dbo].[Check_Period]([DATE_START],[DATE_END])=(2));

go

Insert Into [dbo].[TEMPLATE] (DATE_START, DATE_END, TEMPLATE_NAME) values ('2020-01-01','2020-12-31', 'Test1');
-- (1 row(s) affected)
Insert Into [dbo].[TEMPLATE] (DATE_START, DATE_END, TEMPLATE_NAME) values ('2021-01-01','2022-12-31', 'Test2');
-- (1 row(s) affected)
Insert Into [dbo].[TEMPLATE] (DATE_START, DATE_END, TEMPLATE_NAME) values ('2020-01-01','2020-12-31', 'Test3');
-- The INSERT statement conflicted with the CHECK constraint "FK_overlap_period_t".

Upvotes: 3

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

The CHECK is being executed after the row has been inserted, so the range overlaps with itself.

You'll need to amend your WHERE to include something like: @MyTableId <> MyTableId.

BTW, your WHERE expression can be simplified.

Ranges don't overlap if:

  • end of the one range is before the start of the other
  • or start of the one range is after the end of the other.

Which could be written in SQL like:

WHERE @DateEnd < DateStart OR DateEnd < @DateStart

Negate that to get the ranges that do overlap...

WHERE NOT (@DateEnd < DateStart OR DateEnd < @DateStart)

...which according to De Morgan's laws is the same as...

WHERE NOT (@DateEnd < DateStart) AND NOT (DateEnd < @DateStart)

...which is the same as:

WHERE @DateEnd >= DateStart AND DateEnd >= @DateStart

So your final WHERE should be:

WHERE
    @MyTableId <> MyTableId
    AND @DateEnd >= DateStart
    AND DateEnd >= @DateStart

[SQL Fiddle]

NOTE: to allow ranges to "touch", use <= in the starting expression, which would produce > in the final expression.

Upvotes: 40

effy
effy

Reputation: 430

I'd just like to add onto the Answer of Branko Dimitrijevic the case where the DateEnd is null since I currently have such a scenario.

This can occur when you're keeping punch in logs and the user is still logged in.

WHERE
    @MyTableId <> MyTableId
    AND @DateEnd >= DateStart
    AND DateEnd >= @DateStart
    OR @DateEnd >= DateStart
    AND DateEnd is null
    OR @DateStart >= DateStart
    AND DateEnd is null

I don't know how well this query is performance wise, I'm sure there are ways to optimize it.

Upvotes: 0

Related Questions