Re: Data Primary key vs. Artificial (Autonumber) primary key

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Jamie Collins <jamiecollins@xxxxxxxxxx> wrote in
news:b6165f27-f889-4fe0-85a7-515675f94905@xxxxxxxxxxxxxxxxxxxxxxxxxxx
:

On Dec 6, 6:50 pm, "Pat Hartman" <please no em...@xxxxxxx> wrote:
the autonumber would
ALWAYS be the PK and would be used for all relationships. When I
have business rules to satisfy that revolve around uniqueness of
"natural" keys, I use unique indexes.

The main issue I have with the 'Autonumber PK' movement is that
the message about having table constraints on the candidate keys
(and other business rules) doesn't always come across. You've said
the right things (IMO) about so-called surrogates in this thread
but I would wager than most users in the 'Autonumber PK' camp put
a PK on the Autonumber and think, "Job done" as regards table
constraints.

I'm not sure I think it's a good idea to define "best practices"
based on trying to avoid what stupid people do. If someone fails to
put in place proper constraints on fields other than your surrogate
key, then that's pilot error. It's a mistake.

On the other hand, in a lot of situations, forcing someone to use
the natural key can lead to all sorts of other problems, like
default values to avoid Nulls, and then you have to program around
those (e.g., you won't be able to use this to concatenate the name
fields: Mid(("12" + [LastName]) & (", " + [FirstName]),3), because
with default values, none of your "empty" fields will be Null).

Seems to me you're trading one set of problems for another.

I'm assuming that people will not handle the multi-column key
correctly and you're assuming that they won't handle their
constraints on non-PK keys. Six of one, etc., so I see no reason why
your approach would be superior on the basis of the argument made
above, because to me it applies equally well (and is, in fact,
worse) with multi-column natural keys (I have no objection on
principle to single-column natural keys, though I use them only on a
case-by-case basis).

Another significant issue is when Autonumber values are exposed to
users. Most informed people agree this should be avoided but again
the message isn't always cleat and so it does happen.

This is, once again, an example of pilot error.

In an ideal world,
surrogates would be implemented under the hood and not even be
exposed to DBA, developers, etc. I encourage people to use the
random flavour of Autonumber to discourage this practice (and
persistent offenders should be forced to use GUIDs <g>).

But random Autonumber PKs have problems with clustering and table
order (although in high-concurrency environments, it can be turned
into an advantage).

I know you're joking about GUIDs, but just in case someone reading
this takes you seriously, they should read this:

http://trigeminal.com/usenet/usenet011.asp?1033

(the article is specifically about replication, but many of the
problems with GUIDs in Access apply equally to non-replicated MDBs)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.

Relevant Pages

  • Re: New Database - Primary Key
    ... Whether to use AutoNumber keys is ... as opposed to "natural keys". ... truly unique natural keys like that, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Primary key
    ... hotly contested threads on the subject of natural keys versus ... surrogate keys. ... into autonumber surrogate keys and have never looked back. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Primary key
    ... hotly contested threads on the subject of natural keys versus ... surrogate keys. ... into autonumber surrogate keys and have never looked back. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Autonum field for Relationships and Replication
    ... restrict entries in candidate keys that could have been the PK?" ... autonumber field and a third with the autonumber foreign fields from the ... > CandidateID sequential in the replicated environment. ... > Replica B. Therefore creating duplicates. ...
    (microsoft.public.access.replication)
  • Re: A real world example
    ... You have a relation with candidate keys Person and Position. ... identifies the tuple containing You in the proposed instance, ... maintain the consistency of the database throughout the update. ... argue that no query is needed, merely constraints. ...
    (comp.databases.theory)