Databases Reference
In-Depth Information
{PNO} the primary key of PERSON. On the other hand, I definitely would recommend the
supertype/subtype approach when “the same” primary key is involved everywhere (e.g., if we were dealing
with employees and programmers and system programmers and application programmers, etc., etc., all
identified by employee number). See Example 4 in Chapter 15.
End of aside
.
To summarize: The foregoing example strongly suggests there might be occasions on which it's indeed
desirable (a) to have several different anchor relvars for the same entity type; (b) to have a different primary key in
each of those anchor relvars; and (c) to have different foreign keys referring to those different primary keys in
different subsidiary relvars. Again, please note that I'm
not
saying the apparent need here to violate the rule “one
primary key per entity type” can't be avoided; what I'm saying is I don't see a good way to avoid it, nor do I see a
good reason for adopting a bad way. Again, therefore, I would like to suggest that the “one primary key for one
entity type” precept be treated as a strong (?) guideline, but not as an inviolable rule.
CONCLUDING REMARKS
In this appendix I've presented a number of pragmatic arguments for:
Relaxing the commonly accepted rule that every base relvar have a distinguished key called the primary key
Relaxing the (perhaps less commonly accepted) rule that every foreign key refer specifically to a primary key
instead of to an alternate key
10
Relaxing the commonly accepted rule that there be exactly one anchor relvar for each entity type
Of course, I'm well aware that if we do relax these rules, then we open the door to the possibility of bad
designs. That's why I recommend retaining recommendations such as “one primary key per entity type” as rules of
thumb, or good design guidelines. In other words, the rules in question should be violated only if there's some
really good reason for doing so. But I've tried to show in this appendix that sometimes such good reasons do exist.
10
I say
less commonly accepted
because─to its credit─the SQL standard, at least, does allow foreign keys to reference any candidate key.
