Description:
While trying to introduce foreign keys to a project using MySQL-Server, it was discovered that the name of a foreign key constraint has implications on the behavior upon deleting a row which is referenced in multiple tables (no self-reference, but two distinct tables).
The actual problem was not directly discovered, but we found when another developer tried to reproduce the reported issue (to maybe find a solution/workaround), it was not possible to reproduce the error as described. The description did not contain restrictions or implications on the name of the constraints, so each developer used slightly different names.
Only after thorough inspection, making the examples identical line by line, it was discovered that the naming of foreign key constraints has a major effect.
How to repeat:
Create a fresh database and execute the following to create the tables and some sample data, trying to delete a parent row as the last command. As visible, the constraint is named `fkBA`:
CREATE TABLE `A` (
`idA` INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`idA`) USING BTREE
) ENGINE=InnoDB;
CREATE TABLE `B` (
`idB` INT NOT NULL AUTO_INCREMENT,
`idA` INT NOT NULL DEFAULT '-1',
PRIMARY KEY (`idB`) USING BTREE,
-- This is the problematic one
CONSTRAINT `fkBA` FOREIGN KEY (`idA`) REFERENCES `A` (`idA`) ON DELETE CASCADE
-- End of problem
) ENGINE=InnoDB;
CREATE TABLE `C` (
`idC` INT NOT NULL AUTO_INCREMENT,
`idA` INT NOT NULL DEFAULT '-1',
`idB` INT NOT NULL DEFAULT '-1',
PRIMARY KEY (`idC`) USING BTREE,
CONSTRAINT `fkCA` FOREIGN KEY (`idA`) REFERENCES `A` (`idA`) ON DELETE CASCADE,
CONSTRAINT `fkCB` FOREIGN KEY (`idB`) REFERENCES `B` (`idB`) ON DELETE RESTRICT
) ENGINE=InnoDB;
INSERT INTO `A` (`idA`) VALUES (1);
INSERT INTO `B` (`idB`, `idA`) VALUES (1, 1);
INSERT INTO `C` (`idA`, `idB`) VALUES (1, 1);
-- This fails with Cannot delete or update a parent row: a foreign key constraint fails
DELETE FROM `A` WHERE `A`.`idA` = 1;
However only sligthly changing the name of the constraint to `fkZA` as given here works without any flaw:
CREATE TABLE `A` (
`idA` INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`idA`) USING BTREE
) ENGINE=InnoDB;
CREATE TABLE `B` (
`idB` INT NOT NULL AUTO_INCREMENT,
`idA` INT NOT NULL DEFAULT '-1',
PRIMARY KEY (`idB`) USING BTREE,
-- Note the changed constraint name
CONSTRAINT `fkZA` FOREIGN KEY (`idA`) REFERENCES `A` (`idA`) ON DELETE CASCADE
-- end of problem
) ENGINE=InnoDB;
CREATE TABLE `C` (
`idC` INT NOT NULL AUTO_INCREMENT,
`idA` INT NOT NULL DEFAULT '-1',
`idB` INT NOT NULL DEFAULT '-1',
PRIMARY KEY (`idC`) USING BTREE,
CONSTRAINT `fkCA` FOREIGN KEY (`idA`) REFERENCES `A` (`idA`) ON DELETE CASCADE,
CONSTRAINT `fkCB` FOREIGN KEY (`idB`) REFERENCES `B` (`idB`) ON DELETE RESTRICT
) ENGINE=InnoDB;
INSERT INTO `A` (`idA`) VALUES (1);
INSERT INTO `B` (`idB`, `idA`) VALUES (1, 1);
INSERT INTO `C` (`idA`, `idB`) VALUES (1, 1);
-- This now works
DELETE FROM `A` WHERE `A`.`idA` = 1;
Playing around with the names, it seems very much like the name of the constraints are taken into account in the execution, based on their alphabetical order.
Suggested fix:
It is at least unexpected that the name of a constraint affects the execution of a delete statement and propagation in such a way that deletion is either possible or impossible.
The documentation does not state much about such a dependency / effect on the execution.
Best fix would be that the name of a constraint is irrelevant to the order of execution and deleting the parent row works independent of such a name.
