Access 2010: The Missing Manual

The Primary Key

Design view also lets you set a table’s primary key, which is a field (or a combination of fields) that’s unique for each record. Every table should have a primary key.

The purpose of a primary key is to prevent duplicate records (that is, records with exactly the same information) from slipping into your table. Databases are notoriously fussy, and they definitely don’t like this sort of sloppiness.

The challenge of preventing duplicates isn’t as easy as it seems. Access is designed to be blisteringly fast, and it can’t afford to double-check your new record against every other record in the table to see if there’s a duplicate. So instead, it relies on a primary key. As long as every record in a table has a unique, never-duplicated primary key, you can’t have two identical records. (At worst, they’ll be two almost-identical records that have the same information in all their other fields, but have different primary keys. And this is perfectly acceptable to Access.)

Choosing a primary key is trickier than it seems. Imagine you have a list of friends (and their contact information) in a table named People. You may logically assume that you can create a primary key using a combination of first and last name. Unfortunately, that just won’t do—after all, many are the address books that have two Sean Smiths.

Your best solution is to invent a new piece of information. For example, you can label every individual in your contact list with a unique ID number. ...