|
Dear Jim,
We use keys everyday. We have keys for our house, keys for
our cars, keys for our office, some even say there's a key to
our hearts (my wife Diane insisted I include that). In a similar
manner each table in a database should have a key. Access calls
this particular item a primary key.
This primary key is a building block for any Access database.
|
Keys are Critical |
 |
Primary Keys are critical to the smooth operation of a
database. If you use them correctly they can make your
life easier. If you make mistakes with them, or omit
them, they'll come back to haunt you time and again.
The short and skinny (or the Key to Keys) -- Use
Autonumbers as the Primary Key in every table and you'll
avoid all of the common problems.
What is a Primary Key? The primary key is a
unique identifier for each row in a table. It's the way
that you (and Access) will join related tables. When you
need to research an item, the primary key will give you
an easy way to get to a record.
|
|
Characteristics of Primary Keys |
|
|
1. Primary Keys must be unique
This is a good reason not to try to use something like
initials, or social security number or some combination
of fields. Something which appears to be unique e.g., a
Social Security Number (SSN) really isn't. One project I
inherited used SSN as the primary key. Unfortunately the
database had problems when different employees had the
same SSN. We suspected that the duplicate numbers were
either typos or the result of identity theft.
2. Primary Keys must never (yes that means never)
change
Don't use something like a natural ID, e.g. an employee
number. If your company is bought by another company
it's likely that employees will get new IDs and you'll
have a problem.
3. Primary Keys should be meaningless
If the Primary Key means something to the user, at some
point s/he will want to change it. For instance, I did a
project for the State of New Hampshire and had to
convert their identifiers to a new system. Their IDs
indicated where in the state the person was located.
Unfortunately people move around from time to time. When
they went to a new part of the state, their identifiers
would change, and all their past information was lost.
4. Primary Keys should be a number
Numbers are faster. The reason for this is that they
take up less memory and sorting numbers (especially
integers) is faster than sorting letters
|
|
Primary Key Recommendation |
|
|
|
Use an Autonumber as the Primary Key. Luckily,
Access makes this simple. When you create a table and
select the field type, one of the options available is
Autonumber. It's a sequential number and clicks up 1 by
1 as you add new records to the table. Don't worry that
the Autonumber appears to have gaps (that's caused by
deleted records). You don't need it to indicate the
number of records in the table; if you need to know how
many records are in the table, simply open it up and
look at the bottom left hand corner. What you see there
is the total number of records.
You'll also want to have an Autonumber primary key
for every table. Autonumbers as Primary Keys are simple
to include, don't cost much in terms of space or memory,
and if you ever get to a point where you need the key,
it will already be there.
|
|
Trap of the Month |
|
|
Avoid multiple Primary Keys. You might be tempted to
create a key by using two or more fields, e.g., Last
Name and Street Address. Although this may work in the
short run, over time you'll regret the decision. Every
time you need to join two tables in a query you'll need
to link on both fields. It's a practice that invites
problems. Query results will not be what you expect, and
you'll waste time trying to find out why. Use an
Autonumber as a primary key and you won't go wrong.
|
|
|
Tip of the Month |
 |
|
To see some samples of different types of queries, forms
and reports you can go to the Microsoft link below.
Be aware that most developers (including me) think
that Microsoft makes some significant errors in their
database examples.
Even given this shortcoming, the link below will
provide some ideas on how to approach different
problems.
Sample Access databases that you can download and adapt
|
|
