Do all primary keys use autonumber?

No, of course they don't. But Autonumber is a convenient way to be sure
that you won't have any duplicate values, and an Autonumber field
occupies only about 4 bytes per record, much shorter than many other
fields you might choose to use.

Your primary key could even consist of more than one field, for example
using both [First Name] and [Last Name] (assuming you don't have two
people named "Mary Jones").

If you don't use Autonumber, you'll need to get your key values from
somewhere, and Access is likely to complain to you if a new key value
matches some existing one, as they must be unique in the Table. In such
a case, you'll need to choose some other value, and if you were trying
to enter a name, you will have to modify it somehow, such as by changing
"Mary" to "Mary_1".

To avoid confusing my Autonumber values with something meaningful (such
as people's ages), I usually set the "New Values" property of the field
to Random, making it obvious that they're keys.

-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.

No, certainly not.

An Autonumber is convenient if you don't have a "natural" key, but it
can actually be a bad idea. It makes all records unique automatically,
so that if you have duplicate data, it can be harder to discover.

If you have some field - or combination of up to ten fields - which
uniquely identifies each record in your table, use it. A good Primary
Key candidate should meet three criteria: it should be UNIQUE - only
one record should have it; it should be STABLE - that is, it shouldn't
change very often or at all; and (less importantly) it should be SHORT
so that table indexes, etc. don't have to be overly large and/or slow.

For example, if you have a table of States (or US States and
Territories and Canadian provinces), the Postal Service two-letter
code makes a good primary key. Every state and province has a unique,
two letter code; the codes change only rarely (Newfoundland, Nova
Scotia, and Nunavut in Canada being the last change that I remember,
and that's several years back); and it's only two characters.

Or, if you have a table of Employees, your Human Resources department
probably already has an EmployeeID - perhaps the Social Security
Number (though this has some problems), perhaps something else. It
would be unique, stable, and short, if it's designed properly; you'ld
use it as the Primary Key of the employee table, rather than adding an
autonumber which would really be a redundant duplication of its
function.

John W. Vinson[MVP]

Vincent Johns wrote:
Bear in mind that PRIMARY KEY has a special meaning which makes an
autonumber the choice of last resort for the PRIMARY KEY designation.

Take the example of a table which has two 'unique' columns: a random
integer and an industry standard key, of which the ISBN is an excellent
example. In relational theory there is no issue: all keys are equal,
simply use the one most appropriate to your needs. However, with SQLs
you can only have one PRIMARY KEY per table. You are forced to choose
just one but what criteria should you use to make this choice? Both
could be given NOT NULL UNIQUE constraints and your 'uniquifier' needs
would be satisfied.

But everyone (correctly) says a table should have a PRIMARY KEY. So
what does PRIMARY KEY give you that NOT NULL UNIQUE does not? For
Access/Jet, there's only one answer: physical order on disk a.k.a. the
table's clustered index.

What makes the best clustered index? In a nutshell, the column(s)
primarily used in queries that use GROUP BY and BETWEEN constructs.
Does anyone write SQL that includes GROUP BY (autonumber_col) or
BETWEEN (this autonumber value) AND (that autonumber value)? I doubt
it.

In the earlier example, ISBN is the natural key and wins the PRIMARY
KEY designation hands down. What about a table where the autonumber is
the only one 'unique' column?

Say, a table with just two columns, a random integer column named ID
and a last_name column. The last_name is unlike to be unique. Then
again, last_name is far more likely than the random integer to be used
in GROUP BY or BETWEEN constructs. The best approach is to constrain
the random integer (ID) with NOT NULL UNIQUE and make the PRIMARY KEY
designation to be (last_name, ID) in that order. This will cause the
physical order of the disk to be rebuilt on compact in last_name order.


Choosing an autonumber as the sole PRIMARY KEY column is likely to have
the effect of a performance hit on your database because a random
integer makes for a lousy clustered index.

To avoid duplicate links, can ID keys be linked together or is that a bad idea?