Selecting the Right Primary Key in Microsoft Access
Okay, you're building a Microsoft Access database and the time has come to select your primary key. It's the single most important field in any table in any database because it determines how all tables relate to one another. Mess up your MS Access primary keys, and you're done for.
So let's pretend we have a table and to keep this to the point, we're just going to keep track of names in this table. Each person has a Social Security Number, and that's unique for every individual, so it could make a decent primary key. First let's have a look at the table:
A very common mistake here would be to make a combination of the first and last name as a dual-field primary key. Bad idea.
Rule #1: Never use text fields as primary key fields unless not doing so would result in serious bodily injury.
Text fields make poor primary keys. In particular, fields such as first and last names together make terrible primary keys as there is always the chance that the field will have to repeat - in other words - more than one person can have the same first and last name. That's an instant headache as a primary key.
So our next alternative is to use the SSN as a primary key. Social Security Numbers are unique numbers for individuals, so it makes sense to use it as a primary key. However, there are some problems using this as your sole primary key even in a database where everything is based off of the individual. But why?
Rule #2: Never use a primary key that your users are responsible for typing.
Why? Simple. If your users are responsible for typing their primary keys, they'll make mistakes. As sure as the sun shines, if you leave it up to your users to type things properly, they won't. It's far too easy for an error to occur from mis-typing something, or from twisting numbers or letters around as you're copying them from paper to digital.
In the case of an SSN, it's quite possible someone could mis-type the number, transposing two digits. This error won't be caught immediately because there's nothing in place that can determine if the SSN is correct or not. As more data fills up additional tables, all carrying the mis-typed SSN into it's respective foreign-key fields, bad data propogates throughout the database and before you know it, reports are incorrect and you're busy digging through records to see what went wrong. That brings us to the autonumber primary key.
It's difficult to think of a situation where an autonumber primary key doesn't make sense in a Microsoft Access database. You don't have to worry about updating it on your own. If you end up mis-typing an SSN, you can easily fix it since all records don't relate on the SSN, they relate on the autonumber which stays consistent, and there's no chance of duplication in a properly designed database.
©2013 Blue Moose Technology, LLC
Article Publishing and Re-Print Guidelines
- The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
- When re-published online, all links must be live hyperlinks.
- These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
- These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!
You can easily link to this article!
If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:
On your page, the code will appear like this: