? MySQL UNIQUE Constraint

⚡ Quick Overview

The UNIQUE constraint in MySQL ensures that all values in a column (or a combination of columns) are different. It is commonly used for columns like Email, Username, or any field where duplicates are not allowed.

If you try to insert a duplicate value into a column with a UNIQUE constraint, MySQL will reject the operation and throw an error.

? Key Concepts

UNIQUE (Column Level): Ensures that all values in a single column are unique.
UNIQUE (Table Level / Composite): Ensures that the combination of values in multiple columns is unique.
Difference from PRIMARY KEY: A table can have multiple UNIQUE constraints but only one PRIMARY KEY. Also, a primary key is always NOT NULL.
NULL handling: In MySQL, a UNIQUE column can store multiple NULL values (because NULL is treated as "unknown").

? Syntax

Syntax — Column Level UNIQUE

? View Code Example

-- Add UNIQUE constraint directly to a column definition
CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype
);

Syntax — Table Level (Named UNIQUE Constraint)

? View Code Example

-- Define a named UNIQUE constraint at table level
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    CONSTRAINT unique_constraint_name UNIQUE (column1, column2)
);

? Example — UNIQUE on Email & Name

In this example, both Name and Email must be unique across all employees.

? View Code Example

-- Create Employees table with UNIQUE constraints
CREATE TABLE Employees (
    ID INT NOT NULL,
    Name VARCHAR(100) UNIQUE,
    Age INT,
    Email VARCHAR(100) UNIQUE
);

? What Happens Internally?

After creating the Employees table:

Each value in the Name column must be different.
Each value in the Email column must be different.

Consider the following inserts:

? View Code Example

-- Insert unique rows - both will succeed
INSERT INTO Employees (ID, Name, Age, Email)
VALUES (1, 'Alice', 25, 'alice@example.com');

INSERT INTO Employees (ID, Name, Age, Email)
VALUES (2, 'Bob', 30, 'bob@example.com');

? View Code Example (Duplicate Error)

-- This will fail because Email is already used
INSERT INTO Employees (ID, Name, Age, Email)
VALUES (3, 'Charlie', 28, 'alice@example.com');

The third INSERT will fail with an error similar to: "Duplicate entry 'alice@example.com' for key 'Email'". This is the UNIQUE constraint doing its job.

? Composite UNIQUE Constraint

Sometimes a single column does not need to be unique, but a combination of columns must be. For example, the same student can enroll in multiple courses, but should not be enrolled twice in the same course.

? View Code Example

-- Unique combination of StudentID and CourseID
CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    EnrollDate DATE,
    CONSTRAINT unique_student_course UNIQUE (StudentID, CourseID)
);

? Effect of Composite UNIQUE

Same StudentID can appear many times with different CourseID.
Same CourseID can appear many times with different StudentID.
The pair (StudentID, CourseID) must be unique – no duplicate enrollments for the same course.

✅ Tips & Best Practices

Apply UNIQUE to columns like Email, Username, PhoneNumber, etc., where duplicates would cause logical issues.
Use descriptive names for table-level constraints, like unique_email or unique_username, for easier debugging.
Plan your UNIQUE constraints early in the database design stage to avoid needing complex data cleanup later.
Remember: inserting a duplicate into a UNIQUE column will raise an error — always validate data on the application side when possible.
For logical uniqueness across multiple columns, prefer a composite UNIQUE constraint instead of manually checking in your application code.

? Try It Yourself

Create a table Users with columns UserID, Username, and Email. Make both Username and Email unique.
Insert a few valid rows, then try inserting a duplicate Email. Observe the error message from MySQL.
Design a Bookings table where the combination of RoomNumber and BookingDate must be unique (only one booking per room per day).
Use SHOW CREATE TABLE to inspect how MySQL stores the UNIQUE constraints internally.