Adding Check Constraints Online in MySQL

On PostgreSQL, adding a check constraint to a large table is a two-step, non-blocking routine. Teams bring the same expectations to MySQL 8.0, write ALTER TABLE orders ADD CONSTRAINT chk_orders_total CHECK (total >= 0), and discover that MySQL has no NOT VALID: it validates every existing row as part of the ALTER, and it does so by copying the whole table while concurrent writes wait. The same applies to turning a nullable column into NOT NULL. This guide explains what MySQL actually does when you add a check constraint or tighten nullability, how to make it tell you in advance whether a statement will block, and how to perform these changes online on large tables with gh-ost or pt-online-schema-change. It belongs to Adding Constraints Without Downtime.

MySQL 8.0 Constraint Changes and How They Run Matrix of constraint changes in MySQL 8.0 against the algorithm used, whether concurrent DML is allowed, and the online alternative for large tables. MySQL 8.0 Constraint Changes and How They Run Change Algorithm Concurrent writes Large-table approach ADD CHECK (8.0.16+) COPY (validates rows) blocked gh-ost / pt-osc DROP CHECK INPLACE, metadata allowed native NULL → NOT NULL rebuild limited gh-ost / pt-osc NOT NULL → NULL rebuild (INPLACE) allowed native, off-peak ADD UNIQUE INDEX INPLACE, LOCK=NONE allowed native; watch replica lag
Only unique indexes have a native online path; CHECK and NOT NULL changes validate by rebuilding, so large tables need an online schema change tool.

Symptom / Error Signatures

MySQL tells you plainly when a change cannot run the way you asked. The best habit is to ask for the online algorithm explicitly, so a blocking change fails immediately instead of blocking:

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: ... Try ALGORITHM=COPY.
ERROR 3819 (HY000): Check constraint 'chk_orders_total' is violated.
ERROR 1138 (22004): Invalid use of NULL value

The first means the change requires a table copy; the second means existing rows fail the new check (reported after scanning); the third means a NOT NULL change found NULLs. Without an explicit algorithm, the change simply runs as a copy: the processlist shows copy to tmp table for a long time and application writes to the table wait.

Root Cause Analysis

MySQL has enforced CHECK constraints since 8.0.16 (earlier versions parsed and ignored them). There is no deferred-validation form: adding a check constraint must prove it for existing rows, and InnoDB does that by rebuilding the table with the COPY algorithm, during which concurrent DML is not permitted. Changing a column from NULL to NOT NULL similarly requires a rebuild with validation. Dropping a check constraint, by contrast, is a metadata change.

The rebuild costs grow with table size and hit replicas twice: the primary blocks writes for the copy, and replicas replay the same ALTER as one statement afterwards, lagging by the full duration. Online schema change tools avoid both problems by creating a new table with the desired definition, copying rows in small throttled chunks, keeping it in sync, and swapping it in with a brief cut-over — because each copied row is inserted into a table that already has the constraint, validation happens row by row as part of the copy. That is why they are the standard way to add these constraints to large MySQL tables, as compared in gh-ost vs pt-online-schema-change.

How an Online Tool Adds a CHECK Constraint The online tool creates a ghost table with the new CHECK constraint, copies rows from the original in chunks where each insert is validated by the constraint, applies ongoing changes from the binlog or triggers, then atomically swaps the tables. A violating row makes the copy fail early without touching the original table. How an Online Tool Adds a CHECK Constraint orders (original) serving traffic _orders_gho has CHECK (total >= 0) Chunked copy each row validated on insert Binlog / triggers ongoing changes applied Cut-over atomic rename, seconds
Validation happens as rows are copied into a table that already has the constraint, so the original table never blocks.

Immediate Mitigation

1. Check existing data first. A violating row fails the change after all the copying; find it with a query instead.

-- MySQL 8.0 · read-only · run on a replica for large tables
SELECT COUNT(*) AS violations FROM orders WHERE total < 0;
SELECT COUNT(*) AS nulls FROM orders WHERE region IS NULL;

2. Ask MySQL whether the change can run online. Specify ALGORITHM=INPLACE, LOCK=NONE; if MySQL refuses, you know the plain statement would block.

