wood burning stoves 2.0*
The moose likes JDBC and the fly likes MySQL resetting auto-increment Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL resetting auto-increment" Watch "MySQL resetting auto-increment" New topic
Author

MySQL resetting auto-increment

Zach Porter
Greenhorn

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.

Thanks.
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:
http://en.wikipedia.org/wiki/Surrogate_key
http://en.wikipedia.org/wiki/Natural_key
[ October 21, 2005: Message edited by: Jussi Taimiaho ]
Zach Porter
Greenhorn

Joined: Oct 20, 2005
Posts: 2
Thank you.
Matter resolved.
 
 
subject: MySQL resetting auto-increment
 
Similar Threads
Primary Key Class in CMP & BMP
Getting primary keys
how to know the last auto-increment number using JDBC
insert new parent/child row. How to handle new primary/foreign key inserts
Java DB - Reset auto-increment primary key