How to Add a Primary Key to a MySQL Table

In another post, I showed how to create a new table with a single-column primary key. This example shows how to add a multi-column primary key to an existing table: 

mysql> create table andrews_names

    -> (first_name varchar(10),

    ->  surname    varchar(10));

Query OK, 0 rows affected (0.06 sec)

 

mysql> alter table andrews_names

    -> add primary key

    -> (first_name, surname);

Query OK, 0 rows affected (0.30 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Reid');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Bloggs');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Joe', 'Reid');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Reid');

ERROR 1062 (23000): Duplicate entry 'Andrew-Reid' for key 1

mysql> select *from andrews_names;

+------------+---------+

| first_name | surname |

+------------+---------+

| Andrew     | Bloggs  |

| Andrew     | Reid    |

| Joe        | Reid    |

+------------+---------+

3 rows in set (0.01 sec)

 

mysql>