MySQL UNIQUE Constraint

The UNIQUE constraint in MySQL ensures that all values in a column are different from each other. No two rows can have the same value in a column marked as UNIQUE. This is useful for fields like email addresses, phone numbers, or usernames where duplicates are not allowed.

Why Use UNIQUE?

Consider a user registration system. Two users should never have the same email address. The UNIQUE constraint enforces this rule automatically at the database level, removing the need to check for duplicates in application code every time.

Syntax

At Column Level

CREATE TABLE table_name (
    column_name datatype UNIQUE
);

At Table Level

CREATE TABLE table_name (
    column_name datatype,
    CONSTRAINT constraint_name UNIQUE (column_name)
);

Example: Single Column UNIQUE

CREATE TABLE users (
    user_id INT NOT NULL,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

Each user must have a different username and a different email.

Inserting Valid Data

INSERT INTO users (user_id, username, email)
VALUES (1, 'rahul_99', 'rahul@example.com');

INSERT INTO users (user_id, username, email)
VALUES (2, 'priya_08', 'priya@example.com');

Inserting Duplicate Data (Fails)

INSERT INTO users (user_id, username, email)
VALUES (3, 'rahul_99', 'newemail@example.com');

MySQL returns:

ERROR 1062 (23000): Duplicate entry 'rahul_99' for key 'users.username'

UNIQUE on Multiple Columns (Composite UNIQUE)

A composite UNIQUE constraint ensures that the combination of values in two or more columns is unique, even if individual column values repeat.

CREATE TABLE bookings (
    booking_id INT NOT NULL,
    flight_no VARCHAR(10),
    seat_no VARCHAR(5),
    CONSTRAINT uq_seat UNIQUE (flight_no, seat_no)
);

Here, the same seat number can appear on different flights. But the same seat on the same flight cannot be booked twice.

Valid Inserts

INSERT INTO bookings VALUES (1, 'AI202', '12A');
INSERT INTO bookings VALUES (2, 'AI303', '12A');  -- same seat, different flight

Invalid Insert

INSERT INTO bookings VALUES (3, 'AI202', '12A');  -- same flight AND same seat

UNIQUE vs PRIMARY KEY

Adding UNIQUE to an Existing Table

ALTER TABLE users
ADD CONSTRAINT uq_email UNIQUE (email);

Removing a UNIQUE Constraint

ALTER TABLE users
DROP INDEX uq_email;

In MySQL, a UNIQUE constraint creates an index internally, so it is removed using DROP INDEX.

Key Points

• UNIQUE prevents duplicate values in a column.
• A table can have multiple UNIQUE constraints.
• Unlike PRIMARY KEY, a UNIQUE column can hold one NULL value.
• Composite UNIQUE constraints apply uniqueness to a combination of columns.
• Use DROP INDEX to remove a UNIQUE constraint in MySQL.