Use an AutoNumber Primary Key and Unique Course Index in Microsoft Access
Question details
Prevent duplicate combinations of course title, section, course number, and subject while assigning each course a primary key.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A course table needs a reliable identifier and a constraint that keeps each combination of four course fields unique.
- Observed behavior
- A calculated field cannot serve as the table's primary key.
Identify the four stored fields that define a unique course and check the table for existing duplicate combinations. Resolve duplicates before creating the unique index, and decide whether all four fields must be required.
Add a CourseID primary key and a unique course index
Use an AutoNumber identifier for relationships and a separate unique index to prevent duplicate course combinations.
In the Navigation Pane, right-click the course table and select Design View.
Add a field named CourseID, set its Data Type to AutoNumber, and select Primary Key on the Table Design tab. Save the table.
In Design View, select each of the title, section, course number, and subject fields and set its Required property to Yes if every value must be present.
Select Indexes on the Table Design tab. Enter an index name and the title field on the first row, then add section, course number, and subject on the following rows, leaving their Index Name cells blank. Select the first row and set the Unique property to Yes.
Close the Indexes dialog and save the table. In related tables, add a Number field with Field Size set to Long Integer for CourseID, then use Database Tools > Relationships to link it to the course table's CourseID.

Review exported course lists in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface for working with Office files. Export course data from Access to Excel, then open it in WPS Spreadsheets for review; WPS does not replace Access database keys or unique indexes.

Frequently Asked Questions
Why can't I use a calculated field as the primary key?
Access does not allow a calculated field to serve as a primary key. Add an AutoNumber field such as CourseID in Design View and select Primary Key for that field instead.
How does the unique index prevent duplicate courses?
In Table Design > Indexes, put the four stored course fields in one index and set Unique to Yes. Access then prevents records with the same indexed combination.
Why won't Access save my unique index?
The table may already contain duplicate combinations. Find and resolve those records, then return to Table Design > Indexes and save the unique index again.
What type should the CourseID field use in a related table?
Use a Number field with Field Size set to Long Integer to match the course table's AutoNumber CourseID. Create the link under Database Tools > Relationships.




