alter autonumbers

[tonyx666]

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?

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[PHV]

An AutoNumber should not have any other meaning than to be unique, so why would you update existing values ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rjoubert]

The only way you could do that would be put to append your 70 records into another table with the same structure.

 

[tonyx666]

hmm. ok.. well

if i make the recordid an autonumber, but not have it as the primary key, have the refno as the primary key.. would i be able to do it this way..



London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[Golom]

No. Other than specifying where to start and how large the increment is to be, you have no effective control over what values autonumbers have.

As PHV has pointed out ... autonumbers will be unique (unless you deliberately do something to change that) but there are no guarantees about what values they will have. If you want nice sequential values then you need to build code to compute and supply your own numbers.

 

[rjoubert]

If you added 100 records to your table, then deleted the LAST 30 records, your next record would start at 101, unless you first ran a compact/repair on your database. Once you run the compact/repair, then your next record would start at 71, instead of 101. I know this isn't quite what you're looking for, but I thought I'd throw it in as a FYI.

 

[Golom]

rjoubert

I think that compact & repair resets autonumbers only if the table is completely empty. If it still contains any records then autonumbers will continue to increment from their previous values after a compact & repair.

 

[rjoubert]

Golom...You are correct sir! Well then, ignore my last post.