Deleting from multiple tables with foreign constraints?

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.

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.

I would have thought that perhaps I can force MySQL to delete from the intersectoin tables first before deleting from B. – phpdev Jul 30 at 8: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 :) – Dems Jul 30 at 8:45.

Do the deletes in separate statments and it will work.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.