Database Design and Relational Theory

Appendix A. Primary Keys Are Nice but Not Essential

Life is rather like a tin of sardines—

we’re all of us looking for the key

—Alan Bennett: Beyond the Fringe

Note: Portions of this appendix originally appeared in somewhat different form in my book Relational Database Writings 1991-1994 (Addison-Wesley, 1995).

Recall this text from Chapter 1:

I said it’s usual to choose a primary key. Indeed it is usual—but it’s not 100 percent necessary. If there’s just one candidate key, then there’s no choice and no problem; but if there are two or more, then having to choose one and make it primary smacks a little bit of arbitrariness, at least to me. (Certainly there are situations where there don’t seem to be any good reasons for making such a choice. There might even be good reasons for not doing so. Appendix A [i.e., the present appendix] elaborates on such matters.)

Now, the position articulated in this extract clearly flies in the face of conventional wisdom, somewhat; it might even be said to contravene certain widely accepted precepts of the relational model, or of relational theory in general. To be specific:

• Out of the (necessarily nonempty) set of keys possessed by a given relvar, the relational model as originally defined ascribes a primal role to an arbitrarily chosen member of that set called the primary key.

• Relational design methodologies (though not the relational model per se) tend to suggest, again a trifle arbitrarily, that a given “entity” should be identified and referenced throughout ...