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

Question

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 1

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

Answer 2

I faced the same issue then i disabled the foreign key check using below query and then i was able to delete the row

SET FOREIGN_KEY_CHECKS=0;

You can also enable the foreign key check using below query

SET FOREIGN_KEY_CHECKS=1;