MySQL UNIQUE Constraint

A UNIQUE constraint in MySQL ensures that all values in a column or a group of columns are distinct across every row in a table. In simple words, the UNIQUE constraint prevents duplicate data from being inserted into a column or a group of columns.

Therefore, this constraint is very useful when you want to maintain data accuracy and avoid duplicate records such as email IDs, usernames, phone numbers, roll numbers, or employee IDs. Every value in the specified column must be unique.

A UNIQUE constraint helps maintain data integrity in the database. A table can have multiple UNIQUE constraints, but only one PRIMARY KEY constraint. A UNIQUE constraint can contain NULL values, provided that the non-NULL values remain unique.

In MySQL, multiple NULL values are generally allowed in a UNIQUE column because NULL is treated as an unknown value. This makes it different from a PRIMARY KEY constraint, which cannot contain NULL values.

If you try to insert a duplicate value into a column that has a UNIQUE constraint, MySQL raises an error, ensuring that each value in the specified column remains unique.

Why Use a Unique Constraint in MySQL?

Syntax of UNIQUE Constraint in MySQL

The syntax of the UNIQUE constraint in MySQL varies depending on whether you add it during table creation or after the table has already been created. You can use the following syntax according to your requirement.

1. UNIQUE Constraint During Table Creation

The general syntax to add a UNIQUE constraint directly to a column during table creation is:

CREATE TABLE table_name (
    column_name1 data_type1,
    column_name2 data_type2 UNIQUE,
    ...
);

In this syntax, the constraint UNIQUE is added directly after the column definition. It ensures that all values in column_name2 are unique. Duplicate values are not allowed in that column.

2. UNIQUE Constraint with Constraint Name

Naming a constraint is a best practice because it makes error messages easier to understand and helps you manage or remove the constraint later.

CREATE TABLE table_name (
    column_name1 data_type1,
    column_name2 data_type2,
    CONSTRAINT constraint_name UNIQUE(column_name2)
);

Example of UNIQUE Constraint on Single Column

Suppose we have created a table named Students with an email column where we initially set a UNIQUE constraint to ensure each email address is unique.

Step 1: Create a Table

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

In this SQL query, student_id is the PRIMARY KEY, which uniquely identifies each row in the table. The email column has a UNIQUE constraint, which does not allow duplicate email addresses to be inserted.

Step 2: Insert Values into Table

To insert values into the table columns, type the following SQL commands.

INSERT INTO Students VALUES
(1, 'deepak@gmail.com');

Output:

Query OK, 1 row affected

In this query, MySQL inserts a new row into the Students table. The value 1 is inserted into the student_id column, and the email address ‘deepak@gmail.com’ is inserted into the email column. Since the email address does not already exist in the table, MySQL successfully inserts the record and displays the above message.

Step 3: Attempting a Duplicate Value

INSERT INTO Students VALUES
(2, 'deepak@gmail.com');

Output:

ERROR 1062 (23000):
Duplicate entry 'deepak@gmail.com' for key 'Students.email'

In this query, we have tried to add a duplicate email address ‘deepak@gmail.com’ into the email column. However, the email column has a UNIQUE constraint.

Since the email address ‘deepak@gmail.com’ already exists in the table, MySQL immediately rejects the duplicate insert and protects your data. MySQL generates the above error message, which indicates that a duplicate value was found for the UNIQUE column email.

Example of UNIQUE Constraint with Constraint Name

Let us take an example in which we will define UNIQUE constraint with a constraint name in the table.

Step 1: Create a Table

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    national_id VARCHAR(20),
    CONSTRAINT unique_national_id UNIQUE(national_id)
);

In this SQL query:

• emp_id is the PRIMARY KEY of the table.
• national_id stores the national identification number of employees.
• unique_national_id is the custom name of the UNIQUE constraint.
• The UNIQUE constraint ensures that no two employees can have the same national ID.

Step 2: Insert Valid Records

INSERT INTO Employees VALUES
(1, 'IND12345'),
(2, 'IND67890');

Output:

Query OK, 2 rows affected

Since both national ID values are different, MySQL insert the records successfully.

Step 3: Attempting to Insert Duplicate Value

INSERT INTO Employees VALUES
(3, 'IND12345');

Output:

ERROR 1062 (23000):
Duplicate entry 'IND12345' for key 'unique_national_id'

MySQL displays an error because the national ID ‘IND12345’ already exists in the table. The UNIQUE constraint named unique_national_id prevents duplicate national IDs from being inserted into the national_id column.

UNIQUE Constraint on Multiple Columns

MySQL also allows you to create a UNIQUE constraint on multiple columns together. This is known as a composite UNIQUE constraint or combined unique constraint.

