MySQL Bugs: #59698: Insert with NULL value for recursive constraint fails

Bug #59698	Insert with NULL value for recursive constraint fails
Submitted:	24 Jan 2011 15:36	Modified:	24 Jan 2011 16:03
Reporter:	Ral Carbonneau	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Insert NULL, recursive constraint

Description:
Insert of a NULL value in a field with a recursive constraint fails.

How to repeat:
1) Create a table with a recursive relationship.
CREATE  TABLE `test` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `ParentID` INT NULL ,
  PRIMARY KEY (`ID`) );

2) Create the recursive foreign key constraint:
ALTER TABLE `test` 
  ADD CONSTRAINT `test-ParentID`
  FOREIGN KEY (`ID` )
  REFERENCES `test` (`ID` )
  ON DELETE RESTRICT
  ON UPDATE RESTRICT
, ADD INDEX `ParentID` (`ID` ASC) ;

3) Insert a record without a value specified for ParentID, thus the ParentID field will be NULL which is permitted and should not be verified by the constraint.
INSERT INTO `qlplan`.`test` (`ID`) VALUES (1);

The INSERT fails, which is incorrect since the ParentID field is NULL:
ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (`qlplan`.`test`, CONSTRAINT `test-ParentID` FOREIGN KEY (`ID`) REFERENCES `test` (`ID`))

Suggested fix:
The INSERT should be permitted since the value for the field with the constraint is NULL.

I see the following:

 FOREIGN KEY (`ID` )

Maybe you meant

 FOREIGN KEY (`ParentID` )

Please, clarify.

Yes, you are correct, that is what was meant and it works correctly.  Here is the corrected ADD CONSTRAINT and the INSERT works correctly.

ALTER TABLE `test` 
  ADD CONSTRAINT `test-ParentID`
  FOREIGN KEY (`ParentID` )
  REFERENCES `qlplan`.`test` (`ID` )
  ON DELETE RESTRICT
  ON UPDATE RESTRICT
, ADD INDEX `test-ParentID` (`ParentID` ASC) ;

INSERT INTO `test` (`ID`) VALUES (1);