#1
  1. No Profile Picture
    matt1174
    Guest
    Devshed Newbie (0 - 499 posts)

    Autonumber c*ck up


    Hi all

    I have an issue with an autonumber field in a table.

    Somehow, recently, the same number has been issued twice.

    OK, I've realised this is an issue so want to change the field properties to indexed with no duplicates to ensure it doesn't happen again.

    However, it won't let me change this while there are duplicate numbers in the table and I am unable to modify the numbers manually.

    My next thought was to delete the relevant records, however as so much is linked throughout, it won't allow me to do that either.

    Any suggestions?
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    What database system are you using? I would think that most database systems should enforce a unique constraint on an auto-number field automatically...
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    matt1174
    Guest
    Devshed Newbie (0 - 499 posts)
    I'm using Microsoft Access.

    I too would have thought, that using Autonumber, it would have prevented itself from duplicating numbers.

    I didn't set the properties to 'No Duplicates', but using Autonumber I wouldn't have thought it was necessary.

    However, now the problem has occurred, as stated earlier, I can't delete the records due to the extensive relationships throughout, yet it won't let me manually adjust the numbers.

    ...and until I do one or the other, I can't implement a 'no duplicates' rule in the properties to prevent further errors.

    I've tried chaning the field from autonumber to number, which allowed the editing of the numbers, yet it wouldn't then let me change it back to autonumber.

    The reason this is an issue is because the field in question is a 'quote' number, therefore we don't really want duplicates.
  6. #4
  7. No Profile Picture
    matt1174
    Guest
    Devshed Newbie (0 - 499 posts)
    I've decided to bite the bullet on this one and delete the field, then add it again, with 'no duplicate' properties.

    Not the ideal solution, but better now than later on when there's even more to go wrong....
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by matt1174
    I've decided to bite the bullet on this one and delete the field, then add it again, with 'no duplicate' properties.
    what happened to "My next thought was to delete the relevant records, however as so much is linked throughout, it won't allow me to do that either" ?

    even if it does let you delete the column and reassign the autonumbers, won't this create an even bigger c*ck up because the linked tables will then potentially point to the wrong rows???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    matt1174
    Guest
    Devshed Newbie (0 - 499 posts)
    Thanks for your input on this - it seems ok for now - the autonumber was a 'quote' number which only relevant to a drawing number within the same table.

    The autonumber field has no bearing on any of the links, so I think (hope) I've got away with that one

IMN logo majestic logo threadwatch logo seochat tools logo