SQL constraint for between multiple values

Question

Here is my table

Events
Start : Datetime
End : Datetime

I'm trying to make sure that a new Event does not overlap any previously entered events. I'll admit my SQL knowledge is novice at best. The following is a select statement that gets me close but I can't figure out how to turn it into a constraint (would I use check?)

SELECT e.*
FROM Events
WHERE Start BETWEEN e.START and e.END
OR End BETWEEN new.START and new.END
OR (Start < e.Start and End > e.End)

I'd imagine if something like that returned anything at all there's overlap.

Edit: updated "sql" statement to cover for a logical failure on my part. It's most likely still poorly formed. I really appreciate all of the help!

Answer 1

This'll probably depend on the DBMS you're using, but such a constraint would usually need to be implemented using a trigger which performs a query to determine whether the insert/update is valid and rolls back the transaction if not. CHECK constraints typically only allow to you refer to columns in the 'current row'.

SELECT *
FROM Events E
WHERE E.Start <= NEWEND
AND E.End >= NEWSTART

...will get you the events that are overlapped with (change <= and >= with < and > if you're not interested in 'touching' events).

Answer 2

The SQL you provided isn't syntactically correct - use:

SELECT e.*
  FROM EVENTS e
 WHERE start BETWEEN e.START and e.END
    OR end BETWEEN e.START and e.END
    OR (start < e.START AND end > e.END)

BETWEEN is inclusive, and supported consistently on all databases.

Answer 3

This would actually have to go into code or a trigger. A constraint operates at the row level only

CREATE TRIGGER TRG_Events_INSERT On EventsFOR INSERT
AS
IF EXISTS (SELECT *
    FROM Events E
        JOIN
        INSERTED INS
         ON
           (E.Start Between INS.START and INS.END)
          OR
           (E.End Between INS.START and INS.END)
          OR
           (E.Start < INS.START and E.End > INS.END)

     WHERE
        E.Key <> INS.Key --already inserted at this point

BEGIN
   ROLLBACK TRAN
   --etc
END
GO

Answer 4

Hi Mushin,

What you can do is compare the table to itself in a join. Below is an example on how to do that. I commented out some lines. Uncomment them to see it progress to deal with different cases.

edit: If you need this as a constraint on the table itself you should go with a trigger.

edit2: For SQL 2005 replace the insert syntax with separate insert statements.

Good luck,

GJ

declare @event table (
    Id int,
    Start DateTime,
    Stop DateTime
)

insert  @event (Id, Start, Stop)
values  (1, '2010-08-01', '2010-08-02')
    ,(2, '2010-08-04', '2010-08-06')
    ,(3, '2010-08-05', '2010-08-08')    -- start fals within event 2
    --,(4, '2010-01-01', '2010-12-31')  -- overlaps with all of them
    --,(5, '2010-08-01', '2010-08-02')      -- equal to event 1

select  *
from    @event e1
        inner join @event e2 
            on  e1.Id != e2.Id          -- do not compare to itself
            and e2.Start >= e1.Start    -- events that have a start date
            and e2.Start <= e1.Stop