CODEWITHSUNDEEP
sqlpostgresql
Tom Davies
Tom Davies

Reputation: 2446

Simple SQL constraint 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

Upvotes: 3

Views: 306

Answers (3)

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

First off, you want to hop to this post, which explains how to optimize this kind of query:

PostgreSQL matching interval between start and end time against timestamp

Once you've added a box column, you want to add an exclude constraint (which will add the index automatically):

exclude (datetime_box with &&) using gist

http://www.postgresql.org/docs/current/static/sql-createtable.html

Also, in case you're not aware, be wary of storing time zones:

http://derickrethans.nl/storing-date-time-in-database.html

Upvotes: 0

Boomer
Boomer

Reputation: 1478

do a little search on sql triggers, they are like events that occurs when u are trying to add, update or delete a record from ur table. there u can write ur script to check ur constraints.

Happy coding.

Upvotes: 0

dee-see
dee-see

Reputation: 24078

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.

Upvotes: 1

Related Questions