Adding Constraints Without Downtime

Constraints are the part of a schema that turns application assumptions into guarantees: every order has a customer, no two accounts share an email, a quantity is never negative. They are also the part of a schema change most likely to stall a production table, because adding a constraint to an existing table normally means proving it holds for every row that is already there — a full scan, performed while holding a lock that blocks writes. On a table with a hundred million rows that proof takes minutes, and for those minutes every insert and update queues. This part of Zero-Downtime Schema Evolution Patterns covers how to separate enforcing a constraint for new writes, which is instant, from validating it against existing rows, which can run online. It serves engineers tightening data integrity on live systems and DBAs who approve those changes.

Every technique here follows the same two-phase shape, and it is the constraint-level version of Expand and Contract Methodology: first make the database enforce the rule on new data without checking old data; then check old data under a lock that does not block normal traffic; then, where needed, promote the validated rule into its final form.

Concept & Mechanism

In PostgreSQL, ALTER TABLE ... ADD CONSTRAINT takes a lock on the table (for most constraint types ACCESS EXCLUSIVE; for foreign keys SHARE ROW EXCLUSIVE on both tables) and, by default, scans every existing row to prove the constraint holds, all before committing. The lock is held for the entire scan. The NOT VALID option, available for CHECK and FOREIGN KEY constraints, skips the scan: the constraint is recorded and enforced for all subsequent inserts and updates, but existing rows are not checked, so the lock is held only for a moment. A later ALTER TABLE ... VALIDATE CONSTRAINT performs the scan while holding only SHARE UPDATE EXCLUSIVE on the table (and ROW SHARE on a referenced table), which does not block reads or writes.

Two other constraint types have their own online paths. NOT NULL has no NOT VALID form, but since PostgreSQL 12, ALTER COLUMN ... SET NOT NULL skips its scan if a validated CHECK (col IS NOT NULL) constraint already exists — so you add the check NOT VALID, validate it, then set NOT NULL. UNIQUE and PRIMARY KEY constraints are backed by an index; you build that index with CREATE UNIQUE INDEX CONCURRENTLY and then attach it with ADD CONSTRAINT ... UNIQUE USING INDEX, which is a brief metadata operation.

MySQL 8.0 has a different toolset. CHECK constraints (enforced since 8.0.16) are validated when added and require a table rebuild via ALGORITHM=COPY, blocking writes; foreign keys can be added with ALGORITHM=INPLACE only when foreign_key_checks=0, which skips validation of existing rows entirely rather than deferring it. Unique indexes can be built with ALGORITHM=INPLACE, LOCK=NONE. For heavy changes on large MySQL tables, online schema change tools rebuild the table in the background, as described in Online Schema Change Tools.

Two properties of NOT VALID constraints are worth internalising. First, they are real constraints: inserts and updates that violate them fail immediately, so the application must already produce valid data before you add one. Second, an unvalidated constraint gives the planner and other features fewer guarantees — PostgreSQL will not use an unvalidated CHECK for constraint exclusion or for skipping the SET NOT NULL scan — so validation is a real step, not a formality.

Constraints also interact with replication in ways that matter on busy systems. On PostgreSQL, VALIDATE CONSTRAINT only reads, so it generates almost no WAL and replicas are unaffected apart from the usual lock replay. A plain ADD CONSTRAINT on a large table is similarly light on WAL, but its long exclusive lock is replayed on physical replicas too, where it can cancel conflicting read queries under max_standby_streaming_delay. On MySQL, a COPY rebuild is replayed on each replica as one long statement, lagging them by the full rebuild time — one more reason large MySQL changes go through online schema change tools that replicate as ordinary row changes.

Finally, remember what each constraint costs after it exists. Foreign keys add a lookup on the parent for every child insert or key update and a lookup on the child for every parent delete — cheap with the right indexes, expensive without. Check constraints are evaluated on every insert and update of the row, so keep their expressions simple and immutable. Unique constraints add an index that every write maintains. None of these costs is a reason to avoid constraints; they are reasons to add the supporting indexes and to review expressions as carefully as the migration that introduces them.

Prerequisites & Decision Criteria

Decide the approach from table size, write rate and engine.

Before adding any constraint to a live table:

• Existing data has been checked for violations with a query, and violating rows fixed or quarantined.
• The application version currently running never writes data that would violate the constraint.
• The migration sets lock_timeout (PostgreSQL) or lock_wait_timeout (MySQL), because even the instant phase needs a lock.
• Validation runs in a separate migration or step, so a slow scan never shares a transaction with the brief exclusive lock.
• Referenced columns for foreign keys are indexed on the referencing side, or validation and future deletes will be slow.

Step-by-Step Procedure

The procedure below adds a foreign key and a NOT NULL to a large PostgreSQL table; the other constraint types follow the same rhythm.

1. Find violating rows before touching the schema. A NOT VALID constraint does not care about old rows, but VALIDATE will fail on them — after scanning the whole table. Verify the count is zero before proceeding.

-- PostgreSQL · read-only · run on a replica if the table is large
SELECT count(*) AS orphans
FROM orders o LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id IS NOT NULL AND c.id IS NULL;
SELECT count(*) AS null_regions FROM orders WHERE region IS NULL;

2. Add the constraints as NOT VALID. Each takes its lock for milliseconds; new writes are checked from this moment.

-- PostgreSQL 12+ · migration role · brief locks only
-- WARNING: new writes that violate these constraints fail immediately after COMMIT.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders ADD CONSTRAINT orders_region_nn CHECK (region IS NOT NULL) NOT VALID;
COMMIT;
-- ROLLBACK PATH: ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_customer_fk, DROP CONSTRAINT IF EXISTS orders_region_nn;

