Home -> Usenet -> comp.databases.theory -> Help with cascading deletion in foreign keys

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_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
Received on Mon Feb 09 2004 - 23:45:15 CET












Original text of this message