MySQL Bugs: #59712: Cannot insert a table record with a null value for an optional foreign key

Bug #59712	Cannot insert a table record with a null value for an optional foreign key
Submitted:	25 Jan 2011 11:18	Modified:	25 Jan 2011 12:37
Reporter:	Tim Salter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.48	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	foreign key, optional, optional foreign key

Description:
Cannot insert a table record with a null value for an optional foreign key.
It should be possible to have a foreign key constraint on an optional column.

How to repeat:
Create the following table

CREATE TABLE `test` (
  `test_id` int(11) NOT NULL auto_increment,
  `parent_test_id` int(11) default NULL,
  `test_name` varchar(45) NOT NULL,

  PRIMARY KEY  (`test_id`),
  UNIQUE KEY `test_name_UNIQUE` (`test_name`),
  KEY `parent_test_test` (`test_id`),
  CONSTRAINT `parent_test_test` FOREIGN KEY (`test_id`) REFERENCES `test` (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Cannot insert a record with just test_name set'

Then execute this sql:

insert into `test` (test_name) values ('test')

I think this quote from the manual (http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html):

"Deviation from SQL standards: Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key."

explains your situation. You are inserting a row with auto_generated test_id (1), but there is no row in the table yet where there is a PK value (1) for FR value (1).

Hi Valeriy. No, I think your interpretation is wrong. If you look at the DDL and the DML, the value for the foreign key which is being inserted is null. Therefore, since the foreign key column is optional, it should not cause a foreign-key constraint failure.

Your foreign key is test_id. It is your primary key also:

CREATE TABLE `test` (
  `test_id` int(11) NOT NULL auto_increment,
...

  PRIMARY KEY  (`test_id`),
...
  KEY `parent_test_test` (`test_id`),
  CONSTRAINT `parent_test_test` FOREIGN KEY (`test_id`) REFERENCES `test` (`test_id`)
) ENGINE=InnoDB ...

test_id can NOT be NULL, so auto_increments generate a value for it, but this same generated value (of a foreign key) does NOT exist in the table PK column at the moment.

OK, well spotted. Works OK with the correct FK definition. Many thanks.