Date range overlapping check constraint

Question

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.

Answer 1

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.