Foreign key that can also be zero
It is the right way. 0 is a value and null says that there is nothing in the column.
using a NULL is better than zero for two reasons. first, it's clearer that it's a "special" value (there's nothing that forces table ids to always be non-zero, although it is often true for auto-generated ids), and second it works in SQL with the foreign key constraint.
so what you are doing is common practice - many people use NULL as a marker that says "missing value", and that's what SQL's foreign key constraint expects.
another way to handle missing values is to use a third "link" table that has an entry only if there is a connection between the two classes (as you would do in a many-to-many relation). this avoids the need for a NULL, and so is preferred by some database purists, but makes everything more complex. see Nullable Foreign Key bad practice? for more discussion.
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.
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.