Microsoft Access How-to: Primary Keys, What and Why?

access logoEvery table you create in any database should have a field by which every record in that table can be uniquely identified against any other record in that same table. Say what!!??? OK, I just made that up, but when you read things about primary keys or indexes in databases that is the kind of tech-talk that makes people walk away without even trying. It's really not that complicated, trust me.

If you are not sure about any of the following directions try looking at this article first.

So here's the real point. You (and your Access database) must have a way to uniquely identify every record in a table. That means that it must have a way to say 'I know I'm getting the correct record, because this value right here is unique, and no other record in this table has the same value'. Now, in reality you don't really have to create a primary key. Access will complain when you save your table for the first time if you don't, but it won't stop you from creating the table. Other database systems have different ways of identifying records as being unique, so it may not be as important for some of them. However, I think everyone will agree that in general databases simply work better if you have a unique index (another term for a primary key) on each table. And I've had cases where Access will mis-behave in a big way on tables without primary keys, especially when joining one table to another or when using any kind of ODBC drivers (don't worry if you don't know what I mean here, it's not important for right now).

The best, and easiest, way to do this is to create an AutoNumber field, and tell Access that you want to use this as a primary key. Start by creating a new table, and go directly into design mode:


access primary key 01


Now select the first field, name it ID, and change the datatype to AutoNumber:


access primary key 02


Now move your mouse pointer up to the main toolbar and click on the icon that looks like a little key:


access primary key 03


You'll end up with a table design that looks like this:


access primary key 04


Just a couple of things to notice here. First, you'll see that there there is now a key icon in the field. It's a fast easy way to see where your primary key is. If you look down in the properties box at the bottom of the table under 'Indexed' you'll see that Access had created a unique index on this field. What that means is that you will never be able to duplicate a value in this field.

Additional resources

  • You can find more information on creating tables here.