How to add a primary key to a MySQL table

In this post we will see how we can add a primary key to a MySQL table.

We can add a primary key in MySQL either during table creation or we can also add a primary key after table creation. We will see both ways.

Add a primary key during table creation

We will see how we can add a primary key during table creation. Any column can be marked as primary key by putting the primary key clause along with that column during MySQL table creation.

create table salary(EmpID int primary key, salary varchar(20)); 

We have created a salary table where we have created EmpID column as the primary key by putting the primary key clause along with the EmpID column in MySQL.

We can check whether the primary key has been added or not by using the describe statement.

Describe salary;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| EmpID  | int         | NO   | PRI | NULL    |       |
| salary | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

We will see that the primary key has been added to column EmpID table in MySQL.

Add Primary key to existing table in MySQL

Now we will see how we can add a primary key to an existing table in MySQL. 

We can add the primary key to the existing table by using the Alter statement.

Syntax

Alter table table_name ADD PRIMARY KEY(COLUMN_NAME);

Example

Suppose we have to add PRIMARY KEY to address table then we can use following MySQL statement

Alter table address add primary key(empid);

Above statement will add the primary key to the empid column of the address table.