Lesson 10	Understanding the primary key
Objective	Choose and define a primary key field in MS Access

Choose and Define a Primary Key in Microsoft Access

A primary key is a field (or combination of fields) whose value uniquely identifies each record in a table. In Microsoft Access, a primary key is enforced through a unique index, which ensures two essential rules:

Uniqueness: no two records can share the same primary key value.
Not Null: every record must have a key value (the key cannot be blank).

Primary keys are foundational to table design because they prevent duplicates, support relationships between tables, and improve query performance.

Why primary keys matter

Even if your table data appears unique today, enforcing uniqueness with a primary key prevents future data-entry errors and eliminates ambiguity. A well-chosen primary key supports three core goals:

Data integrity: prevents duplicate records and ensures updates/deletes target exactly one record.
Relationships: provides the “parent” identifier that other tables reference as a foreign key in one-to-many designs.
Performance: improves search and join operations because Access can use the key’s index for fast lookups.

What makes a good primary key

A good primary key should be stable, unique, and simple to store and reference. In most Access databases, the best practice is to use an AutoNumber field as a surrogate (system-generated) key and store real-world identifiers separately.

Surrogate key (recommended): an AutoNumber field used only for identification (for example, ClientID or ProjectID). This key never needs to change and avoids user input mistakes.
Natural key (use carefully): a real-world value that is inherently unique (for example, a membership number). Avoid natural keys that can change or that are sensitive (for example, SSNs) in real systems.
Composite key: two or more fields that together form a unique identifier (for example, OrderID + LineNumber). Composite keys are valid, but they can make joins and relationships more complex.

How Access uses primary keys

Primary keys are commonly used to connect related tables. For example, instead of repeating a customer name in every project record, you store the customer’s primary key (such as CustomerID) in the Projects table. This reduces duplication and makes updates safer: if a customer name changes, you update it once in the Customers table instead of in many project rows.

Defining the primary key

There are two common ways to define a primary key in Access:

Let Access create it automatically: When you create a new table (especially in Datasheet view), Access often adds an AutoNumber ID field and sets it as the primary key.
Choose and define it yourself in Design View: You create the fields first and then designate one field (or multiple fields) as the primary key.

The Design View approach is important because real databases often involve imported data, legacy tables, or scenarios where the best key is not an AutoNumber. Regardless of method, Access can only enforce a primary key if the selected field(s) contain no duplicates and no Null values.

Set a primary key in Design View

Use these steps when your table already has fields and you want to designate the primary key:

Open the table in Design View. Click the row selector (gray box) to the left of the field you want to use as the primary key.
If you need a composite primary key, hold Ctrl and select additional fields.
Click the Primary Key button (key icon) on the ribbon. A key symbol appears next to the selected field(s).
Save the table design (Ctrl + S). If Access detects duplicates or Nulls, correct the data before the key can be enforced.
To remove a primary key later, select the key field(s) and click the Primary Key button again.

The key icon indicates the field is now the primary key. From this point forward, Access blocks duplicate key values and uses the key as the default identifier for relationships and lookups.

Defining a primary key using SQL (advanced)

Access also supports defining keys using SQL. This is useful if you script database changes, apply repeatable builds, or manage table design through queries. For most learners, Design View is the preferred method, but it is helpful to know SQL exists as an option.

Example: create a table with a primary key


CREATE TABLE Employees (
    EmployeeID INTEGER PRIMARY KEY,
    FirstName TEXT,
    LastName TEXT
);

Example: add a primary key constraint to an existing table


ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);

This approach is useful for advanced users or when you want repeatable, scripted database changes.

Modern guidance and best practices

Prefer AutoNumber surrogate keys for most Access tables. If you have a business identifier (like an invoice number), enforce it with a Unique index in addition to the surrogate key.
Avoid sensitive identifiers as keys in real systems. Training examples sometimes use SSNs to demonstrate uniqueness, but real applications should avoid using sensitive personal identifiers as primary keys.
Keep keys stable. If a value might change (company name, email address, phone number), it should not be the primary key.
Don’t confuse “Required” with “Unique.” Required fields prevent Nulls; primary keys prevent Nulls and duplicates.
Validate data before assigning keys. If you add a key after data exists, ensure there are no duplicates and no blanks in the key field.

The next page reviews the key terms and concepts you have encountered in this module.

Primary Key - Exercise

Click the Exercise link to practice creating a primary key.
Primary Key - Exercise