Re: expressing one-or-more constraint structurally
Date: 27 Jun 2004 09:09:08 -0700
I need to edit that "cut & paste" before I use it again.
CREATE TABLE Teachers
(teacher_id CHAR(9) NOT NULL PRIMARY KEY, ..);
CREATE TABLE Classes
(class_nbr INTEGER NOT NULL PRIMARY KEY, ..);
Now we need a table with the relationship of "scheduled:
CREATE TABLE Schedule
(class_nbr INTEGER NOT NULL PRIMARY KEY,
REFERENCES Classes(class_nbr_id) ON DELETE CASCADE ON UPDATE CASCADE, teacher_id CHAR(9) NOT NULL REFERENCES Teachers(teacher_id) ON DELETE CASCADE ON UPDATE CASCADE,
The hard part is getting every teacher at least one class. Right now, I can have an unassigned teacher. A teacher has to come into existence in both tables at the same instant in time, so you need to defer constraints. In full SQL-92, it is easy:
CONSTRAINT AllTeachersHaveClass -- needs to be DEFERABLE CHECK((SELECT COUNT(*) FROM Teachers AS T1)
- (SELECT COUNT(DISTINCT teacher_id) FROM Schedule))
I wrote it this way, instead of using joins, hoping that COUNT(*) and COUNT(DISTINCT ) can be easily computed from index structures or statistics tables.
I can use the same CHECK() on either Teachers or Classes. Unfortunately, most SQLs do not have ASSERTIONs or table-level CHECK() constraints yet. So you fake it with triggers, allow access only thru procedures, etc. in most products for now. Received on Sun Jun 27 2004 - 18:09:08 CEST