MySQL UNIQUE Constraint

The UNIQUE constraint ensures that every value stored in a column is different across all rows. No two rows in the table can hold the same value for that column. This is perfect for fields like email addresses, usernames, phone numbers, or any other data that must be one-of-a-kind.

Unlike the PRIMARY KEY, a table can have multiple UNIQUE constraints — one on each column that needs to hold distinct values.

---

Syntax

You can apply UNIQUE inline on a column or as a separate table-level constraint:

-- Inline on a single column
column_name data_type UNIQUE

-- Table-level (allows naming the constraint)
CONSTRAINT constraint_name UNIQUE (column_name)

---

Using UNIQUE in CREATE TABLE

The example below creates a users table where every email address must be unique:

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

With this table, inserting two users with the same email will not be allowed.

---

What Happens on a Duplicate Insert?

If you attempt to insert a value that already exists in a UNIQUE column, MySQL rejects the operation:

INSERT INTO users (name, email) VALUES ('Karim', 'karim@example.com');
INSERT INTO users (name, email) VALUES ('Sara', 'karim@example.com');

-- ERROR 1062 (23000): Duplicate entry 'karim@example.com' for key 'users.email'

---

UNIQUE and NULL Values

One important difference between UNIQUE and PRIMARY KEY: a UNIQUE column does allow NULL — and you can have multiple NULL values in the same column. This is because NULL represents an unknown value, and two unknowns are not considered equal in SQL.

-- Both inserts succeed — NULL is not considered a duplicate
INSERT INTO users (name, email) VALUES ('Rafi', NULL);
INSERT INTO users (name, email) VALUES ('Nila', NULL);

---

Named UNIQUE Constraint

Naming a constraint makes it easier to reference later — for example, when you want to drop it:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT uq_users_email UNIQUE (email)
);

---

Composite UNIQUE Constraint

A composite UNIQUE constraint spans two or more columns together. The combination of those columns must be unique — but individual columns may repeat on their own.

For example, a student can only enroll in the same course once:

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrolled_at DATE NOT NULL,
    CONSTRAINT uq_enrollment UNIQUE (student_id, course_id)
);

Here, the same student_id can appear in multiple rows (enrolled in different courses), and the same course_id can appear for different students — but the pair (student_id, course_id) must be unique.

---

Adding UNIQUE to an Existing Column

If the table already exists, use ALTER TABLE to add a UNIQUE constraint:

ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);

You can check for duplicates first:

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

---

Removing a UNIQUE Constraint

In MySQL, a UNIQUE constraint is stored as an index. To remove it, use DROP INDEX:

ALTER TABLE users
DROP INDEX uq_users_email;

If you did not give the constraint a name, MySQL uses the column name as the index name by default.

---

Key Points to Remember

• UNIQUE prevents duplicate values in a column across all rows.
• A table can have multiple UNIQUE constraints — one per column that needs distinct values.
• UNIQUE columns allow NULL, and multiple NULLs are permitted since NULL ≠ NULL in SQL.
• Combine with NOT NULL when the field is both required and must be distinct.
• A composite UNIQUE constraint checks the uniqueness of a combination of columns, not each column individually.
• To remove a UNIQUE constraint, use ALTER TABLE ... DROP INDEX constraint_name.