13

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.

Adi Inbar
  • 12,097
  • 13
  • 56
  • 69
notfound90
  • 155
  • 1
  • 1
  • 8
  • A foreign key can be null, of course. Your table structure however looks somewhat wonky. What are you trying to achieve? – Lion Dec 24 '12 at 14:56
  • Consider reviewing your design. This design gives you little flexibility. A good design will not give you this situation. Look on the net for 'database normalisation' – Steffe Dec 24 '12 at 15:03
  • @Lion I am trying to sale a vehicle or piece at one tim ,not both of them, in our autogallery database project.However, I am confused because the foreign keys are the primary key of the another table.Should I seperate the sale as VehicleSale and PieceSale or something different. – notfound90 Dec 24 '12 at 16:00

4 Answers4

14

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.

APC
  • 144,005
  • 19
  • 170
  • 281
  • There is one more problem with the two table solution: what if each sale needs to be uniquely identified regardless of the "kind" of sale (i.e. you can't have both "vehicle" and "piece" sale with the same ID)? Just for the sake of enforcing the integrity of the key, there would need to be at least a common (if not only) table on which such key can be defined. – Branko Dimitrijevic Dec 24 '12 at 17:52
  • 2
    @BrankoDimitrijevic - I thought I had been clear that the alternative implementation was a generic table with sub-type tables i.e. a three table solution. The generic table will enforce uniqueness. – APC Dec 24 '12 at 19:30
  • @APC Can I use trigger to control whether both of the Piece_ID and Vehicle_ID are NULL or not.Can I put a control in trigger when inserting some sale.Because, in one time, there can be one sale of vehicle, one sale of piece or both of them.That is at least one of them must be sold.Both of them cannot be NULL. – notfound90 Dec 24 '12 at 21:26
  • @APC Sorry, I somehow missed that. – Branko Dimitrijevic Dec 24 '12 at 22:11
6

Answer:

Yes, you can do that - make the FKs themselves NULL-able, but add a CHECK to ensure exactly one of them contains a non-NULL value.

Elaboration:

A FK can be NULL-able, which models a 1..0:N relationship. In other words, a "child" row can (but is not required to) have a "parent" row.

A NOT NULL foreign key models a 1:N relationship. In other words, every child must have a parent.

When a FK is composite1, and at least one of its fields is NULL-able, a mix of NULL and non-NULL values is handled in a special way:

  • If the FK is MATCH FULL, either all values must be NULL or all values must be non-NULL and match some parent row.
  • If the FK is MATCH PARTIAL, only those values that are non-NULL must match some parent row (NULLs are ignored).
  • If the FK is MATCH SIMPLE, either all values are non-NULL and must match some parent row, or there is at least one NULL value (in which case the non-NULLs are not required to match).

Most DBMSes default to MATCH SIMPLE (with the notable exception of MS Access) and most don't support anything but the default.

1 Which you don't have here - just mentioning it for completeness.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
1

Depending on what you mean by "exclusive foreign keys", you might be thinking of vehicles and pieces as two subclasses of some larger superclass, call it saleable items.

If you use a design pattern called "class table inheritance", you will have three tables, one for the superclass, and one for each subclass table. If in addition, you use a design called "shared primary key", you can use the same primary key for all three tables.

This would enable your Sale table to have a single foreign key, Saleable_Item_Id, that references the Saleable_Item table and also either the Vehicle or the Piece table, depending on the case. This could work out better for you than the existing design.

google "class table inheritance" and "shared primary key" for more details.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • To be fair, I introduced the word 'exclusive" into the question's text, because I thought it would clarify what the OP was asking. Specifically, that a Sale could apply to a Vehicle or a Piece but not to both. – APC Dec 24 '12 at 17:13
0

Oracle shouldn't complain if you have a null foreign key.

Were you running across some errors?

Andrew Walters
  • 4,763
  • 6
  • 35
  • 49
  • Oracle didn't give me any error,but I couldn't understand the logic:/Because the foreign key is the primary key of the another table. – notfound90 Dec 24 '12 at 15:57
  • 3
    A null is the absence of a value. In your case, a value would be a foreign key that references a primary key elsewhere. A null would indicate the absence of a value, just as it always does. At the next level of abstraction, foreign keys represent relationships. Nullable foreign keys represent optional relationships. No problem. – Walter Mitty Dec 24 '12 at 16:12