Re: Recreating primary index on MyIsam table
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 0s - 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]
Recreating primary index on MyIsam table
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 0s - 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]
Re: Recreating primary index on MyIsam table
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 0s - 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 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recreating primary index on MyIsam table
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 0s - 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 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005 -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
