Unique Key in MySQL

Updated May 11, 2023

Introduction to Unique Key in MySQL

A Unique key in MySQL is a Key constraint in every table to ensure the uniqueness of all the values in a table column. A Primary Key and Unique Key Constraints are the keys in MySQL that guarantee that the value in a column or group of columns is different and not duplicated. Generally, a Primary Key automatically includes a Unique Key Constraint since a Primary Key value must be unique and NOT NULL, as NULL is not a value. However, in a MySQL table, there can be more than one Unique Key Constraint, but it must have only one Primary Key. A Unique Key holds a single or a set of columns or records uniquely identified throughout the table. A unique Key can include a NULL value, but only one NULL value per column is allowed.

Why do we need Unique Key in MySQL?

• In MySQL, a Unique Key constraint helps prevent two records or rows from having identical values in a column. So, a unique key is a group of one or multiple fields or columns of a table that distinctively determines a record in a database table when we retrieve or access the records.
• We can say that a Unique Key Constraint in MySQL helps remove the duplication of data records in a table which maintains the integrity and reliability of the database to store and access information in an organized way.
• A Foreign Key Constraint can also reference a Unique Key to maintain the uniqueness of the table while combining two tables in a database where each table includes both Primary and Unique key constraints on a priority basis.

Syntax:

We have the following basic SQL Syntax to write a column with a Unique key when we are creating a table:

1. UNIQUE Constraint to Create TABLE:

CREATE TABLE Table_name (Column_name Datatype UNIQUE, Column_name Datatype);

• For Oracle | MS Access | SQL Server

Create Table Persons (PersonID int NOT NULL UNIQUE, Name, varchar (255) NOT NULL, Address varchar (255), Age int);

• For MySQL

Create Table Persons (PersonID int NOT NULL, Name, varchar (255) NOT NULL, Address varchar (255), Age int, UNIQUE (PersonID));

We have used a Unique key on the column ‘PersonID’.

Now, the syntax to define multiple columns:

Create Table Persons (PersonID int NOT NULL, Name, varchar (255) NOT NULL, Address varchar (255), Age int, CONSTRAINT UC_Person UNIQUE (PersonID, Name));

2. UNIQUE Constraint to ALTER TABLE

ALTER TABLE Persons ADD UNIQUE (ID);

3. DROP a UNIQUE Constraint

• For MySQL

ALTER TABLE Persons DROP INDEX UC_Person;

OR

DROP INDEX IndexName ON TableName;

• For SQL | MS Access| Oracle:

ALTER Table Persons DROP CONSTRAINT UC_Person;

Working on Unique Key in MySQL

• In MySQL, a clustered index is automatically generated when we define a Primary Key, but a Unique key creates the non-clustered index.
• A Unique Key prevents any columns or rows in a table from containing duplicate values and helps to store distinct ones. Suppose we have a Customer table which contains all the info of customers. There may be a condition and requirement that we need to have customers with no same age. Then, a Unique Key Constraint in that column value of the table prevents this type of condition.
• We can define multiple Unique keys on a table where one or more columns combine to make a Unique key. According to ANSI, we can use various NULL values, but we can add only one NULL value in the SQL server. Multiple NULLs cannot be inserted with the Unique key, but you can create a Unique Non-Clustered index with the NOT NULL filter and use various NULL values. As per standards, no rule is that only one NULL should be used.
• Logically, a Unique Key Constraint holds nonduplicate values in a column, and if compared and found any equal values then it will violate the MySQL rule.
• Thus, the individual uniqueness of records is managed in the table’s columns using Unique constraints.

How to use a Unique Key in MySQL with Row, Column, and Table?

Let us consider some tables to see how we can use a Unique key in MySQL with rows and columns in a table.

Example #1 – MySQL Unique Key Create Table

For example, we have created a table ‘Students’ and added a Unique constraint:

create Table If Not Exists Students (
Id Int Auto_Increment Primary Key, 
Name Varchar(50) Not Null, 
Address Varchar(255) Not Null, 
Phone Varchar(15) Not Null, 
Email Varchar(100) Not Null, 
Unique Key Unique_Email (Email))

Output:

If we view the table, it will display this layout below:

Again, if we use the below code, then we will view that MySQL has produced a Unique index for the Email column:

SHOW INDEXES FROM students;

Output:

Let us enter some data:

INSERT INTO students (ID,Name, Address, Phone,Email) VALUES ('12', 'Nikhil', 'Delhi', '8954780145', '[email protected]');

Output:

Now, let us try to insert a duplicate email value that is already present in the column and what happens using the following SQL statement:

INSERT INTO students (ID,Name, Address, Phone,Email) VALUES ('16', 'Sangam', 'Bareilly', '9580014257', '[email protected]');

When executed, you will receive an error message from MySQL as follows:

This is how the Unique key works to prevent duplicity factors in RDBMS.

Example #2 – MySQL Unique Key Alter Table

We will use ALTER to change Unique Column Phone then we will use the below query:

ALTER TABLE students ADD CONSTRAINT Unique_Phone UNIQUE (Phone);

When we insert the same phone value then, it also shows the error:

INSERT INTO students (ID,Name, Address, Phone,Email) VALUES ('16', 'Sangam', 'Bareilly', '8954780145', '[email protected]');

Output:

Example #3 – MySQL Unique Key Drop Table

Now we will DROP the Unique key constraint from the column Email.

The SQL statement for this is:

ALTER TABLE students DROP INDEX UNIQUE_Email;

When we execute it, a confirmation message box is displayed asking if we want this execution of the query; you can proceed to click on OK and then view the result:

After clicking, it will remove the Unique key constraint from the email column:

Let us now see the indexes with the query:

SHOW INDEXES FROM students;

Now, you cannot view the Unique_Email to be Unique.

Output:

If you insert the same email value, then it will be inserted in the column:

INSERT INTO students (ID,Name, Address, Phone,Email) VALUES ('16', 'Sangam', 'Bareilly', '9580014257', '[email protected]');

Output:

Conclusion

In MySQL, a Unique Key enforces the integrity of a column by allowing only distinct values to be entered into a table. Sometimes, we have columns like Email, Phone, ID, Address, etc., which need to be unique and different for security reasons and to keep the database tables with organized records to provide valid information to users. So, if a table uses Unique key columns, we cannot add, insert, or even update the particular column or set of columns. If we try to execute so, MySQL will reject the change and produce an error.

We hope that this EDUCBA information on “Unique Key in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. MySQL Trigger
2. Composite Key in SQL
3. Table in MySQL
4. Foreign Key in SQL
5. MySQL IN Operator | Examples