on delete cascade or join delete: dilemna

Question

When two tables are linked by a third table which contains one constraint built with the two fk, is it a good practice to use on delete cascade or a join delete ?

create T1 (
id1 ...
...
CONSTRAINT pk_id1 PRIMARY KEY (id1) );

create T2 (
id2 ...
...
CONSTRAINT pk_id2 PRIMARY KEY (id2) );

create T3 (
id1 ...,
id2 ...,
CONSTRAINT pk_T3 PRIMARY KEY (id1, id2),
CONSTRAINT fk_T3_1 FOREIGN KEY (id2)
  REFERENCES T2 (id2) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_T3_2 FOREIGN KEY (id1)
  REFERENCES T1(id1) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE )

Some people say it's dangerous to use on delete cascade, then is a join delete less dangerous ?

Edit: I didn't find the correct query to delete:
if I do

delete from T1 where id1=$id1

then the row $id1 is deleted from T1 and the row $id1,$id2 from T3 BUT not $id2 from T2.
If I do

delete from T3 where id1=$id1 and id2=$id2

then the row $id1 is not deleted in T1, the row $id2 is not deleted in T2, the couple ($id1,$id2) is deleted in T3.
How can I delete in ONE query ?
Edit: I thought to find a trick by moving one of the constraint in the T2 table, like so:

 create T1 (
id1 ...
...
CONSTRAINT pk_id1 PRIMARY KEY (id1) );

create T2 (
id2 ...
...
CONSTRAINT pk_id2 PRIMARY KEY (id2) ),
CONSTRAINT fk_T2 FOREIGN KEY (id2)
  REFERENCES T3 (id2) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE);

create T3 (
id1 ...,
id2 ...,
CONSTRAINT pk_T3 PRIMARY KEY (id1, id2),

CONSTRAINT fk_T3_2 FOREIGN KEY (id1)
  REFERENCES T1(id1) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE );

But I have the message "No unique constraint ... for the referenced T3 table"

And the danger is . . . what? – Mike Sherrill 'Cat Recall' Jun 08 '13 at 15:17 — Mike Sherrill 'Cat Recall', Jun 08 '13 at 15:17

score 3 · Accepted Answer · answered Jun 08 '13 at 15:28

3

On delete cascade is occasionally referred to as "dangerous" in the sense that a mindless query can end up deleting a lot more than you thought. I've seen at least one case where an incorrect where clause (which inadvertently deleted root nodes in a tree) ended up deleting heaps more than was expected (namely, those root nodes' subtrees) through cascade deletes.

A join delete is no less dangerous in this sense.

What is occasionally suggested to users who are learning SQL is to not use cascade deletes at all. This forces them to mind what they delete and in which order, as they get handy error messages when their where clauses are incorrect. But then, if you're comfortable enough with SQL to worry about using join deletes, you're likely way past that point.

answered Jun 08 '13 at 15:28

Denis de Bernardy

75,850
13
131
154

This is an answer for mysql, not standard which doesn't use delete cascade ! – Bertaud Jun 10 '13 at 16:05
If I do delete from T1 where id1=$id1 then the row $id1 is deleted from T1 and the row $id1,$id2 from T3 BUT not $id2 from T2 – Bertaud Jun 10 '13 at 16:23
In your schema, there is no direct link between T1 and T2. If the mysql-link solution doesn't work for your db, you'll want to issue two separate deletes (at your option, using a trigger on T3 for the second one, but I wouldn't recommend). Re fk_T2, you'd need a unique constraint on T3 (id2), else you cannot create the foreign key. – Denis de Bernardy Jun 11 '13 at 18:20

on delete cascade or join delete: dilemna

1 Answers1