In what order are ON DELETE CASCADE constraints processed?

Question

Here is an example of what I've got going on: CREATE TABLE Parent (id BIGINT NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB; CREATE TABLE Child (id BIGINT NOT NULL, parentid BIGINT NOT NULL, PRIMARY KEY (id), KEY (parentid), CONSTRAINT fk_parent FOREIGN KEY (parentid) REFERENCES Parent (id) ON DELETE CASCADE) ENGINE=InnoDB; CREATE TABLE Uncle (id BIGINT NOT NULL, parentid BIGINT NOT NULL, childid BIGINT NOT NULL, PRIMARY KEY (id), KEY (parentid), KEY (childid), CONSTRAINT fk_parent_u FOREIGN KEY (parentid) REFERENCES Parent (id) ON DELETE CASCADE, CONSTRAINT fk_child FOREIGN KEY (childid) REFERENCES Child (id)) ENGINE=InnoDB; Notice there is no ON DELETE CASCADE for the Uncle-Child relationship; i.e. deleting a Child does not delete its Uncle(s) and vice-versa. When I have a Parent and an Uncle with the same Child, and I delete the Parent, it seems like InnoDB should be able to just "figure it out" and let the cascade ripple through the whole family (i.e. deleting the Parent deletes the Uncle and the Child as well). However, instead, I get the following: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`cascade_test/uncle`, CONSTRAINT `fk_child` FOREIGN KEY (`childid`) REFERENCES `child` (`id`)) InnoDB is trying to cascade-delete the Child before the Uncle(s) that refer to it. Am I missing something? Is this supposed to fail for some reason I don't understand? Or is there some trick to making it work (or is it a bug in MySQL)? JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

In the simpler case, what happens if a record is deleted from Child and it has a referencing Uncle? That's unspecified, so the constraints fail for that anyway. If deleting a Child does not delete its Uncles, then what happens instead? Uncle.childid cannot be null. What you want is one of these three things: Uncle.childid can be null, and you want ON DELETE SET NULL for childid. Uncle.childid cannot be null, and you want ON DELETE CASCADE for childid. Childid does not belong on Uncle, and you want a ChildsUncle relation with ON DELETE CASCADE foreign key constraints to both Child and Uncle. Uncleid would be a candidate key for that relation (i.e. it should be unique).