How to make an id auto_increment from where it last left off after a record delete?

Question

This isn't that big of a deal but I was wondering if it can be done. Right now when I insert a new record, the id field which is auto_increment and set as primary key increases it's value by one:

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

But if I delete record 2, the next one I insert is going to have an id of 3. Is there any way to make it have an id of 2 automatically (without having to manually insert it)?

Answer 1

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

Answer 2

The auto_increment counter (at least, with InnoDB) is kept in the table meta-data, and is independant of the data that's in the table : it is incremented when some data is inserted, and that is all.

So, no, it's not possible to have your auto_increment column get the biggest value + 1 -- or, at least, not at insert-time.

I suppose a solution could be to alter the table, to force the auto_increment counter to a new value, though. Not sure you can set it to a lower value than it's current one, though (which is precisely what you're trying to do).

For more informations, see this paragraph on the manual page of alter table (quoting, emphasis mine) :

To change the value of the AUTO_INCREMENT counter to be used for new rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;

You cannot reset the counter to a value less than or equal to any that have already been used.
For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one.
For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

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 about the way you're doing this, though, as trying to use a too-low value, with InnoDb, will result in no change.

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