How to create an auto-incrementing column that doesn't skip numbers after deleting a row?

Question

I'd like to have a column that's simply (automatically) enumerating the sequence of rows. So I have this table:

Nr  Name  
1   Test  
2   Test2  
3   Abc  
4   Def  

In which I first delete the last row followed by adding a new one. The problem is that the table then looks like this:

Nr  Name  
1   Test  
2   Test2  
3   Abc  
5   Ghi  

How can I make it resume with 4 ?

Answer 1

Short answer: Don't do it

At least, don't do it with the key - keys should be stable.

There are performance issues with this (cascading updated key values through foreign key relationships in an atomic fashion - covered by other answers here) and also logical issues (for example, data stored outside the database system).

For example, using your data above, you delete the row for 'Def' (which was id 4) and subsequently renumber the row for 'Ghi' to be id 4 (it was id 5). What happens when customer 'Ghi' phones and quotes a key of 5, because that is what they have recorded for their customer id? What happens when customer 'Def' visits, carrying a printed invoice showing a key of 4?

For example, does your bank give you new a bank account number every time one of its customers switches to another bank?

Row number at query time

If you want a 'row number' concept then generate one at query time using row_number() (added in SQL Server 2005).

SELECT row_number(), <other columns>
FROM schema.table
ORDER BY <ordering criteria> ;
-- or
SELECT row_number() OVER ( ORDER BY <ordering criteria> ), <other columns>
FROM schema.table ;

Answer 2

You can't (with auto-increment values). The Auto-increment value does, what it's name is telling you: it automatically increments after EACH Insert (and after each INSERT ... ON DUPLICATE KEY UPDATE statement even if it was the update case).

You can manually set the next auto-increment value, yes - but doing so will only cause problems, because then mysql would proceed with id 5, after reusing id 4 -> already there -> error.

If you really need id's without gaps, you could generate them, but you should keep the surrogate id as well for internal reference. (Only that and natural keys are reliable to exactly work with data)

But creating a system that reliable reuses ids is hard - especially when it should happen during runtime with multiple clients actually using the data. You would need to spend a lot of time for synchronizing the usage of ids - per table!

---

ids are nothing the user should get into touch with. The user shouldn't have any different experience, whether the id of an element is 3 or 3487563673. If it is just about displaying continuing numbers in an enumeration, you should either use a program-language based variable for that, or mysqls row_number() function to get the display-information.

Answer 3

The formal answer is that you reseed the value. Here is one description of what to do.

The real answer, though, is not to worry about it. Identity values are not guaranteed to be sequential and there are a variety of reasons why gaps would appear. Even without deletes. You should get used to the gaps in the auto-incremented identity.

If you need values that sequential with no gaps, there are solutions. One is to calculate the value on output:

row_number() over (partition by id)

works quite well.

The other is to write a trigger to enforce all the rules that you want. Just so you know, though, the folks who write database engines know that everyone would want the identity to be gap-less. However, they probably don't want to take the performance hit to guarantee this, so they have a reasonable compromise in terms of functionality and performance. if you try to enforce sequential-ness without gaps, you will affect performance, particularly for high-volume inserts and deletes.