What is a UNIQUE Constraint?

A UNIQUE constraint ensures that all values in a column (or a combination of columns) are different — no duplicates are allowed.

• Similar to a PRIMARY KEY, but:
  • A table can have only one primary key.
  • A table can have multiple UNIQUE constraints.
• UNIQUE allows NULL values (depending on the database, usually one NULL is allowed, sometimes more).

Key Properties of UNIQUE Constraint

Property	Explanation
Ensures uniqueness	No two rows can have the same value in the UNIQUE column(s).
Allows multiple constraints	You can have more than one UNIQUE constraint in a table.
Allows NULLs (with some DBs)	Most databases allow one NULL in a UNIQUE column (e.g., MySQL, PostgreSQL).
Can be single or composite	Can apply to one column or multiple columns (composite unique constraint).

---

Example: Single Column UNIQUE

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

email must be unique across all users — no two users can have the same email.

Example: Composite UNIQUE (Multiple Columns)

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

A student can enroll in multiple courses.

A course can have multiple students.

But a student cannot enroll in the same course twice — (student_id, course_id) must be unique together.

What Happens if You Violate a UNIQUE Constraint?

If you try to insert duplicate values:

INSERT INTO Users (user_id, email) VALUES (2, 'alice@example.com'); -- OK
INSERT INTO Users (user_id, email) VALUES (3, 'alice@example.com'); -- ❌ Error: duplicate key

The database throws an error:

ERROR: duplicate key value violates unique constraint

UNIQUE vs PRIMARY KEY

PRIMARY KEY	UNIQUE Constraint
Only one per table	Many UNIQUE constraints allowed
Cannot have NULLs	Can have NULLs (one or more)
Enforces uniqueness + NOT NULL	Enforces uniqueness only

In Short

UNIQUE constraint = No duplicate values allowed in the column(s) — ensures data integrity.