Databases Reference
In-Depth Information
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).
2
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
.
ARGUMENTS IN DEFENSE OF THE PK:AK DISTINCTION
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,
3
perhaps I should begin by summarizing, and
with hindsight responding to, my own arguments! The principal ones were as follows:
1.
Dropping the PK:AK distinction would imply among other things that the
entity integrity
rule would have to
be extended to apply to all candidate keys (all candidate keys in base relvars, at any rate).
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.
2
The quote is from Codd's paper “Domains, Keys, and Referential Integrity in Relational Databases” (
InfoDB 3
, No. 1, Spring 1988).
3
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.
