Bug #9762 Can't delete records which have foreign keys to themselves
Submitted: 8 Apr 2005 14:47 Modified: 22 Jan 2014 14:57
Reporter: Matthew Sgarlata Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.1 OS:Windows (Win XP Pro)
Assigned to: CPU Architecture:Any

[8 Apr 2005 14:47] Matthew Sgarlata
Description:
If a table has a foreign key to itself, then rows which point to themselves cannot be deleted.  For example, say we have a table tree with a primary key of treeid and a column roottreeid which points to the root of the tree.  So roottreeid is a foreign key that references treeid.  With this data structure, we can't delete the root of the tree.  For example, the root node of a tree might have treeid=1 and thus would have roottreeid=1.  The following SQL command doesn't work:

delete from tree where treeid = 1

The error message is

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

How to repeat:
Try the scenario I described above
[8 Apr 2005 14:54] Heikki Tuuri
Hi!

A workaround is to do:

SET FOREIGN_KEY_CHECKS=0;

delete...

SET FOREIGN KEY CHECKS=1;

'Deferred' constraint checking is in the TODO.

--Heikki
[8 Apr 2005 15:02] Matthew Sgarlata
FYI I figured out another workaround that will allow code to be run without any special checks for MySQL:

update tree set treerootid = null where treeid = 1
delete from tree where treeid = 1
[30 Sep 2008 10:39] Konstantin Osipov
WL#148
[22 Jan 2014 14:57] Ståle Deraas
Abandoned use of "to be fixed later" setting to status "verified"
[13 Nov 2017 0:21] Federico Razzoli
Still happens in 8.0.3.

mysql> CREATE TABLE t (
    ->   id INT PRIMARY KEY,
    ->   parent INT NOT NULL,
    ->   FOREIGN KEY fk_test (parent)
    ->     REFERENCES t (id)
    ->     ON DELETE RESTRICT
    -> ) ENGINE InnoDB;
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO t (id, parent) VALUES (1, 1);
Query OK, 1 row affected (0.05 sec)

mysql> DELETE FROM t WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t`, CONSTRAINT `fk_test` FOREIGN KEY (`parent`) REFERENCES `t` (`id`))