Setting a new value to a field with an auto_increment attrib

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
NEWDAY
Forum Newbie
Posts: 12
Joined: Wed Jan 31, 2007 5:15 pm

Setting a new value to a field with an auto_increment attrib

Post 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?
User avatar
dude81
Forum Regular
Posts: 509
Joined: Mon Aug 29, 2005 6:26 am
Location: Pearls City

Post by dude81 »

drop the table and rebuild the table structure. That will do
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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".
Post Reply