Menu
I have the following tables:
Cateogories
Items
There is a foreign key constraint on Items.CategoryID. There is a chance that when a new item is created that there will be no category assigned.
Is it better to set Items.CategoryID to allow nulls and deal with the nulls in my code OR better to not allow nulls, set the default CategoryID to 1, and create a dummy record in the Categories table called "Uncategorized" and then deal with that dummy category in my code?
749
Foreign keys allow key values that are all NULL , even if there are no matching PRIMARY or UNIQUE keys. By default (without any NOT NULL or CHECK clauses), the FOREIGN KEY constraint enforces the match none rule for composite foreign keys in the ANSI/ISO standard.
Yes, foreign keys are allowed to have NULL values, Foreign keys simply require that the value in that field must exist first in a different table (the parent table). However, Null by definition is not a value. Null signifies that we do not yet know what the value is.
A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. Note: A NULL value is different from a zero value or a field that contains spaces.
The logically correct way would be for the CategoryID column to be NULL when there is no Category for the item.
If you get trapped by any of the gotchas that are associated with using NULL, then that is most likely a sign that the design hasnt taken account of the fact that items cannot have a category. Fix the design. The NULL will ensure you stick to solving the correct problem.
144
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
