Re: Help with cascading deletion in foreign keys

dsugar_at_dolphinsoft.com (Dave Sugar) wrote in message news:<55ab74ad.0402091445.84a8dc2_at_posting.google.com>...
> I am working on a database design for a many-to-many relationship
> between two tables (using a third table as an intermediate table to
> hold keys). And I'm trying to use foreign keys with cascading
> deletion to fulfill the following business rules. This is a simple
> case of an educational website with students and classes.
>
> How it should function
> 1) For each student there is a row in the students table
> 2) For each class there is a row in the classes table
> 3) As a student is added to a class there is a row added to the
> stu_class table.
> 4) If a student is in multiple classes there are multiple rows for
> that user in the stu_class table.
> 5) If a student is removed from a class the row is removed from the
> stu_class table.
> 6) When a student is removed from their last class the student row is
> removed from the students table.
> 7) If a class is removed all the rows in stu_class 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.
>
> Example tables (for MySQL version 4):
> create table students (
> stu_id integer not null,
> f_name varchar(32) not null,
> l_name varchar(32) not null,
> pw varchar(32) not null,
> constraint students_pk primary key (stu_id)
> ) type=innodb;
>
> create table classes (
> class_id integer not null,
> name varchar(32) not null,
> constraint classes_pk primary key (class_id)
> ) type=innodb;
>
> create table stu_class (
> stu_id integer not null,
> class_id integer not null,
> constraint stu_class_pk primary key (stu_id, class_id),
> index stu_class_index1 (stu_id),
> index stu_class_index2 (class_id),
> constraint stu_class_fk1 foreign key (stu_id) references students
> (stu_id) on delete cascade,
> constraint stu_class_fk2 foreign key (class_id) references classes
> (class_id) on delete cascade
> ) type=innodb;
>
> And some sample data:
>
> insert into students values (1, 'Student', 'one', 'asdf');
> insert into students values (2, 'Student', 'Two', 'asf');
> insert into students values (3, 'Student', 'Three', 'asfd');
> insert into students values (4, 'Student', 'Four', 'asfd');
>
>
> insert into classes values (1, 'One');
> insert into classes values (2, 'Two');
> insert into classes values (3, 'Three');
> insert into classes values (4, 'Four');
>
> insert into stu_class values (1, 1);
> insert into stu_class values (1, 2);
> insert into stu_class values (1, 3);
> insert into stu_class values (1, 4);
> insert into stu_class values (2, 2);
> insert into stu_class values (2, 3);
> insert into stu_class values (2, 4);
> insert into stu_class values (3, 3);
> insert into stu_class values (3, 4);
> insert into stu_class values (4, 4);
>
>
>
> This all fulfills the rules 1-5. But I'm having a tough time with
> rule 6 (and 7 but I think that when 6 is solved 7 will follow).
>
> I attempted to add the following foreign key:
> alter table students add (constraint students_fk1 foreign key (stu_id)
> references stu_class (stu_id) on delete cascade);
>
> But when that is introduced it will delete a row in the students table
> when any row is removed from the stu_class table that has a matching
> stu_id - even though there are other rows for the student.
>
> Any suggestions are welcome. I realize this can all be done in the
> application code but I was looking at options also.
>
> Please note - to simplify the example I have not done auto indexing
> and stuff on the tables. This is just a simple sample - not the real
> DB structure.
>
> Dave Sugar
> dsugar_at_dolphinsoft.com

Foreign keys can only cascade "down" (from parent to child), not "up" (from child to parent). You would need to use a database trigger (or application code) to achieve these "cascade up" rules.

I would question whether they are desirable in any case: what if a student is assigned to only one course, and it is the wrong course? One way to fix that is to delete the erroneous stu_class row and insert the correct one. But with your rule 6, by the time the user goes to insert the correct row, the student and all his details have disappeared from the system! Received on Tue Feb 10 2004 - 11:43:17 CET