Page 1 of 1
Is it possible to reset the Primary Key?
Posted: Mon Jul 30, 2007 6:46 am
by legend986
I was wondering where I could reset a primary key that had an auto_increment... Any advice please?
Posted: Mon Jul 30, 2007 7:08 am
by Charles256
Umm.Through phpmyadmin hit the "empty" tab when viewing the table.. mind you this deletes everything in table..
Re: Is it possible to reset the Primary Key?
Posted: Mon Jul 30, 2007 8:24 am
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;
Posted: Mon Jul 30, 2007 11:54 am
by legend986
Thanks a lot... I'll try it now...
Posted: Wed Aug 01, 2007 9:15 am
by legend986
SOrry but that is not working. Is there a way I could do this without loosing all me data?
Posted: Wed Aug 01, 2007 9:19 am
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.)
Posted: Wed Aug 01, 2007 10:14 am
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!
Posted: Thu Aug 02, 2007 9:39 pm
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!!
Posted: Thu Aug 02, 2007 10:16 pm
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.
Posted: Thu Aug 02, 2007 10:43 pm
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...