Reset auto increment after deleting a table row

It is often the case that a column such as the ID column on a table will auto increment. This simply means that the next insert into the table will have an ID that is one more then the previous one and therefore all ID's will be unique.

However, if you delete a row from the table, the table will auto increment as if the row had not been deleted at all. The result is a gap in the sequence of numbers. This is normally not that much of an issue, but you may want to reset the auto increment field.

There are two simple steps in resetting an auto increment field:

Find the highest number in the auto increment field

To find the highest number, run the following SQL command:

Replace 'column' with the name of the auto incrementing column. Replace table with the name of the table.

Reset the auto increment field

The next step is to take the number that you get from the first command and auto increment from there. So add one to that number and run the following command:

Replacing 'number' with the result of the previous command plus one and replacing table with the table name.

If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.