Setting the Primary Key


Every Access table should have a primary key defined. The primary key is the field that contains data unique to that record. Primary keys are often IDs - product IDs, employee IDs, or record IDs. Remember that Access is a relational database - the primary key is the basis for relationships between different tables.



To set the primary key in a table:

  1. In Design view, click the field that is going to be the primary key.
  2. Click the Primary Key toolbar button.
  3. Click the Primary Key toolbar button again to remove the primary key designation.


Once you establish a field as the primary key, Access automatically sets the duplicates property to no to ensure that each record has a unique primary key. You'll learn more about field properties in the Modifying Field Properties in Design View task.

There are three types of primary keys:

  1. AutoNumber: If your data does not already contain a field that is unique for each record, you can add a new field that uses the AutoNumber data type. Using an AutoNumber field ensures that each record has a unique numerical ID.
  2. Single field: If your database contains a field that you know is unique for each record (such as a previously established product ID, employee ID or part number), you can set this field as the primary key.
  3. Multiple-field: Some tables have records that do not have a single unique field. In this case, you need to create a multiple-field primary key. Select multiple fields by clicking the row selectors while holding down the Ctrl key; then click the Primary Key toolbar button.