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.
Michael
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:
>
>>Nils,
>>
>> >So the task is to recreate the current primary key (c_serial),so that the
>> >current index would start with 1,2,3,4,....
>>
>>SET @i=0;
>>UPDATE c1 SET c_serial=(@i:=@i+1);
>>
>>PB
>>
>>-----
>>
>>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`)
>>>) TYPE=MyISAM
>>>----
>>>
>>>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
>>>mind ;-).
>>>
>>>I know this shouldnt be too difficult, but perhaps I am just too long in
>>>front of the screen. ;-)
>>>
>>>Best regards
>>>
>>>Nils Valentin
>>>Tokyo / Japan
>>>
>>>www.be-known-online.com
