ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails
cascade, mysql-error-1451, sql
Solution
I believe the answer is in the documentation (scroll down to the bottom):
Deviation from SQL standards: If `ON UPDATE CASCADE` or `ON UPDATE SET NULL` recurses to update the same table it has previously updated during the cascade, it acts like `RESTRICT`. This means that you cannot use self-referential `ON UPDATE CASCADE` or `ON UPDATE SET NULL` operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential `ON DELETE SET NULL`, on the other hand, is possible, as is a self-referential `ON DELETE CASCADE`. Cascading operations may not be nested more than 15 levels deep.
Demo: http://www.sqlfiddle.com/#!2/e29db/1
Problem
``` CREATE TABLE `categories` ( `idcategories` INT NOT NULL AUTO_INCREMENT , `idparent` INT NULL , `description` VARCHAR(45) NULL , PRIMARY KEY (`idcategories`) ); ALTER TABLE `categories` ADD CONSTRAINT `FK_idparent` FOREIGN KEY (`idparent` ) REFERENCES `ilmercatinodelpulcino`.`categories` (`idcategories` ) ON DELETE CASCADE ON UPDATE CASCADE , ADD INDEX `FK_idparent` (`idparent` ASC) ; INSERT INTO `categories` (`idcategories`, `description`) VALUES (1, 'cat1'); INSERT INTO `categories` (`idcategories`, `idparent`, `description`) VALUES (2, 1, 'cat1_child'); ``` So this table represents a category, with an ID and a self pointing parent ID. I have inserted a category cat1 and a subcategory cat1_child with parent id of cat1. Now, I want to be able to change idcategory of cat1 from 1 to 10 and because I set the foreign key on update CASCADE, I expect that idparent of cat1_child will be set to 10 as well. But when I do: ``` UPDATE `categories` SET `idcategories`=10 WHERE `idcategories`='1'; ``` I get an error: ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails (`categories`, CONSTRAINT `FK_idparent` FOREIGN KEY (`idparent`) REFERENCES `categories` (`idcategories`) ON DELETE CASCADE ON UPDATE CASCADE) SQL Statement: UPDATE `categories` SET `idcategories`=10 WHERE `idcategories`='1' The delete instead work as expected and deleting cat1, cat1_child will be deleted as well. Where is the error? Than you.