Bug #24668 | CASCADING FOREIGN KEYS do not work on same TABLE | ||
---|---|---|---|
Submitted: | 28 Nov 2006 19:02 | Modified: | 13 May 2010 16:04 |
Reporter: | Sebastien Caisse | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
Version: | 5.0.32-BK, 5.0.27 | OS: | Linux (Linux, Windows XPsp2) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[28 Nov 2006 19:02]
Sebastien Caisse
[29 Nov 2006 1:22]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.32-BK on Linux: mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.32-debug | +--------------+ 1 row in set (0.00 sec) mysql> create table tc (col int primary key, fk_col int); Query OK, 0 rows affected (0.02 sec) mysql> alter table tc engine=InnoDB; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into tc values (1,1); Query OK, 1 row affected (0.00 sec) mysql> alter table tc add CONSTRAINT `FK` FOREIGN KEY (`fk_col`) REFERENCES tc( col) ON UPDATE CASCADE; Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into tc values(2,2); Query OK, 1 row affected (0.00 sec) mysql> update tc set col=3 where col=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai nt fails (`test/tc`, CONSTRAINT `FK` FOREIGN KEY (`fk_col`) REFERENCES `tc` (`co l`) ON UPDATE CASCADE) But it is documented (http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html): "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." So, not a bug, formally.
[8 Jan 2007 15:45]
Sebastien Caisse
Changed to feature request, I would need this! I've already found workarounds but they are very annoying.
[8 Jan 2007 15:57]
Valeriy Kravchuk
Yes, this is a reasonable feature request.
[9 Apr 2008 2:15]
Karl Patrick
I second this request--the lack of compliance with SQL Standards coupled with the inability to hand roll this with a trigger due to similar error messages breaks makes it impossible to have a mutable column that is referenced from within the same table. If there is a workaround that implements a cascading update within a table, I'd really like to know.