Is it possible to reset the Primary Key?

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
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Is it possible to reset the Primary Key?

Post by legend986 »

I was wondering where I could reset a primary key that had an auto_increment... Any advice please?
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post by Charles256 »

Umm.Through phpmyadmin hit the "empty" tab when viewing the table.. mind you this deletes everything in table..
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Is it possible to reset the Primary Key?

Post by superdezign »

legend986 wrote:I was wondering where I could reset a primary key that had an auto_increment... Any advice please?

Code: Select all

alter table `table` auto_increment = 0;
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Thanks a lot... I'll try it now...
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

SOrry but that is not working. Is there a way I could do this without loosing all me data?
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Got the solution :) Here's how to do it for people who need it:

For those that are looking to "reset" the auto_increment, say on a list that has had a few deletions and you want to renumber everything, you can do the following.

DROP the field you are auto_incrementing.
ALTER the table to ADD the field again with the same attributes.

You will notice that all existing rows are renumbered and the next auto_increment number will be equal to the row count plus 1.

(Keep in mind that DROPping that column will remove all existing data, so if you have exterior resources that rely on that data, or the numbers that are already there, you may break the link. Also, as with any major structure change, it's a good idea to backup your table BEFORE you make the change.)
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

I know there's been a solution posted, but I just wanted to add this...

Messing with your primary key is a BAD THING. Yes you can do it, but you really should avoid it at all costs. If this is a temporary or prototype database (or a single table...), well, all rules go out the window, but on anything more permanent, altering your primary key is asking for trouble.

I'm sure the OG poster has reason, but as a warning to the db newbies, don't do this!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

ReverendDexter wrote:I know there's been a solution posted, but I just wanted to add this...

Messing with your primary key is a BAD THING. Yes you can do it, but you really should avoid it at all costs. If this is a temporary or prototype database (or a single table...), well, all rules go out the window, but on anything more permanent, altering your primary key is asking for trouble.

I'm sure the OG poster has reason, but as a warning to the db newbies, don't do this!
THANK YOU, ReverendDexter! Newcomers often want to reassign primary keys "to keep it tidy" or "to save wasted numbers", but it is BAD, BAD, BAD!! Autonumbers are not for human beings to deal with, they are entirely for the database engine to insure that there are no duplicate keys. LEAVE 'EM ALONE!!
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Also, if you have any links that use a primary key in the URL (which is commonly used in query strings to ensure the data loads fast by using the primary key to retrieve it), then your links would become inaccurate. W3C insists that your links remain generally permanent.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

califdon wrote:
ReverendDexter wrote:I know there's been a solution posted, but I just wanted to add this...

Messing with your primary key is a BAD THING. Yes you can do it, but you really should avoid it at all costs. If this is a temporary or prototype database (or a single table...), well, all rules go out the window, but on anything more permanent, altering your primary key is asking for trouble.

I'm sure the OG poster has reason, but as a warning to the db newbies, don't do this!
THANK YOU, ReverendDexter! Newcomers often want to reassign primary keys "to keep it tidy" or "to save wasted numbers", but it is BAD, BAD, BAD!! Autonumbers are not for human beings to deal with, they are entirely for the database engine to insure that there are no duplicate keys. LEAVE 'EM ALONE!!
:) You're right sir atleast in my case. Being a beginner I failed to understand the whole purpose of a Primary Key. I thought its just for numbering the records. Though I did know that it can cause a lot of problems, I was dare enough to do it because I was resetting it on a stand alone table. I shall take your advice and will never mess with it again...
Post Reply