Deleting from multiple tables with foreign constraints

user_624884 0
Viewed 26158

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?

sql-delete foreign-keys mysql sql
5 Answers

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;
user_22402740
Related