CODEWITHSUNDEEP
mysql
hetelek
hetelek

Reputation: 3886

Fixing auto-increment ID in MySQL table

Hey I was wondering how I could fix my auto increment ID... it seems that everytime I add a row to the table, the ID is 1 off. So here's an example.

My table currently looks like this:

MySQL Table

Now, as you can see, the last element jumps from 198 to 200. I want this one to be 199. So say I manually change that last value(200) to 199, it will obviously work, but the next time my script adds a row to the table, it will be on off again(201). Any idea how I could fix it? Thanks for any help, sh042067.

Upvotes: 0

Views: 5088

Answers (2)

Daren Chandisingh
Daren Chandisingh

Reputation: 2165

Jim's right that you should find out the root cause of the 'missing' id.

But if you really just want to re-set the auto-increment counter, you can do this:

ALTER TABLE my_table AUTO_INCREMENT=200;

That should make the next auto-increment id value 200. (Assuming it's MySQL)

Upvotes: 3

Jim
Jim

Reputation: 1568

This probably happened because you removed a row from your table and then added a new row afterward. Is there a reason that you need syncrosy for this table in your id column? Are you planning on removing, replacing, and/or adding new rows. If so, this column will not remain in sync and you'll find that whatever steps you take to fix this problem (could be as easy as dropping the column and recreating it) will need to be done as often as you are removing and adding data.

In short, we need more information about what you're trying to accomplish.

Upvotes: 0

Related Questions