UNIQUE Constraint in SQL

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

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

Each email address must be unique.

UNIQUE is useful when a value should identify or distinguish records without being the table's primary key.

• Prevents duplicate values
• Helps maintain data accuracy
• Useful for email addresses
• Useful for usernames
• Useful for phone numbers and other unique identifiers

The basic column-level syntax is:

column_name data_type UNIQUE

Example:

email VARCHAR(100) UNIQUE

You can define a UNIQUE constraint while creating a table.

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

The database will prevent duplicate email values.

A value that does not already exist can be inserted successfully.

INSERT INTO students (student_id, email)
VALUES (101, 'rahul@example.com');

If the email does not already exist, the record can be inserted.

If you try to insert the same UNIQUE value again, the database can reject the operation.

INSERT INTO students (student_id, email)
VALUES (102, 'rahul@example.com');

If rahul@example.com already exists, this creates a UNIQUE constraint violation.

UNIQUE is commonly used with VARCHAR columns.

CREATE TABLE users (
    username VARCHAR(50) UNIQUE,
    name VARCHAR(100)
);

Each username must be unique.

Email addresses are a common example of UNIQUE values.

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

The same email cannot be stored as a duplicate under the UNIQUE rule.

A mobile number can also be defined as UNIQUE when each user must have a different number.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    mobile VARCHAR(15) UNIQUE
);

Duplicate mobile values are prevented.

A table can have more than one UNIQUE constraint.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(150) UNIQUE,
    username VARCHAR(50) UNIQUE,
    mobile VARCHAR(15) UNIQUE
);

Email, username, and mobile values are independently required to be unique.

You can give a UNIQUE constraint a specific name.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(100),
    CONSTRAINT uq_student_email
    UNIQUE (email)
);

The constraint is named uq_student_email.

A UNIQUE constraint can be defined separately at the table level.

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

The email column must contain unique values.

You can add a UNIQUE constraint to an existing table.

ALTER TABLE students
ADD UNIQUE (email);

The email column is now required to have unique values.

You can add a named UNIQUE constraint using ALTER TABLE.

ALTER TABLE students
ADD CONSTRAINT uq_student_email
UNIQUE (email);

The constraint is given the name uq_student_email.

A UNIQUE constraint can contain multiple columns.

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.

student_id | course_id
101        | 1
101        | 2
102        | 1

These combinations are different, so they can coexist.

A duplicate combination violates a composite UNIQUE constraint.

student_id | course_id
101        | 1
101        | 1

The second combination duplicates the first one.

UNIQUE and NULL behavior depends on the database system. In MySQL, a UNIQUE column can generally contain multiple NULL values because NULL is treated as an unknown value rather than a duplicate ordinary value.

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

If every record must have an email, combine UNIQUE with NOT NULL.

Use UNIQUE and NOT NULL together when a value must be provided and must also be unique.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE
);

Every student must provide an email, and no two students can use the same email.

PRIMARY KEY and UNIQUE both enforce uniqueness, but they have different purposes.

• PRIMARY KEY uniquely identifies each record.
• A table has one primary key constraint.
• PRIMARY KEY values cannot be NULL.
• A table can have multiple UNIQUE constraints.
• UNIQUE is useful for additional columns that must not contain duplicate values.

UNIQUE and FOREIGN KEY serve different purposes.

• UNIQUE prevents duplicate values.
• FOREIGN KEY creates a relationship between tables.

email VARCHAR(100) UNIQUE

FOREIGN KEY (course_id)
REFERENCES courses(course_id)

In MySQL, a UNIQUE constraint is implemented using a unique index, which can be removed using ALTER TABLE.

ALTER TABLE students
DROP INDEX uq_student_email;

If the UNIQUE constraint was created with an automatically generated index name, use the actual index name shown by the table definition.

In MySQL, you can inspect the table definition to check UNIQUE constraints.

SHOW CREATE TABLE students;

This displays the CREATE TABLE statement and its constraints and indexes.

When updating a UNIQUE column, the new value must also satisfy the UNIQUE rule.

UPDATE students
SET email = 'newemail@example.com'
WHERE student_id = 101;

If the new email already belongs to another record, the database can reject the update.

Deleting a record removes its stored UNIQUE value from that row.

DELETE FROM students
WHERE student_id = 101;

After the record is deleted, the unique value may become available for another record, subject to the database transaction state.

A common mistake is adding a UNIQUE constraint to a column that already contains duplicate values.

ALTER TABLE students
ADD UNIQUE (email);

If duplicate email values already exist, the database may reject the operation.

You can use GROUP BY and HAVING to find duplicate values.

SELECT email, COUNT(*) AS total
FROM students
GROUP BY email
HAVING COUNT(*) > 1;

This helps identify duplicate email values before adding a UNIQUE constraint.

Suppose each student should have a unique email address.

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

The database prevents two students from using the same email.

In a student management system, student ID can be the primary key while email and mobile can be UNIQUE.

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

This allows each student to have a unique email and, when provided, a unique mobile number.

Here is a complete example using PRIMARY KEY, UNIQUE, NOT NULL, and a composite UNIQUE constraint.

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

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

Here, email and mobile must be unique, while each student-course combination in enrollments must also be unique.