Expressing data constraints in SQL

Question

I'm writing up a scheduling application for my wife, and I ran into the following question.

I have a schema that can be described in ActiveRecord terms as such:

Each resource has and belongs to many events
Each event has and belongs to many resources, and has many timespans
Each timespan belongs to an event, and has three attributes: day, start-time, and end-time.

I want to put the following constraints on my data:

    A. For each timespan, timespan.start-time ≤ timespan.end-time
    B. For each event, for each t_a, t_b ∈ TIMESPANS(event), 
       either t_a = t_b
           or t_a.day ≠ t_b.day
           or t_a.end-time ≤ t_b.start-time
           or t_a.start-time ≥ t_b.end-time
    C. For each resource, for each e_a, e_b ∈ EVENTS(resource),
       either e_a = e_b
           or for each t_a ∈ TIMESPANS(e_a) and t_b ∈ TIMESPANS(e_b),
              either t_a.day ≠ t_b.day
                  or t_a.end-time ≤ t_b.start-time
                  or t_a.start-time ≥ t_b.end-time

(A) makes sure timespans are well-formed, (B) makes sure events don't self-conflict, and (C) makes sure resources aren't over-scheduled.

Currently I'm enforcing these constraints at the application layer, but, in the interest of self-learning, I was wondering if I can put these constraints in the database layer.

Is there any way I can express these constraints in SQL?

EDIT: So I found that what I wanted was SQL's CREATE ASSERTION statement (at least, for the ones that couldn't be covered by a simple CHECK), but it doesn't seem like any RDBMS that supports it (at least, as of 2005)

You're scheduling your wife? ;-)

Ben
– Ben

2009-02-02 15:20:30 +00:00
Commented Feb 2, 2009 at 15:20 — Ben
– Ben, Commented Feb 2, 2009 at 15:20

Cade Roux · Accepted Answer · 2009-02-02 15:30:10Z

2

A is easily expressed in a CHECK CONSTRAINT on your timespan table, since each row can check itself for consistency without checking any other rows.

B and C are more complex and would require a reading information out of other tables. In this case (at least in SQL Server), one could use a user-defined function (passing it the values from the current row) to check against the other tables or you could use a TRIGGER which would check the other tables.

NB: When writing such a trigger, remember that a trigger fires once for a SQL statement. If that statement INSERTs or UPDATEs multiple rows, the INSERTED table will contain multiple rows, which should all be checked to match your domain constraints using an appropriate technique, like a JOIN.

BTW: Joe Celko had a good article recently on constraints.

answered Feb 2, 2009 at 15:30

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Expressing data constraints in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related