Skip to content
Sarj Sarj Sarj Standards

existing-table-check-or-foreign-key-requires-not-valid

CHECK and foreign-key constraints added to existing PostgreSQL tables should defer validation.

SARJ111 Rule source Tests Catalog JSON

Why

Validating a CHECK or foreign key while adding it scans existing rows while PostgreSQL holds locks that can block writes to the altered table and, for a foreign key, the referenced table.

Fix

Add the constraint with NOT VALID and commit that migration or transaction. Validate it in a later committed migration or transaction so the lower-lock validation scan does not inherit the ADD lock.

Examples

Before — flagged Constraint validated while it is added
supabase/migrations/001_age.sql
ALTER TABLE public.users
ADD CONSTRAINT check_age CHECK (age >= 18);
After — preferred Constraint validation is deferred to a later migration
supabase/migrations/001_age.sql
ALTER TABLE public.users
ADD CONSTRAINT check_age CHECK (age >= 18) NOT VALID;
supabase/migrations/002_validate_age.sql
ALTER TABLE public.users VALIDATE CONSTRAINT check_age;

Formerly: add-constraint-not-valid, add-constraint-requires-not-valid, existing-table-check-or-fk-requires-not-valid