MySQL - Autoincrement does not increment sequentally if last row was deleted

Question

I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to change that the primary key starts at 6?

CREATE TABLE animals (
 id MEDIUMINT NOT NULL AUTO_INCREMENT,
 name CHAR(30) NOT NULL,
 PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');

Result:
id name
1 dog
2 cat
3 penguin
4 lax
5 whale
6 ostrich

DELETE FROM animals WHERE id = 6;
INSERT INTO animals (name) VALUES
('x');

Result:
id name
1 dog
2 cat
3 penguin
4 lax
5 whale
7 x

Thanks for advice.

Answer 1

This is by design - all DBMS act this was with auto-increment columns.

If they did not external referential integrity could be damaged. For a simple example of this, imagine you are storing URLs for a shortening service using an auto-increment column as the key. You don't know if the shortened URL has been given out to anyone yet, and the database certainly doesn't, so reusing ID 1234 could result in someone's poor granny visiting somenastypornsite.xxx instead of loverlyknitting.org when she clicks http://shortthi.ng/1234 in an old email, instead of getting a "sorry but this link no longer exists in our records" message.

Also if you reset the increment after deleting the last item, will you also be going through all the work (or expecting the database to) of renumbering everything after the 5th item of 5 million when the 5th item is removed? complete with changes to other tables where their are foreign key constraints pointing at the increment column? Such extra work could get very expensive IO-wise.

If you do reset the increment point after deleting the last item, do be very very careful of your transaction isolation levels: You might reset it just as another transaction makes use of the value, leading to errors (or worse, silent failures), unless you make sure that your action is fully 100% isolated.

I generally recommend people working with databases read "SQL Antipatterns" which has a chapter on this issue named "Psuedo-Key Neat Freaks" (which covers the matter in a friendlier manner than the chapter title may imply to some!). Essentially, if the value has meaning beyond being a key (or at most carrying insert order information) then it probably should not be an auto-incrementing column, and if it does have no meaning beyond being a key (or at most carrying insert order information) then gaps should not matter.

Answer 2

You can reset the auto_increment value using the following:

ALTER TABLE MyTable AUTO_INCREMENT = 1234;

You'd need to determine the maximum value present in the table, and add 1 to that value to use in the statement above.