You are better off doing the following DROP TABLE IF EXISTS users_new; CREATE TABLE users_new ( uname varchar(20) NOT NULL default '', passwd varchar(15) NOT NULL default '', fname varchar(25) NOT NULL default '', lname varchar(40) NOT NULL default '', dir varchar(28) NOT NULL default '', pict varchar(50) NOT NULL default '', level int(4) NOT NULL default '0', email varchar(40) NOT NULL default '', rank int(4) NOT NULL default '0', dgroup int(4) NOT NULL default '0', parent varchar(20) NOT NULL default '', seminar int(11) NOT NULL default '0', getnewsletter int(11) default '0', id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), KEY uname_users (uname), KEY dir_users (dir), KEY seminar_users (seminar), KEY user_lvl_idx (level) ); INSERT INTO users_new (uname,passwd,fname,lname,dir,pict,level,email,rank,dgroup,parent,seminar,getnewsletter) SELECT uname,passwd,fname,lname,dir,pict,level,email,rank,dgroup,parent,seminar,getnewsletter FROM users; DROP TABLE users; ALTER TABLE users_new RENAME TO users;
That's it. You may want to create the table as mentioned before. However, if you prefer the 'uname' as the primary key, then create the table like this instead: CREATE TABLE users_new ( uname varchar(20) NOT NULL default '', passwd varchar(15) NOT NULL default '', fname varchar(25) NOT NULL default '', lname varchar(40) NOT NULL default '', dir varchar(28) NOT NULL default '', pict varchar(50) NOT NULL default '', level int(4) NOT NULL default '0', email varchar(40) NOT NULL default '', rank int(4) NOT NULL default '0', dgroup int(4) NOT NULL default '0', parent varchar(20) NOT NULL default '', seminar int(11) NOT NULL default '0', getnewsletter int(11) default '0', id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (uname), UNIQUE KEY id (id), KEY dir_users (dir), KEY seminar_users (seminar), KEY user_lvl_idx (level) ); ----- Original Message ----- From: Randy Paries <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 9:23:55 PM GMT-0500 US/Eastern Subject: help trying to add an autoincrement col to an exisiting table Hello, currently i have the following table structure CREATE TABLE users ( uname varchar(20) NOT NULL default '', passwd varchar(15) NOT NULL default '', fname varchar(25) NOT NULL default '', lname varchar(40) NOT NULL default '', dir varchar(28) NOT NULL default '', pict varchar(50) NOT NULL default '', level int(4) NOT NULL default '0', email varchar(40) NOT NULL default '', rank int(4) NOT NULL default '0', dgroup int(4) NOT NULL default '0', parent varchar(20) NOT NULL default '', seminar int(11) NOT NULL default '0', getnewsletter int(11) default '0', PRIMARY KEY (uname), KEY uname_users (uname), KEY dir_users (dir), KEY seminar_users (seminar), KEY user_lvl_idx (level) ) TYPE=MyISAM; I want to add an autoincrement field when i first tried i got the error Incorrect table definition; there can be only one auto column and it must be defined as a key so then i tried ALTER TABLE `users` DROP PRIMARY KEY; ALTER TABLE `users` ADD PRIMARY KEY (id); ALTER TABLE `users` CHANGE `id` `keyid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT; and i get the error Duplicate entry '0' for key 1 Can some one please tell me what i am doing wrong Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
