The Database Journal
03/10/2006
Adjusting Autonumber Increments

03/10/2006
Moving Access Data to Microsoft Excel

03/10/2006
Exporting Access Data with TransferSpreadsheet

03/10/2006
Export Access Data with VBA

03/13/2006
Data Layout in Microsoft Excel

03/17/2006
Excel Text to Number Conversion

03/21/2006
Transposing Excel Ranges

03/25/2006
Access VBA Timer Event

03/29/2006
What is MySQL?

04/02/2006
Microsoft Access Parameter Query Tutorial Video

04/06/2006
How does PHP Work?

04/10/2006
Microsoft Access VBA Tools - References

Click here to view all articles.

Database Design
Website Design
Search Engine Marketing
Home
Contact Us
About Us
Database Journal
Newsletters
Database Newsletter

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:

Figure 1

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.

Figure 2

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.

Figure 3

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.


©2014 Blue Moose Technology, LLC

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

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:

Selecting the Right Primary Key in Microsoft Access