Skip to content

unique-key-constraint-creating-index (US014)#

Automatic fix is not available.

What it does#

Checks unique key constraint creating index.

Why not?#

Unique key constraint is supported by a unique index in the background. Adding a unique key constraint to an already populated table will create a unique index in non-concurrent mode, scanning and validating that there are no violating records in the table. This causes the table to be locked, in which no other operations can be performed on the table for the duration of the validation. This will cause downtime if the table is concurrently being accessed by other clients.

When should you?#

If the table is empty. If the table is not empty but is not being concurrently accessed.

Use instead:#

  1. Create a unique index in concurrent mode: CREATE UNIQUE INDEX CONCURRENTLY .. ON .. (..).
  2. Add the unique constraint using the index created in step 1: ALTER TABLE .. ADD CONSTRAINT .. UNIQUE USING INDEX ..