Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: about primary key

    I have question, if i delete one record in the access database, i would like the next record's primary is continuous. What i did, after delete, i compact and repair database, but the primary key still is not continuous. What should i do, i remembered i did in sql server database, but i also forgot too. Could you please let me know how to do it in both access database and sql server database.

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by yyu
    What should i do,
    Don't worry about it.
    Is the key referenced in other tables? Do you really want to change a whole bunch of keys every time someone deletes a record?
    If you really want to renumber everything every once in a while, create a column for that purpose; don't mess with keys. Keys are not meant for presentation purposes.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Primary keys should have no meaning and therefore continuous numbering should not matter.
    If it matters to your application then you've done something wrong
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2004
    Posts
    660
    Yes, i understand. But i would like to learn. I know it did has a way to do it. Thank you very much!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    may i add my two cents to the discussion: don't do it

    do not mess with autonumbers -- they are intended to be unique and that's all

    if you can explain the requirements for continuity of numbers, perhaps there is some other solution, but autonumbers is not the way to do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    100% rudy.

    my preference is for meaningless surrogate keys (e.g. autonumbers) in almost all circumstances, but if you have a perverse need for sequential numbers, i have documented a viable method in the code-bank (see pootle's index of the bank).

    issuing sequential numbers at record-create does not necessarily keep them sequential:
    1,2,3,4,5
    delete 3
    ...what do you want to happen now???????????

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Someone in a newsgroup, I forget who, said it best "Autonumbers are unfit for human consumption!"
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev (with an edit in bold by poots)
    Surrogate Primary keys should have no meaning and therefore continuous numbering should not matter.
    If it matters to your application then you've done something wrong
    I've just added a small clarification to your post. An intelligent primary key by definition has meaning. A surrogate should not. Meaning is not a property of a primary key but a property of the type of primary key you employ.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    you know what I use them for. Proactive for upsizing to sql -
    Dale Houston, TX

  10. #10
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    great for upsizing to sql -
    Dale Houston, TX

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •