SQL guide · Tables, views & databases (DDL)

SQL CREATE UNIQUE INDEX: Prevent Duplicate Values

CREATE UNIQUE INDEX builds an index like a normal CREATE INDEX, with one extra rule: the database rejects any insert or update that would put the same value into the indexed column, or the same combination of values across several columns. It gives you fast lookups and a data integrity guarantee in a single object.

A unique index and a UNIQUE constraint achieve almost the same result. In fact, most databases implement a UNIQUE constraint by creating a unique index behind the scenes. Use a constraint when you want the rule to be part of the table definition, and a unique index when you need features constraints do not offer, such as a partial index that only enforces uniqueness for some rows, or an expression like the lowercase form of an email address.

When you create the index on a table that already holds data, the build fails if duplicates exist. You must find and fix those rows first, usually with a GROUP BY ... HAVING COUNT(*) > 1 query. Once the index exists, inserting a duplicate raises an error that your application can catch and report.

Basic syntax

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

With a single column, that column must hold distinct values. With several columns, the combination must be distinct, so (1, 'a') and (1, 'b') can both exist. Whether NULL values count as duplicates depends on the database, as described in the dialect notes below.

SQL CREATE UNIQUE INDEX examples

Make email addresses unique

A unique index on customers.email stops two accounts from sharing an address and also makes login lookups fast. Any attempt to insert an existing email fails with a duplicate key error.

CREATE UNIQUE INDEX uq_customers_email
ON customers (email);

-- This second insert fails if the email already exists
INSERT INTO customers (customer_id, name, email)
VALUES (101, 'Dana Lee', 'dana@example.com');

Unique across a combination of columns

A student should only be enrolled once per course, but can appear in many courses. A composite unique index enforces exactly that pairing rule without restricting either column alone.

CREATE UNIQUE INDEX uq_enrollments_student_course
ON enrollments (student_id, course_id);

Find duplicates before creating the index

If the build fails because of existing duplicates, list them first. This query shows each email that appears more than once so you can merge or remove the extra rows.

SELECT email, COUNT(*) AS copies
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Partial unique index (PostgreSQL, SQLite, SQL Server)

Sometimes uniqueness applies only to active rows. A partial unique index lets a username be reused after an account is soft-deleted, while still blocking two active accounts with the same name.

CREATE UNIQUE INDEX uq_users_active_username
ON users (username)
WHERE deleted_at IS NULL;

Where SQL dialects differ

PostgreSQL: Treats NULLs as distinct by default, so multiple NULLs are allowed in a unique index. From PostgreSQL 15 you can add NULLS NOT DISTINCT to treat them as equal. Supports partial and expression unique indexes, and CONCURRENTLY for online builds.

MySQL: A unique index allows multiple NULL values in InnoDB. Partial indexes are not supported, but functional key parts (an expression in parentheses) are available from MySQL 8.0.13. A UNIQUE constraint and a unique index are the same object.

SQL Server: A unique index allows only one NULL per column, because NULLs are treated as equal. Use a filtered unique index with WHERE column IS NOT NULL to allow many NULLs. IGNORE_DUP_KEY = ON can silently skip duplicate rows during inserts.

SQLite: Unique indexes allow multiple NULLs. Partial unique indexes with a WHERE clause are supported, as are indexes on expressions since version 3.9.

Oracle: Unique indexes allow multiple rows where all indexed columns are NULL, but not duplicates of a partly NULL combination. A UNIQUE constraint will automatically create a unique index if none exists.

Common mistakes

  • Creating the index on a table that already has duplicates. The statement fails, so check with GROUP BY and HAVING first and clean the data.
  • Assuming NULLs are always blocked or always allowed. Behaviour differs by database, so test it, especially in SQL Server where only one NULL is permitted.
  • Using case-sensitive uniqueness by accident. 'Dana@Example.com' and 'dana@example.com' are different values unless you index lower(email) or use a case-insensitive collation.
  • Relying only on an application-side check. Two requests running at once can both pass the check, while the unique index guarantees only one insert succeeds.
  • Forgetting to handle the error. Catch the duplicate key error in your code, or use INSERT ... ON CONFLICT (PostgreSQL, SQLite) or ON DUPLICATE KEY UPDATE (MySQL) when the situation is expected.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator