Re: Restart Autonumber

From: Alan Webb (knogeek_at_hotmail.com)
Date: 07/18/04 

Date: Sun, 18 Jul 2004 19:06:02 -0400

James,
Before the turn of the milennia I managed to kick off a long winded and
passionate thread complete with troll posts and flames on the issue of
whether arbitrary or significant keys are better. This tends to be a bit of
a sore subject for some. I maintained then that in all cases arbitrary keys
are better. Since then I've had consulting contracts where I inherited a
schema that used significant keys and imposing my own arbitrary keys would
have unleashed a great deal of havoc for the client. I did the smart thing
and left things as they were. These days I tend to include both an
arbitrary key maintained by the rdbms that is the primary key and does
uniquely identify that row in a table as well as any significant keys the
client needs to maintain compatibility with their existing systems.
Processor, disk space and memory is cheap these days so any overhead
generated by the additional columns is probably cheaper than an overhaul of
the schema to shift to or from an arbitrary or significant key naming
convention.

"James" <dragonzfang@hotmail.com> wrote in message
news:63468c52.0407162031.6a668de@posting.google.com...
> I have been reading many posts by users that claim to have years of
> experience with Access that promote the use of Autonumber generated
> values as primary keys. Don't get me wrong; I use Autonumber values as
> primary keys for standalone tables, such as those that really do not
> have a primary key unless you use multiple key fields. But I keep
> asking myself why anyone would create an architecture of two related
> tables upon which the relationship is based on an Autonumber key
> field. I'm currently working on one right now where I'm converting an
> "Orders" table that uses Autonumber to generate Order numbers. This
> Autogenerated value is then stored in the OrderDetails records. If the
> scenario that you describe below occurs, guess what? You've just lost
> your relationship between the two fields, because you cannot enter
> your own values in an Autonumber field, and your OrderDetails table is
> useless because the reference to the Orders table is gone. The
> OrderDetails table will use an Autonumber key to basically serve as a
> surrogate primary key, but I use a "natural" key that I control -
> which in this case is my own "rolled" invoice #. The database I'm
> converting is going to use my own "rolled" method, upon which a
> function will return an incremented value from a table. With the
> experience I've had with tables related based on Autonumbers, I will
> never willingly design one to be as such.
>
>
> "Jeff Boyce" <JeffBoyce_IF@msn.com-DISCARD_HYPHEN_TO_END> wrote in message
news:<O8ykCGLXEHA.4020@TK2MSFTNGP09.phx.gbl>...
> > Traci
> >
> > In addition to the cautions noted by Doug in his response, be aware that
if
> > your table's primary key (?your Autonumber) is used as a foreign key in
> > one/more child tables, "restarting" your Autonumbers could hose the
> > relationship between parent and child records.

Relevant Pages

  • 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: Restart Autonumber
    ... I maintained then that in all cases arbitrary keys ... I use Autonumber values as ... > have a primary key unless you use multiple key fields. ...
    (microsoft.public.access.gettingstarted)
  • Re: Data Primary key vs. Artificial (Autonumber) primary key
    ... Presumably, if there are candidate keys available, they are what the user ... sequence by its primary key whenever the database is compacted. ... the clustering has no impact ... The main issue I have with the 'Autonumber PK' movement is that the ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Simple database layout - Am I on the right track?
    ... someone chose to use an Autonumber as an identifier and the bookkeeper, ... to the argument over whether an Autonumber surrogate key is "better" than ... the multi-part composite keys will be slower. ...
    (comp.databases.ms-access)
  • Re: Data Primary key vs. Artificial (Autonumber) primary key
    ... "natural" keys, I use unique indexes. ... the message about having table constraints on the candidate keys ... but I would wager than most users in the 'Autonumber PK' camp put ... worse) with multi-column natural keys (I have no objection on ...
    (microsoft.public.access.tablesdbdesign)