Simone Conti Simone Conti - 3 months ago 23
SQL Question

ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails

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.

Answer

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