Recreate a auto increment column in mysql

From The IT Community
Jump to: navigation, search

If you have a MySQL table which uses a auto increment column, you might have noticed that sometimes this column can skip a few numbers, especially if you delete from the table.

Since this can cause problems with queries, here is how to recreate a auto increment column in MySQL:

For this example lets assume that your table is called websites and the auto increment column is called id.

So, first drop the column from the table:

alter table websites drop id;

Then, initialize the auto_increment:

alter table websites auto_increment=1;

Last, all you have to do is recreate the auto increment column:

alter table websites add id int not null auto_increment primary key first;

In this example, id is your primary key. If it isn´t in your table, just leave it out.

Was this article helpful? Then please donate to keep The IT Community alive...

If you found this article helpful please share it, comment and help others by writing your own article.

Translate this page: