Database Reference
In-Depth Information
Note
I commonly create an ID field (such as E
MPLOYEE
ID, V
ENDOR
ID, D
EPARTMENT
ID, C
ATEGORY
ID, and so on) and use it as an artificial candidate key. It always
conforms to the Elements of a Candidate Key, makes a great primary key (even-
tually), and, as you'll see in
Chapter 10
,
makes the process of establishing table
relationships much easier.
Review the candidate keys you've selected and make absolutely certain that they thor-
oughly comply with the Elements of a Candidate Key. Don't be surprised if you discover
thatoneofthemisnotacandidatekeyafterall—incorrectlyidentifyingafieldasacandid-
atekeyhappensoccasionally.Whenthisdoesoccur,justremovethe“CK”designatorfrom
thefieldnameinthetablestructure.Deletingacandidatekeywon'tposeaproblemsolong
as the table has more than one candidate key. If you discover, however, that the only can-
didatekeyyouidentifiedforthetableis
not
acandidatekey,you
must
establishanartificial
candidatekeyforthetable.Afteryou'vedefinedthenewcandidatekey,remembertomark
its name with a “CK” in the table structure.
Primary Keys
By now, you've established all the candidate keys that seem appropriate for every table.
Your next task is to establish a
primary
key for each table, which is the most important key
of all.
• A
primary key field
exclusively identifies the table throughout the database struc-
ture and helps establish relationships with other tables. (You'll learn more about
this in
Chapter 10
.)
• A
primary key value
uniquely identifies a given record within a table and exclus-
ively represents that record throughout the entire database. It also helps to guard
against duplicate records.
A primary key must conform to the exact same elements as a candidate key. This require-
ment is easy to fulfill because you select a primary key from a table's pool of available
candidate keys. The process of selecting a primary key is somewhat similar to that of a
presidential election. Every four years, several people run for the office of President of the
United States. These individuals are known as “candidates” and they have all of the quali-
fications required to become president. A national election is held, and a single individual
fromthepoolofavailablepresidentialcandidatesiselectedtoserveasthecountry'sofficial
president. Similarly, you identify each qualified candidate key in the table, run your own
