CODEWITHSUNDEEP
mysql
nash
nash

Reputation: 725

how to add new column to existing composite primary key

I have encountered a problem in that I already have a composite primary key in a MYSQL table. But now I have added another column to that table and due to some requirement changes, I have to modify that composite primary key in such a way that I need to add that previously mentioned column to that composite primary key list. Can anyone tell me how to alter that table without dropping existing composite primary key. I am doing this in a Rails project

Upvotes: 31

Views: 23921

Answers (2)

user2536480
user2536480

Reputation: 11

but if a key no exist? example:

ALTER TABLE xxxx ADD id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id,id2,id3);

Upvotes: 1

Jeremy Stein
Jeremy Stein

Reputation: 19661

You can't alter the primary key. You have to drop and re-add it:

ALTER TABLE MyTable
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (old_col1, old_col2, new_col);

Upvotes: 44

Related Questions