Constructing single query for cascading deletes

Question

I have one question about DELETE query in SQL. I have 3 child tables say B,C,D and one parent Table A. Priamry key of A is shared in all its child tables. If I have to delete a record from the child tables and then from the parent in a single SQL then is it possible? If yes can you guide me for an SQL query contruct? If not is it at least possible to delete record from the child tables in a single query?

It's possible in one query if foreign keys are created with cascade option otherwise it depends on DMBS. — Nahuel Fouilleul, Nov 16 '12 at 08:58

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

If you don't have a foreign keys with ON DELETE CASCADE then you should do it with transaction:

BEGIN;
  DELETE FROM B WHERE ...
  DELETE FROM C WHERE ...
  DELETE FROM D WHERE ...
COMMIT;

ADDED: If you use mysql read DELETE doc and discussion here stackoverflow delete-from-two-tables-in-one-query

edited May 23 '17 at 10:24

Community

1
1

answered Nov 16 '12 at 09:12

sufleR

2,865
17
31

thanks for your answers. just wanted to know if I can have a construct of the following format delete from B,C,D where ... – Sidd Nov 16 '12 at 09:47

score 0 · Answer 2 · answered Nov 25 '12 at 04:40

Nahue's comment and sufleR's answer are right. If you have an ON DELETE CASCADE specified on the foreign key relationships, you will be able to delete from all these tables using a single DELETE statement.

If you do not have this constraint enforced, you can enforce it with ALTER TABLE statement. I would take your schema as

A (a_pk, ...) -- PRIMARY KEY specified on a_pk
B (b_pk, a_fk, ...)
C (c_pk, a_fk, ...)
D (d_pk, a_fk, ...)

Then,

ALTER TABLE b
ADD CONSTRAINT b_a_fk
a_fk REFERENCES a(a_pk) ON DELETE CASCADE;

ALTER TABLE c
ADD CONSTRAINT c_a_fk
a_fk REFERENCES a(a_pk) ON DELETE CASCADE;

ALTER TABLE d
ADD CONSTRAINT d_a_fk
a_fk REFERENCES a(a_pk) ON DELETE CASCADE;

All the tables will enforce a FOREIGN KEY with ON DELETE CASCADE referential constraint on all these tables.

Now, if you issue

DELETE FROM a WHERE a_pk = <some value>

then this would delete records from B, C and D where each of their a_fk = <some value>.

Note that I have used Oracle syntax in my queries. You will have to convert these to suit your DBMS syntax but I don't think that is necessary as this is the standard syntax.

Constructing single query for cascading deletes

2 Answers2