Life is rather like a tin of sardines—
we’re all of us looking for the key
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 the database by the same (primary) key value everywhere it’s represented.
As indicated, however, these recommendations—some might even call them prescriptions—both involve a certain degree of arbitrariness. The first in particular has always been the source of some slight embarrassment to relational advocates (myself included). One of the strongest arguments in favor of the relational model is and always has been its claim to a solid logical foundation. However, whereas this claim is clearly justified for the most part, the distinction between primary and alternate keys[170]—i.e., the idea of having to choose one member from a set of equals and make it somehow “more equal than the others”—has always seemed to rest on grounds that don’t enjoy the same degree of theoretical respectability. Certainly there doesn’t seem to be any formal justification for the distinction; it seems to smack more of dogma than logic, which is why (as I said) I find the situation embarrassing. This appendix grew out of my own increasing dissatisfaction with the seeming lack of solid justification for the orthodox relational position on these matters. (As a friend of mine once put it, these are the areas where in live presentations “you talk quickly and hope no one will notice.”)
What’s more, not only does there seem to be no formal justification for the primary vs. alternate key distinction, there doesn’t seem to be any formal way of making the choice, either. Indeed, Codd himself is on record as saying “The normal basis [for making the choice] is simplicity, but this aspect is outside the scope of the relational model” (my italics).[171] But why should it be necessary to make the choice in the first place?—i.e., why, in those cases where a genuine choice does exist, is it necessary, or desirable, to introduce such an element of arbitrariness?
Furthermore, the relational model as originally defined goes on to insist that all references via foreign keys, anywhere in the database, to (tuples in) a given relvar must always be via that relvar’s primary key specifically, never via some alternate key. Thus we see that a decision that was essentially arbitrary in the first place—the choice of which key is to be primary—can lead to arbitrary restrictions on subsequent decisions as well; that is, it might constrain the set of decisions as to what can and can’t be a legal foreign key, in ways that might not have been foreseen when that first decision (i.e., the primary key decision) was made.
I claim, then, that the idea that a distinction (hereinafter referred to as the PK:AK distinction) should be made, in the relational model as such, between primary and alternate keys introduces an unpleasant note of arbitrariness, artificiality, awkwardness, and asymmetry into what is otherwise a formally defined system (i.e., the relational model itself). I claim further that it can also serve to introduce an unpleasant degree of arbitrariness, artificiality, awkwardness, and asymmetry into the database. And I claim still further that it can also lead to an undesirable and unnecessary distinction between base and derived relvars, as I’ll show.
All of that being so, can the PK:AK distinction truly be justified? This appendix offers what I consider to be strong arguments in support of the position that the answer to this question must be no.
Before I consider consequences of the PK:AK distinction in detail, I should first examine the arguments in its defense. Since I’m on record as a defender of that distinction myself,[172] perhaps I should begin by summarizing, and with hindsight responding to, my own arguments! The principal ones were as follows:
As I expect you know, the entity integrity rule is a rule to the effect that attributes participating in the primary key of a base relvar don’t allow nulls. Now, I’ve argued for a long time that this rule should be dropped anyway, partly because it has to do with nulls (a concept I categorically reject), and partly because it draws a distinction between base and other relvars and thereby violates The Principle of Interchangeability of base relvars and views. (In case you’re unfamiliar with this latter principle, it basically just says there shouldn’t be any unnecessary distinctions between base relvars and views—views should “look and feel” to the user just like base relvars.) Thus, I now find this first argument in favor of the PK:AK distinction to be irrelevant.
The discipline of using the same symbol to identify a given entity everywhere it’s referenced allows the system to recognize the fact that those references do all refer to the same thing.
This argument is clearly valid as far as it goes, but I now feel the discipline referred to should be treated as an informal guideline rather than a hard and fast requirement. See the discussions later in this appendix—in particular, the applicants and employees example—for examples of situations in which it might be desirable not to follow such a guideline in practice. In any case, the guideline in question really has to do with design (in other words, with how to apply the relational model in some specific situation), not with the relational model as such; in particular, therefore, it has nothing to do with whether the relational model as such should insist on primary keys. I must have been a little confused when I advanced this argument originally.
“Metaqueries”—i.e., queries against the catalog—can be more difficult to formulate if entities are identified in different ways in different places. For example, consider what’s involved in formulating the metaquery “Which relvars refer to employees?” if employees are referred to sometimes by employee number and sometimes by social security number.
The idea here is basically that the discipline referred to under point 2 above can be beneficial for the user as well as the system. Again, however, it seems to me that we’re really talking about informal guidelines, not absolute requirements.
My next point wasn’t exactly an argument for the PK:AK distinction, but rather a criticism of an argument against it. This latter argument went as follows: Suppose some user is prevented, for security reasons, from seeing some primary key; then that user needs access to the data by some alternate key instead; so why make the PK:AK distinction in the first place?
I still don’t find “this latter argument” very convincing, but of course criticizing an argument against some position doesn’t prove the contrary position is correct!
My final point was an appeal to Occam’s Razor (“concepts should not be multiplied beyond necessity”). In effect, I was arguing that to treat all candidate keys as equals was to complicate the relational model’s tuple level addressing scheme unnecessarily. But it might well be argued (and now I would argue) that Occam’s Razor actually applies the other way around, and that it’s the concepts of primary key and alternate key that are unnecessary!—i.e., all we really need is candidate keys, or in other words just keys tout court.
In a nutshell, the foregoing arguments no longer seem to me very compelling; the only one that still appears to have any validity is the one summarized under points 2 and 3 above, which (as I’ve said) isn’t really an argument for making the PK:AK distinction in the relational model as such, anyway. As I’ve also said, I now feel the position supported by that particular argument should be seen more as a guideline than as an inviolable rule (again, see later for examples to justify this position).
I note in passing, though, that I did hedge my bets somewhat in my original paper ... Here’s another extract (I’ve reworded it just slightly here):
Note that if we can agree on retaining the PK:AK distinction for now, there’s always the possibility of eliminating that distinction if desirable at some future time. And note moreover that this argument doesn’t apply in the opposite direction: Once we’re committed to treating all candidate keys equally, a system that requires a distinguished primary key will forever be nonstandard.
Although I didn’t say as much at the time, this quote effectively constitutes an appeal to The Principle of Cautious Design, a principle I do still strongly believe in.[173] Indeed, it seems to me the very fact that I’m able to shift my position on the PK:AK distinction now—which is indeed what I’m doing—can be regarded as a vindication of that principle.
Before closing this section, I remark that Codd himself is also on record, in the same paper where he said there was no formal basis for choosing the primary key, as a defender of the PK:AK distinction (not surprisingly, since he originated it):
Severe problems would arise ... if any relvar whatsoever were permitted to have more than one primary key [sic] ... The consequences of permitting more than one primary key ... for a single base relvar [would be] disastrous.
(I’ve taken the liberty of replacing the term relation by the term relvar twice in this extract.) And he goes on to give an example involving employees with “several distinct responsibilities”—project management, department management, inventory management, etc.—and then he says:
Comparing for equality of identifiers ... is intended to establish that one and the same employee is involved ... This objective is dealt a severe blow if the types of identifiers used for employees can be different depending on which pair of employee-identifying [attributes] is selected for the comparison.
Well, I think you can see this argument is essentially the same as the one given under points 2 and 3 above, which (a) as I’ve already indicated, is slightly confused, and (b) as we’ll see later in this appendix, doesn’t fully stand up under close scrutiny anyway.
[170] The term alternate key was defined in Chapter 1, but I repeat the definition here for convenience: Let relvar R have two or more keys and let one be chosen as primary; then the others are alternate keys. The term isn’t used much in practice, but I do need to use it in this appendix.
[171] The quote is from Codd’s paper “Domains, Keys, and Referential Integrity in Relational Databases” (InfoDB 3, No. 1, Spring 1988).
[172] In “Why Every Relation [sic] Should Have Exactly One Primary Key,” in Relational Database: Selected Writings (Addison-Wesley, 1986); “Referential Integrity and Foreign Keys,” in Relational Database Writings 1985-1989 (Addison-Wesley, 1990); and elsewhere.
[173] The Principle of Cautious Design says: Given a design choice between options A and B, where A is upward compatible with B and the full implications of B aren’t yet known, the cautious decision is to go with A.