This constraint ensures that the combination of values in two or more columns remains unique across the table. Individual column values may contain duplicates, but the combined values of all specified columns must be unique.

A composite UNIQUE constraint is useful when one column alone cannot make each record unique, but the combination of two or more columns must be different for every row in the table. For example:

• A student can enroll in many courses.
• A course can contain many students.
• But the same student should not enroll in the same course more than once.

In such cases, a composite UNIQUE constraint is very useful. A table can contain multiple composite UNIQUE constraints.

Syntax of UNIQUE Constraint on Multiple Columns

The general syntax of UNIQUE constraint on multiple columns in MySQL is:

CREATE TABLE table_name (
    column_name1 data_type1,
    column_name2 data_type2,
    column_name3 data_type3,
    CONSTRAINT constraint_name UNIQUE(column_name1, column_name2)
);

In this syntax:

• CONSTRAINT constraint_name specifies the custom name of the UNIQUE constraint.
• UNIQUE(column_name1, column_name2) creates a composite UNIQUE constraint on multiple columns.
• The combination of values in column_name1 and column_name2 must be unique.
• Individual column values can repeat, but duplicate combinations are not allowed.

Example of Composite UNIQUE Constraint

Let us take an example in which we will define composite UNIQUE constraint on multiple columns in the table.

Step 1: Create a Table

CREATE TABLE Course_Enrollment (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    CONSTRAINT unique_enrollment UNIQUE(student_id, course_id)
);

In this table:

• student_id stores the ID of students.
• course_id stores the ID of courses.
• unique_enrollment is the name of the UNIQUE constraint.
• The combination of student_id and course_id must be unique.

This means:

• A student can enroll in multiple courses.
• Multiple students can enroll in the same course.
• But the same student cannot enroll in the same course more than once.

Step 2: Insert Valid Records

INSERT INTO Course_Enrollment VALUES
(101, 1, '2026-05-10'),
(101, 2, '2026-05-11'),
(102, 1, '2026-05-12');

Output:

Query OK, 3 rows affected

These records are inserted successfully because each combination of student_id and course_id is different.

Step 3: Insert Duplicate Combination

INSERT INTO Course_Enrollment VALUES
(101, 1, '2026-05-15');

Output:

ERROR 1062 (23000):
Duplicate entry '101-1' for key 'unique_enrollment'

MySQL generates an error because the combination (101, 1) already exists in the table. Although student_id and course_id individually may contain duplicate values, their combined value must remain unique due to the composite UNIQUE constraint.

Adding UNIQUE Constraint to Existing Table

You can also add a UNIQUE constraint to an existing table using the ALTER TABLE statement. The general syntax is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE(column_name);

Example: Add UNIQUE Constraint to Existing Table

ALTER TABLE Students
ADD CONSTRAINT unique_email UNIQUE(email);

The above SQL statement adds a UNIQUE constraint to the email column of the Students table.

• The ALTER TABLE is used to modify an existing table.
• Students is the name of an existing table that you want to change.
• The ADD CONSTRAINT is used to add a new constraint to the table.
• unique_email is the name given to the constraint.
• You can choose any meaningful name for the constraint.
• UNIQUE constraint ensures that all values in the email column are different. Duplicate email addresses are not allowed.

If you try to insert a duplicate email, MySQL will generate an error.

Removing UNIQUE Constraint in MySQL

You can remove a UNIQUE constraint from an existing table using the DROP INDEX statement. This is because a UNIQUE constraint in MySQL is implemented internally as a unique index. The basic syntax is:

ALTER TABLE table_name
DROP INDEX constraint_name;

Example: Remove UNIQUE Constraint from Table

ALTER TABLE Students
DROP INDEX unique_email;

Since the above statements remove the UNIQUE constraint from the table, duplicate values can now be inserted.

UNIQUE Constraint and NULL Values in MySQL

A UNIQUE constraint ensures that all values in a column are different from each other. However, a column with a UNIQUE constraint can still contain NULL values. For example:

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    phone VARCHAR(20) UNIQUE
);

Now we will add the NULL values.

INSERT INTO Users VALUES
(1, NULL),
(2, NULL);

Since MySQL treats NULL values as different values, multiple NULL values are allowed in a UNIQUE column.

Key Points About UNIQUE Constraint

• A table can contain multiple UNIQUE constraints on different columns..
• MySQL automatically creates a unique index when a UNIQUE constraint is added.
• Use a UNIQUE constraint for columns that require unique data.
• Always use meaningful constraint names because they improve readability and maintenance.
• A UNIQUE constraint does not allow duplicate non-null values. However, multiple NULL values are allowed in MySQL.
• Check for existing duplicate data before adding a UNIQUE constraint.
• Combine UNIQUE with NOT NULL when necessary.