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.
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:
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
NOTE: to allow ranges to "touch", use <=
in the starting expression, which would produce >
in the final expression.