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?
Setting a new value to a field with an auto_increment attrib
Moderator: General Moderators
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".
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".