How can I restart my autoincrement in phpmyadmin?

Question

I have some tables in my phpmyadmin with one column that is auto incremented.

The problem is that when I delete some rows from the table (For example the element Car, with index auto incremented 1) and I create another row into the table, the new row have the index 2, but in the table there is only one row.

I want that this second element created to have the index equal to the position of the row, for example, if I have 3 rows that the third element will have the index equals to 3.

I was looking for a method that let me to use my phpMyAdmin like this but I couldn't find anything.

Is it possible? If it is true, what should I have to do? Do I have to create the table again?

Answer 1

It is possible, but you shouldn't do that on production.

The SQL query is:

ALTER TABLE tablename AUTO_INCREMENT = 1

The most important part of this is to prevent overriding. For example you have an user and this user has id of 81 and if you delete this user and the database doesn't remember that this id 81 has ever been taken by an user (and for example, you have some relations - like friend lists) the user that is going to have the same ID will probably have the same data.

So basically, you don't want to reset auto increment values.

Answer 2

Execute this SQL sentence:

ALTER TABLE tablename AUTO_INCREMENT = 1

Answer 3

It is possible. It is not a feature of phpmyadmin, but of mysql. Execute this

ALTER TABLE tablename AUTO_INCREMENT = 1

More info on this on stackoverflow

And in the mysql reference

Answer 4

This is generally a bad idea. Auto increment is used for creating unique ID of the row. Imagine you have a record "1 - John". Then you delete it and add another "1 - Jack". From the point of common database logic, it will seem that John was renamed to Jack (it has the same ID = it is the same entity) rather than it is another record. You should let DB assign new ID to each new record, even with leaving gaps after deleted records.

If you really want to do so, you can modify auto increment value using this query:

ALTER TABLE users AUTO_INCREMENT=123

but it is still not the way auto increment is designed for.

Answer 5

I struggled with this a bit too, then found in the "Operations" tab, there is a setting under "Auto Increment" where you can set the number it will start from, so you can roll it back if you want to do that.