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

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.

Answer 2

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?

Answer 3

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.

Answer 4

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.

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.

Answer 6

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.