If the column was full of 0s when you recreated it, that means you
forgot to make it AUTO_INCREMENT. Since it didn't complain about
duplicate key entries, you also didn't make it a PRIMARY KEY. That is,
you must have done something like
ALTER TABLE c1 ADD c_serial INT NOT NULL;
Peter's solution, then, is incomplete. You've got the values you want
in the current rows, but c_serial still isn't the primary key, and the
next row added will have the default value, 0.
What you should have done was
ALTER TABLE c1 ADD c_serial INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
Since you're half way there, you should be able to fix what you have with
ALTER TABLE c1
CHANGE c_serial c_serial INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
See the manual <http://dev.mysql.com/doc/mysql/en/alter-table.html> for
more on ALTER TABLE.
P.S. Reordering the ids is usually a bad idea, and rarely, if ever,
necessary. If unsequenced ids bother the admin, he/she needs to get
over it. Trying to keep ids in order complicates things unnecessarily.
Nils Valentin wrote:
> Hi Peter,
> thanks a bunch,
> I new that it must have been something simple like this. I am just no
> programmer. ;-)
> Thanks a bunch !!
> Best regards
> Nils Valentin
> Tokyo / Japan
> On Sunday 10 April 2005 23:41, Peter Brawley wrote:
>> >So the task is to recreate the current primary key (c_serial),so that the
>> >current index would start with 1,2,3,4,....
>>UPDATE c1 SET c_serial=(@i:=@i+1);
>>Nils Valentin wrote:
>>>Hi MySQL fans ;-),
>>>I was just asked recently with the task to recreate a tables index
>>>"gracefully" on a MyIsam table.
>>>This is the table layout:
>>>CREATE TABLE `cl` (
>>> `c_serial` int(11) NOT NULL auto_increment,
>>> `cname` tinytext NOT NULL,
>>> `cl_vals` text NOT NULL,
>>> `utime` int(11) NOT NULL default '0',
>>> PRIMARY KEY (`c_serial`)
>>>So the task is to recreate the current primary key (c_serial),so that the
>>>current index would start with 1,2,3,4,....
>>>Currently the numbers are all over the place. To make this simple (for
>>>now) there are no dependencies to other tables so the order of the index
>>>doesnt really matter, its more for the admins peace of mind ;-)
>>>I tried the obvious first, removing the column completely and adding the
>>>primary key, which left me with a lot of "0"s - which is not what I had in
>>>I know this shouldnt be too difficult, but perhaps I am just too long in
>>>front of the screen. ;-)
>>>Tokyo / Japan