MS Access —

Adding a Primary Key to a Table

Create a primary key by clicking the field you want to use as the primary field and then clicking the Primary Key button on the toolbar.

A primary key is a special kind of indexed field that uniquely identifies each record in a table. When you think about primary key fields, think unique each primary key value must be the only one of its kind in a table. A customer ID or invoice number would be two good examples of fields that could be used as a table's primary key.

Here are some things you need to know about :

  • 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.

  • Every table you create should have a primary key because it helps keep your data organized and easy to work with. In fact, if you create a table without a primary key, Access will ask if you want to add one. If you answer Yes, Access will create an at the beginning of the table and set it as the primary key.

  • The primary key field is automatically indexed.

  • Yes/No, OLE, and hyperlink 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 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.

If a table doesn't have a unique field that is suitable as the primary key (and most tables don't), you can 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.

This lesson explains how to add a primary key to a table.

  1. Make sure the tblCustomers table is open in Design view.

    First you need to click the name of the field you want to use as your primary key. If your table doesn't have a natural primary key field, you will need to add one. AutoNumber fields make great primary keys.

    For this exercise we'll use the CustomerID AutoNumber field as the table's primary key.

  2. Click the CustomerID field.

    Now you can set the CustomerID field as the table's primary key.

  3. Click the Primary Key button on the toolbar.

    Another way to set the primary key is to right-click the field you want to use as the primary key and select Primary Key from the shortcut menu.

    A key symbol () appears next to the CustomerID field, as shown in figure, indicating that it is the table's primary key. Notice that Access also sets the Indexed field to "Yes (No Duplicates)." Access automatically indexes the CustomerID field so that sorts and queries using the field will be faster and so that you cannot enter duplicate values in the field.

  4. Click the Save button on the toolbar to save the changes you've made to the table.

TO ADD A PRIMARY KEY TO A TABLE:

  1. DISPLAY THE TABLE IN DESIGN VIEW, AND CLICK THE FIELD THAT YOU WANT TO SET AS THE PRIMARY KEY. IF SUCH A FIELD DOESN'T EXIST, YOU WILL HAVE TO CREATE IT.

    AUTONUMBER FIELDS MAKE GREAT PRIMARY KEYS.

  2. CLICK THE PRIMARY KEY BUTTON ON THE TOOLBAR.

OR...

RIGHT-CLICK THE FIELD YOU WANT TO USE AS THE PRIMARY KEY AND SELECT PRIMARY KEY FROM THE SHORTCUT MENU