Chapter 2: Creating Access Tables
77
Good primary keys
l Uniquely identify each record.l (^) Cannot be null.
l Must exist when the record is created.
l (^) Must remain stable — you should never change a primary key value once it’s established.
l Should be simple and contain as few attributes as possible.
In addition to uniquely identifying rows in a table, primary keys provide other benefits:
l A primary key is always an index.
l (^) An index maintains a presorted order of one or more fields that greatly speeds up queries,
searches, and sort requests.
l (^) When you add new records to your table, Access checks for duplicate data and doesn’t
allow any duplicates for the primary key field.
l (^) By default, Access displays a table’s data in the order of its primary key.
By designating a field such as CustomerID as the primary key, data is displayed in a meaningful
order. In our example, because the CustomerID field is an AutoNumber, its value is assigned
automatically by Access in the order that a record is put into the system.
Although all the tables in the Collectible Mini Cars application use AutoNumber fields as their
primary keys, you should be aware of the reasons why AutoNumber fields make such excellent
primary keys.
The ideal primary key is, then, a single field that is immutable and guaranteed to be unique within
the table. For these reasons, the Collectible Mini Cars database uses the AutoNumber field exclu-
sively as the primary key for all tables.
Creating the primary key
The primary key can be created in any of three ways. With a table open in Design view:
l Select the field to be used as the primary key and click the Primary Key button (the key
icon) in the Tools group in the ribbon’s Design tab.
l Right-click on the field to display the shortcut menu and select Primary Key.
l (^) Save the table without creating a primary key, and allow Access to automatically create an
AutoNumber field.
After you designate the primary key, a key icon appears in the gray selector area to the left of the
field’s name to indicate that the primary key has been created.