Add a field to a composite primary key

Posted on 2008-11-19
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
    LVL 19

    Expert Comment

    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

    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

    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

    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`)                                     
    ALTER TABLE phonecharges DROP PRIMARY KEY, ADD PRIMARY KEY  (`ID`,`FK`,`date`);
    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

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now