Using 10.0.31-MariaDB-1~jessie.
I have two tables: provider_contact and provider_contact_x_role where contact_id column in provider_contact_x_role table has a foreign key reference to id column in provider_contact table.
When I run the following query the result is success:
DELETE cr, c
FROM provider_contact_x_role AS cr
INNER JOIN provider_contact AS c
ON cr.contact_id = c.id
WHERE c.is_test_contact = 0;
When i run next query:
DELETE cr, c
FROM provider_contact_x_role AS cr
INNER JOIN provider_contact AS c
ON cr.contact_id = c.id
WHERE c.email_address <> '[email protected]';
the result is the following error:
Cannot delete or update a parent row: a foreign key constraint fails
(`ins_db`.`provider_contact_x_role`, CONSTRAINT
`FK_contact_id--provider_contact.id` FOREIGN KEY (`contact_id`)
REFERENCES `provider_contact` (`id`))
Note that the only difference between those two queries is the WHERE condition.
Questions:
1) Is there anything in SQL ensuring that when those queries are executed then the rows from the provider_contact_x_role are being deleted first ?
2) Any idea what makes a difference between those 2 queries (why 1st one succeeds and the 2nd one fails) ?
Extracted from MySQL documentation:
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.
And if you use EXPLAIN you will see that it prefers to first filter (and delete) by the main table as it need it to do the JOIN.
If you use an INNER JOIN, you could try STRAIGHT_JOIN which is the same as an INNER JOIN except that the left table is always read before the right table and put the tables in the order of deletion you want to achieve.
Alternatively to the cascade, you could disable the foreign keys in the delete statement with SET FOREIGN_KEY_CHECKS=0;.