Re: Recreating primary index on MyIsam table

[Michael Stassen]

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:[EMAIL PROTECTED]);
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]