MySQL Bugs: #3439: ON UPDATE CASCADE doesn't work on self-referential tables

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

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

OK, it's critical to me, but I suppose not to too many other people ...

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

Hi!

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

Regards,

Heikki

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?

Bug #5103 was marked as a duplicate of this.

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?