In message , Armen Stein
writes
On Mon, 8 Jun 2009 16:00:05 +0100, Bernard Peek
wrote:

Hi Bernard,

If you add an
autonumber field you would call it a surrogate key.

Yes, an AutoNumber can act as a surrogate key with automatically
generated values.

But Access refers to
an autonumber field as a primary key, which I consider to be a serious
flaw in its design.

Not really. Access supports a table having a primary key, which is a
field (or combination of fields) that uniquely identifies each record.

It's not that simple unfortunately. The primary key is *always* made up
of attributes in the underlying entity and this does not include the
autonumber field. Access should identify the data field(s) as the
primary key and the autonumber field as a surrogate key. Both primary
and surrogate keys uniquely identify a record and either can be used to
implement joins. By merging the two ideas Access makes it more difficult
than it should be to follow good practise. But that's probably an
unrealistic expectation for a tool that's designed for people with no
formal training in database design.



--
Bernard Peek