Member Avatar for crystality

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
  • 121 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by crystality
Member Avatar for crystality

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!
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.