Any example of a necessary nullable foreign key?

Question

Customers
 customer_id

Orders
 order_id
 customer_id fk

If I have two tables and define a foreign key on customer_id in the Orders table, by allowing it to be null I am saying that I can have an order that does not have a customer associated with it. As such, the notion of a nullable foreign key seems at odds with the purpose of a foreign key, which is to enforce this constraint.

Is there a simple example of a situation in which a nullable foreign key would be necessary? Or an argument in favor of permitting them?

Ulyssesumayyad

Answer 1

Imagine a table that holds the TODOs of a team. If a TODO is not yet assigned to a member of the team, its user_id is NULL. If it is not NULL it is a foreign key to the users table.

Canaliculus

The alternative, which many would prefer, is to have an "unassigned" user and assign unassigned TODOs to them. You can then make the column NOT NULL. Most books on database design discuss this issue at great length. – Limn

RE: Neil Butterworth I have worked with such a system, and I can say that NULL is much more obvious (What better describer for nothing than nothing) Now if performance polling shows that NULL foreign keys are a problem then avoid them, in fact that was why the system did, but otherwise you are doing a premature optimization. (IMHO) – Outlay

eggdrop asked if there was a use case in which a null FK was "necessary", not "possible". This answer is misleading because it suggests that such a case exists on an example which does not support the conclusion: adding a UserTasks (user_id, task_id) table with a user_id as its PK and task_id as a FK to the TODO table would support the exact same use case, only without using nulls. The question remains open which approach is better, but I don't believe a use case exists where it is impossible to avoid null FKs. – Barner

+1 @Tomislav: This answer is not an example of a necessary nullable-FK. Such a thing doesn't exist. molf's answer is the correct one. – Carpeting

I never claimed that nullable FKs are necessary, I was merely trying to provide an example where it would - in my opinion - make sense. I personally find "dummy records" to be a makeshift solution in most cases where a NULL value would be perfectly sensible. But that is probably more a matter of taste than anything. – Canaliculus

One could also have an UnassignedTask table and an AssignedTask table, the latter having an FK constraint to User and the former not. – Pappy

@TomislavNakic-Alfirevic: This question just came up on my radar, and I just realised that your schema has some errors in it. user_id should not be the primary key. That would allow each user only one task. task_id should be the primary key (meaning each task can belong to at most one user), and both columns should have foreign keys to their respective columns in the User and Task tables. – Carpeting

@n3rd: What's a "dummy record"? – Carpeting

@MarceloCantos Indeed, there is an error in the schema change I proposed. Thanks for pointing it out. – Barner

@MarceloCantos: I meant the "unassigned user" (which is no "real" user) mentioned by anon in the first comment. – Canaliculus

Answer 2

No, nullable foreign keys are never necessary.

You can always normalize an optional 1-many relationship. Taking your example, you may have the following tables:

Customers: customer_id, ...
Orders: order_id, ...
OrdersCustomers: order_id, customer_id
  UNIQUE(order_id)

The two unique constraints make sure that one order can belong to only one customer, and never to the same customer twice.

Whether you should always normalize such a relationship is a different story. In some cases denormalization may lead to simpler implementations.

Metempsychosis

+1, but I disagree with the final paragraph. Denormalization only superficially simplifies things. It will always bite you in the long run. The only valid reason to do so is to accommodate the current SQL engines that punish you by performing poorly in the presence of correct normalization. (A properly designed relational engine would allow you to define the above normalized schema as well as an automated and completely transparent mapping to an efficient denormalized storage layout, thus giving you the best of both worlds.) – Carpeting

@Marcelo - "Denormalization only superficially simplifies things" - Not true. As n3rd's example demonstrates, there are absolutely instances where enforcing normalization ends up obscuring what is intended to be an optional relationship- Something that Nullable FKs make immediately clear. If NULLs are disallowed when modeling optional relationships, then custom default values must be introduced, which introduces complexity- and potential confusion- for programmers and admins that is much more likely to "bite you in the long run". – Atypical

@Yarin: The scenario n3rd cites doesn't require NULLs or special values, nor would a properly normalized solution obscure the intent. On the contrary, the intent would be explicit and unambiguous: Todo { todoId PK, … }, UserHasTodo { todoId PK FK Todo(todoId), userId FK User(userId) }. – Carpeting

@Yarin: In fact, I just noticed that two years ago I up-voted Tomislav's comment on n3rd's answer. It addresses precisely this point and proposes the same form of solution that I just did (though he got the PKs and FKs wrong). – Carpeting

@Marcelo- I'd argue that introducing a join table for this case would both introduce complexity (extra table) and obscure intent (0/1-to-many constraint is no longer enforced by DB) – Atypical

@Yarin: Adding a table does not automatically qualify as an increase in complexity. If that were true, one table holding both users and tasks would be simpler than one table for each. Also, NULLs induce all manner of subtle bugs that are difficult to even diagnose, let alone fix; they add far more complexity than an extra table. As for the constraint-enforcement comment, I don't know which case you're referring to, the one in the original question or n3rd's TODOs example. But either way, molf's answer explicitly addresses how to enforce the necessary constraints. – Carpeting

@Marcelo- Sorry you're right, I overlooked molf's example- You can indeed enforce the constraint with the normalized solution. Still not convinced it's the simpler solution, but good debate nonetheless. – Atypical

