delete rows in 3 tables with on delete cascade

Question

For a relation 0..n between two tables (t1,t2), the mpd generates an intermediate table (t3) with two fk.
Let two constraints on the t3 table, each one with "on delete cascade", if I do:

delete from t1 where id1=$id1;

I want to delete all rows of the t3 which contain the fk id1 and all rows of the t2 which contain the fk id2.
Is it possible in one query ?
Edit: here are my tables:

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 )

So you have two values $id1, $id2 and you want to DELETE from t1 where id1=$id1; DELETE from t2 where id2=$id2; and all the related rows deleted from t3 (which should be as you have cascade delete enabled)? — ypercubeᵀᴹ, Jun 10 '13 at 15:41
@ypercube: no, 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 15:58
Which is the expected behaviour. You don't usually want to delete from t2 as well because you may have another row in t3 with $id37, $id2. What should happen to that row, deleted too? — ypercubeᵀᴹ, Jun 10 '13 at 16:11
By deleting id1, I expect that all rows in t3 containing id1 are deleted and, from this list, all rows in t2 also deleted. — Bertaud, Jun 10 '13 at 16:16

ypercubeᵀᴹ · Accepted Answer · 2013-06-10T19:19:24.073

It seems you want these two statements inside a transaction:

DELETE FROM t2
WHERE EXISTS
      ( SELECT 1
        FROM t3
        WHERE t3.id2 = t2.id2
          AND t3.id1 = $id1
      ) ;

DELETE FROM t1
WHERE t1.id1 = $id1 ;

The cascading delete effects will take care of the rest. Not sure though what locking or isolation level will be best for this operation.

Note that if you specify $id1 = 1, the above will delete all rows from t3 that have id1 = 1 and some rows that do not. If for example, you have 3 rows in t3 with (1,5), (1,6), (1,7), the operation will delete them and also delete all rows that have id2=5 or id2=6 or id2=7. So rows with (4,5), (8,5), (28,5), (2,6), (6,6), (40,7) will also be deleted due to the cascading effects, when the three rows of t2 (with id2 5,6,7 are deleted).

The 2nd query deletes from t3, too, due to the cascades. – ypercubeᵀᴹ Jun 10 '13 at 21:10 — ypercubeᵀᴹ, Jun 10 '13 at 21:10

delete rows in 3 tables with on delete cascade

1 Answers1