SDB401[medium]Constraints

CHECK constraint added without NOT VALID

Validating a new CHECK scans the whole table while holding a lock.

Why it's risky in PostgreSQL

ADD CONSTRAINT … CHECK validates every existing row while blocking writes. Adding it NOT VALID is instant; VALIDATE CONSTRAINT later only needs a lighter lock.

What triggers it, and the safer way

unsafe.sql

ALTER TABLE orders ADD CONSTRAINT qty_positive CHECK (qty > 0);

safer.sql

ALTER TABLE orders ADD CONSTRAINT qty_positive CHECK (qty > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT qty_positive;

Suppress it when you're sure

Put this comment on the line before the statement. The reason is required and shows in the report.

suppression

-- shadowdb:allow SDB401 <why this is safe here>

Applies to PostgreSQL 14, 15, 16, 17.