MySQL Bugs: #9762: Can't delete records which have foreign keys to themselves

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

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

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

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

WL#148

Abandoned use of "to be fixed later" setting to status "verified"

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`))