Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Foreign key that can also be zero

When I am using Foreign Keys in MySQL, I will get an error if the source value is 0 (because there is no target record with ID 0). Therefore, I am changing the source column to be NULL, and then it works. However, I am not sure if this is the right way this should be done. Is it the right way, or can I somehow keep the source ID set to 0 instead of NULL?

like image 323
User402841 Avatar asked Mar 18 '12 15:03

User402841


3 Answers

Foreign keys are constraints. This means that if the value of the column that has the foreign key is set to anything (and "anything" does not include NULL), that value must exist in the referenced table or MySQL will throw an error.

So, in short, you can either set the value to NULL, remove the foreign key constraint and set the value to whatever you desire, including 0, or add a record with a 0 in the referenced table. Of these options setting the value to NULL seems the cleanest.

like image 59
Joe Lencioni Avatar answered Oct 07 '22 05:10

Joe Lencioni


It is the right way. 0 is a value and null says that there is nothing in the column.

like image 29
elrado Avatar answered Oct 07 '22 04:10

elrado


Yes, this is the right way. The whole point of an FK is to enforce that a record with the referenced ID actually exists. So if you set the FK column to 0, there must be a record with ID 0.

The only way around this is to make the FK column NULLable, as you did.

At any rate, why would you want to set the FK column to 0? The canonical value for "does not exist" in SQL is NULL.

like image 5
sleske Avatar answered Oct 07 '22 04:10

sleske