The selection of a primary key is one of the most critical decisions you’ll make in the design of a new database. The most important constraint is that you must ensure that the selected key is unique. If it’s possible that two records (past, present, or future) may share the same value for an attribute, it’s a poor choice for a primary key. When evaluating this constraint, you should think creatively. Let’s consider a few examples that caused issues for real-world databases:
- ZIP Codes do not make good primary keys for a table of towns. If you’re making a a simple lookup table of cities, ZIP code seems to be a logical primary key. However, upon further investigation, you may realize that more than one town may share a ZIP code. For example, four cities in New Jersey (Neptune, Neptune City, Tinton Falls and Wall Township) all share the ZIP code 07753.
- Social Security Numbers do not make good primary keys for a table of people for many reasons. First, most people consider their SSN private and don’t want it used in databases in the first place. Second, some people don’t have SSNs – especially those who have never set foot in the United States! Third, SSNs may be reused after an individual’s death. Finally, an individual may have more than one SSN over a lifetime – the Social Security Administration will issue a new number in cases of fraud or identity theft.
Those are the basics on primary keys. Remember to choose carefully, as it’s difficult to change the primary key in a production table. For a more in-depth look at all the types of database keys, read Database Keys.