Why Autonumbers

[op]

What are the advantages and disadvantages of using
autonumbers? I had taken a course and autonumbers were
not introduced. The classwork used primary keys that were
user generated, like social security numbers or a serial
number. I personally find it difficult to transfer data
from one database to one that utilizes autonumber as a
primary key.

 

[Jeff Boyce]

Take a look in the tablesdbdesign newsgroup, at the mvps.org website, and on
Google.com with the topic of "autonumber". Autonumber is a way to provide a
unique row identifier in Access.

When you have a "natural key" (I guess that makes an Autonumber an
"unnatural key"), it makes sense to use it. A primary key needs to be
unique (i.e., assigned to one and only one entity) -- are you quite certain
that SSN is unique enough?!

And if you create a serial number in one system, how does that have any
relationship to some serial number created in a totally separate system?
(just trying to see how this would help you transfer data between databases)

By the way, why would you need to transfer data between databases?

 

[Arvin Meyer]

The only advantage to an autonumber (but an extremely important one) is that
they are guaranteed, when used as a Primary Key, to be unique.

Social Security Numbers are definitely not unique due to inaccuracies of
data entry, fraud, identity theft, intentional "mistakes", and use by
illegal aliens. There are at least 35 million cases of identity fraud in the
last 10 years alone.

Serial numbers fare better, but still suffer from inaccurate data entry
problems.

I usually use autonumbers, but also use natural keys (like a state or
country code) where appropriate. Others will extol the use of compound
natural keys for everything. There is no perfect system, so you need
carefully evaluate your requirements and use a key which suits your
application.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[op]

The reason for two databases is while I was waiting for
the one that has the autonumber to be created, I created
one without the autonumber. And Now I have to append the
one into the other (autonumber).

 

[Lynn Trapp]

I tend to agree with Arvin pretty well. However, I would add that, while an
Autonumber is virtually guaranted to be unique, it is not a guarantee of the
uniqueness of a record. It can, in fact, mask duplication of records.
Therefore, while I use Autonumber fields as a means of joining tables in
relationships, it is important to find an additional candidate key to serve
as a unique record identifier and put a unique constraint on that key.