Autonumbers (question, not problem) (1 Viewer)

Alc

Registered User.
Local time
Today, 05:09
Joined
Mar 23, 2007
Messages
2,407
One of my users has a database they created a couple of years ago. A table within it has an autonumber as the primary key. During testing, he got as far as 200 records, which were then deleted after the first 'live' use (yes, they should have been deleted beforehand, but they weren't). This means that the first number in the primary key field is 201.

The numbers for all records since then have increased by one, until around the primary key had reached around 600. At this point, he noticed that the next record created was assigned a primary key of 1, the following 2, and so on. So the table now has records with ID 1, 2, 3, 201, 202 - 600.

My questions are:
1. Is this normal?
2. If not what could have caused it?
3. Is there any way that they could hit problems in the future as a result of this? So far, nothing untoward appears to be happening.

I did toy with the idea of selecting the records into a new table having the same structure, so the autonumbers restart at 1, but I'm told the numbers are used as foreign keys for a number of other table, so this option might be more trouble than it's worth.
 

Ranman256

Well-known member
Local time
Today, 05:09
Joined
Apr 9, 2015
Messages
4,337
autonumber continue to count from where they left off.
If you want them to start back at 1,
delete all records,
compact the db.
Done.

but if you have child records , that will be problem. They will never match up again.
 

Alc

Registered User.
Local time
Today, 05:09
Joined
Mar 23, 2007
Messages
2,407
Thanks for the reply.

I don't want them to restart, I wanted to know why they increased for a year or two, then dropped back down and started to 'fill in the blanks'.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:09
Joined
Jan 20, 2009
Messages
12,849
The mysteries of autonumber. I have not seen that one before but I suspect it may have happened after a compact.

Usually changes to the autonumber seed come from inserting records with the autonumber field value via a query. This tends to reset the autonumber back to the key of the inserted record. But this does not appear to be the case this time.

The problem will come as an insert error when it passes 200 and tries to insert 201 again. I would suggest exporting the record with the highest key to another table then delete it from the original table and insert it again including the autonumner value using a query.

This usually resets the autonumber seed.
 

Alc

Registered User.
Local time
Today, 05:09
Joined
Mar 23, 2007
Messages
2,407
The problem will come as an insert error when it passes 200 and tries to insert 201 again.
And it did, indeed, happen like that.

Compacting and repairing appeared to 'reset' the autonumbers correctly.

Thanks for the help.
 

Users who are viewing this thread

Top Bottom