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.
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.