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

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.