-- MySQL 8.0 · migration session · fails fast instead of blocking
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD CONSTRAINT chk_orders_total CHECK (total >= 0), ALGORITHM=INPLACE, LOCK=NONE;
-- expected on 8.0: ERROR 1846 ... ALGORITHM=INPLACE is not supported

3. For small tables, run the copy in a quiet window. If the table copies in seconds, the plain statement with a short lock_wait_timeout is acceptable.

4. For large tables, use an online schema change tool.

# Shell · migration host · gh-ost user with REPLICATION CLIENT, REPLICATION SLAVE, ALTER privileges
# WARNING: a row that violates the new CHECK makes the copy fail; clean data first (step 1).
gh-ost --host=replica-a --database=shop --table=orders \
  --alter="ADD CONSTRAINT chk_orders_total CHECK (total >= 0), MODIFY region VARCHAR(32) NOT NULL" \
  --max-lag-millis=1500 --chunk-size=1000 \
  --postpone-cut-over-flag-file=/var/run/migrations/postpone-orders \
  --execute
# ROLLBACK PATH: before cut-over, stop gh-ost and drop _orders_gho/_orders_ghc; after, ALTER TABLE orders DROP CHECK chk_orders_total.

Throttling and cut-over for long runs are covered in throttling gh-ost on replica lag and cutting over gh-ost migrations safely.

Permanent Fix / Long-Term Pattern

Batch constraint tightening with other rebuild-requiring changes. Because each check constraint or nullability change on a large table costs a full online rebuild, combine them: the tightening steps of several expand-and-contract migrations can share one gh-ost run. Keep the data clean continuously — application-level validation plus a periodic violation query — so that when the rebuild runs, it does not fail on the last chunk.

Always write MySQL DDL with explicit ALGORITHM and LOCK clauses in migrations. It documents the expected behaviour and turns a surprise table copy into an immediate, explainable error in CI. For the checks that do not justify a rebuild — informative rules on huge, append-only tables, for example — consider enforcing them in the application and monitoring violations with a query, and record that decision next to the schema. The broader MySQL DDL caveats, such as implicit commits, are in avoiding implicit commits in MySQL DDL migrations.

One more MySQL-specific detail catches teams moving from PostgreSQL: check constraints in MySQL are schema-scoped by name, so two tables cannot both have a constraint called chk_total. Prefix names with the table (chk_orders_total) to avoid ERROR 3822: Duplicate check constraint name when similar rules are added to several tables. And if you use NOT ENFORCED to stage a constraint — define it now, enforce it later — remember that turning it on with ALTER TABLE ... ALTER CHECK chk_orders_total ENFORCED validates existing rows and therefore needs the same rebuild planning as adding it.

Adding a CHECK or NOT NULL in MySQL Decision tree. If existing rows violate the rule, clean them first. Otherwise, if the table copies in seconds, run the plain ALTER in a quiet window with a short lock_wait_timeout. Otherwise use gh-ost or pt-online-schema-change, batching other rebuild changes into the same run. Adding a CHECK or NOT NULL in MySQL Do existing rows violate the rule? yes no Clean data first, then retry Does the table copy in seconds? yes no Plain ALTER, quiet window gh-ost / pt-osc, batch changes
Clean data is a precondition either way; table size decides between a plain copy and an online tool.

Verification Checklist

Frequently Asked Questions

Does MySQL enforce CHECK constraints? Yes, from MySQL 8.0.16. Earlier versions accept the syntax but ignore the constraint, so check your server version before relying on them.

Is there a NOT VALID option in MySQL? No. MySQL validates existing rows when a check constraint is added, and a constraint can be marked NOT ENFORCED, which disables checking entirely rather than deferring it. For online addition to large tables, use an online schema change tool.

Why specify ALGORITHM=INPLACE, LOCK=NONE if it fails? Because the failure is the point: MySQL refuses immediately if the change cannot run online, instead of silently falling back to a blocking copy. It turns an operational surprise into a clear error during testing.

Does dropping a check constraint block? No. ALTER TABLE ... DROP CHECK name is a metadata change in MySQL 8.0 and completes quickly, needing only a brief metadata lock — still worth protecting with a short lock_wait_timeout.

Related Articles