Roger Carlson wrote:
the battle between whether to use Natural Keys or Surrogate Keys has
waged for years

Again, you've misunderstood. That's a different debate which I don't
want to do.

A Primary Key has two properites: 1) it must be unique,
and 2) it must not be NULL.

Again, you've misunderstood. I can add to your list of properties: 3)
It has special meaning in the SQL implementation. In the case of
Access/Jet, this special meaning is clustered index. Are you aware of
this special meaning? If you are, why do you think an incrementing
integer with no meaning in the data model makes for a good clustered
index?

Your two properties can be satisfied by constraining with NOT NULL
UNIQUE. There's only one way of creating a clustered index in
Access/Jet and that's using PRIMARY KEY.

As you noted,
autonumber fields are lousy in terms of real-world uniqueness.
However, they are perfect for creating uniqueness for relating records.

Perfect? As I said up thread, the only good things about autonumber is
that it is convenient (because it provided by the 'system') and it is
efficient (data type = INTEGER). Do you really think there is no better
key generation algorithm than autonumber's? If autonumbers were
perfect, we wouldn't see daily posts of the like, 'Why do I have gaps
in my ID numbers?' I can see how 'quick and dirty' may equate to 'good'
given the right circumstances but 'perfect' may be an overstatement.

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.

Why not use a UNIQUE on the autonumber column and use your clustered
index (PRIMARY KEY) for a more worthy candidate?

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.

I am saying I believe you have misunderstood what PRIMARY KEY means.
All you have said indicates you are unaware of the special clustered
index meaning that PRIMARY KEY has in Access/Jet. I am open minded to
there being another explanation: why do you think an autonumber makes a
good clustered index?

Apologies if you feel disparaged. That was not my intention. We all
make misstatements from time to time.