Skip to content

primary-key-constraint-creating-index (US015)#

Automatic fix is not available.

What it does#

Checks primary key constraint creating index.

Why not?#

Primary key constraint is supported by a unique index and a NOT NULL constraint in the background. Adding a primary 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. In case the primary key column(s) are nullable, create a non-validating check constraint: CHECK (column(s) IS NOT NULL) NOT VALID.
  3. Validate the constraint.
  4. Set the primary key column(s) as NOT NULL.
  5. Drop the constraint.
  6. Add the primary key constraint using the index created in step 1: ALTER TABLE .. ADD CONSTRAINT .. PRIMARY KEY USING INDEX ..