How to reset/continue the auto increment? (phpMyAdmin)

[OmniX]

I am using phpMyAdmin and as I delete a row, it stuffs up the auto increment once I insert a new row.

Example:
Delete - 21
Insert - 22 (should be 21)

Thanks, Regards X

[Shanti C]

i think,its not possible because of auto incremented one....

[OmniX]

There must be a fix or way around.

This is a big problem which ive seen re occuring all the time.

Any Ideas?

Thanks, Regards X

[mwasif]

Why you want it to re-order? Still you can use
ALTER TABLE table_name AUTO_INCREMENT=21;
More on this http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

[mwasif]

Keep one thing in mind, if you have records till 30 and deletd 21. The next auto increment value should be 31 not 21. If you run the above provided query, you will be in trouble in this scenario.

[OmniX]

Oh yes thats fine but im just wondering cause its a bug that been around for ages.

Example:
- I add a row with the wrong values and that takes up the auto increment of number 31
- I delete the row just added and insert another row and the auto increment number is 32

Can you see how annoying this is?

I think the idea is to ALTER rather than DELETE but :(

Anyone have any ideas be much appericated.

Thanks, Regards X

[mwasif]

ALTER TABLE query takes a longgggggggggg time on a biggggggggggg table.

I don't think you need to remember the numbers, let this work done by the database.