Microsoft Access Tutorial - Primary Key and IDs

The primary key in a Microsoft Access table represents an identifier for a unique record in that table. In this reading, I will teach you how to create a primary key for a Microsoft Access database table.

1. Open Microsoft Access.

Open the database that has a table for which you what to create a primary key on. You can create a new table if you don't already have one and still follow this tutorial.

2. Open the Design View for New Table

Read a previous tutorial to lean how to create a table in design view.

3. Create and Name the Field

Once you are in the design view for the table under field name give this field any name but I would suggest using the name ID. For data type select AutoNumber. If you look at the bottom at the field properties the field size should be long integer which just means that the size of the number is going to go to a max that a long integer size allows. The "New values" should be "Increment" which means whenever a new row is added that number for the ID is incremented by one. For "Indexed", select "No", which means you will not be having any duplicate IDs since we are creating a primary key which is to be a unique value for each row.

4. Set Field as Primary Key

Once you have created the field, right click on the cell that is directly to the left of the cell that has the name of the field and click on "Primary Key". This will cause that field to become the primary key for this table.

If you wanted to test this out you would open up the table in the datasheet view and enter your data as an entry into the table. You will notice that the ID column increments itself by one every time you enter a new record, which is the result that we are looking for.

Once you get to the point where you are executing complicated queries against your tables, you will realize the importance of having a primary key and an ID column for the Access table that identifies each row as being unique. Ideally every table you use should have some sort of identifier that is unique for each row.

I would suggest creating a primary key for every table you have so that it is easier for you to use later on when you perform complicated queries against that table.

Keep in mind that each table can only have one primary key and should have only one unique identifier column. Creating a primary key / ID field for each table is very common and is considered best practice when designing a Microsoft Access table.