Well, the battle between whether to use Natural Keys or Surrogate Keys has
waged for years and we will not resolve it here. But please do not tell me
and others what I do not understand just because our opinions differ.

A Primary Key has two properites: 1) it must be unique, and 2) it must not
be NULL. In any table there may be several fields (or combinations) of
fields which could possibly fulfill these conditions. These are called
Candidate Keys. The job of the database developer is to decide between
these CKs to see which is the absolute best. In the case of the Contacts
table (mentioned in a separate post); last_name, first_name and
postal_address could provide a key, but it is not a really good choice.
While this may provide uniqueness (maybe), there is a real possibility that
a person does not have a postal address (no part of a compound primary key
can be NULL). This is one of the difficulties of finding a good natural
keys to make up the primary key.

Now, there are actually two purposes for uniqueness: 1) Real-world
uniqueness to make sure an entity appears only once in a table, and 2)
Relational uniqueness for relating records between tables. As you noted,
autonumber fields are lousy in terms of real-world uniqueness. However,
they are perfect for creating uniqueness for relating records. In this
sense, the autonumber field is absolutely unique. It can appear ONLY once
and can never be repeated. Further, as a long integer, it is VERY efficient
for relating records compared to a compound primary key composed of text
fields.

So when I recommend autonumbers for primary keys, I also recommend creating
a separate UNIQUE INDEX that is composed of fields from a Natural Key. This
fulfils the function of real-world uniqueness and is more flexible, because
you CAN allow Nulls in a unique index if you want. In this way, both
functions of a Primary Key are fulfilled to their maximum efficiency.

You may not agree with this. I know many respected developers who don't.
Opinions differ, but this does not give you the right to disparage those who
disagree with you.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L



wrote in message
oups.com...

BruceM wrote:
That you disagree with somebody does not make that person wrong.

That someone has a track record of providing a wide range of assistance
does not make that person right ;-)

In this case, I disagree with the person because they do not understand
what PRIMARY KEY means. Autonumber does not make a good uniquifier, let
alone a good PK (different concepts). Remember this list? the
advantages of using autonumber a

1. Convenience, because it's provided by the 'system';
2. It's an 'efficient' data type;
3. Erm...
4. That's it!