how to add new column to existing composite primary key

Question

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

Note that I have foreign keys to some of the columns that are in the composite primary key — nash, Feb 25 '10 at 15:00
A foreign key TO a partial primary key? That's bad mojo. You really should switch to an auto-increment primary key. — MindStalker, Feb 25 '10 at 15:16
Dropping a key shouldn't drop the values - why can't you drop the PK? — reech, Feb 25 '10 at 15:44

score 42 · Accepted Answer · answered Feb 25 '10 at 16:50

42

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

answered Feb 25 '10 at 16:50

Jeremy Stein

19,171
16
68
83

This is the way to do it if there is something preventing you from dropping the primary key – PrashanD Jun 27 '17 at 08:34

score 1 · Answer 2 · edited Dec 06 '13 at 16:26

1

but if a key no exist? example:

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

edited Dec 06 '13 at 16:26

bensiu

24,660
56
77
117

answered Dec 06 '13 at 16:04

user2536480

11
1

how to add new column to existing composite primary key

2 Answers2

Linked