SQL UNIQUE Constraint

The UNIQUE constraint tells the database engine that every value (or combination of values) stored in a column, or group of columns, must be different from every other row in the table. It is how you enforce business rules like “no two users can share an email address” or “a student can only enroll in the same course once” directly at the data layer, instead of hoping application code catches every duplicate.

Overview / How It Works

When you attach UNIQUE to a column, the database engine doesn’t just remember a rule in the abstract — it builds an internal index over that column (or column combination) whose sole job is to detect duplicates instantly. In SQLite, PostgreSQL, MySQL, and SQL Server alike, a UNIQUE constraint is implemented as a unique index behind the scenes. That means two things in practice: first, every INSERT or UPDATE that touches a unique column triggers a fast index lookup to check whether the new value already exists; if it does, the engine rejects the statement and raises a constraint-violation error instead of writing the row. Second, because a unique index exists, queries that filter or sort on that column often get faster as a side benefit, even though performance isn’t the reason you added the constraint.

A table can have any number of UNIQUE constraints, unlike PRIMARY KEY, which is allowed only once per table. This is the key difference between the two: a PRIMARY KEY is automatically NOT NULL and unique, and it identifies the row itself; a UNIQUE column enforces distinctness but still allows NULL values (with one notable exception, covered below), and a table can have several unique columns alongside its single primary key. For example, a users table might use an auto-incrementing id as the primary key, while also marking email and username as UNIQUE so no two accounts can collide on either.

You can apply UNIQUE to a single column (column-level) or to a combination of columns (table-level, sometimes called a composite or compound unique constraint). A composite UNIQUE (a, b) constraint does not require a or b individually to be unique — only the pairing of the two together must be. This is exactly the pattern used for join/junction tables, such as preventing a student from enrolling in the same course twice while still allowing the same student in many courses and the same course to have many students.

Syntax

-- Column-level, at table creation
CREATE TABLE table_name (
  column1 datatype UNIQUE,
  column2 datatype
);

-- Table-level, single column (equivalent to above)
CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  UNIQUE (column1)
);

-- Table-level, composite (multiple columns together)
CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  UNIQUE (column1, column2)
);

-- Adding uniqueness to an existing table via a unique index
CREATE UNIQUE INDEX index_name ON table_name (column1);

• Column-level UNIQUE — written right after the column’s data type; only applies to that one column.
• Table-level UNIQUE (a, b, …) — written as its own clause after all columns; lets you group multiple columns into one constraint.
• CREATE UNIQUE INDEX — the portable way to retrofit uniqueness onto a table that already exists. SQLite does not support ALTER TABLE ... ADD CONSTRAINT at all, so this is the standard workaround there; MySQL, PostgreSQL, and SQL Server support ALTER TABLE ... ADD CONSTRAINT name UNIQUE (column) directly, but a unique index works identically on all of them.
• NOT NULL — often paired with UNIQUE (as UNIQUE NOT NULL) when you also want to forbid missing values, since UNIQUE alone still permits NULL.

Examples

Example 1: Column-level UNIQUE on two columns

CREATE TABLE employees_demo (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE,
  username TEXT NOT NULL UNIQUE
);

INSERT INTO employees_demo (id, email, username) VALUES (1, 'ana@example.com', 'ana01');
INSERT INTO employees_demo (id, email, username) VALUES (2, 'bo@example.com', 'bo02');

SELECT * FROM employees_demo;

Result:

id	email	username
1	ana@example.com	ana01
2	bo@example.com	bo02

Both rows insert cleanly because every email and every username value is distinct. If a third row tried to reuse 'ana@example.com', the engine would reject it before it ever reached the table.

Example 2: Composite UNIQUE across two columns

CREATE TABLE enrollments (
  student_id INTEGER,
  course_id INTEGER,
  enrolled_on TEXT,
  UNIQUE (student_id, course_id)
);

INSERT INTO enrollments VALUES (1, 100, '2026-01-10');
INSERT INTO enrollments VALUES (1, 101, '2026-01-10');
INSERT INTO enrollments VALUES (2, 100, '2026-01-11');

SELECT * FROM enrollments ORDER BY student_id, course_id;

Result:

student_id	course_id	enrolled_on
1	100	2026-01-10
1	101	2026-01-10
2	100	2026-01-11

Notice student 1 appears twice and course 100 appears twice — neither column is unique on its own. What the constraint actually forbids is the exact pairing (1, 100) appearing a second time, which would mean the same student enrolling in the same course twice.

Example 3: Adding UNIQUE to an existing table, and how NULL behaves

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  phone TEXT
);

CREATE UNIQUE INDEX idx_contacts_phone ON contacts (phone);

INSERT INTO contacts (id, phone) VALUES (1, '555-0100');
INSERT INTO contacts (id, phone) VALUES (2, NULL);
INSERT INTO contacts (id, phone) VALUES (3, NULL);

SELECT * FROM contacts ORDER BY id;

Result:

id	phone
1	555-0100
2	NULL
3	NULL

All three rows insert successfully, including two rows with NULL phone numbers. In SQLite, PostgreSQL, and MySQL, NULL is never considered equal to another NULL, so a UNIQUE column can hold many NULLs at once — only actual duplicate non-null values are rejected. SQL Server is the notable exception: its plain UNIQUE constraint allows only one NULL per column, and you need a filtered unique index (WHERE phone IS NOT NULL) to get the same multi-NULL behavior shown here.

