MySQL Bugs: #57768: FOREIGN KEY ON UPDATE CASCADE does not work with self-reference

Bug #57768	FOREIGN KEY ON UPDATE CASCADE does not work with self-reference
Submitted:	27 Oct 2010 11:58	Modified:	29 Oct 2010 5:00
Reporter:	Luca Esculapio	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.41	OS:	Windows (Win 7)
Assigned to:		CPU Architecture:	Any
Tags:	foreign key on update cascade self-reference

Description:
Dear all, thanks in advance for the help..

PURPOSE: Ensuring that when updating the parent value, the related values in childs get updated accordingly.

SUMMARY: Once such foreing key relationship is set up within the same table, attempts to update the key field of a parent having childs, not only do not work, but generate an error as well.

REFERENCE:
MySQL Manual, in section:
"14.2.6.4. FOREIGN KEY Constraints"
it reads:
"Note that InnoDB supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table."

HOW TO REPRODUCE IT:

- Done with:

Windows 7
XAMPP for Windows Version 1.7.3
MySQL: 5.1.41
PhpMyAdmin 3.2.4

- Create the table together with the foreign key reference pointing to itself:

CREATE TABLE `table` 
(
id INT NOT NULL, 
parent_id INT, 
PRIMARY KEY (id),
FOREIGN KEY (parent_id) 
REFERENCES `table`(id) 
ON DELETE CASCADE 
ON UPDATE CASCADE
)
ENGINE=INNODB

- Insert parent record:

INSERT INTO `table` (
`id` ,
`parent_id` 
)
VALUES (
'0', NULL 
);

- Insert child record:

INSERT INTO `test`.`table` (
`id` ,
`parent_id` 
)
VALUES (
'1', '0'
);

- Attempt to update parent's related field:

UPDATE `table` SET `id` = 2 WHERE `table`.`id` = 0 

- I'd expect both parent's and child's fields updated; instead, the above returns the following error (test done with PhpMyAdmin, within database named `test`):

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`table`, CONSTRAINT `table_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Why?
Perhaps did MySQL refuse to update the parent row because it considers that parent being a child to another parent (which does non exist, by the way)?
So I also tested adding such a parent record with id = NULL and parent_id = NULL (and to do so I created the table as not having any primary key but a simple index unique replacing it). Does not work either: same update attempt, same error message as above.

Any clue? Is there a way out?
I do have to solve it, and I think many others may as well, so thanks a lot in advance for any help.

Very Best

How to repeat:
- Create the table together with the foreign key reference pointing to itself:

CREATE TABLE `table` 
(
id INT NOT NULL, 
parent_id INT, 
PRIMARY KEY (id),
FOREIGN KEY (parent_id) 
REFERENCES `table`(id) 
ON DELETE CASCADE 
ON UPDATE CASCADE
)
ENGINE=INNODB

- Insert parent record:

INSERT INTO `table` (
`id` ,
`parent_id` 
)
VALUES (
'0', NULL 
);

- Insert child record:

INSERT INTO `test`.`table` (
`id` ,
`parent_id` 
)
VALUES (
'1', '0'
);

- Attempt to update parent's related field:

UPDATE `table` SET `id` = 2 WHERE `table`.`id` = 0

See bug: http://bugs.mysql.com/bug.php?id=47417.

Doesn't our manual (http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html) explain this:

"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. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE."

Dear Miguel and Valeriy, you're right.

Your replies have been perfectly accurate and exceptionally fast. Thanks a lot for your kindness.

I apologize for not consulting
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
carefully enough and thus being time-consuming.

As to the seriousness of the issue, well..

on one hand, to worsen my position even more, I cannot see the recursion, though: if in my example I could update the id field of a parent row and this would cause the update of the parent_id field of its child rows, it seems to me the action would stop there.

On the other hand, I was about to ask you if you think this point may be overcome, but anyway I think it can be handled in the application that's using MySQL.

So I apologize and thank you a lot again.

Very Best
Luca Esculapio

Dear Miguel and Valeriy, you're right.

Your replies have been perfectly accurate and exceptionally fast. Thanks a lot for your kindness.

I apologize for not consulting
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
carefully enough and thus being time-consuming.

As to the seriousness of the issue, well..

on one hand, to worsen my position even more, I cannot see the recursion, though: if in my example I could update the id field of a parent row and this would cause the update of the parent_id field of its child rows, it seems to me the action would stop there.

On the other hand, do you agree that being able to have fully functional parent and child records within the same table would be a valuable feature? Then I was about to ask you if you think this point may be overcome, but I came to think it may be handled in the application that's using MySQL.

Anyway, I apologize and thank you a lot again.

Very Best
Luca Esculapio