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>

How to Create a MySQL Table with a Primary Key

This is one way to create a table with a primary key. In this example, the primary key consists of one column. In a future post, I will show how to set up a primary key with more than one column: 

mysql> create table andrews_table

    -> (col1 varchar(10), primary key(col1));

Query OK, 0 rows affected (0.02 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.00 sec)

mysql>
  

The purpose of a primary key is to ensure that the values in the primary key column remain unique. So, if you try to insert Fred into the primary key column again, you would expect the insert to fail:

 

mysql> insert into andrews_table values ('Fred');

ERROR 1062 (23000): Duplicate entry 'Fred' for key 1

mysql> select * from andrews_table;

+------+

| col1 |

+------+

| Fred |

+------+

1 row in set (0.00 sec)

 

mysql>