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!