Re: Help with cascading deletion in foreign keys

CREATE TABLE Students
(stu_id INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE Classes
(class_id INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE Registration
(stu_id INTEGER NOT NULL,

   REFERENCES Students (stu_id)
   ON UPDATE CASCADE
   ON DELETE CASCADE,
 class_id INTEGER NOT NULL
   REFERENCES Classes
   ON UPDATE CASCADE
   ON DELETE CASCADE,
 PRIMARY KEY (stu_id, class_id));

How it should function:

1. For each student there is a [single] row in the Students table. Done via PRIMARY KEY on Students.
2. For each class there is a [single] row in the Classes table Done via PRIMARY KEY on Classes.
3. As a student is added to a class there is a row added to the Registration table. Done via compound PRIMARY KEY on Registration.
4. If a student is in multiple Classes there are multiple rows for that student in the Registration table. Done via compound PRIMARY KEY on Registration.
5. If a student is removed from a class the row is removed from the Registration table. Done via compound PRIMARY KEY on Registration.
6. When a student is removed from their last class the student row is removed from the Students table.

DELETE FROM Student
WHERE NOT EXISTS

     (SELECT *
        FROM Registration AS R1
       R1.stu_id = Student.stu_id);

7) If a class is removed all the rows in Registration are removed from that class. And all Students who were only in that class are deleted but Students that are still in other Classes are not deleted.

DELETE FROM Classes
WHERE NOT EXISTS

     (SELECT *
        FROM Registration AS R1
       WHERE R1.class_id = Classes.class_id);

Okay, we could put these DELETEs in triggers or even a CREATE ASSERTION statement. But try this nightmare:

CREATE TABLE Students
(stu_id INTEGER NOT NULL PRIMARY KEY,

 stu_id_1 INTEGER NOT NULL UNIQUE
  REFERENCES Registration (stu_id)
  ON DELETE CASCADE,
  CHECK (stu_id = stu_id_1));

CREATE TABLE Classes
(class_id INTEGER NOT NULL PRIMARY KEY,
 class_id_1 INTEGER NOT NULL UNIQUE
  REFERENCES Registration (class_id)
  ON DELETE CASCADE,
CHECK (class_id = class_id_1)
);

CREATE TABLE Registration
(stu_id INTEGER NOT NULL UNIQUE

   REFERENCES Students (stu_id)
   ON DELETE CASCADE,
 class_id INTEGER NOT NULL UNIQUE
   REFERENCES Classes
   ON DELETE CASCADE,
 PRIMARY KEY (stu_id, class_id));

This passes the Mimer validation, but you'd better defer constraints so you can insert data into the tables. Received on Wed Feb 11 2004 - 19:09:04 CET