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?

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.

Answer 2

We have a component that accepts individual items of work. Each item is put into a batch of (say) 100 items, which is then sent off for processing, depending on what type of item it is and what data it carries, it can be put into one of a dozen different batches.

To this end, the individual rows are created with a null foreign key to the overall batch record. When 100 items are ready to be processed, a batch record is created, and the batch id is assigned to each of the 100 items.

This is a solution which requires a nullable forign key

Answer 3

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)
  UNIQUE(order_id, customer_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.

Answer 4

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.

Answer 5

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.