Page 1 of 1

Setting a new value to a field with an auto_increment attrib

Posted: Mon Feb 19, 2007 3:15 am
by NEWDAY
I'm trying to design an internet forum these days ...

In my database i have got a field called (ID) of type integer which represents the id of the topics and i have assigned it an (auto_increment) attribute...

My problem is that when i delete all the topics from the forum and after that add a new topic to the database the (ID) of that new topic continues to increment from the last deleted topic,
so how can i set the (ID) of the new topic to 1 in case all the previous topics were deleted?

Posted: Mon Feb 19, 2007 4:49 am
by dude81
drop the table and rebuild the table structure. That will do

Posted: Mon Feb 19, 2007 7:05 am
by onion2k
Onion's Webdev Rule #1: Never, ever mess with auto incrementing values.

If you really need to reset it to 1 you've got several options. First, like dude81 says, you can rebuild the table. I don't like that method because it's a pain to do it from a script. Second you can use "TRUNCATE table". That will drop all the data and reset the auto increment value. And lastly you can use "ALTER TABLE table AUTO_INCREMENT = 1;". That's about the best solution in my opinion because it does exactly what you need and no more.

But, like Rule #1 says, you really shouldn't ever reset it. If I've bookmarked your forum using http://www.domain.com/forum/view.php?id=123, and you reset the auto increment value, once there are 123 threads my bookmark will point to the wrong place. It'll point to a different thread to the one I bookmarked. If you didn't reset it then my bookmark will always point to a deleted thread and you can display a page accordingly, eg "This thread has been deleted".