Add a field to a composite primary key

Posted on 2008-11-19
Medium Priority
Last Modified: 2012-05-05
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".
Question by:a2hIT
  • 2
LVL 19

Expert Comment

ID: 22996242
I think you need to drop the primary key, then add it again with the new definition, so
alter table table_name drop primary key;
alter table table_name add primary key (field1, field2, field3);


Author Comment

ID: 22996355
I didn't think that a table could exist in MySQL without having a primary key, but what you say makes sense, so I tried it.  The first statement failed though and returned this error:

ERROR 1025 (HY000): Error on rename of '.\a2h\#sql-700_7' to '.\a2h\phonecharges' (errno: 150)

At this point i can tell you that 'a2h' is the name of my database, and 'phonecharges' is the name of the table I'm working with.
LVL 26

Accepted Solution

Umesh earned 2000 total points
ID: 22996464
This should work...

ALTER TABLE phonecharges  DROP PRIMARY KEY, ADD PRIMARY KEY  (`field1`,`field1`,`field1`);

BTW, , if an ALTER TABLE fails and it refers to errno 150, that means a foreign key definition would be incorrectly formed for the altered table.

LVL 26

Expert Comment

ID: 22996492
I just tried.. its working...

 CREATE TABLE `phonecharges` (                                
             `ID` int(10) unsigned NOT NULL auto_increment,          
             `FK` int(10) default NULL,                              
             `date` datetime NOT NULL,                               
             PRIMARY KEY  (`ID`,`FK`)                                     
show create table phonecharges;
CREATE TABLE `phonecharges` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `FK` int(10) NOT NULL default '0',
  `date` datetime NOT NULL,
  PRIMARY KEY  (`ID`,`FK`,`date`)

Open in new window


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question