How to reset the auto_increment

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

Moderator: General Moderators

matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

How to reset the auto_increment

Post by matth2004 »

Hi,

Does anyone know how to reset the auto_increment in a column in a mysql database. I have 4 columns and I have auto_increment set on one of the columns. When I delete the data in that table (all of it) and then add a row the number for the auto_increment just keeps going. The only way is to delete and remake the whole table. Does anyone know a quicker way?

Regards,
Matt
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Code: Select all

ALTER TABLE `table`  AUTO_INCREMENT = 0
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

Do you get out of values?
In most cases you don't need to reset an auto-increment column.

Look for reset in user comments.
http://dev.mysql.com/doc/refman/5.0/en/ ... ement.html
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

ou can use the syntax:

Code: Select all

ALTER TABLE table_name AUTO_INCREMENT = 1;
to reset the auto_increment on a particular table.

Further:
There are a couple of system parameters that could be useful to you: auto_increment_offset and auto_increment_increment.

These parameters control the starting point for the auto_increment, and the amount by which the column gets incremented. Beware when using these paramters - make sure you read the manual well.
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post by matth2004 »

Thanks heaps for all this, works like a charm.

Regards,
Matt
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

*sigh*

SQL != PHP.

:roll:
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post by matth2004 »

Also,

Would anyone know how to alter it back one? Say its on 73 with 73 people in the database, then I delete one person and want to set the counter back to 72 so the next person I add will get 73 instead of 74.

Regards,
Matt
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post by wtf »

Why would you want to do that anyway? There's a reason why auto_increment is auto_increment! Is any other table related to that table because if it is, you'll end up killing references which will produce unrelated data. Just leave it as it is.
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post by matth2004 »

No theres no other table related to it. It's just standalone. So does anyone know how to alter it?

Regards,
Matt
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It's intended not to be altered. It can be, but you shouldn't.

If you really want this functionality then you don't need auto_increment set on it.
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post by matth2004 »

Ok then. I'll change it so that members are entered along with a member number instead of auto_increment so it saves me the hassle.

Regards,
Matt
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post by wtf »

matth2004 wrote:Ok then. I'll change it so that members are entered along with a member number instead of auto_increment so it saves me the hassle.

Regards,
Matt
What if member enters the number that already taken? You'll have to check for that. That means, member could spend hours trying to enter a number thats available. Why force the user to enter thata that he/she doesn't really understand what is it for. You're just looking for trouble but hey, go ahead then write about your experiene and what you've learned.

:D
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

matth2004, I think you are missing the concept of an auto_increment field. The point is that the value in that field, which is usually the primary key, will always be unique -- e.g. no duplicates. Typically those numbers are not visible to users, but are used as keys for relations. I does not matter whether the numbers are sequential, only that they are unique. If you want sequential values for users to use, and to reuse abandoned values, then you should use a different scheme.
(#10850)
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post by matth2004 »

Yes I know all of this. That's the thing, it's a football tipping manager. Two members will not have the same number already because the human being who runs the competition will already have every families name written down and a member number for them. They just enter all the members into it. It was a quick system I whipped up in 20 minutes just to make life easier.

Thanks for all your help,

Regards,
Matt
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

I sounds like for that you don't need auto_increment at all.
(#10850)
Post Reply