UNIQUE

Declaring UNIQUE in SQL

While the conceptual role of a unique/candidate key was covered earlier, here we focus purely on how you write the constraint. UNIQUE guarantees that no two rows share the same value in the constrained column(s) — except that most engines (PostgreSQL, MySQL, SQL Server, Oracle) treat multiple NULLs as not duplicating each other, so several rows can each have NULL in a UNIQUE column simultaneously.

Column-Level Syntax

sql
CREATE TABLE users (
    user_id  INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email    VARCHAR(150) UNIQUE NOT NULL
);

Table-Level Syntax (required for multi-column uniqueness)

sql
CREATE TABLE enrollments (
    student_id INT,
    course_id  INT,
    UNIQUE (student_id, course_id)   -- a student can't enroll in the same course twice
);

A column-level UNIQUE can never express "this combination of columns must be unique" — only the table-level clause can span more than one column.

Adding UNIQUE to an Existing Table

sql
ALTER TABLE users ADD CONSTRAINT uq_users_username UNIQUE (username);

This statement scans the existing data first; if duplicate username values already exist, the ALTER TABLE is rejected until they're resolved (e.g. via a GROUP BY ... HAVING COUNT(*) > 1 cleanup query).

Dropping It

sql
-- PostgreSQL / Oracle / SQL Server style
ALTER TABLE users DROP CONSTRAINT uq_users_username;

-- MySQL treats a UNIQUE constraint as an index
ALTER TABLE users DROP INDEX uq_users_username;

This MySQL quirk — unique constraints are implemented as unique indexes internally — means SHOW INDEX FROM users; is how you discover the name if it wasn't chosen explicitly.

UNIQUE vs PRIMARY KEY, Syntactically

A table can have only one PRIMARY KEY but many UNIQUE constraints. UNIQUE columns can accept NULL (subject to the multiple-NULLs behavior above) whereas PRIMARY KEY columns cannot, since PRIMARY KEY = UNIQUE + NOT NULL bundled together.

Edge Cases

• SQL Server, notably, allows only one NULL in a UNIQUE column by default (its unique index treats NULLs as equal), which differs from Postgres/MySQL/Oracle — a genuine cross-dialect portability trap.
• Composite UNIQUE (a, b) still allows duplicate a values or duplicate b values individually; only the pair must be distinct.
• A UNIQUE constraint automatically creates a supporting index in most engines, which is why heavy write workloads sometimes avoid over-constraining columns that don't need it.

Key Takeaways / Q&A

Q: Can two rows both have NULL in a UNIQUE column in PostgreSQL? A: Yes — Postgres (and MySQL, Oracle) treat NULL as "unknown," and two unknowns are never considered equal to each other, so the uniqueness check doesn't fire.

Q: When must you use table-level UNIQUE syntax instead of column-level? A: Whenever the uniqueness rule spans two or more columns together, such as UNIQUE (student_id, course_id).

Q: How do you find the auto-generated name of a UNIQUE constraint to drop it? A: Query the catalog — information_schema.table_constraints (Postgres/MySQL) or SHOW INDEX (MySQL) — since an unnamed constraint gets a system-generated identifier.