Bug #3439 ON UPDATE CASCADE doesn't work on self-referential tables
Submitted: 11 Apr 2004 19:55 Modified: 13 May 2010 16:03
Reporter: Matthias Urlichs Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:4.0.1.18 OS:Linux (Linux 2.4)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[11 Apr 2004 19:55] Matthias Urlichs
Description:
On a table with a self-referential column, ON UPDATE CASCADE fails (error 1217).

It works perfectly when using two tables, but as this is a third-party application I can't just rewrite it.

How to repeat:
CREATE TABLE `x1` (
  `k` char(20) NOT NULL default '',
  `i` int(11) default NULL,
  `kk` char(20) default NULL,
  PRIMARY KEY  (`k`),
  KEY `im` (`kk`),
  CONSTRAINT `x1_ibfk_1` FOREIGN KEY (`kk`)
     REFERENCES `x1` (`k`) ON UPDATE CASCADE
) TYPE=InnoDB;
INSERT INTO x1 SET k='foo';
UPDATE x1 SET kk='foo';
UPDATE x1 SET k='bar';  ## error 1217

Suggested fix:
Your job. ;-)

NB: 
$ perror 1217
Error code 1217:  Unknown error 1217
[11 Apr 2004 19:56] Matthias Urlichs
OK, it's critical to me, but I suppose not to too many other people ...
[11 Apr 2004 20:26] Miguel Solorzano
Verified against 4.0.19 Windows server:

mysql> UPDATE x1 SET kk='foo';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE x1 SET k='bar';
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
[14 Sep 2004 13:04] Heikki Tuuri
Hi!

This is the documented behavior. Therefore I am converting this bug report to a feature request.

Regards,

Heikki
[14 Sep 2004 13:30] Matthias Urlichs
Well, that's your call.

I'd like to note, however, that while the current behavior may be documented as such, it can't possibly be described as 'correct'.

In fact, this problem prevents a client from switching to MySQL. I'll have to ask them how far they'd go in expressing their happiness to be able to drop $EXPENSIVE_DB in terms of money, but ...

Any rough idea about a timetable for implementing this?
[9 May 2007 10:08] Marko Mäkelä
Bug #5103 was marked as a duplicate of this.
[24 Apr 2012 2:53] Wutikrai Pornchai
I am one facing this problem. I bet most advanced developer frequently use self-reference style where it's appropriate. I myself intensively use this style.  Lack of this feature make MYSQL look less interesting.  

It does not make sense not having this basic feature while it have done normally for 2 table relation.

Is there any workaround?