MySQL Bugs: #24668: CASCADING FOREIGN KEYS do not work on same TABLE

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	CPU Architecture:	Any
Triage:	D5 (Feature request)

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.

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.

Changed to feature request, I would need this!

I've already found workarounds but they are very annoying.

Yes, this is a reasonable feature request.

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.