MySQL Bugs: #11305: On Update Cascade not working with two references to the same field

Bug #11305	On Update Cascade not working with two references to the same field
Submitted:	14 Jun 2005 7:05	Modified:	16 Jun 2005 17:00
Reporter:	Andreas Fey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.0.16	OS:	Windows (Windows 2000)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
On Update Cascade is not working when a UPDATE-Command is send. The Error is "#1216 - Cannot add or update a child row: a foreign key constraint fails "

This happens if the following conditions are true:
1.) In the child table You have two fields. I call them "A" and "B". A and B  have foreign key references to the same field on a parent table
2.)The row You wish to update contains the same values for A and B.
3.) A or B are member of the primary key.

How to repeat:

Working Example:
Parent-Table:
CREATE TABLE `persons` (
  `userid` varchar(40) NOT NULL default '',
  `prename` varchar(40) NOT NULL default '',
  `lastname` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`userid`)
) TYPE=InnoDB;

Child-Table:
CREATE TABLE `players` (
  `player` varchar(40) NOT NULL default '',
  `trainer` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`player`),
  KEY `trainer` (`trainer`)
) TYPE=InnoDB;

Foreign Keys:
ALTER TABLE `players`
  ADD CONSTRAINT `0_1825` FOREIGN KEY (`trainer`) REFERENCES `persons`
(`userid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `0_1822` FOREIGN KEY (`player`) REFERENCES `persons`
(`userid`) ON UPDATE CASCADE;

INSERT INTO `persons` ( `userid` , `prename` , `lastname` ) VALUES
('test-id', 'foo', 'bar');
INSERT INTO `players` ( `player` , `trainer` ) VALUES ('test-id',
'test-id');

UPDATE `persons` SET `userid` = 'new-id' WHERE `userid` = 'test-id' LIMIT 1 

Suggested fix:
I think, there is no workaround. A manual multiple-table-UPDATE-Commandcan fail, because Mysql-manual said:

"If you use a multiple-table UPDATE statement involving InnoDB tables for
which there are foreign key constraints, the MySQL optimizer might process
tables in an order that differs from that of their parent/child
relationship. In this case, the statement fails and rolls back. Instead,
update a single table and rely on the ON UPDATE capabilities that InnoDB
provides to cause the other tables to be modified accordingly. "

Hi!

We should document this limitation.

Workaround: SET FOREIGN_KEY_CHECKS=0 and update both tables in the application.

Regards,

Heikki

<listitem><para>
     <literal>CASCADE</literal>: Delete or update the row from the
     parent table and automatically delete or update the matching rows
     in the child table. <literal>ON DELETE CASCADE</literal> is
     available starting from MySQL 3.23.50 and <literal>ON UPDATE
     CASCADE</literal> is available starting from 4.0.8.
     You should not define several <literal>ON UPDATE CASCADE</literal>
     clauses that act on the same column in the parent table or in the
     child table.
    </para></listitem>

I cannot see why this bug is closed although it has not been fixed. The described behaviour is not comprehensible at all. Please reopen this bug and look for a real solution.

Documenting it as "known behaviour" will lead to a database that is not reliable because you will always have to look for some unexpected bahaviour.

Ralf,

in the example, the id of the parent is updated. Why would one update a surrogate key in a practical application?

Regards,

Heikki

Hello!

Sorry, I don't get the point. I do not think that having two references to the same table is that special and therefore I would expect some kind of predictable behaviour. What do you think about this? 

In my special case, persons (the references table) have a unique 13-digit number and because of ON UPDATE CASCADE I did not see a reason why this number should not be used as primary key. 

Regards,

Ralf

I have an application for precisely this: it's a transitive closure table for digraph-structured data.

And I concur with Ralf.  This is a bizarre restriction, and should by any objective standard be regarded as a bug.

And, there is no workaround for me.  Updates are simply impossible no matter which way I cut it.

Please can you re-open the bug?

OK, so obviously it has been decided that this will not be fixed... probably due to the InnoDB storage engine architecture... fair enough!

Would the next best thing than be not to allow someone to "On Update Cascade with two references to the same field", fail when they try to create these constraints at this stage with an error number that says "Cannot On Update Cascade with two or more references to the same field"

If would save alot of man hours and make the would a better place :-)