[SOLVED] Resetting auto-increment in database

[cliftonbazaar]

At the moment I am testing my database and inserting and deleting records, this pushes the auto-increment number through the roof.

 

When I have finished testing is there a way of resetting this?

[jackpf]

Truncate or drop your table

[Maq]

I found this reply for a similar problem on MySQL,

 

In order to reset the auto_increment, in a situation where some of the most recently added rows were deleted, use:

 

ALTER TABLE theTableInQuestion AUTO_INCREMENT=1234

 

and future insertions will be numbered from 1234 again (unless you still had rows numbered greater than 1234, and then the future insertions will start from the greatest number + 1 ).

 

 

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

 

 

Hope this helps.

 

[jackpf]

Wow - didn't know you could do that. Tis pretty useful.

[Maq]

Wow - didn't know you could do that. Tis pretty useful.

 

Me either, just found it on Google.  As far as helpfulness, I've never actually needed to do something like this.  But I could see how such large numbers could get annoying.

[jackpf]

I agree. Oddly, my users table suddenly started incrementing from 60,000 one day, so I tried changing user's IDs back to their proper value but new users still incremented from 60,000. I think I shall try this

[cliftonbazaar]

Thanks for all the replies; just about to reset it now

[Maq]

I agree. Oddly, my users table suddenly started incrementing from 60,000 one day, so I tried changing user's IDs back to their proper value but new users still incremented from 60,000. I think I shall try this

 

Yes, it's meant to do this to retain its "uniqueness".  But ALTERing the table this way, should work.