Date range overlapping check constraint

17,585

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.

Share:
17,585

Related videos on Youtube

Antonio Manello
Author by

Antonio Manello

SOreadytohelp

Updated on June 03, 2022

Comments

  • Antonio Manello
    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
    Antonio Manello almost 12 years
    Thank 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
    Branko Dimitrijevic almost 12 years
    @AntonioManello If two DateStarts 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 make DateStart the primary key. In fact, this might be beneficial for clustering.
  • Hernán Eche
    Hernán Eche over 11 years
    Thinking 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
    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 and DateEnd.
  • Hernán Eche
    Hernán Eche over 11 years
    you are right if ID is not a key, it's just to put WHERE @MyTableId = MyTableId.. thanks
  • neo112
    neo112 almost 8 years
    this 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
    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
    Yllzarith over 6 years
    Without any parentheses this won't have the effect you may be thinking it would.
  • Leonardo Lopez
    Leonardo Lopez over 4 years
    Hey @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
    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 as Clustered Index Insert or Clustered Index Update, meaning it is executed later.
  • Branko Dimitrijevic
    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.