Geek asks and answers

Simple SQL Restriction Question

I can't get around what, in my opinion, 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 restriction that says that any two events cannot intersect; that is, if two events are on the same day, the start time of one should be after the end of the other or vice versa.

In propositional logic, I would like 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 new to SQL, although I just can't figure out how to do the same! Any pointers?

Thanks tom

+3
source share
3 answers

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

. PostgreSQL .

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

- , NEW, , . , NEW.date .

+1

SQL-, , , , ur. ur script ur.

.

0

, , , :

PostgreSQL

exclude ( ):

exclude (datetime_box with &&) using gist

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

Also, if you don’t know, be careful when storing time zones:

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

0
source

More articles:


All Articles