alter autonumber

[antonyx]

if i create 100 records with autonumbers as primary keys (record_id)

then deleted 30 at random

could i update the record ids so they become 1-70?

 

[ghudson]

No. You are mistaking the purpose of the autonumber field. It is only to give a unique reference number to each record. The user should never see the autonumber field since it has no meaning to the record [from the users side].

 

[antonyx]

i understand..

however.. at the moment i have 2 unique fields in my database..

the id and the refno

the refno is 8 digits long.. eg 06070234

the id were straight autonumbers so 1-1000 etc

it is a lot easier for my user to remember job id 235 then 04938790 you see..

so can i not do this then... have the refno as the primary key.. then have the autonumber as just a plain id number.. then perform an update query that will push all the ids upwards..

so if i have 100 records.. and delete 20 at random.. i can re-number the ids 1-80 so there are no gaps in between them?

 

[gemma-the-husky]

if you already have a unique key (refno), then the automunber id serves no purpose. however if you have already used the autonumber in a relationship than you cannot simply renumber the values in one table. If you want to sort the records you can include a sort key, or date or area ref, or any other data you want.

Autonumbers (or numeric indices) are actually more efficient than strings to work with. It is often advantageous to have a (hidden) true link to relate your tables, which your users do not need to see, as then you can change your true refno if you ever needed to.

 

[ejstefl]

Plus, if the user remembers the autonumber (235 in your example), you shouldn't go and change it on them!!

If the user is forced to remember an 8 digit number at any point in your database, there is probably a design deficiency you need to correct.