Re: expressing one-or-more constraint structurally
Date: 27 Jun 2004 09:09:08 -0700
Message-ID: <18c7b3c2.0406270809.524641f1_at_posting.google.com>
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 - 18:09:08 CEST