3. Validate each constraint in its own statement. Validation scans under SHARE UPDATE EXCLUSIVE; reads and writes continue. Verify that each validation completes and that pg_constraint.convalidated is true before the next step.

-- PostgreSQL · migration role · run as separate statements (or separate migrations)
SET lock_timeout = '3s';
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_nn;

4. Promote the validated check to NOT NULL. PostgreSQL 12+ sees the validated check and skips the scan, so this is instant; the helper check can then be dropped.

-- PostgreSQL 12+ · migration role · instant because orders_region_nn is validated
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_region_nn;
COMMIT;

5. Add supporting indexes for foreign keys. PostgreSQL does not index the referencing column automatically; without an index, deletes on customers scan orders. Build it concurrently, per building indexes with CREATE INDEX CONCURRENTLY.

6. Record and monitor. Confirm the final state in the catalog and watch for constraint-violation errors from the application, which would mean some code path still writes invalid data.

Verification & Observability

The catalog records whether each constraint is validated:

-- PostgreSQL · read-only
SELECT conname, contype, convalidated
FROM pg_constraint
WHERE conrelid = 'orders'::regclass
ORDER BY conname;

During validation, watch progress and blocking. VALIDATE CONSTRAINT does not report progress in a dedicated view, but its session is visible in pg_stat_activity with its runtime, and pg_stat_user_tables.seq_scan increments when it starts. Confirm it is not blocking anyone with the lock-tree query in finding the blocking session with pg_blocking_pids. On MySQL, confirm constraints in information_schema.TABLE_CONSTRAINTS and information_schema.CHECK_CONSTRAINTS.

After the change, the most important signal is the application’s error rate for constraint violations — SQLSTATE 23503 (foreign key), 23514 (check), 23502 (not null), 23505 (unique) on PostgreSQL. A rise means a code path writes data the new rule rejects; find it before it becomes a customer-visible failure. The alerting side is covered in Migration Observability.

Rollback Path

Every phase is reversible with a drop, and drops are cheap: ALTER TABLE ... DROP CONSTRAINT takes a brief ACCESS EXCLUSIVE lock and does no scan. That makes constraints one of the safest changes to roll back — provided the drop also uses a lock timeout.

-- PostgreSQL · migration role · reverses any phase of the procedure
-- WARNING: DROP CONSTRAINT still needs a brief exclusive lock; keep the timeout.
SET lock_timeout = '3s';
ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_customer_fk;
ALTER TABLE orders ALTER COLUMN region DROP NOT NULL;

Roll back when the application turns out to write data that violates the rule and you cannot fix the writer quickly. After validation and promotion, removing a constraint is still safe for the database, but review whether any code now relies on the guarantee — for example, queries that assume every order has a customer. Removing constraints as part of a contract phase has its own sequencing, covered in dropping constraints safely during the contract phase.

Common Errors & Fixes

ERROR: insert or update on table "orders" violates foreign key constraint "orders_customer_fk" right after adding NOT VALID. Root cause: an application path writes orphaned rows. Fix: this is the constraint working; find the writer. If it cannot be fixed immediately, drop the constraint and retry after the fix.

ERROR: check constraint "orders_region_nn" of relation "orders" is violated by some row during VALIDATE. Root cause: violating rows existed or were written before the constraint was added. Fix: fix the rows (the constraint already prevents new ones), then rerun VALIDATE.

SET NOT NULL still takes minutes. Root cause: the check constraint is not validated, is written differently (for example CHECK (region <> '')), or the server is older than PostgreSQL 12. Fix: confirm convalidated = true and the exact form col IS NOT NULL.

ERROR: could not create unique index ... Key (email)=(a@example.com) is duplicated. Root cause: duplicates exist. Fix: deduplicate first; after a failed concurrent build, drop the invalid index before retrying — see adding unique constraints using an existing index.

Child Page Index

Each constraint type has its own guide. Adding foreign keys with NOT VALID and VALIDATE CONSTRAINT covers orphan cleanup, lock modes on both tables, and indexing the referencing column. Adding NOT NULL via a CHECK constraint in Postgres walks through the PostgreSQL 12+ proof-based SET NOT NULL. Adding unique constraints using an existing index builds the index concurrently and attaches it. Adding check constraints online in MySQL covers MySQL 8.0’s constraint behaviour and rebuild tools. And dropping constraints safely during the contract phase handles the reverse direction.

Framework-specific spellings of the same techniques appear in Django Migrations Without Downtime and Rails Active Record Migrations.

Frequently Asked Questions

Does a NOT VALID constraint protect new data? Yes. It is enforced for every insert and update after it is added. Only rows that existed before are unchecked until VALIDATE CONSTRAINT runs.

What lock does VALIDATE CONSTRAINT take? SHARE UPDATE EXCLUSIVE on the table being validated, which allows reads and writes but conflicts with other schema changes and with VACUUM. For foreign keys it also takes ROW SHARE on the referenced table.

Why can’t I just use NOT VALID for NOT NULL? PostgreSQL has no NOT VALID form of NOT NULL. The equivalent is a CHECK (col IS NOT NULL) NOT VALID constraint, validated online; PostgreSQL 12 and later then use it to set NOT NULL without scanning.

Is adding a foreign key in MySQL with foreign_key_checks=0 safe? It avoids the table copy, but it also skips validation entirely: existing orphaned rows remain and are never checked. Verify there are none with a query before adding the constraint this way.