Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 36 ยท Tables and Relationships

Primary Keys and Indexes in Access

Choose primary keys in Access: AutoNumber, natural and composite keys. Add single and multiple-field indexes, enforce uniqueness and know what indexes cost.

Two ideas keep a database fast and consistent: keys and indexes. A primary key gives every record an identity, so you can point to exactly one row. An index is a sorted lookup structure that lets Access find rows without scanning the whole table. Primary keys are required for relationships; indexes are optional but make large tables usable.

This lesson explains how to choose and set a primary key, when a composite key is the right answer, how to add indexes and how to see what Access built for you.

What makes a good primary key

A primary key has three jobs: it must be unique, never empty and stable (it should not change). Ask of any candidate: could two records ever share this value? Could it be blank? Might it change next year?

Candidate Problem
Customer name Two people can share a name, and names change
Email address Unique today, but people change addresses
Phone number Shared by families, changes
Social security or national ID Sensitive data, privacy risk, errors in entry
AutoNumber Meaningless but unique, never changes, never empty

The usual answer is a surrogate key: an AutoNumber with no meaning outside the database. It is small (4 bytes), fast to compare and joins cleanly. Access creates ID this way automatically in a new table.

A natural key uses a real-world value, such as an ISBN for books or a country code. It is acceptable when the value is guaranteed unique, assigned by an authority and never reused, but even then many designers still add an AutoNumber and put a unique index on the natural value.

A good primary key is

Unique No two rows match
Never empty Always has a value
Stable Does not change over time
Small Fast to compare and join

AutoNumber ticks all four boxes.

1 / 2

Setting a primary key

  1. Open the table in Design view.
  2. Click the grey row selector to the left of the field name.
  3. On the Table Design tab, click Primary Key. A key icon appears.

Access creates a unique index called PrimaryKey automatically. Clicking the button again removes the key. If the table already contains duplicate values in the field, Access refuses to make it the key until you fix them.

If you save a new table without a key, Access asks whether it should create one. Answering Yes adds an AutoNumber field named ID. Say yes unless you plan to pick the key yourself right away.

You can only have one primary key per table, but it may consist of several fields.

Composite keys

A composite (or multi-field) primary key combines two or more fields whose combination is unique. The classic example is a junction table for a many-to-many relationship. A student can enrol in many courses and a course can have many students, so an Enrolments table holds StudentID and CourseID. A student should enrol in a given course only once, so the pair is unique.

To make one, hold Ctrl and click the row selectors of both fields, then click Primary Key. Both fields show the key icon.

Composite keys make sense for junction tables. For other tables, many developers still prefer a single AutoNumber key plus a unique index on the pair, because single-field keys are simpler to reference from other tables.

SQL A composite primary key

Press Run (or Ctrl + Enter) to see the result here.

Now try enrolling Amira in Databases a second time. The key blocks the duplicate pair:

SQL Duplicate pair rejected

Press Run (or Ctrl + Enter) to see the result here.

In Access you would see: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship."

What an index is

An index is a separate, sorted structure that stores field values together with pointers to the rows that contain them. When you search for LastName = "Okafor", Access can jump through the index like a phone book instead of reading every row. Sorting and joining on indexed fields is faster too.

You do not need to index everything. Each index uses space and slows inserts, updates and deletes slightly, because Access must keep the index current. Index fields you often search, sort or join on. Foreign key fields (the fields that point to another table) are almost always worth indexing; Access indexes them automatically when you create a relationship through the Relationships window.

Creating a single-field index

In Design view, click a field and set the Indexed property:

  • No (default): no index.
  • Yes (Duplicates OK): indexed, repeated values allowed (a LastName column).
  • Yes (No Duplicates): indexed and values must be unique (an Email or ProductCode column).

Long Text, Hyperlink, OLE Object and Attachment fields cannot be indexed in a normal way.

Multiple-field indexes

Sometimes you search on two fields together, such as last name then first name. Choose Indexes on the Table Design tab. In the dialog:

  1. In the Index Name column, type a name, such as FullName.
  2. In Field Name on the same row, choose LastName.
  3. On the next row, leave Index Name blank and choose FirstName.
  4. Set Unique in the lower pane if the pair must be unique.

The first field matters most. An index on (LastName, FirstName) helps a search on last name alone or on both, but not on first name alone.

SQL Indexes and the query plan

Press Run (or Ctrl + Enter) to see the result here.

The plan changes from SCAN to SEARCH ... USING INDEX for the first two queries. Access does not show query plans in the same way (it has a hidden ShowPlan registry option), but the idea is identical.

Unique indexes versus the primary key

A table has one primary key but may have many unique indexes. Use them for other fields that must not repeat: an email address, a product SKU, a username. The difference is mainly semantic: the primary key is what other tables reference. In Access SQL:

SQL
CREATE UNIQUE INDEX idxEmail ON Customers (Email);

If you try to add a duplicate, Access displays the duplicate values error.

Ignore Nulls

On a unique index, the Ignore Nulls setting (in the Indexes dialog) decides whether records with no value in that field are left out of the index. Turn it on for optional unique fields, such as an optional national ID, so that many records can have no value without breaking the uniqueness rule. Standard SQL allows multiple NULLs in a unique index; Access normally treats them as equal unless Ignore Nulls is set.

Seeing and removing indexes

Open the Indexes dialog on the Table Design tab to list every index, including those Access added for relationships. To delete one, select its row and press Delete. Do not delete the PrimaryKey index unless you mean to remove the key.

When indexing does not help

  • Tables with only a few hundred rows: the scan is already instant.
  • Fields where almost every value is the same, such as a Yes/No flag that is Yes for 99% of rows.
  • Fields you only display and never search.
  • Tables that receive very heavy bulk imports: drop non-essential indexes first and rebuild afterward.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is an AutoNumber a popular primary key?

    AutoNumbers are surrogate keys: small, unique and stable. They carry no business meaning, which is why they are safe.
  2. 2. When is a composite primary key the natural choice?

    A junction table's two foreign keys together are unique, so the pair makes a good key.
  3. 3. You index (LastName, FirstName). Which search benefits most?

    A multiple-field index is sorted by its first field, so it helps when that first field is part of the search.
  4. 4. What is a downside of adding many indexes?

    Each index must be maintained on every change, and it takes space. Index the fields you search, sort or join on.

Summary

  • A primary key must be unique, never empty and stable; an AutoNumber surrogate key is the safe default.
  • Composite keys suit junction tables; set them by selecting several rows before clicking Primary Key.
  • Set the Indexed property for single fields and use the Indexes dialog for multi-field indexes.
  • Unique indexes enforce rules such as one email per customer; Ignore Nulls lets optional values stay empty.
  • Index fields you search, sort and join on, and avoid indexing everything.

Next: Relationships and Referential Integrity