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?

This should not be an issue..... autoincrement is __not__ about creating consecutive id values, but about creating unique id values; you should never write code that is dependent on consecutive values — Mark Baker, Jun 08 '15 at 14:28

score 2 · Answer 1 · answered Jun 08 '15 at 14:30

2

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.

answered Jun 08 '15 at 14:30

amik

5,613
3
37
62

_"When 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) "_ Not really, not if your foreign key constraints cascade properly. – Lightness Races in Orbit Jun 08 '15 at 14:32
@LightnessRacesinOrbit All my tables contains cascade property. If I change my index column. It will be changed in all the tables, it's correct? – Francisco Romero Jun 08 '15 at 14:33
@Error404: Yes, it should be. Test to be sure. – Lightness Races in Orbit Jun 08 '15 at 14:47

score 1 · Answer 2 · answered Jun 08 '15 at 14:27

1

Execute this SQL sentence:

ALTER TABLE tablename AUTO_INCREMENT = 1

answered Jun 08 '15 at 14:27

Ramy Deeb

583
6
15

score 1 · Accepted Answer · answered Jun 08 '15 at 14:27

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.

answered Jun 08 '15 at 14:27

Ivanka Todorova

9,964
16
66
103

1

_"the user that is going to have the same ID will probably have the same data"_ No, not if your foreign key constraints cascade properly. – Lightness Races in Orbit Jun 08 '15 at 14:32
Yep, that's right. I hadn't made it clear enough. Basically, the uniqueness of the column is lost. *imo* – Ivanka Todorova Jun 08 '15 at 14:33
@IvankaTodorova Thank you man! And where I have to execute this line? – Francisco Romero Jun 08 '15 at 14:39
@Error404, you said that you're using phpmyadmin. Just paste it in the SQL tab and hit execute. – Ivanka Todorova Jun 08 '15 at 14:40
If you're assuming that an auto-incremented ID is always going to be a unique value, you've _already_ lost. IMO. – Lightness Races in Orbit Jun 08 '15 at 14:46
@LightnessRacesinOrbit, I'm sorry, can you elaborate? I read somewhere that by using an Identity Column, which MySQL's AI column is equivalent , uniqueness is guaranteed. – Ivanka Todorova Jun 08 '15 at 15:01
1

@IvankaTodorova: Well, wherever you read it, it was wrong. `AUTO_INCREMENT` columns auto-generate values. That's it. A `UNIQUE` key on that column isn't going to make such columns unique _for all time_ in the face of row deletions and the like, and you can modify the auto-generated value to your heart's content. – Lightness Races in Orbit Jun 08 '15 at 15:03

score 1 · Answer 4 · answered Jun 20 '15 at 15:50

1

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.

answered Jun 20 '15 at 15:50

Jesse

11
4

score 0 · Answer 5 · edited May 23 '17 at 12:14

0

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

edited May 23 '17 at 12:14

Community

1
1

answered Jun 08 '15 at 14:27

Raymond

3,630
2
19
22

How can I restart my autoincrement in phpmyadmin?

5 Answers5