CODEWITHSUNDEEP
mysqlforeign-keys
user1162541
user1162541

Reputation:

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?

Upvotes: 21

Views: 19161

Answers (5)

RicardoPHP
RicardoPHP

Reputation: 574

Yes, this is the right way and the correct pattern to use in those cases.

As stated, what is indicated in those structures is to leave the column as null, indicating the line would not be linked to any counterpart in the foreign table. It whould not be considered "right" in database teories, but is a very used pattern, so, its not actualy considered "wrong" by the most of database designers. I gues you could say its the kind of pattern you try not to look to when trying to find mistakes in the structure.

The pattern is considered incorrect because it is expected to use a non-null column in a primary key and in this sense the columns in the table that will receive the key must be identical to the primary column of the table, that is, they would never be null. However, in most databases physically, there are no impediments to creating a different column, which makes the null value possible.

The problem with this architecture is when the table gets too big, when, p. eg, it have more than 1000 lines (yes, the "big" would be that low!), in these situations, specially in a small infrastructure, the answer time starts to get too long and "questionable". It happens that null records can not be part of indexes and the algorithm ends up doing a full scan. Therefor this type of pattern is implemented when we know the table will always be verry, verry small! Otherwise, I recommend use the pattern creating an external table where the "null" option in your case would be a "not found" in this other table.

Upvotes: 0

andrew cooke
andrew cooke

Reputation: 46892

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.

Upvotes: 4

Joe Lencioni
Joe Lencioni

Reputation: 10531

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.

Upvotes: 34

elrado
elrado

Reputation: 5282

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

Upvotes: 6

sleske
sleske

Reputation: 83635

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.

Upvotes: 5

Related Questions