foreign key constraint fails with NULL flag

[Brice Targat]

Hi,

I've just experienced some weird behaviour while trying to execute an update.
Let's imagine you have two tables T1 and T2.

T1 { id_T1 }
T2 { id_T2, id_T1, name }

T1 contains 1 record : { 1 }
T2 contains 1 record : { 1 , NULL , 'me' }

The field T2.id_T1 can be null (and this is actually the case)
Well now, i have to execute something like :

UPDATE T2 SET id_T1 = NULL, name = 'not_me' WHERE id_T2 = 1

But I get "#1452 - Cannot add or update a child row: a foreign key constraint fails (`mydb`.`T2`, CONSTRAINT `fk_T2_id_T1` FOREIGN KEY (`id_T1`) REFERENCES `T1` (`id_T1`) ON DELETE NO ACTION ON UPDATE NO ACTION)"

It seems that MySQL don't want to set T2.id_T1 to NULL because of the foreign key constraint ? Again please not that the field is declared as "optional" (NULL checked), and that the record is already successfuly defined with T2.id_T1 = NULL.
Of course my real SQL request is way more complicated, it's only a reducted problem.

Any idea ?


(MySQL 5.1.54)



Edited 2 time(s). Last edit at 08/13/2012 08:10AM by Brice Targat.