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:

SELECT MAX( `column` ) FROM `table` ;

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:

ALTER TABLE `table` AUTO_INCREMENT = number;

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.

Comments

EXCELLENT!

To reset the auto_increment field after clearing a table, you can use
TRUNCATE 'table'.

This resets the auto_increment value to 0, and saves you from doing it explicitly with the ALTER TABLE... command

Blair Wadman's picture

Good point, thanks BartG

But I need to delete only one row at middle. what can I do for that? please help me

BartG is right, but that is if you drop the whole table.
As far as I understand Blair's point was to have continuous id's when you delete only a few rows.
So while Bart's response is true, it is not relevant to Blair's excellent post.

great stuff!

what if I delete the first or second row?

tahnk u very much..............

ty........very very help ful............

10x, this is very useful for me :)

in drupal 7 users table its a completely different story.
One which I haven't worked out yet.
Drupal is managing the autoincrement, not mysql

Thank you

As far as I understand Blair's point was to have continuous id's when you delete only a few rows.
Lucas

Thank you for your excellent post. I hope the exisiting id's(auto-increment column which we are modifying) of the table do not get affected by this.

Thanks so much, u saved my ICT coursework!!

Thank you for the post. I benefited from the "ALTER TABLE" statement to reset my auto increment count.

I'm in need of knowing if with all Blair explained works also in visual basic 2010

Is it worth locking the table to prevent any changes happening while selecting and then updating the table to ensure that you don't set the increment incorrectly if the table is altered by another process?

  1. if (lock_acquire('table')) {
  2. $result = db_query('SELECT MAX(id) as id FROM table')->fetchObject();
  3. db_query('ALTER TABLE table AUTO_INCREMENT = ' . $result->id);
  4. }
  5. lock_release('mercury_price');

Very useful.
Thank u!

I was looking for this solutions since 2-3 days. Glad that I found it finally.

Very Useful, Thank You

I was looking for this solutions since 2-3 days. Glad that I found it finally.

Add new comment