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 21:55	Modified:	6 May 2005 14:29
Reporter:	Matthias Urlichs
Status:	Verified
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:	4.0.1.18	OS:	Linux (Linux 2.4)
Assigned to:	Heikki Tuuri	Target Version:
Triage:	Triaged: D5 (Feature request)

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.