InnoDB, NULL foreign key...

[lopes_andre]

Hi,

 

I have one table that have the column UserID as foreign key. But this column(UserID) will not have allways a value, this column could be NULL or have an ID of the table Users(With the prumary key UserID).

 

My problem: I can't insert new row with UserID with NULL value. And I have set the column UserID to allow NULL values.

 

If I send this query does not work:

insert into `table2` values ('', '', 'email', 'contacts', 'menssage', NOW())

 

Give me this error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`database/table2`, CONSTRAINT `fk_UserId` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION) 

 

 

If I do this query, it works:

insert into `table2` values ('', '1', 'email', 'contacts', 'menssage', NOW())

 

 

What can I do to use a foreign key and allow NULL values in Foreign Keys?

 

 

Best Regards,

André.

[Mchl]

The definition of foreign key is that it cannot be null. You might want to create in parent table row with ID = 0 to refer to it rows from child table.

[lopes_andre]

Thks for you reply.

 

In Oracle is possible to have foreign keys with NULL values. And because of that, I was thinking that is MySQL that was possible too.

 

Best Regards,

André.

[Mchl]

This hardly makes sense to me, but maybe it is indeed possible. I would have to do some checking.