The second key is redundant. (Since each order_id is unique, so must every order_id-customer_id pair be.) If you want at most one order_id per customer_id then make customer_id unique. – Unwashed

@Unwashed I was thinking along the same lines. The second UNIQUE constraint is redundant according to the purpose -- "The two unique constraints make sure that one order can belong to only one customer, and never to the same customer twice" quoted in the answer. The first UNIQUE constraint also ensures the same. – Symphysis

It's redundant period, mathematically. PS I realize that you didn't specify "at most one order_id per customer_id"; I mentioned it for completeness/symmetry/comparison re the two associated entity types. – Unwashed

Answer 3

As such, the notion of a nullable foreign key seems at odds with the purpose of a foreign key, which is to enforce this constraint.

The purpose of a foreign key is the make explicit the concept the random integer in the Orders table actually refers to an item in the Customers table. Actually enforcing that as a constraint is incidental.

Nathanialnathaniel

Answer 4

To set a foreign key nullable or null use below sql script

ALTER TABLE Return_COMMENTS MODIFY order_ID Number NULL;

Hyperbolism

Can I insert NULL in Foreign Key in table? – Allonge

Answer 5

there is, make some tree structure, a table which related to itself. Consider this:

table_node(node_id, parent_node_id, name)

For the root, parent_node_id should be null, right?

Mccormack

You might argue that using "is_root" field would be a better solution to avoid nullable foreign key need. But by enforce to use "is_root", "parent_node_id" will be meaningless. Should we forced to fill meaningless data only because it should not be null? – Mccormack

what about simply setting the parent_node of the root node = to itself. – Pantaloons

good idea, but it would make "inserting" command more complicated if you make node_id as auto-increment. – Mccormack

yes, good point. the application would still have to perform a check on retrieval either way...(WHERE node_id = parent_node_id) vs (WHERE parent_node_id IS NULL)...so i agree using NULL is probably best. – Pantaloons

@Mccormack It's not very complicated to insert a single root node. You only do it once, and you can assume it matches the identity property, so no big deal. – Pappy

@banton Checking for NULLness always bites you in the long run. – Pappy

@EriKE: Okay, let me revise my answer. In some case, we use such a structure to store "graph-like" structure (navigation menu for example). Therefore, sometimes there are more than one root node. – Mccormack

@Mccormack You never need more than one root node. If there is "more than one root node" then you make a true root node above that. – Pappy

@ErikE: What I mean is something like this: github.com/goFrendiAsgard/No-CMS/blob/master/install/resources/… In this case, I don't think adding 1 row of "true root node" is better. – Mccormack

@ErikE: exactly as it shown. I think when you click the link, you'll be directed to line 255, aren't you? There is a cms_navigation table. Imagine it as "menu". There will be many "menu" with many "sub-menu". Every "menus" are root. So in this case we have more than one root-node. And in this case, making one "true-root" node is insufficient for me. What do you think? – Mccormack

@goFrendi My total apologies--I followed the link on my phone and missed the line# part. Using NULL is a totally fine way to do it and I have designed tables this way, myself (I didn't mean to make it sound like it really wasn't good, just that it's not required). Creating a true root node is also possible. Adding another one-to-zero-or-one relationship table to record the parent would also work. The ultimate point is that the NULLable foreign key is, as far as I can tell, never required. In your answer you say "it should be NULL" but I disagree with that. – Pappy

Answer 6

A common design scenario for setting a column to null is if you have a one to many parent child relationship, but the children do not have to be present.

When a parent record (like an account) has children that can be deleted (like help requests) this is a good usage.

You may have a column for MostRecentRequest that includes the ID of the most recent help request. When the request is removed from the system the MostRecentRequest column is set to NULL meaning there are none present.

Another example is when you want NULL to mean that a row has not been assigned a parent yet. Perhaps you have help requests that come in and a NULL on the technician field means that no tech is assigned to the request. When you delete that technician you want all his uncompleted work to go back to the pool by resetting the value to NULL.

Mlawsky

If the FK is in the parent, then it is not really a parent-child relationship, because the use of "parent-child" implies that there can be many children. When the relationship is one-to-zero-or-one, it is more of an "is a" than a "has a". In this case, make the child ID be the same as the parent ID, put the FK in the child (with a PK on the same column) and the NULLable problem entirely goes away. So no NULLable column is required, and in fact is (in my opinion) a inferior to using the supertype/subtype design pattern. – Pappy

Answer 7

We have lots of these things as our application is something that starts with some basic information for an event and over time, as the event is more fully planned, more information is added. But when the information is added we want to make sure it meets the FK constraint. FKs are for data integrity but all data is not always known at the time the initial data is inserted, so nulls are allowed.

Chordophone

It's still not required as one could have special "Unassigned" values to put in the FK column. – Pappy

Answer 8

There is another situation that I can think of:

Within one table, you have an ID field that uniquely identifies that record. That same record may (or may not) have a single parent.

Table with the following columns:

• id as an integer, auto-increment, not nullable
• parentid as an integer, nullable.

With a foreign key constraint, you can ensure that if the field is not null, then it refers to a valid parent. Furthermore, if you attempt to delete the parent record without fixing the children, then the parent cannot be deleted.

Manville