Deleting from multiple tables with foreign constra

Question

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?

Answer 1

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

Answer 2

Do the deletes in separate statments and it will work.

Answer 3

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.

Answer 4

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