wood burning stoves
The moose likes JDBC and Relational Databases and the fly likes MySQL resetting auto-increment Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "MySQL resetting auto-increment" Watch "MySQL resetting auto-increment" New topic

MySQL resetting auto-increment

Zach Porter

Joined: Oct 20, 2005
Posts: 2
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.

Jussi Taimiaho
Ranch Hand

Joined: Mar 01, 2004
Posts: 40
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:
[ October 21, 2005: Message edited by: Jussi Taimiaho ]
Zach Porter

Joined: Oct 20, 2005
Posts: 2
Thank you.
Matter resolved.
I agree. Here's the link: http://aspose.com/file-tools
subject: MySQL resetting auto-increment
jQuery in Action, 3rd edition