0

Hi! This is for a homework assignment, and I'm trying to do cascaded deletions on tables that refer to each other.

Homework question:
Give an example instance of C and D, having at least 4 tuples in each of C and D, such that a deletion of a single tuple from C would result in cascaded deletions of all tuples in D and C. Try this in MySQL and document and explain the output you obtain!

This is what I tried, and as expected, when I do 'delete from C where ck = 3;' (line 16) only the tuple '3' gets deleted from both tables. =\ I've been trying to stick 'on delete cascade' in other places, but nothing is working. How would I get it to cascade to the ENTIRE parent table AND child table?

create table C (
	ck integer,
	primary key(ck)
) engine = innodb;
create table D (
	dk integer,
	primary key(dk),
	foreign key(dk) references C(ck) 
	  on delete cascade
) engine = innodb;
insert into C values (3), (24), (5), (32);
insert into D values (24), (5), (32), (3);
alter table C add foreign key(ck) references D(dk) on delete cascade;
select * from C;
select * from D;
delete from C where ck = 3;
select * from C;
select * from D;

Oh, I've tried 'alter table D add foreign key(dk) references D(dk) on delete cascade;' and 'alter table C add foreign key(ck) references C(ck) on delete cascade;' but no luck.

Any help would be appreciated. Thanks!

1
Contributor
1
Reply
2
Views
7 Years
Discussion Span
Last Post by crystality
0

Never mind, I got it. This is what I did:

create table C (
	ck1 integer,
	ck2 integer,
	primary key(ck1)
) engine = innodb;
create table D (
	dk1 integer,
	dk2 integer,
	primary key(dk1),
	foreign key(dk2) references C(ck1) 
	  on delete cascade
) engine = innodb;
insert into C values (3, 8), (8, 8), (5, 8), (32, 8);
insert into D values (3, 8), (8, 8), (5, 8), (32, 8);
alter table C add foreign key(ck2) references D(dk1) on delete cascade;
select * from C;
select * from D;
delete from C where ck1 = 8;	# delete a single tuple from C where ck1 = 8
select * from C;	# all tuples from C get deleted!
select * from D;	# all tuples from D get deleted!
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.