Unique indexes overview - PolarDB

A unique index enforces uniqueness on a column's values, or on the combined values of multiple columns. Only B-tree indexes support uniqueness.

CREATE UNIQUE INDEX name ON table (column [, ...]);

Parameter	Description
`name`	The name of the index to create.
`table`	The table the index belongs to.
`column [, ...]`	One or more columns to index. For multicolumn indexes, uniqueness is enforced across all listed columns together.

How it works

When you insert or update a row, the database checks the unique index and rejects duplicate indexed values before writing. The index is the enforcement mechanism — removing it also removes the uniqueness guarantee.

PolarDB automatically creates a unique index when you define a UNIQUE constraint or primary key on a table. The auto-created index covers the same columns as the constraint. Creating a separate index on those columns is redundant and provides no benefit.

Create a unique index

Single-column unique index

The following example creates a unique index on the email column of an employees table.

Create the table and add an email column:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name  VARCHAR(255) NOT NULL,
    last_name   VARCHAR(255) NOT NULL,
    email       VARCHAR(255)
);

Create the unique index:

CREATE UNIQUE INDEX idx_employees_email ON employees (email);

Insert a row:

INSERT INTO employees (first_name, last_name, email)
VALUES ('Jane', 'Doe', 'jane.doe@example.com');

Try to insert a second row with the same email:

INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Smith', 'jane.doe@example.com');

The database rejects the insert with an error similar to:

ERROR:  duplicate key value violates unique constraint "idx_employees_email"
DETAIL:  Key (email)=(jane.doe@example.com) already exists.

Multicolumn unique index

A multicolumn unique index rejects a row only when all indexed columns have equal values across rows. Individual columns may repeat as long as at least one column differs.

The following example enforces uniqueness on the combination of work_phone and extension, allowing multiple employees to share a work phone number as long as their extensions differ:

CREATE UNIQUE INDEX idx_employees_work_phone
    ON employees (work_phone, extension);

NULL values and uniqueness

By default, the unique index treats null values as distinct from each other, so a column with a unique index can contain multiple nulls. If the column is optional and multiple nulls are acceptable, no additional configuration is needed.

To prevent null values entirely, add a NOT NULL constraint to the column:

ALTER TABLE employees ALTER COLUMN email SET NOT NULL;

Verify automatically created indexes

When you define a primary key or a UNIQUE constraint, PolarDB creates a unique index automatically. Query pg_indexes to confirm:

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'employees';

The output shows all indexes on the table, including auto-created ones:

 tablename |          indexname          |                                        indexdef
-----------+-----------------------------+---------------------------------------------------------------------------------
 employees | employees_pkey              | CREATE UNIQUE INDEX employees_pkey ON public.employees USING btree (employee_id)
 employees | idx_employees_email         | CREATE UNIQUE INDEX idx_employees_email ON public.employees USING btree (email)

Usage notes

Only B-tree indexes can be declared unique.
Do not manually create a unique index on columns already covered by a UNIQUE constraint or primary key — the auto-created index already enforces uniqueness.