In order to set up relationships which enforce referential integrity, each table must contain a Primary Key. A primary key is a field which will serve to uniquely identify each record in the table. When deciding which field to use as the primary key for a table, you should consider three questions:

1. Will the data in the field be unique for each record?
2. Will the field always contain data?
3. Will the data in the field remain the same over time?

If you can answer ‘yes’ to all three of these questions then a field may be a good candidate for the primary key. Consider the following fields for potential candidacy for primary key in a Student table in a college database:

• Last name: fails questions 1 and 3. Multiple people can have the same last name. Last names can change when people get married.
• Social security number: fails question 2. International students would not have a social security number.
• Student ID Number: passes all three questions.

So, Student ID Number could be used as the primary key in the student table.
Within Access, primary keys are designated by a key symbol when viewing a table in design view. The image belows shows the Pets table from the Menagerie Kennel database in design view. To the left of the Field Name ‘PetID’ is a small gold key. The key indicates that the field named PetID is the primary key for the Pets table.

As the database developer you can set a primary key or Access will create one for you. If Access creates a primary key for you it will name the field ‘ID” and choose the data type of AutoNumber. The data in the field will automatically and sequentially count up from 1 each time a new record is entered into the table. No number will ever be repeated.

If you choose to set the primary key you then have control over the field name and the data type. There are different ways to set the primary key depending on how the table is set up in the database. Those will be explored in the next unit and demonstrated in video tutorials.