UNIQUE Constraint

A UNIQUE constraint ensures that every value stored in a column, or every combination of values across a set of columns, is distinct across all rows in the table. It is the tool for enforcing business rules like "no two users can share the same email address" or "a product code must not be reused."

Declaring a UNIQUE constraint

SQL

-- Single-column UNIQUE, declared inline
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email   VARCHAR(255) NOT NULL UNIQUE,
  name    VARCHAR(100) NOT NULL
);

-- Multi-column UNIQUE, declared as a table-level constraint
CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id    INT NOT NULL,
  course_id     INT NOT NULL,
  UNIQUE (student_id, course_id)
);

The enrollments example prevents the same student from enrolling in the same course twice, while still allowing that student to enroll in other courses, and other students to enroll in that same course.

SQL

-- Fails: student 12 is already enrolled in course 5
INSERT INTO enrollments (enrollment_id, student_id, course_id)
VALUES (301, 12, 5);

ERROR: duplicate key value violates unique constraint "enrollments_student_id_course_id_key"
DETAIL: Key (student_id, course_id)=(12, 5) already exists.

UNIQUE vs PRIMARY KEY

UNIQUE and PRIMARY KEY both prevent duplicate values, but they differ in a few important ways:

	PRIMARY KEY	UNIQUE
How many per table	At most one	As many as needed
Allows NULL	No — implicitly NOT NULL	Yes, in most databases (a NULL is not considered equal to another NULL, so multiple NULLs are typically allowed)
Purpose	The single, canonical identifier for a row	Enforcing distinctness on any column(s) that should never repeat
Implies NOT NULL	Yes	No — must be added explicitly if required

Note

A table can have one PRIMARY KEY but multiple UNIQUE constraints. For example, a users table might use a surrogate integer user_id as the PRIMARY KEY, while still declaring both email and username as separate UNIQUE columns — each independently guaranteed not to repeat, without either of them being the row's primary identifier.

Use case: unique email addresses

A signup system should never allow two accounts to register with the same email address. Rather than relying on application code to check "does this email already exist?" before every insert (which is vulnerable to race conditions when two signups happen at nearly the same moment), a UNIQUE constraint makes the guarantee absolute at the database level:

SQL

ALTER TABLE users
  ADD CONSTRAINT users_email_unique UNIQUE (email);

Even if two concurrent requests both pass the application's own duplicate check at the same instant, only one of the two competing INSERT statements will succeed — the second will be rejected by the constraint, guaranteeing no duplicate ever reaches the table.

Tip

Most databases automatically build an index to support a UNIQUE constraint, which means UNIQUE columns also get a performance benefit: lookups filtering on that column are as fast as lookups on an explicitly indexed column.