MySQL: change the columns of my PRIMARY KEY

Question

I have a table with the following primary key:

PRIMARY KEY (`Id`,`InputOutputConfigurationServerAccountId`,`Identifier`) 

I want to change this so that PK is only an Id column. How to do it?

Answer 1

The problem is that your Id is defined as auto_increment . You need to change it to a simple int first, make the change, and then put it back in auto_increment.
Try the following:

 ALTER TABLE SO1 MODIFY COLUMN ID INT; ALTER TABLE SO1 DROP PRIMARY KEY; ALTER TABLE SO1 ADD PRIMARY KEY (id); ALTER TABLE SO1 MODIFY COLUMN ID INT AUTO_INCREMENT; 

Here's the test above (btw, I got the error you mentioned in your comment on another answer if I didn't change the first column):

 drop table if exists SO1; create table SO1 ( id int auto_increment, InputOutputConfigurationServerAccountId int, Identifier int, PRIMARY KEY (`Id`,`InputOutputConfigurationServerAccountId`,`Identifier`) ); ALTER TABLE SO1 MODIFY COLUMN ID INT; ALTER TABLE SO1 DROP PRIMARY KEY; ALTER TABLE SO1 ADD PRIMARY KEY (id); ALTER TABLE SO1 MODIFY COLUMN ID INT AUTO_INCREMENT; show create table SO1; 

Everything is done OK. Final result:

 CREATE TABLE `SO1` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `InputOutputConfigurationServerAccountId` int(11) NOT NULL DEFAULT '0', `Identifier` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) ) 

Answer 2

• Delete old PC ALTER TABLE table_name DROP PRIMARY KEY
• Add New PC ALTER TABLE table_name ADD PRIMARY KEY (Id)