MySQL Bugs: #30813: Foreign Key constraint fails

Bug #30813	Foreign Key constraint fails
Submitted:	4 Sep 2007 23:04	Modified:	5 Oct 2007 5:19
Reporter:	Yuri Koba	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.37	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Fails to update the records even though there is no foreign key violation

How to repeat:
CREATE TABLE `t1` (
  `ID` int(11) NOT NULL auto_increment,
  `I1` int(11) default NULL,
  `parentID` int(11) default NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `index_2` (`ID`,`I1`),
  KEY `FK_t1_1` (`parentID`,`I1`),
  CONSTRAINT `FK_t1_1` FOREIGN KEY (`parentID`, `I1`) REFERENCES `t1` (`ID`, `I1`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `t1` (`ID`,`I1`,`parentID`) VALUES 
 (1,11,NULL),
 (2,11,1);

Update T1 Set I1 = 12 WHERE ID = 1

I get an error number 1451.
Expected results - both records shold have I1 equal to 12;

Thank you for a problem report. I was able to repeat the behaviour you described, but looks like it is not a bug, but a documented limitation. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html:

"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. Cascading operations may not be nested more than 15 levels deep."

Try without ON UPDATE CASCADE and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".