Is it possible to reset the Primary Key?
Moderator: General Moderators
Is it possible to reset the Primary Key?
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
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: Is it possible to reset the Primary Key?
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;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.)
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.)
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
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!
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!!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!
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
califdon wrote: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!!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!