List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:April 11 2005 2:39pm
Subject:Re: Recreating primary index on MyIsam table
View as plain text  
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
Thread
MySQL vs. DB2Rhino9 Apr
  • Re: MySQL vs. DB2Rhino10 Apr
    • Re: MySQL vs. DB2Peter Brawley10 Apr
      • Recreating primary index on MyIsam tableNils Valentin10 Apr
        • Re: Recreating primary index on MyIsam tablePeter Brawley10 Apr
          • Re: Recreating primary index on MyIsam tableNils Valentin10 Apr
            • Re: Recreating primary index on MyIsam tableMichael Stassen11 Apr