Add a field to a composite primary key
Posted on 2008-11-19
In a MySQL database, I have a table already created with several fields. Let's say field1 and field2 make up my composite primary key. What command would I enter to add a third field to my composite primary key? My end result would mean that my composite primary key is now field1, field2 and field3. I want to be able to do this without having to delete and recreate the entire table... I've already got a ton of data in there!
These are the 2 queries I've tried:
1. alter table table_name add NEW_field int primary key after field2;
2. alter table table_name add primary key (EXISTING_field);
In the first query, I try adding a new field to the table and making it part of the composite key. In the second query, I try adding an existing field to the composite key. Both of these queries fail - I get the error : "ERROR 1068 (42000): Multiple primary key defined".