A primary key is a special kind of indexed field that uniquely identifies each record, or row, in a table. Each primary key value must be the only one of its kind in a table. When you create a new table, Access automatically creates a primary key with the field name ID and the AutoNumber data type.
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.
- 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.
- 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.
- If a table doesn’t already have a unique field that is suitable as the primary key, add an AutoNumber field to your table. The AutoNumber field will automatically add a new, unique number to each of the records in a table.
- If a field appears in more than one table and is a primary key in one table, it is called a foreign key in the other table (because it is another table’s primary key).
- To remove a primary key, just click the Primary Key button again. However, if the primary key is involved in any table relationships, you’ll first need to delete the relationships before you can remove the primary key.
You can also set up a field so that its new values are automatically incremented.
- In Design View, select the field that is the primary key.
- Click the New Values field in the Field Properties section.
- Click the New Values list arrow and select either:
- Increment: AutoNumbers will be added in sequential order, for example 1, 2, 3…
- Random: AutoNumbers will be assigned random numbers, for example 345044, 503056, 160403.
Any new values that are created in the field will follow the standard you set.