Re: Restart Autonumber

From: James (dragonzfang_at_hotmail.com)
Date: 07/17/04 

Date: 16 Jul 2004 21:31:03 -0700

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: Multi-Field Primary Key
    ... Surrogate numeric keys do solve a lot of technical ... problems for database and database application developers and many seasoned ... I recognise *three* uses for autonumber. ... "Although a primary key isn't required, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Concatenate and Null Values -- Features
    ... Tony Toews dislikes cascade deletes as well as cascade updates, ... fence regarding the use of natural versus surrogate (autonumber) keys. ... Database Normalization Tips ... For optimal database design and performance, the primary key of a table ...
    (microsoft.public.access.reports)
  • Re: Autonumber Fields
    ... this special meaning is clustered index. ... believe that a clustered index is a requirement for a primary key. ... questions don't understand the purpose of an autonumber primary key. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Does each related table have to have their own primary key ass
    ... I agree with Rick Brandt to some extent, and I can't think of any real problems that you might encounter by adding Autonumber fields that you never use, so you perhaps should go ahead and stick them in there and nothing will go wrong, although they will make your Tables slightly larger and require you to name them and keep track of them. ... one such reason for ensuring that all the records are easily distinguishable (by having a unique primary key field in each record) might be that the Table is part of a self-join. ... The reason I ask is that when I was 1st setting up my database I had the 1st table set with a primary key I assigned (policy #) and I set the next table with an autonumber, but I was getting error messages when I was trying to do reports and forms extrapulating data from both tables. ...
    (microsoft.public.access.gettingstarted)
  • Re: Primary Keys & Relationships
    ... Remember that an autonumber will almost inevitably have gaps in the ... If you don't intend to enforce RI, ... > you will be presented with the options to Cascade Delete and Cascade Update. ... > Many people prefer to use autonumbers as their primary key when the natural ...
    (microsoft.public.access.tablesdbdesign)
Loading