What is primary key in MySQL

In the previous chapter, we looked at creating MySQL tables. We simply created a table (tbl_employee) for our example with four columns. The table had four columns as follows:

1. emp_name
2. emp_age
3. emp_salary
4. joining_date

An example of a table with primary key

Now consider a scenario where two employees with similar names join at the same date, how you will differentiate both? You have to create a key that differentiates between two or more records. There can be many other scenarios where you will need to differentiate records while working with databases. This is where a primary key is important.

A primary key is a field or column in a table that cannot contain duplicate values or it uniquely identifies each row in a table.

A MySQL primary key example

To demonstrate the primary key in MySQL, we will use the same table as mentioned earlier (tbl_employee). The  table will be created with above mentioned four columns as well as an emp_id as a primary key. See the following command to learn how we may create a primary key:

The MySQL command will create a table, tbl_employee with five fields including a primary key, emp_id.

Example of primary key in an existing table

You can also create a primary key in an existing table by using the Alter table statement of MySQL. To make it clearer, consider we have a table without a primary key as shown below:

Using Alter statement to add a primary key

Now we will use the Alter Table statement to create a primary key as follows:

Note that, in order to make the emp_id field as a primary key, it must have been created with NOT NULL constraint. If it was created as a NULL, the above statement will produce an error.

Also see: MySQL create table