Constraint Validation

Two Different Validation Moments

Constraint validation happens at two conceptually distinct times, and understanding the difference is essential for working with real, populated databases:

1. Statement-time validation — every ordinary INSERT or UPDATE is checked against all constraints currently defined on the table, immediately (by default; see Topic 10.11 for the deferred exception).
2. Constraint-addition-time validation — when you run ALTER TABLE ... ADD CONSTRAINT on a table that already contains rows, the engine must first verify that every existing row already satisfies the new rule before the constraint is allowed to attach.

Statement-Time Validation

sql
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    salary NUMERIC(10,2) CHECK (salary > 0)
);

INSERT INTO employees VALUES (1, -500);
-- ERROR: new row violates check constraint "employees_salary_check"

Each statement is checked in isolation, at the moment it executes (immediate constraints, the default for every constraint type in every engine unless you explicitly opt into deferred checking).

Constraint-Addition-Time Validation

sql
-- Table already has rows, some with salary = -200
ALTER TABLE employees ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);
-- ERROR: check constraint "chk_salary_positive" is violated by some row

The engine effectively runs an implicit validation scan equivalent to:

sql
SELECT COUNT(*) FROM employees WHERE NOT (salary > 0);

before it will commit the new constraint. If that count is nonzero, the ALTER TABLE is rejected outright.

Splitting Addition Into "Add Unchecked" + "Validate Later" (PostgreSQL)

For very large tables, scanning every row while holding a lock during ADD CONSTRAINT can be disruptive. PostgreSQL offers a two-step pattern:

sql
-- Step 1: add the constraint but skip validating existing rows (fast, brief lock)
ALTER TABLE employees ADD CONSTRAINT chk_salary_positive CHECK (salary > 0) NOT VALID;

-- Step 2: validate existing rows separately, without blocking concurrent writes as heavily
ALTER TABLE employees VALIDATE CONSTRAINT chk_salary_positive;

Critically, even in the NOT VALID interim state, the constraint is already fully enforced for all new INSERT/UPDATE statements — NOT VALID only means "we haven't yet confirmed the pre-existing rows comply," not "this constraint is inactive."

Edge Cases

• FOREIGN KEY validation at add-time requires a full scan of the child table checking every row against the parent — on large tables this can be slow and lock-heavy, which is exactly why the NOT VALID / VALIDATE CONSTRAINT split exists in PostgreSQL.
• Validating a UNIQUE constraint addition is effectively a duplicate-detection scan (GROUP BY ... HAVING COUNT(*) > 1), which the engine performs internally.
• Disabling constraint checking temporarily (e.g., MySQL's SET FOREIGN_KEY_CHECKS=0 or Oracle's ALTER TABLE ... DISABLE CONSTRAINT) is sometimes used for bulk data loads, but re-enabling afterward may itself trigger revalidation, and skipped-checking windows can let bad data slip in if not carefully audited afterward.

Key Takeaways / Q&A

Q: When is a constraint checked for an ordinary application INSERT? A: Immediately, at statement execution time, by default.

Q: What must happen before ADD CONSTRAINT succeeds on a populated table? A: The engine validates every existing row against the new rule; any violation blocks the operation.

Q: What does PostgreSQL's NOT VALID actually mean? A: The constraint is fully enforced going forward for new writes; only the retroactive check of pre-existing rows is deferred to a later VALIDATE CONSTRAINT step.