Help with cascading deletion in foreign keys

From: Dave Sugar <dsugar_at_dolphinsoft.com>
Date: 9 Feb 2004 14:45:15 -0800
Message-ID: <55ab74ad.0402091445.84a8dc2@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 Received on Mon Feb 09 2004 - 16:45:15 CST