Choosing a Primary Key.doc

Choosing a Primary Key: Natural or Surrogate?

This article overviews strategies for assigning primary keys to a table within a relational database. In particular, it focuses on the issue of when to use natural

keys and when to use surrogate keys. Some people will tell you that you should always use natural keys and others will tell you that you should always use

surrogate keys. These people invariably prove to be wrong, typically they're doing little more than sharing the prejudices of their "data religion" with you. The

reality is that natural and surrogate keys each have their advantages and disadvantages, and that no strategy is perfect for all situations. In other words, you

need to know what you're doing if you want to get it right. This article discusses:

 Common key terminology 

 Strategies for assigning keys

whose value(s) uniquely identifies a row within a relational table.

 Composite key. A key that is composed of two or more attributes.

 Natural key. A key that is formed of attributes that already exist in the real world. For example, U.S. citizens are issued a Social Security Number

(SSN) that is unique to them (this isn't guaranteed to be true, but it's pretty darn close in practice). SSN could be used as a natural key, assuming

privacy laws allow it, for a Person entity (assuming the scope of your organization is limited to the U.S.).

 Surrogate key. A key with no business meaning.

 Candidate key. An entity type in a logical data model will have zero or more candidate keys, also referred to simply as unique identifiers (note: some

people don't believe in identifying candidate keys in LDMs, so there's no hard and fast rules). For example, if we only interact with American citizens

then SSN is one candidate key for the Person entity type and the combination of name and phone number (assuming the combination is unique) is

potentially a second candidate key. Both of these keys are called candidate keys because they are candidates to be chosen as the primary key, an

column as its primary key and SocialSecurityNumber as an alternate key. This indicates that the preferred way to access customer information is through the

value of a person’s customer number although your software can get at the same information if it has the person’s social security number. The

CustomerHasAddress table has a composite primary key, the combination of CustomerNumber and AddressID . A foreign key is one or more attributes in an

entity type that represents a key, either primary or secondary, in another entity type. Foreign keys are used to maintain relationships between rows. For

example, the relationships between rows in the CustomerHasAddress table and the Customer table is maintained by the CustomerNumber column within the

CustomerHasAddress table. The interesting thing about the CustomerNumber column is the fact that it is part of the primary key for CustomerHasAddress as

well as the foreign key to the Customer table. Similarly, the AddressID column is part of the primary key of CustomerHasAddress as well as a foreign key to

the Address table to maintain the relationship with rows of Address.



Figure 1. A simple PDM modeling Customer and Address.