Keys - Database Design for Mere Mortals - page 196

able you to ensure that a table has unique records and that the fields you use to es-
tablish a relationship between a pair of tables always contain matching values.
• They serve to establish table relationships. As you'll learn in Chapter 10 , you'll
use keys to establish a relationship between a pair of tables.
Alwaysmakecertainthatyoudefinetheappropriatekeysforeachtable.Doingsowillhelp
you guarantee that the table structures are sound, that redundant data within each table is
minimal, and that the relationships between tables are solid.
Establishing Keys for Each Table
Your next task is to establish keys for each table in the database. There are four main types
of keys: candidate, primary, foreign, and non-keys . A key's type determines its function
within the table.
Candidate Keys
The first type of key you establish for a table is the candidate key, which is a field or set of
fields that uniquely identifies a single instance of the table's subject. Each table must have
at least one candidate key. You'll eventually examine the table's pool of available candid-
ate keys and designate one of them as the official primary key for the table.
Beforeyoucandesignateafieldasacandidatekey,youmustmakecertainitcomplieswith
all of the Elements of a Candidate Key . These elements constitute a set of guidelines you
can use to determine whether the field is fit to serve as a candidate key. You cannot desig-
nate a field as a candidate key if it fails to conform to any of these elements.
Elements of a Candidate Key
• It cannot be a multipart field. You've seen the problems with multipart fields, so
you know that using one as an identifier is a bad idea.
• It must contain unique values. This element helps you guard against duplicating a
given record within the table. Duplicate records are just as bad as duplicate fields,
and you must avoid them at all costs.
• It cannot contain null values. As you already know, a null value represents the ab-
sence of a value. There's absolutely no way a candidate key field can identify a
given record if its value is null.
• Its value cannot cause a breach of the organization's security or privacy rules.
Values such as passwords and Social Security numbers are not suitable for use as a
candidate key.