MySQL Primary Key

MySQL primary key is a single or combination of the field, which is used to identify each record in a table uniquely. If the column contains primary key constraints, then it cannot be null or empty. A table may have duplicate columns, but it can contain only one primary key. It always contains unique value into a column.

When you insert a new row into the table, the primary key column can also use the AUTO_INCREMENT attribute to generate a sequential number for that row automatically. MySQL automatically creates an index named "Primary" after defining a primary key into the table. Since it has an associated index, we can say that the primary key makes the query performance fast.

Rules for Primary key

Following are the rules for the primary key:

1. The primary key column value must be unique.
2. Each table can contain only one primary key.
3. The primary key column cannot be null or empty.
4. MySQL does not allow us to insert a new row with the existing primary key.
5. It is recommended to use INT or BIGINT data type for the primary key column.

We can create a primary key in two ways:

• CREATE TABLE Statement
• ALTER TABLE Statement

Let us discuss each one in detail.

Primary Key Using CREATE TABLE Statement

In this section, we are going to see how a primary key is created using the CREATE TABLE statement.

Syntax

The following are the syntax used to create a primary key in MySQL.

If we want to create only one primary key column into the table, use the below syntax:

If we want to create more than one primary key column into the table, use the below syntax:

Parameter Explanation

The following table explains the parameters in detail.

Primary Key Example

The following example explains how a primary key used in MySQL.

This statement creates a table named "Login" whose "login_id" column contains the primary key:

Next, use the insert query to store data into a table:

Output

In the below output, we can see that the first insert query executes successfully. While the second insert statement fails and gives an error that says: Duplicate entry for the primary key column.

If you want to define the primary key on multiple columns, use the query as below:

In the output, we can see that the primary key value contains two columns that are Student_ID and Roll_No.

Primary Key Using ALTER TABLE Statement

This statement allows us to do the modification into the existing table. When the table does not have a primary key, this statement is used to add the primary key to the column of an existing table.

Syntax

Following are the syntax of the ALTER TABLE statement to create a primary key in MySQL:

Example

The following statement creates a table "Persons" that have no primary key column into the table definition.

After creating a table, if we want to add a primary key to this table, we need to execute the ALTER TABLE statement as below:

We can see the output where both statements executed successfully.

If the table needs to add the primary key into a table that already has data into the column, then it must be sure to the column does not contains duplicates or null values.

DROP Primary Key

The ALTER TABLE statement also allows us to drop the primary key from the table. The following syntax is used to drop the primary key:

Example

Primary Key vs. Unique Key

The following comparison chart explains some of the common differences between both of them: