CODEWITHSUNDEEP
mysqldatabase
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40653

MySQL: alter the columns of my PRIMARY KEY

I have a table with the following primary key:

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

I want to modify this so that the PK is only the Id column. How do I do this?

Upvotes: 19

Views: 59963

Answers (2)

Mike
Mike

Reputation: 144

  1. Remove the old PK ALTER TABLE table_name DROP PRIMARY KEY
  2. Add the new PK ALTER TABLE table_name ADD PRIMARY KEY (Id)

Upvotes: 6

Bohemian
Bohemian

Reputation: 425448

The problem seems to be that you have Id defined as auto_increment. You need to first change it to just plain int, them make the changes, then turn it back to auto_increment.
Try this:

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 a test of the above (btw, I got the error you mentioned in your comment on other answer if I didn't first modify the 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;

All executed OK. Final Output:

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`)
)

Upvotes: 37

Related Questions