You'll need three alter statements: - one to add the new column - one to get rid of the old primary key - one to set the new column as the primary key
Something like this, i.e. I've left out some details of the syntax: ALTER TABLE FOO ADD USERID CHAR(8); ALTER TABLE FOO DROP PRIMARY KEY; ALTER TABLE FOO ADD PRIMARY KEY(USERID); Your basic mistake is that you're trying to make all the changes in one statement; SQL pretty much forces you to change each aspect of the table in a separate ALTER TABLE statement. Rhino -----Original Message----- From: bruce [mailto:[EMAIL PROTECTED] Sent: Monday, June 13, 2005 10:30 AM To: mysql@lists.mysql.com Subject: alter table - add a column hi.. i have a table that i want to add a column to, and define the columm to be the primary key. however, i already have a column in the table that's used as the primary. can someone tell me the approach/commands i should use to get my results? table foo username varchar(50), primary what i want... userid int(), primary username varchar(50) with userid listed before username!! can someone tell me what the commands are that i need to enter to get this!! i thought something like, alter table foo add userid int(8), primary first (but i couldn't get it to work.. errors) and then i couldn't see how to remove the primary key fom username... thanks -bruce [EMAIL PROTECTED] -- 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]
