Add a Primary Key and Auto-Increment Fields

Here are some things you need to know about primary keys:

• A table can have only one primary key.
• The values in the primary key fields must be unique. For this reason, many people use an AutoNumber field as their primary key. AutoNumber fields automatically add a new, unique number to each record in a table. Another reason to use AutoNumber fields for your primary keys is because they are “factless”—meaning they don’t contain factual information that describe the row. Factless fields are better than factual fields like Last Name because they are less likely to change.
• A primary key field needs to always contain a value and should rarely or never change.
• Every table you create should have a primary key because it helps keep your data organized and easy to work with.
• The primary key field is automatically indexed.
• Memo, OLE, calculated, and attachment fields can’t be used as the primary key.
• The primary key is normally a single field, but two or more fields can act together as the primary key, so long as their combined values are unique. Such multi-field keys are usually difficult and confusing to work with, however.
• Primary keys are especially important in creating relationships between tables.

So, what makes a good primary key field? The most important consideration for a primary key is its uniqueness. A primary key field must always be different in every record, so you might be able to use a Customer ID, Invoice Number, or Social Security Number field as your table’s primary key.

Fortunately, in Access you won’t usually have to worry about assigning a primary key because Access does it for you. When you create a new table, Access automatically creates a primary key with the field name ID and the AutoNumber data type.

However, if you have an existing table to which you want to add a primary key field, you’ll want to pay close attention to the steps in this lesson, which explain how to add a primary key.

Add a Primary Key

1. In Design View, click the name of the field you want to use as your primary key.

   If a table doesn’t already have a unique field that is suitable as the primary key, add an AutoNumber field to your table.

2. Click the Primary Key button on the Design tab of the ribbon.

A key symbol appears next to the field, indicating that it is the table’s primary key.