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