Database Reference
In-Depth Information
Rules for Establishing a Primary Key
1.
Each table must have one—and only one—primary key.
Because the primary key
must
conform to each of the elements that govern it, only one primary key is ne-
cessary for a particular table.
2.
Each primary key within the database must be unique—no two tables should have
the same primary key unless one of them is a subset table.
You learned at the be-
ginning of this section that the primary key exclusively identifies a table
throughout the database structure; therefore, each table must have its own
unique
primary key in order to avoid any possible confusion or ambiguity concerning the
table's identity. A subset table is excluded from this rule because it represents a
more specific version of a particular data table's subject—both tables
must
share
the same primary key.
Later in the database design process, you'll learn how to use the primary key to help estab-
lish a relationship between a pair of tables.
Alternate Keys
Now that you've selected a candidate key to serve as the primary key for a particular table,
you'll designate the remaining candidate keys as
alternate
keys. These keys can be use-
ful to you in an RDBMS program because they provide an alternative means of uniquely
identifying a particular record within the table. If you choose to use an alternate key in
this manner, mark its name with “AK” or “CAK” (composite alternate key) in the table
structure; otherwise, remove its designation as an alternate key and simply return it to the
status of a normal field. You won't be concerned with alternate keys for the remainder of
the database design process, but you will work with them once again as you implement
the database in an RDBMS program. (Implementing and using alternate keys in RDBMS
programs is beyond the scope of this work—our only objective here is to designate them
as appropriate. This is in line with the focus of the topic, which is the logical design of a
database.)
Figure8.8
showsthefinalstructurefortheEMPLOYEEStablewiththeproperdesignation
for both the primary key and the alternate keys.
