MySQL resetting auto-increment

[Zach Porter]

As a newbie, I had failed to realize that when using auto-increment on the Primary Key field I didn't need to enter a value in that field as I populated my database manually one row at a time.

Subsequently, using phpMyadmin, I deleted rows and added rows and in between I un-set and reset the auto-increment function on the Primary Key field. I also Indexed and then Unindexed that same field.

Now I have this value showing in the Indexes grid of phpMyadmin:

Keyname: Primary, Type: Primary, Cardinality 23

But, when I add a new row, the value comes up as not 24 (as it should be) but 29.

Also, if I leave the value of the Primary Key blank (which is the correct method) but fill in the other values for the row and click Add Row and Return to the table row display page, I get 2 blank rows, with the value of the Primary Key field +5 of where it ought to be.

Is there a way to reset the auto-increment value?

I hope this is the right forum to post this in.

Thanks.

[Jussi Taimiaho]

Now you should consider why do you want to reset this? If you are using surrogate keys properly, the value of the field should not matter at all. Whether the value is 24, 124, or 1234567, should make no difference to you application. However if the value does matter, then you should either
a) not use it as primary_key
b) consider it as a natural key, and therefore not use auto_increment

Also you can check out:
http://en.wikipedia.org/wiki/Surrogate_key
http://en.wikipedia.org/wiki/Natural_key
[ October 21, 2005: Message edited by: Jussi Taimiaho ]

[Zach Porter]

Thank you.
Matter resolved.