Date range overlapping check constraint
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
NOTE: to allow ranges to "touch", use <=
in the starting expression, which would produce >
in the final expression.
Related videos on Youtube
![Antonio Manello](https://i.stack.imgur.com/IFFr8.jpg?s=256&g=1)
Comments
-
Antonio Manello about 2 years
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.
-
Antonio Manello almost 12 yearsThank you Branko for your answer (also with fiddle :-)) with very very clear explanation. Do you think a could use DateStart as primary key instead of MyTableId or it is not a good idea ?
-
Branko Dimitrijevic almost 12 years@AntonioManello If two
DateStart
s are equal, the corresponding ranges would overlap, which is forbidden by your business rules. So,DateStart
must be unique, making it a key. Whether it should be primary key is a different matter: Do ranges ever change and do you have FKs towards other tables? If yes, then the surrogate PK would cut the ON UPDATE CASCADE. If no, you might just as well makeDateStart
the primary key. In fact, this might be beneficial for clustering. -
Hernán Eche over 11 yearsThinking about avoid interval overlapping only for same id ( not a key but a value from other column) I mean, not to avoid overlap at table level but just for some entries that belongs to same ID.. is it possible?
-
Branko Dimitrijevic over 11 years@HernánEche I don't know what "ID" means in the context above, but I don't see any particular reason why you shouldn't be able to add whatever restriction you need to the WHERE condition above. You'd probably need to pass that ID as the function parameter, similarly to what is already done with
MyTableId
,DateStart
andDateEnd
. -
Hernán Eche over 11 yearsyou are right if ID is not a key, it's just to put WHERE
@MyTableId = MyTableId
.. thanks -
neo112 almost 8 yearsthis approach won't be consistent when using READ COMMITTED SNAPSHOT isolation though? Since each transaction get their own snapshot at the start of the transaction which may lead to both transactions seeing they've ended up with a valid state (ie transaction B won't see that transaction A has already added a row that will cause overlap)
-
Branko Dimitrijevic almost 8 years@neo112 Yes. And race conditions exist for non-SNAPSHOT isolation too. This can be solved by locking the entire table (which has obvious scalability consequences), or very carefully locking individual rows (not in the original table but in a special table of predefined date ranges) . Thanks for pointing the problem out - I probably should have mentioned these complications in my answer...
-
Yllzarith over 6 yearsWithout any parentheses this won't have the effect you may be thinking it would.
-
Leonardo Lopez over 4 yearsHey @BrankoDimitrijevic, in the first sentence in your answer "The CHECK is being executed after the row has been inserted", where did you happen to read this, or how were you able to check that this is really what is happening? I'm reading all of the documentation I can about CHECK constraints, and I am unable to find any mention of it.
-
Branko Dimitrijevic over 4 years@LeonardoLopez This is not a documented behavior, as far as I'm aware, so you should not rely on it (where possible, of course) - it's best to write CHECKs to be agnostic of when they are actually evaluated. That being said, it seems this has been a long-standing behavior in SQL Server, and you can see it directly in the execution plan: the
Asset
node will always be above nodes such asClustered Index Insert
orClustered Index Update
, meaning it is executed later. -
Branko Dimitrijevic over 4 years@LeonardoLopez You can also see it from a debugger: break in the function under the CHECK, and then use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED from another session and query the table - the new data will already be there.