In message , Armen Stein
writes
On Tue, 9 Jun 2009 13:11:28 +0100, Bernard Peek
wrote:

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.

Hi Bernard,

It seems your definition of "primary key" is narrower than mine. In
Access you can choose what your primary key is. It can be a natural
data field (or combination of them). Or it can be an AutoNumber. Or
both, although there's no real reason to do that.

I've spent quite a lot of time in the comp.databases.theory newsgroup
where terminology tends to be used very carefully. In fact some of the
people there want to abolish the term and just use "key" so that it's
clear that a table may have more than one key (even though that's
generally seen as bad practise.)


The writeup at http://en.wikipedia.org/wiki/Surrogate_key is pretty
informative. We consistently use surrogate keys as primary keys, as
we feel the advantages outweigh the disadvantages.

I often use surrogate keys too, but only after I've established that
there isn't a real primary key that I can use. There were once very good
arguments for using surrogate keys even if a table had a real primary
key, but I believe that they have mostly gone away.



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.

Well, it is designed for an easier initial learning curve. But we've
been developing custom database applications using Access, while
following good database design practices, for over 15 years. I have
no problem with Access's implementation of AutoNumbers and primary
keys.

For the benefit of those that have tuned in late to this particular
debate before, many practitioners consider that using a surrogate key
when a real primary key is available is bad practise. The argument is
that you still need to maintain a unique index on the real data and
keeping a second one on the autonumber just adds processing overhead.
There's a countervailing argument that using a small field like a
long-integer for creating joins is more efficient. Personally I doubt
that either effect is particularly strong when considering the type of
databases that are commonly built using Access.

My personal view is that adding a surrogate key adds unnecessary
complexity and another point of failure, so I discourage it. And as I'm
the boss it's my opinion that counts.



--
Bernard Peek