Skip to content
Sarj Standards

SQL · performance

add-constraint-requires-not-valid

sql:add-constraint-requires-not-valid

ADD CONSTRAINT (CHECK/FK) without NOT VALID blocks writes during full-table validation.

Code
SARJ111
Default
error
Fix
none
Languages
sql

Why

Validating a new CHECK or foreign key while adding it can hold disruptive locks while PostgreSQL scans existing rows.

Fix

Add the constraint as NOT VALID, then validate it in a separate ALTER TABLE statement.

Before / after

Executed by this rule’s unit tests.

Before

Constraint validated while it is added

supabase/migrations/001_age.sql · focus
ALTER TABLE users ADD CONSTRAINT check_age CHECK (age >= 18);

After

Constraint added without scanning existing rows

supabase/migrations/001_age.sql · focus
ALTER TABLE users ADD CONSTRAINT check_age CHECK (age >= 18) NOT VALID;

Limits

  • Only PostgreSQL migration files and CHECK or foreign-key constraints added to existing tables are inspected.

Formerly: add-constraint-not-valid