Database Reference
In-Depth Information
Primary and Foreign Keys
A
primary key
(PK) is an attribute or group of attributes that uniquely
identifies each instance in an entity. The PK must always contain data; it
cannot be null. Two examples of PKs are employee numbers and ISBNs.
These numbers identify a single employee or a single book, respectively.
When you're modeling, nearly every entity in your logical model should
have a PK, even if you have to make one up using an arbitrary number.
If the data has no natural PK, it is often necessary to add a column for
the sole purpose of acting as a PK. These kinds of PKs are called
surro-
gate keys.
Usually, this practice leans toward the physical implementation
of a database instead of the logical model, but modeling a surrogate key
will help you build relationships based on PKs. Such keys are often built
on numbers that simply increase with each new record; in SQL Server
these numbers are called
identities.
Another modeling rule is to avoid using meaningful attributes for PKs.
For example, social security numbers (SSNs) tend to be chosen as PKs for
entities such as Employee. This is a bad choice for a number of reasons.
First, SSNs are a poor choice because of privacy concerns. Many identity
thefts occur because the thief had access to the victim's SSN. Second, al-
though it is assumed that SSNs are unique, occasionally SSNs are reissued,
so they are not always guaranteed to be unique.
Third, you may be dealing with international employees who have no
SSN. It can be tempting to create a fake SSN in this case; but what if an
international employee becomes a citizen and obtains a real SSN? If this
happens, records in dependent entities could be tied to either the real SSN
or the fake SSN. This not only complicates data retrieval but also could
leave you with orphaned records.
In general, PKs should
Be highly unlikely
ever
to change
■
Be composed of attributes that will never be null
■
Use meaningless data whenever possible
■
A close cousin to the PK is the
foreign key
(FK). FKs are attributes
in a given entity that are actually based on a key, usually the PK, of another
entity. Consider, for example, the Employee entity and a new entity called
Vehicle. To know which vehicle the employee has been assigned, you must
relate these two entities. In this case an FK exists on the Vehicle entity that
points to the PK on the Employee entity. Simply put, an attribute in the
Search WWH ::
Custom Search