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 ...

Get Database Design and Relational Theory now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.