25

I am trying to delete from multiple tables. Here's what my tables look like

    A_has_B ---- B ---- C_has_B
(many to many)        (many to many)

I am trying to delete all rows from A_has_B, B and C_has_B given the ID of a record in B. I am using MySQL with the innodb storage engine with foreign keys defined for A_has_B and C_has_B referencing the IDs in B.

I am trying to perform my delete like so:

DELETE A_has_B.*, C_has_B.*, B.*

FROM
A

join
B
on (B.B_id = A.B_id)

join
C
on (C.B_id = B.B_id)

where B.B_id IN(1,2, 4);

The problem is that when I execute the query, mysql complains:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`db`.`C`, CONSTRAINT `fk_C` FOREIGN KEY (`B_id`) REFERENCES `B` (`B_id`) ON DELETE NO ACTION ON UPDATE NO)

How can I go about fixing this?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
F21
  • 32,163
  • 26
  • 99
  • 170

5 Answers5

36

The simplest way would be to delete from each table individually:

-- Remove all connections from A which reference
-- the B-rows you want to remove
DELETE FROM A_has_B
WHERE B_id IN (1,2,4);

-- Remove all connections from C which reference
-- the B-rows you want to remove
DELETE FROM C_has_B
WHERE B_id IN (1,2,4);

-- Finally remove the B-rows
DELETE FROM B
WHERE B_id IN (1,2,4);

MySQL also allows you to delete from multiple tables in one statement. But there is no way to control the order of the deletions. From the manual:

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

Markus Jarderot
  • 86,735
  • 21
  • 136
  • 138
  • Thanks for your answer:) Is it definitely impossible to delete with one statement? I would have thought that perhaps I can force MySQL to delete from the intersectoin tables first before deleting from B. – F21 Jul 30 '11 at 08:39
  • 2
    In SQL, it's `transactional` nature is intended to allow multiple statements to complete *as-if* they're a single statement. Rather than implementing both an impossibly complex language and an impossibly complex engine/optimiser. So, if it's important that everything is deleted "at once", look at transactions :) – MatBailie Jul 30 '11 at 08:45
12

Actually, in MySQL, you can turn off checks for foreign key constraints

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
--your SQL statements
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

The statement on the first line forces MySQL server to turn off foreign key checks and the last line turns them back on (very important). Two things to keep in mind:

  • It's fairly dangerous to turn off checks for constraints and isn't something that should be done in, say, a production DB... the safest way is to use separate statements.
  • Always turn the constraint checks back on
Andrey
  • 8,882
  • 10
  • 58
  • 82
  • I got "Error Code: 1231. Variable 'foreign_key_checks' can't be set to the value of 'NULL'"; Although tables dropped successfully. – tsh Oct 16 '20 at 02:58
  • I've used this method but I hate it. I'd want to have MySQL verify FKs after turning it back on to make sure I didn't break something. Would much prefer if MySQL mult-table deletes were smarter about FKs! – Josh M. May 19 '21 at 17:06
4

You can specify the "delete cascade" on the foreign key. When you delete the parent row, the mysql engine will delete the records in the related child tables

Emanuele Scozzafava
  • 41
  • 1
0

UPDATE 2021-01-09: The "IGNORE" modifier indeed happened to work for me for a specific situation. But the next time I tried it, with a different set of tables, it was of no help and I had to resort to temporarily disabling foreign_key_checks. It does not hurt to give "IGNORE" a try, you might be lucky, but unfortunately you cannot count on it.

With MySQL 8.x, adding the modifier "IGNORE" solved the problem of foreign-key constraints for me. Here is a somewhat involved real-world example. It finds all records that are related to Partner 100 through some foreign-key relationship and deletes them, including the Partner itself:

DELETE IGNORE bit, bd, id, i, br, pi, a, pe, pa
  FROM Partner pa
  LEFT JOIN Person pe ON pe.partner_id = pa.idPartner
  LEFT JOIN Account a ON a.partner_id = pa.idPartner
  LEFT JOIN PaymentInformation pi ON pi.partner_id = pa.idPartner
  LEFT JOIN BillingRun br ON br.account_id = a.idAccount
  LEFT JOIN Invoice i ON i.account_id = a.idAccount
  LEFT JOIN InvoiceDocument id ON id.invoice_id = i.idInvoice
  LEFT JOIN BillingDoc bd ON bd.billingRun_id = br.idBillingRun
  LEFT JOIN BIT_Billed bit ON bit.billingRun_id = br.idBillingRun
  WHERE pa.idPartner = 100;

If that still fails, you can turn foreign_key_checks off temporarily, as mentioned in other answers. The least intrusive way of doing so that I can think of would be like this:

SET SESSION foreign_key_checks=OFF;
DELETE a, b, c ...
SET SESSION foreign_key_checks=ON;
StaticNoiseLog
  • 1,370
  • 17
  • 27
-1

Do the deletes in separate statments and it will work.

Tom Squires
  • 8,848
  • 12
  • 46
  • 72