Simple SQL constraint question

Question

I can't get my ahead around what I feel should be a simple SQL constraint. Let's say I have the following table:

create table event(
    eventID    serial PRIMARY KEY,
    eventDate  date,
    start      time,
    end        time
);

And I want a constraint that says any two events cannot overlap; that is to say if two events are on the same day, the start time of one must be after the end time of the other, or vice versa.

In propositional logic, I'd so something like

FORALL e1,e2 in Events, e1.date = e2.date IMPLIES  (e1.start > e2.end OR e2.start > e1.end)

i.e. almost trivial. I'm fairly new to SQL though, and I just can't see how to do the same thing! Any pointers?

Thanks Tom

Answer 1

You would need to use a trigger that checks, on every insert or update, if the new event is on the same date/time as a previous one.

See PostgreSQL documentation for triggers here.

CREATE TRIGGER Trigger_check_overlap
BEFORE INSERT OR UPDATE ON event
FOR EACH ROW
EXECUTE PROCEDURE check_overlap();

Here is the documentation for trigger procedures, take note of the NEW special variable which allows you to single out the element being inserted. So you can compare NEW.date to the dates already in the table.