UNIQUE Constraint

What is a UNIQUE Constraint?

The UNIQUE constraint ensures that all values in a column (or combination of columns) are different across every row. It prevents duplicate entries for things like email addresses, usernames, or product codes.

Syntax

Column-level:

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);

Table-level (multi-column):

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

When to Use

• Ensuring email addresses are unique per user
• Preventing duplicate enrollments for the same student in the same course
• Enforcing business rules that require distinct values

Key Points

1. Allows One NULL — Unlike PRIMARY KEY, a UNIQUE column can contain NULL values (multiple NULLs are allowed since NULL is not equal to NULL).
2. Auto Index — PostgreSQL creates a unique B-tree index automatically.
3. Multiple Allowed — A table can have many UNIQUE constraints.
4. Named Constraints — You can name them for clarity: CONSTRAINT uq_email UNIQUE (email).
5. Composite — A multi-column UNIQUE constraint enforces uniqueness on the combination of values.