About primary keys (MDB)

The power of a relational database system such as Microsoft Access comes from its ability to quickly find and bring together information stored in separate tables using queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.), forms (form: An Access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields.), and reports (report: An Access database object that you can print, which contains information that is formatted and organized according to your specifications. Examples of reports are sales summaries, phone lists, and mailing labels.). In order to do this, each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the table. Once you designate a primary key for a table, Access will prevent any duplicate or Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) values from being entered in the primary key fields.

There are three kinds of primary keys that can be defined in Microsoft Access:

ShowAutoNumber primary keys

An AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field can be set to automatically enter a sequential number as each record is added to the table. Designating such a field as the primary key for a table is the simplest way to create a primary key. If you don't set a primary key before saving a newly created table, Microsoft Access will ask if you want it to create a primary key for you. If you answer Yes, Microsoft Access will create an AutoNumber primary key.

ShowAutoNumber primary keys in a replicated database

There are additional considerations if your table will be used with database replication (database replication: The process of creating two or more special copies (replicas) of an Access database. Replicas can be synchronized, changes made to data in one replica, or design changes made in the Design Master, are sent to other replicas.).

If more than 100 records are routinely added between synchronizing replicas, you should use Replication ID for the FieldSize property setting to prevent records from being assigned the same primary key value in each replica. Note, however, that an AutoNumber field with a Replication ID field size produces a 128-bit value that will require more disk space.

ShowSingle-field primary keys

If you have a field that contains unique values such as ID numbers or part numbers, you can designate that field as the primary key. You can specify a primary key for a field that already contains data as long as that field does not contain duplicate values or Null values.

ShowMultiple-field primary keys

In situations where you can't guarantee the uniqueness of any single field, you may be able to designate two or more fields as the primary key. The most common situation where this arises is in the table used to relate two other tables in a many-to-many relationship (many-to-many relationship: An association between two tables in which one record in either table can relate to many records in the other table. To establish one, create a third table and add the primary key fields from the other two tables to this table.). For example, an Order Details table can relate the Orders and Products tables. Its primary key consists of two fields: OrderID and ProductID. The Order Details table can list many products and many orders, but each product can only be listed once per order, so combining the OrderID and ProductID fields produces an appropriate primary key.

Each product can be listed only once per order.

Callout 1 Each product can be listed only once per order.

Another example would be an inventory database that uses a field part number of two or more fields (part and subpart).

If you are in doubt about whether you can select an appropriate combination of fields for a multiple-field primary key, you should probably add an AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field and designate it as the primary key instead. For example, combining FirstName and LastName fields to produce a primary key is not a good choice, since you may eventually encounter duplication in the combination of these two fields.

In a multiple-field primary key, field order may be important to you. The fields in a multiple-field primary key are sorted according to their order in table Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.). You can change the order of the primary key fields in the Indexes window (Indexes window: In an Access database, a window in which you can view or edit a table's indexes or create multiple-field indexes.).

Applies to:
Access 2003