MySQL Unique Key

A unique key in MySQL is a single field or combination of fields that ensure all values going to store into the column will be unique. It means a column cannot stores duplicate values. For example, the email addresses and roll numbers of students in the "student_info" table or contact number of employees in the "Employee" table should be unique.

MySQL allows us to use more than one column with UNIQUE constraint in a table. It can accept a null value, but MySQL allowed only one null value per column. It ensures the integrity of the column or group of columns to store different values into a table.

Needs of Unique Key

• It is useful in preventing the two records from storing identical values into the column.
• It stores only distinct values that maintain the integrity and reliability of the database for accessing the information in an organized way.
• It also works with a foreign key in preserving the uniqueness of a table.
• It can contain null value into the table.

Syntax

The following syntax is used to create a unique key in MySQL.

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

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

If we have not specified the name for a unique constraint, MySQL generates a name for this column automatically. So, it is recommended to use the constraint name while creating a table.

Parameter Explanation

The following table explains the parameters in detail.

Unique Key Example

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

This statement creates a table "Student2" with a UNIQUE constraint:

Next, execute the insert queries listed below to understand how it works:

Output

In the below output, we can see that the first INSERT query executes correctly, but the second statement fails and gives an error that says: Duplicate entry '1' for key Stud_ID.

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

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

To verify this, execute the following statement:

Here, we can see that the unique constraint has successfully added into the table:

DROP Unique Key

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

In the above syntax, the table_name is the name of the table that we want to modify, and constraint_name is the name of the unique key we are going to remove.

Example

This statement will remove the uc_rollno_email constraint from the table permanently.

We can execute the SHOW INDEX statement to very this.

Unique Key Using ALTER TABLE Statement

This statement allows us to do the modification into the existing table. Sometimes we want to add a unique key to the column of an existing table; then, this statement is used to add the unique key for that column.

Syntax

Following are the syntax of the ALTER TABLE statement to add a unique key:

Example

This statement creates a table "Students3" that have no unique key column into the table definition.

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

We can see the output where both statements executed successfully.

To verify this, execute the following statement:

Here, we can see that the unique constraint has successfully added into the table: