A while ago C. J. Date responded to a question regarding foreign keys referencing candidate, instead of primary keys. Here is further on the subject.

From: Dennis Slater

To: Gowri S. Paramasivam

CC: Editor

Date:16 May 2005

While there is no logical reason for the selection of a primary key from among candidate keys (according to Date), there is a very practical consideration for the approach I have chosen.

I use a few rules to govern our database design:

1.      The consequences of allowing modifications to a candidate key used as a reference by a foreign key are so severe that I prohibit modifications to any candidate key which is used in a foreign key relationship, except under special circumstances where relationships to more than one candidate key are required (a very rare occasion in my career).

2.      Since there are perfectly valid reasons for changing any candidate key derived from "real world" values, we create  "synthetic candidate keys" (unique number values) that have no valid reason to be modified and I use those for all relationship definitions.

3.      Since the database server products we are using do not implement any support for Date's position, and since they provide reasonable support for their own implementation of Codd's original primary key concept, we designate our "synthetic candidate key" as the primary key.

While I agree with Date's position on the Relational Model, I favor the practical design rules given above.  This is not to say we don't "bend" our own design rules under special circumstances, but it is a rare condition.  An additional benefit of these rules is the fact that our "primary keys" are almost exclusively single part primary keys which also help to simplify the design.

Date wrote several papers in the '80's about primary keys and single part primary keys in particular.  We should definitely find out if he has any more recent papers that explain his current position.

C. J. Date Responds:

1.      Perfectly reasonable.

2.      Fine. Often reasonable.

3.      Actually, the SQL Standard does support it.

My position has not changed.

Fabian Pascal Responds: See chapter 3 in PRACTICAL ISSUES IN DATABASE MANAGEMENT. It provides criteria for selecting PKs, including surrogate keys of the kind described by Slater, references the source by Date where he explains his change of heart regarding The Primacy of Primary Keys, and I explain there why that change does not make much of a difference in average practice.

Posted 7/01/05