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:
None 
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
Triage: D5 (Feature request)

[28 Nov 2006 19:02] Sebastien Caisse
Description:
When making a table with foreign keys on a key of it's own table, cascading fails with error 1451 (Cannot delete or update a parent row: a foreign key constraint fails (`schema/table`, CONSTRAINT `FK` FOREIGN KEY (`fk_col`) REFERENCES `table` (`col`) ON UPDATE CASCADE))

CREATE TABLE  `schema`.`table` (
  `col` int(10) NOT NULL default '0',
  `fk_col` int(10) NOT NULL default '0',
  PRIMARY KEY  (`col`),
  CONSTRAINT `FK` FOREIGN KEY (`fk_col`) REFERENCES `table` (`col`) ON UPDATE CASCADE
)

How to repeat:
Create above mentioned table (possibly changing names appropriatly), disable keys to create the first row, re-enable them.

Add new rows then try to change a `col` with a set `fk_col` pointing to them -> get error 1451.
[29 Nov 2006 1:22] Valerii 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] Valerii 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.