Rename a primary key column

[clarissa1996]

Hi all,

I have the table t1 with the column A.
The column A is defined as:
INT NOT NULL PRIMARY KEY AUTO_INCREMENT.

I need to rename A -> B but the following command report an error:

ALTER TABLE t1 CHANGE A B INT NOT NULL PRIMARY KEY AUTO_INCREMENT

ERROR 1068: Multiple primary key defined

Any help is appreciated.

Clarissa

 

[jmeckley]

if B can contain null values than it cannot be the primary key.

I have also found that you will need to remove any foreign keys that column A links to before you can drop column A as the primary key and change it to column B.

You will also need to remove the primary key from column A before making column B a primary key.

Jason Meckley
Database Analyst
WITF