Re: expressing one-or-more constraint structurally

I need to edit that "cut & paste" before I use it again.

>> Let's say I want it that a teacher has one or more classes. <<

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 - 11:09:08 CDT