MySQL - foreign key when deleting a cascade - is there a certain execution order?

Question

I have a problem with MySQL rules - CASCADE ON DELETE for multiple tables. Obviously, the execution order of the "CASCADE ON DELETE" -rules depends on their definition order.

But is this execution order well defined or depends on it in the MySQL version?

Here is my simple example for the three tables "A", "B", "C"

create table `A` (`id` NUMERIC(10,0) NOT NULL) ENGINE=INNODB; create table `B` (`id` NUMERIC(10,0) NOT NULL, `a_id` NUMERIC(10,0) NOT NULL) ENGINE=INNODB; create table `C` (`id` NUMERIC(10,0) NOT NULL, `a_id` NUMERIC(10,0) NOT NULL, `b_id` NUMERIC(10,0) NOT NULL) ENGINE=INNODB; ALTER TABLE `A` ADD PRIMARY KEY (`id`); ALTER TABLE `B` ADD PRIMARY KEY (`id`); ALTER TABLE `C` ADD PRIMARY KEY (`id`); ALTER TABLE `C` ADD CONSTRAINT `C1` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`) ON DELETE CASCADE; ALTER TABLE `B` ADD CONSTRAINT `B1` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`) ON DELETE CASCADE; ALTER TABLE `C` ADD CONSTRAINT `C2` FOREIGN KEY (`b_id`) REFERENCES `B` (`id`); 

If I insert the following data:

 INSERT INTO A (id) VALUES (1); INSERT INTO B (id, a_id) VALUES (1,1); INSERT INTO C (id, a_id, b_id) VALUES (1,1,1); 

... and try to delete the only entry in table "A":

 delete from A where id=1; 

... The following error message appears:

 Cannot delete or update a parent row: a foreign key constraint fails (`C`, CONSTRAINT `C2` FOREIGN KEY (`b_id`) REFERENCES `B` (`id`)) 

But if I changed the definition of foreign key constraints to:

 ALTER TABLE `C` ADD CONSTRAINT `C1` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`) ON DELETE CASCADE; ALTER TABLE `C` ADD CONSTRAINT `C2` FOREIGN KEY (`b_id`) REFERENCES `B` (`id`); ALTER TABLE `B` ADD CONSTRAINT `B1` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`) ON DELETE CASCADE; 

... everything is good...

thanks for the help

Answer 1

Your ALTER TABLE table ADD CONSTRAINT foreign_key FOREIGN KEY match, so they cannot be the cause of the error.

(... text deleted)

---

I can not explain this strange behavior. May offer a workaround:

 SET FOREIGN_KEY_CHECKS = 0; DELETE FROM A WHERE ID=1; SET FOREIGN_KEY_CHECKS = 1;