From: FM

To: Editor

Date: 17 Mar 2005

 

Perhaps this is a "fool question", but I hope you could help me. The relational model has always said that a foreign key must reference a primary key ... could you tell me what are the advantages

or disadvantages of a FK that references a candidate key instead?

 

 

C. J. Date Responds: It's not a "fool question"; this is an aspect of the original model that relational advocates like myself were always a little uncomfortable with (it smacked of dogma, not logic).  And some ten years ago or so, I finally "bit the bullet," rejected the orthodox position, and went on record as saying this: 

 

While any given relvar certainly has to have at least one candidate key, there's no logical reason for it to have a primary key specifically. 

 

Partly as a consequence of the previous point, there's no logical reason for a foreign key to have to refer to a primary key specifically, instead of just to some candidate key. 

 

The whole situation is analyzed in depth in my paper The Primacy of Primary Keys: An Investigation, in RELATIONAL DATABASE WRITINGS 1991-1994.  See also Chapter 9 of my book AN INTRODUCTION TO DATABASE SYSTEMS (8th edition).

 

 

Ed. Note: While the choice of one candidate key as primary is a pragmatic, not theoretical decision, nevertheless, it is usually disadvantageous not to make such a choice, and useful to reference it (see chapter 3 in my book PRACTICAL ISSUES IN DATABASE MANAGEMENT).

 

 

Posted 5/6/05