SQL CREATE UNIQUE INDEX

What does SQL CREATE UNIQUE INDEX do?

Creates an index that guarantees that the indexed columns contain only unique combinations of values.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CREATE UNIQUE INDEX: PostgreSQL, MySQL/MariaDB (CREATE UNIQUE INDEX or UNIQUE KEY), SQL Server, Oracle, SQLite, Snowflake, Redshift, DB2

SQL CREATE UNIQUE INDEX Full Explanation

CREATE UNIQUE INDEX builds a physical index structure on one or more columns and simultaneously enforces a uniqueness constraint. During creation, the database scans existing data and aborts if duplicate combinations are found. After creation, any INSERT or UPDATE that would duplicate an indexed key fails with an error. Because the index is unique, the optimizer can use it for fast look-ups and to eliminate duplicates in query plans. Some systems automatically create a unique index when you declare a PRIMARY KEY or UNIQUE constraint; CREATE UNIQUE INDEX is the manual, fully-qualified form that offers finer control over index name, included columns, sort order, storage parameters, and, in some dialects, filtered conditions or tablespaces. Be aware that uniqueness rules usually treat NULL as a distinct value per row in PostgreSQL, Oracle, and SQLite, but treat multiple NULLs as duplicates in SQL Server unless otherwise configured.

SQL CREATE UNIQUE INDEX Syntax

CREATE UNIQUE INDEX index_name
    ON table_name (column1 [ASC|DESC] [, column2 ...])
    [INCLUDE (non_key_column [, ...])]      -- supported in PostgreSQL, SQL Server
    [WHERE predicate]                       -- partial / filtered index
    [WITH (storage_parameter = value [, ...])];

SQL CREATE UNIQUE INDEX Parameters

index_name (identifier) - Name of the index to create.
table_name (identifier) - Table that owns the index.
column_list (identifiers) - One or more columns whose combined values must be unique.
ASC (DESC) - keyword|||Optional sort direction per column.
INCLUDE (keyword) - Extra columns stored in index but not part of key (not all dialects).
WHERE predicate (expression) - Optional filter for a partial/filtered unique index.
WITH (...) - clause – Storage parameters such as fillfactor or tablespace.

Example Queries Using SQL CREATE UNIQUE INDEX

-- 1. Ensure each email appears only once in users
CREATE UNIQUE INDEX ux_users_email
    ON users (email);

-- 2. Composite unique index on order_id + product_id
CREATE UNIQUE INDEX ux_order_items_unique
    ON order_items (order_id, product_id);

-- 3. Partial unique index: only active records must be unique
CREATE UNIQUE INDEX ux_active_usernames
    ON accounts (username)
    WHERE deleted_at IS NULL;

-- 4. Include clause for covering queries (PostgreSQL / SQL Server)
CREATE UNIQUE INDEX ux_invoices_number
    ON invoices (invoice_number)
    INCLUDE (customer_id, total_amount);

Expected Output Using SQL CREATE UNIQUE INDEX

- Index metadata added to catalog
- Unique constraint enforced from this point forward
- If duplicates exist at creation time, the statement fails and no index is created

Use Cases with SQL CREATE UNIQUE INDEX

Enforce business rules such as unique emails, usernames, or SKU codes.
Speed up equality joins and look-ups on natural keys.
Replace a UNIQUE constraint when you need INCLUDE columns or partial filtering.
Add uniqueness to a subset of rows (e.g., where deleted_at IS NULL).

Common Mistakes with SQL CREATE UNIQUE INDEX

Attempting to create the index when duplicate values already exist.
Forgetting that multiple NULLs are allowed in some databases, leading to unexpected duplicates.
Omitting a schema qualifier for index or table names, causing creation in the wrong namespace.
Assuming INCLUDE or WHERE is supported in all dialects.
Creating overlapping unique indexes that bloat storage without added benefit.

First Introduced In

SQL-92 (formalized); available earlier in vendor implementations such as Oracle 7 and PostgreSQL 6

Frequently Asked Questions

What happens if duplicates already exist?

The command aborts with an error and no index is created. Remove or update duplicate rows, then rerun CREATE UNIQUE INDEX.

Is CREATE UNIQUE INDEX faster than adding a UNIQUE constraint?

Speed is identical because both create the same underlying structure. Use CREATE UNIQUE INDEX when you need extra options like INCLUDE or WHERE.

How do I add a unique index on a JSON field?

Cast or extract the JSON value to a deterministic type in the index expression, for example:CREATE UNIQUE INDEX ux_contacts_email ON contacts ((email_json->>'address'));

Can I convert an existing non-unique index to unique?

Most dialects do not allow ALTER INDEX to change uniqueness. Drop the old index and recreate it as UNIQUE.