MySQL UNIQUE Constraint

A UNIQUE constraint ensures that values in a column are not duplicated.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(150) UNIQUE
);

Here, two students cannot have the same email address.

The UNIQUE constraint is useful when a value must be different for every record.

• Email addresses
• Mobile numbers
• Username
• Employee codes
• Registration numbers
• Product codes

The basic syntax is:

column_name data_type UNIQUE

Example:

email VARCHAR(150) UNIQUE

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(150) UNIQUE
);

The email column cannot contain duplicate non-NULL values.

Suppose this record already exists:

INSERT INTO students
(name, email)
VALUES
('Amit', 'amit@gmail.com');

Trying to insert the same email again will violate the UNIQUE constraint:

INSERT INTO students
(name, email)
VALUES
('Rahul', 'amit@gmail.com');

MySQL rejects the duplicate value.

A UNIQUE column can generally contain NULL values. Multiple NULL values can be allowed because NULL is treated differently from an ordinary duplicate value.

CREATE TABLE students (
    id INT PRIMARY KEY,
    email VARCHAR(150) UNIQUE
);

If an email is not known, the column can be left NULL unless it is also defined as NOT NULL.

A UNIQUE constraint can be applied to multiple columns together.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    UNIQUE (student_id, course_id)
);

The combination of student_id and course_id must be unique.

A multi-column UNIQUE constraint is sometimes called a composite UNIQUE constraint.

UNIQUE (student_id, course_id)

The individual columns may contain repeated values, but the complete combination cannot be duplicated.

You can give a UNIQUE constraint a specific name.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(150),

    CONSTRAINT uq_student_email
    UNIQUE (email)
);

Here, uq_student_email is the constraint name.

You can add a UNIQUE constraint using ALTER TABLE.

ALTER TABLE students
ADD UNIQUE (email);

The existing email values must satisfy the uniqueness requirement.

You can also specify a constraint name:

ALTER TABLE students
ADD CONSTRAINT uq_email
UNIQUE (email);

This makes the constraint easier to identify later.

A UNIQUE constraint is implemented using a unique index. It can be removed by dropping the corresponding unique index.

ALTER TABLE students
DROP INDEX uq_email;

If the UNIQUE constraint was created without an explicit name, use the actual unique index name shown by MySQL.

Use SHOW CREATE TABLE to inspect UNIQUE constraints and indexes.

SHOW CREATE TABLE students;

This displays the table definition, including UNIQUE constraints.

MySQL implements a UNIQUE constraint using a unique index.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(150),
    UNIQUE (email)
);

The unique index prevents duplicate non-NULL values in the indexed column.

You can also create a unique index directly.

CREATE UNIQUE INDEX idx_student_email
ON students(email);

This ensures that duplicate email values are not allowed.

A unique index can cover multiple columns.

CREATE UNIQUE INDEX idx_student_course
ON enrollments(student_id, course_id);

The combination of student_id and course_id must be unique.

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(150) UNIQUE,
    password VARCHAR(255)
);

Both username and email must be unique.

A table can have multiple UNIQUE constraints.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_code VARCHAR(20) UNIQUE,
    email VARCHAR(150) UNIQUE,
    mobile VARCHAR(15) UNIQUE
);

Here, employee_code, email, and mobile must each be unique.

UNIQUE and NOT NULL can be used together when a value must both exist and be unique.

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE
);

Now every row must have an email and no two rows can have the same email.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE
);

This is useful for login systems where every username must be different.

A UNIQUE constraint can be used for mobile numbers when each account must have a different number.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    mobile VARCHAR(15) UNIQUE
);

MySQL will reject duplicate non-NULL mobile numbers.

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(20) NOT NULL UNIQUE,
    course_name VARCHAR(100)
);

Each course receives a unique course code such as PYTHON01 or JAVA01.

Suppose the following table contains an existing email:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(150) UNIQUE
);

INSERT INTO users (email)
VALUES ('amit@gmail.com');

This second insert fails:

INSERT INTO users (email)
VALUES ('amit@gmail.com');

because the email already exists.

INSERT INTO users
(username, email)
VALUES
('amit', 'amit@gmail.com');

INSERT INTO users
(username, email)
VALUES
('rahul', 'rahul@gmail.com');

Both records are accepted because their unique values are different.

• Trying to insert duplicate values
• Assuming UNIQUE is the same as PRIMARY KEY
• Forgetting that NULL has special uniqueness behavior
• Adding UNIQUE when existing data already contains duplicates
• Dropping the wrong unique index
• Using UNIQUE on columns where duplicates are actually valid
• Not considering whether a value should also be NOT NULL

You can inspect indexes using:

SHOW INDEX FROM students;

This displays information about the indexes defined on the table.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    mobile VARCHAR(15) UNIQUE
);

INSERT INTO students
(student_code, name, email, mobile)
VALUES
('STU001', 'Amit Kumar', 'amit@gmail.com', '9876543210');

INSERT INTO students
(student_code, name, email, mobile)
VALUES
('STU002', 'Priya Singh', 'priya@gmail.com', '9876543211');

Each student has a unique student code, email, and mobile number.

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,

    UNIQUE (student_id, course_id)
);

A student can enroll in several courses, but the same student cannot have the same course combination twice.

For example:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_code VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    mobile VARCHAR(15),

    CONSTRAINT uq_student_code
    UNIQUE (student_code),

    CONSTRAINT uq_student_email
    UNIQUE (email),

    CONSTRAINT uq_student_mobile
    UNIQUE (mobile)
);

INSERT INTO students
(student_code, name, email, mobile)
VALUES
('STU001', 'Amit', 'amit@gmail.com', '9876543210');

INSERT INTO students
(student_code, name, email, mobile)
VALUES
('STU002', 'Priya', 'priya@gmail.com', '9876543211');

SELECT *
FROM students;

SHOW CREATE TABLE students;

SHOW INDEX FROM students;

The table uses three separate UNIQUE constraints to prevent duplicate student codes, email addresses, and mobile numbers.