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 INSERT
s or UPDATE
s 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.