Database - Why dont AutoNumber/Identity/etc re-use deleted numbers?

It can seem rather odd that AutoNumber/Identity/etc numbers do not get re-used after they have been deleted, but there are very good reasons for it to be done this way.

An important conceptual issue to bear in mind is that AutoNumber/Identity/etc are not intended to be sequential (even if that is usually what happens), they are intended to be unique - so that each record can be specifically identified, and thus can be edited etc without accidentally working with the wrong record.

When you delete a record the AutoNumber/Identity/etc number is not re-used because that would not actually be guaranteed to be unique (they might accidentally refer to the original record). The reasons for this include:

• you may have data in other tables which refers to the AutoNumber/Identity/etc field, and depending on your table design (and programs etc) those other records may not have been deleted at the same time. This means that if you re-use the number for a new record, the existing 'orphan' data in the other tables will automatically get linked to the new record - so you will have bad data for the new record.
• the data may have left the database in some way (such as being printed or emailed), and it is possible that somebody will be asked to make changes to a record that has been deleted. If the number was re-used, they could easily modify the new record, which would be the wrong thing to do.

In addition to the issues above, you may find that users/programs that interact with the database keep track of which records they have worked with, so if the numbers are re-used they are likely to accidentally ignore the new records.


While it is natural to want the numbers to be sequential, you should ask yourself if there is actually any need for it at all, or do you just want it because it would be "nice" that way?

In general AutoNumber/Identity/etc fields are used as a Primary Key, in which case the actual values that they have do not matter - the values should be treated as if they are meaningless, and only used to uniquely identify records.

As an example, if you have a list of people (perhaps staff or customers) or products etc, it does not matter if their reference number is one higher than the previous, only that it isn't the same as any other - so you can be sure that you are referring to the correct one. Any information you think you could get from a sequential number can be got much more safely (and often more easily) in another way, for example to find the amount of people/products/etc, you should run a Select Count() query.


If you do actually have a need for sequential numbers (rather than just wanting it), it is probably best to create your own method of doing it, but bear in mind all of the issues above, and make sure that you deal with the other issues that AutoNumber/Identity/etc deal with (such as multiple users creating new records at the same time).


Note that if you are certain that resetting the highest AutoNumber/Identity/etc value is safe (eg: you have been adding and removing test data), there are ways to do it. For MS Access see kb 812718, and for MS SQL Server see DBCC CHECKIDENT; for any other database system, the method should be explained in its documentation for its equivalent of AutoNumber/Identity.