MySQL UNIQUE Constraint

The UNIQUE constraint in MySQL ensures distinct values in a column or a set of columns. This constraint acts as a gatekeeper ensuring that every data entered into the column or group of columns remains unique. A unique constraint can hold NULL values as long as the combination of the values is unique. This makes it different from the primary key as the primary key cannot contain null values and there can be only one primary key in a table wherein there can be multiple UNIQUE constraints in MySQL table.

MySQL UNIQUE Constraint Syntax

To use the UNIQUE key constraint, there are two methods available in MySQL.

  1. UNIQUE key on CREATE TABLE
  2. UNIQUE key on ALTER TABLE

1) UNIQUE key on CREATE TABLE

The following syntax is for adding a UNIQUE key constraint while creating a table using CREATE TABLE.

CREATE TABLE table_name (
	column_name data_type UNIQUE,
	.....
);

You can add UNIQUE constraints on multiple columns using the below syntax.

CREATE TABLE table_name (
	column_name1 data_type,
	column_name2 data_type,
	.....
	UNIQUE (column_name1, column_name2)
);

2) UNIQUE key on ALTER TABLE

For adding a UNIQUE key to an existing table, you need to use the following syntax.

ALTER TABLE table_name
ADD CONSTRAINT contraint_name
UNIQUE (column_name);

MySQL UNIQUE Constraint Example

Let’s create a table named teachers to understand the concept of the MySQL UNIQUE constraint.

CREATE TABLE teachers (
	id INT AUTO_INCREMENT PRIMARY KEY,
	first_name varchar(30),
	last_name varchar(30),
	email_id varchar(30) UNIQUE
);

Here, the column email_id is defined with the UNIQUE constraint to ensure that each email_id is unique.

This would prevent two email id have the same email address, maintaining the table integrity.

Let’s insert some data into the teachers table.

INSERT INTO teachers(id,first_name,last_name,email_id) VALUES
(1,'Raghu','Ram','raghu1970@gmail.com'),
(2,'Priya','Saha','priyasaha123@gmail.com'),
(3,'Sagar','Sharma',NULL);

Table: teachers

mysql> select * from teachers;
+----+------------+-----------+------------------------+
| id | first_name | last_name | email_id               |
+----+------------+-----------+------------------------+
|  1 | Raghu      | Ram       | raghu1970@gmail.com    |
|  2 | Priya      | Saha      | priyasaha123@gmail.com |
|  3 | Sagar      | Sharma    | NULL                   |
+----+------------+-----------+------------------------+
3 rows in set (0.00 sec)

If you try to insert the same email address again MySQL throws an error.

mysql> INSERT INTO teachers(id,first_name,last_name,email_id) VALUES
-> (4,'Rupam','Islam','priyasaha123@gmail.com');
ERROR 1062 (23000): Duplicate entry 'priyasaha123@gmail.com' for key 'teachers.email_id'

Dropping UNIQUE key in MySQL

For dropping a UNIQUE constraint from the MySQL table, you may use the following syntax.

ALTER TABLE teachers
DROP INDEX email_id;

Adding a UNIQUE key using ALTER TABLE

You also can UNIQUE key constraints using the ALTER TABLE statement as below.

ALTER TABLE teachers
ADD CONSTRAINT unique_email_id UNIQUE(email_id);

In the above example, we have added a UNIQUE constraint on email_id column again using the ALTER TABLE statement and the name of the constraint is unique_email_id.