disallowUniqueConstraint

Diagnostic Category: lint/safety/disallowUniqueConstraint

Since: vnext

Sources: - Inspired from: squawk/disallow-unique-constraint

Description

Disallow adding a UNIQUE constraint without using an existing index.

Adding a UNIQUE constraint requires an ACCESS EXCLUSIVE lock, which blocks all reads and writes to the table. Instead, create a unique index concurrently and then add the constraint using that index.

Examples

Invalid

ALTER TABLE table_name ADD CONSTRAINT field_name_constraint UNIQUE (field_name);

code-block.sql:1:1 lint/safety/disallowUniqueConstraint ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  × Adding a UNIQUE constraint requires an ACCESS EXCLUSIVE lock.

  > 1 │ ALTER TABLE table_name ADD CONSTRAINT field_name_constraint UNIQUE (field_name);
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i Create a unique index CONCURRENTLY and then add the constraint using that index.

ALTER TABLE foo ADD COLUMN bar text UNIQUE;

code-block.sql:1:1 lint/safety/disallowUniqueConstraint ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  × Adding a UNIQUE constraint requires an ACCESS EXCLUSIVE lock.

  > 1 │ ALTER TABLE foo ADD COLUMN bar text UNIQUE;
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i Create a unique index CONCURRENTLY and then add the constraint using that index.

Valid

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

How to configure

{
  "linter": {
    "rules": {
      "safety": {
        "disallowUniqueConstraint": "error"
      }
    }
  }
}