Inconsistent delete query with left join of multiple tables

This is the result of the InnoDB storage engine which is used (as default) to create the tables and the foreign key constraints.

Check the demo without foreign keys and the result is that all rows of all tables are deleted.

You would get the same result, even with the foreign key constraints if you created the tables with the MyISAM storage engine.
Check the demo.

But for the InnoDB storage engine and the foreign key constraints, as it is mentioned in 13.2.2 DELETE Statement:

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.

so what you get may be the result of a rollback for the deletion of id = 102 of tm, because the order of the tables processed is such that it would violate a foreign key constraint.
Check the demo.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top