Re: Help with cascading deletion in foreign keys

Thanks for these suggestions. The triggers containgin deletes make a lot of sense to me and it isn't something that I would have considered on my own.

I think it will solve the referential integety items that I was concerned about in the database.

Dave Sugar
dsugar_at_dolphinsoft.com

In article <a264e7ea.0402111009.18326b01_at_posting.google.com>, --CELKO-- <joe.celko_at_northface.edu> wrote:

> 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 Sat Feb 14 2004 - 23:00:33 CET