How to make the identifier auto_increment from the place where it was last after deleting the record?

Question

This is not such a big deal, but I was wondering if this could be done. Right now, when I insert a new record, the id field, which is auto_increment and set as the primary key, increments its value by one:

id | first_name | last_name ------------------------------ 1 | John | Smith 2 | Jane | Doe 

But if I delete entry 2, the next insert will have identifier 3. Is there a way to make it have identifier 2 automatically (without having to manually insert it)?

Answer 1

The auto_increment counter (at least with InnoDB) is stored in the metadata of the table and is independent of the data in the table: it increases when some data is inserted, and that’s all.

So no, it's not possible for the auto_increment column to get the highest value + 1 - or at least not during insertion.

I believe that the solution could be in the alter table to force the auto_increment counter to a new value. Not sure if you can set it to a lower value than the current one though (this is exactly what you are trying to do).

For more information, see this paragraph on the alter table page (citation, emphasis mine):

To change the value of the auto_increment counter that will be used for new lines, do the following:

 ALTER TABLE t2 AUTO_INCREMENT = value; 

You cannot reset the counter to a value less than or equal to any that has already been used . For a MyISAM image, if the value is less than or equal to the maximum value currently in the auto_increment column, reset to the current maximum plus one.
For InnoDB, if a value less than the current maximum value in the error column does not occur, and the current sequence value does not change.

So, according to the manual:

• You can change the value of auto_increment
• You can set it to the maximum value (plus one?) Of the column in the table.
  • Be careful how you do this, but if you try to use a too low value with InnoDb, there will be no change.

However, note that using an alter table should probably not be done too often ...

Answer 2

 DELETE FROM tbl WHERE id = 4; ALTER TABLE tbl AUTO_INCREMENT = MAX(id) + 1;