Choosing a Primary Key

Databases depend upon keys to store, sort and compare records. If you’ve been around databases for a while, you’ve probably heard about many different types of keys – primary keys, candidate keys, and foreign keys. When you create a new database table, you’re asked to select one primary key that will uniquely identify records stored in that table.

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.

So, what makes a good primary key? If you’re unable to find an obvious answer, turn to your database system for support. A best practice in database design is to use an internally generated primary key. The database management system can normally generate a unique identifier that has no meaning outside of the database system. For example, you might use the Microsoft Access AutoNumber datatype to create a field called RecordID. The AutoNumber datatype automatically increments the field each time you create a new record. While the number itself is meaningless, it provides a great way to reference an individual record in queries.

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.