How It Works Step by Step

When you run an INSERT or an UPDATE against a column with a UNIQUE constraint, the engine does the following, in order:

• 1. It computes the new value(s) for the constrained column(s) for the row being written.
• 2. It probes the unique index that backs the constraint to see if that exact value (or combination, for a composite constraint) already exists among the other rows.
• 3. If a match is found, the entire statement is aborted and a constraint-violation error is returned — no partial write happens.
• 4. If no match is found, the row is written and the index is updated to include the new value, so the next check will see it.

This check happens per-row and per-statement, which matters for bulk operations: a multi-row INSERT that introduces two identical new values in the same statement will also fail, even though neither value existed in the table beforehand, because the engine treats the batch as trying to create a duplicate pair.

Common Mistakes

Mistake 1: Assuming the app can insert first and check later

Relying on “check if it exists, then insert” logic in application code is a race condition under concurrent requests, and even without concurrency it just crashes when violated:

CREATE TABLE users_bad (id INTEGER PRIMARY KEY, email TEXT UNIQUE);
INSERT INTO users_bad (id, email) VALUES (1, 'sam@example.com');
INSERT INTO users_bad (id, email) VALUES (2, 'sam@example.com');

The second INSERT fails with a UNIQUE constraint failed error because 'sam@example.com' already exists. The fix is to let the constraint do the work and handle the conflict explicitly, rather than pre-checking:

CREATE TABLE users_fixed (id INTEGER PRIMARY KEY, email TEXT UNIQUE);
INSERT INTO users_fixed (id, email) VALUES (1, 'sam@example.com');
INSERT OR IGNORE INTO users_fixed (id, email) VALUES (2, 'sam@example.com');
SELECT * FROM users_fixed;

Result: only one row, (1, 'sam@example.com') — the second insert is silently skipped instead of erroring. (In MySQL the equivalent is INSERT IGNORE; in PostgreSQL and SQL Server it’s an ON CONFLICT DO NOTHING / MERGE style upsert.)

Mistake 2: Trying to add UNIQUE with ALTER TABLE in SQLite

Many tutorials show adding a constraint after the fact with ALTER TABLE ... ADD CONSTRAINT. That works in MySQL, PostgreSQL, and SQL Server, but SQLite’s ALTER TABLE only supports adding/renaming/dropping columns and renaming the table itself — it has no ADD CONSTRAINT form at all:

ALTER TABLE employees ADD CONSTRAINT uq_department UNIQUE (department);

This statement fails outright in SQLite. The portable fix that works everywhere, including SQLite, is to create a unique index instead:

CREATE UNIQUE INDEX idx_customers_country ON customers (country);

This achieves the same enforcement as a named UNIQUE constraint and succeeds as long as the existing data in the column has no duplicates yet — if it does, you must resolve those duplicates first, since the engine will not create an index over data that violates it.

Best Practices

• Put UNIQUE on any column that represents a real-world identifier your business logic depends on being singular, such as email, username, SKU, or ISBN — don’t rely on application-level checks alone.
• Pair UNIQUE with NOT NULL when a missing value should be just as invalid as a duplicate one.
• Use a composite UNIQUE (a, b) constraint for junction/many-to-many tables to prevent duplicate relationship rows, rather than trying to enforce it in code.
• Remember that UNIQUE constraints are backed by an index, so they add a small write-time cost and storage overhead — don’t add them to columns that don’t need enforced uniqueness just for query speed; use a plain (non-unique) index instead.
• When you need to add uniqueness to a table that already has rows, first query for existing duplicates and resolve them before creating the constraint or index.
• Prefer CREATE UNIQUE INDEX when writing SQL you want to be portable to SQLite, since it works identically across all major engines, unlike ALTER TABLE ... ADD CONSTRAINT.
• Give named constraints (where your database supports naming them) meaningful names like uq_users_email so error messages and later migrations are easier to understand.

Practice Exercises

• Exercise 1: Create a table products_catalog with columns id, sku, and name, where sku must be unique. Insert three products with different SKUs, then try inserting a fourth with a SKU that duplicates one already in the table and observe what happens.
• Exercise 2: Create a table team_members with columns team_id, person_id, and a composite UNIQUE (team_id, person_id) constraint. Insert rows showing the same person on two different teams, and confirm that works, then try adding that same person to the same team twice and confirm it fails.
• Exercise 3: Given an existing table customers with a country column that currently has duplicate values, write the CREATE UNIQUE INDEX statement you would run, and explain what step must happen first before it will succeed.

Summary

• UNIQUE enforces that a column, or a combination of columns, never repeats a value across rows in the table.
• Internally it is implemented as a unique index, so violations are caught via a fast index lookup at write time.
• A table can have many UNIQUE constraints, unlike the single PRIMARY KEY; unlike PRIMARY KEY, UNIQUE columns still allow NULL (multiple NULLs, in most engines except SQL Server by default).
• Composite UNIQUE (a, b) constraints enforce uniqueness on the pairing, not on each column individually — ideal for junction tables.
• SQLite has no ALTER TABLE ... ADD CONSTRAINT; use CREATE UNIQUE INDEX instead, which is also portable to other engines.
• Handle constraint violations explicitly (e.g. INSERT OR IGNORE / ON CONFLICT) rather than pre-checking for duplicates in application code.