Can Foreign Key be null? [duplicate]

Question

In our database project we have a table Sale that has an primary key and two exclusive foreign keys: Vehicle_ID and Piece_ID . For example if we sell a vehicle we need Vehicle_ID as a foreign key but not Piece_ID. Can we put NULL to Piece_ID, could a foreign key be null? Or is there a way to do this job?

Thanks.

Answer 1

The column (or columns) of a primary key must be NOT NULL. A record cannot be uniquely identified by a NULL. So the ID columns on the referenced end of the foreign key must be defined as NOT NULL.

However, it is a legitimate design decision for a foreign key relationship to be optional, and the way to represent that is by making the referencing end of the key optional, i.e. allowing NULLs.

In data modelling terms what you have described is an (exclusive) arc: "a table ... with two or more foreign keys where one and only one of them can be non-null." In logical modelling arcs are perfectly acceptable, but there is a strong body of opinion in favour of implementing them as separate tables. In your scenario that would be a generic Sale table plus two sub-type tables, VehicleSale and PieceSale.

The advantages of the separate table implementation are:

• easier to enforce the foreign key constraints;
• easier to add additional columns relating to (say) vehicle sales which don't apply to piece sales;
• easier to extend the model with additional sub-types;
• clearer data model, which can simplify application development.

However, the advantages aren't all one-way. While it is pretty easy to ensure that a Sale applies either to a VehicleSale or a PieceSale but not both, enforcing a rule that a Sale must have a child record actually gets pretty gnarly.

So, the prevailing advice is that an exclusive arc is mistaken, and it is generally good advice. But it's not as clear as some make out.