Skip to content
Sarj Code 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;
Before — flagged A mixed-case quoted name does not exempt a different existing table
supabase/migrations/001_boundary.sql
CREATE TABLE "Users" (age integer);


ALTER TABLE users
ADD CHECK (age >= 18);
After — preferred Quoted lowercase names retain the fresh empty-table exemption
supabase/migrations/001_boundary.sql
CREATE TABLE "users" (age integer);


ALTER TABLE users
ADD CHECK (age >= 18);
Before — flagged An existing relation cannot inherit a former fresh spelling
supabase/migrations/001_renamed.sql
CREATE TABLE scratch (id bigint);


ALTER TABLE scratch
RENAME TO scratch_empty;


ALTER TABLE old_existing
RENAME TO scratch;


ALTER TABLE scratch
ADD CONSTRAINT positive_id CHECK (id > 0);
After — preferred A reused relation name safely defers constraint validation
supabase/migrations/001_renamed.sql
CREATE TABLE scratch (id bigint);


ALTER TABLE scratch
RENAME TO scratch_empty;


ALTER TABLE old_existing
RENAME TO scratch;


ALTER TABLE scratch
ADD CONSTRAINT positive_id CHECK (id > 0) NOT VALID;

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