Handling Foreign Keys With pt-online-schema-change

You launch pt-online-schema-change against a parent table — customers, say, or orders — and instead of copying rows it stops cold with a warning about child tables and foreign keys, or worse, it runs to completion and an hour later your application starts throwing Cannot add or update a child row: a foreign key constraint fails on inserts that worked yesterday. The tool’s whole design is a rename: it copies the table into a _new shadow and then swaps names so the shadow becomes the real table. Foreign keys are the one thing that rename cannot silently absorb, because a child table’s constraint is bound to the parent it was declared against, and pt-osc is about to pull that parent out from under it. This page explains exactly what breaks, walks through the --alter-foreign-keys-method choices that fix it — rebuild_constraints versus drop_swap — and the trade-off each one forces between a longer lock and a riskier atomic swap. It is the referential-integrity deep dive under the broader online schema change tools guide.

Symptom / Error Signatures

You are on this page because one of these exact signals appeared, either before the run started or after it finished:

  • pt-osc refuses to start and prints: The table ... has children that reference it via foreign key constraints. ... You did not specify --alter-foreign-keys-method.
  • The tool halts with Error creating triggers: ... foreign key constraint is incorrectly formed while installing its sync triggers on a table tangled in constraints.
  • A rebuild_constraints run aborts late with Error rebuilding foreign key constraints or a 1059: Identifier name '...' is too long — the renamed constraint blew past MySQL’s 64-character identifier limit.
  • The migration finished, but child inserts now fail: Error 1452: Cannot add or update a child row: a foreign key constraint fails (shop._orders_old, CONSTRAINT ...) — the child still references a table pt-osc dropped.
  • SHOW CREATE TABLE line_items reveals a REFERENCES _orders_old (...) clause pointing at the discarded copy, or an orphaned constraint with a leading-underscore name like _fk_order.
  • Child inserts briefly returned Error 1146: Table 'shop.orders' doesn't exist during the swap window — the fingerprint of a drop_swap cutover caught mid-flight.

Root Cause Analysis

The mechanism that makes pt-osc work is also what breaks foreign keys. As the deeper gh-ost vs pt-online-schema-change comparison covers, pt-osc copies the parent into a _parent_new shadow, keeps it current with AFTER triggers, then runs RENAME TABLE parent TO _parent_old, _parent_new TO parent. In InnoDB a foreign key is stored against the referenced table’s identity, not merely its name — so when the original orders is renamed to _orders_old, MySQL follows the child constraints along with it. Every child table that had REFERENCES orders now silently references _orders_old. The fresh orders (the promoted shadow) has no children pointing at it, and the moment pt-osc drops _orders_old, those constraints are pointing at a table that no longer exists. That is why inserts fail after an apparently successful run.

--alter-foreign-keys-method tells pt-osc how to keep the children attached to the right parent across that swap. The two real strategies trade the same axis in opposite directions.

rebuild_constraints is the default-preferred, integrity-first method. After the parent swap, pt-osc issues an ALTER TABLE on each child to drop its foreign key and re-add one that references the new parent. Integrity is never disabled, and no window exists where the parent table is missing. The cost is that each child gets a real ALTER TABLE, which itself takes a metadata lock and rebuilds that child — so a child table that is itself huge turns a fast parent migration into a slow, locking one. pt-osc also has to rename the constraint (it prepends underscores) to avoid a name collision, which is where the 64-character identifier limit bites.

drop_swap trades integrity guarantees for speed. It disables foreign_key_checks, drops the original parent, and renames the shadow into place — no child is rebuilt. It is fast and constant-time regardless of how large the children are, but it opens a brief window in which the parent table does not exist (child queries can hit Table doesn't exist) and referential checks are off, so a bad write can slip through unvalidated. If the rename fails after the drop, the parent is simply gone. auto lets pt-osc pick — it leans toward rebuild_constraints unless the children look small enough — and none leaves the constraints broken for you to repair by hand.

Decision axis rebuild_constraints drop_swap
Child tables ALTER TABLE each child to re-point the FK Untouched — no child rebuild
Integrity during swap Never disabled foreign_key_checks=0 during the swap
Parent-missing window None — parent always present Brief — table absent between drop and rename
Speed Scales with child table size (can be slow) Near-constant, independent of child size
Failure blast radius Contained; original recoverable Parent can be lost if rename fails mid-swap
Constraint naming Renamed (underscore prefix); 64-char limit risk Preserved
Safe for Small-to-moderate children, strict integrity Large children, a genuinely quiet write window
pt-osc rename: how the child FK follows the parent to _orders_old Before the swap the child line_items references orders while _orders_new is built alongside. After RENAME the FK follows orders to _orders_old, leaving the promoted orders childless. rebuild_constraints re-points the FK to orders; drop_swap drops _orders_old with foreign_key_checks off. 1 · Before swap line_items child · FK REFERENCES orders _orders_new shadow copy 2 · After RENAME TABLE line_items FK dragged along FK now points here orders promoted · no child _orders_old about to drop 3 · The fix rebuild_constraints ALTER each child: drop FK, re-add REFERENCES orders integrity never off drop_swap foreign_key_checks = 0, drop _orders_old, rename in no child rebuilt
The RENAME drags the child FK onto _orders_old; rebuild_constraints re-points it, drop_swap discards the old copy with checks off.

Immediate Mitigation

If a run just failed or a child table is now throwing constraint errors, act in order.

1. Inventory the children before doing anything else. You cannot choose a method without knowing how many child tables reference the parent and how large they are.

-- MySQL 8.0 · read-only · run as any role with access to information_schema
-- Lists every child table whose FK references the parent you are about to migrate.
SELECT TABLE_NAME AS child_table, CONSTRAINT_NAME, COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'shop'
  AND REFERENCED_TABLE_NAME  = 'orders';

2. If nothing has run yet, choose the method explicitly — never let it default silently. For moderate children, run rebuild_constraints; the parent is never absent and integrity stays on.

# bash · run from a host that can reach the primary · safe only at low-write windows
# rebuild_constraints re-points each child FK with an ALTER TABLE after the parent swap.
pt-online-schema-change \
  --alter="MODIFY COLUMN total_amount DECIMAL(14,2) NOT NULL" \
  --alter-foreign-keys-method=rebuild_constraints \
  --max-load="Threads_running=20" \
  --critical-load="Threads_running=60" \
  --chunk-size=500 \
  D=shop,t=orders --execute

3. Only if a child table is too large to rebuild in your lock budget, use drop_swap — and gate it hard. It is fast, but the parent briefly vanishes and FK checks are off, so treat it as a maintenance-window operation.

# bash · run ONLY in a scheduled low/zero-write window · parent is briefly absent
# drop_swap disables foreign_key_checks and renames in place — no child rebuild.
pt-online-schema-change \
  --alter="MODIFY COLUMN total_amount DECIMAL(14,2) NOT NULL" \
  --alter-foreign-keys-method=drop_swap \
  --set-vars="lock_wait_timeout=3" \
  D=shop,t=orders --execute

4. If a prior run already left children pointing at _orders_old, re-point them by hand. Drop the broken constraint and re-add it against the live parent, one child at a time.

-- MySQL 8.0 · migration role · run at a quiet moment · each ALTER locks that child
-- Confirm the current (broken) constraint name first: SHOW CREATE TABLE line_items\G
ALTER TABLE line_items
  DROP FOREIGN KEY _fk_line_items_order;      -- the underscore-prefixed leftover
ALTER TABLE line_items
  ADD CONSTRAINT fk_line_items_order
  FOREIGN KEY (order_id) REFERENCES orders (id);
Choosing the foreign-key method: a decision tree If no child references the parent, run pt-osc normally. Otherwise, if no child is too large to ALTER, use rebuild_constraints. If a child is too large, use drop_swap only when a true low-write window exists; otherwise postpone or split the change. A side note reminds to rename constraints near 64 characters first. Child tables reference the parent? no yes Run pt-osc normally — no FK dance Any child too large to ALTER within lock budget? no yes rebuild_constraints integrity stays on True low / zero-write window available? yes no drop_swap in the window, fail-fast lock Postpone or split expand-and-contract instead Side check · constraint name near 64 chars? rename it shorter first so the underscore-prefixed rebuild does not overflow
Pick the method by walking three questions: does a child reference the parent, is any child too big to rebuild, and is there a genuine quiet window.

Permanent Fix / Long-Term Pattern

The durable fix is to stop treating foreign keys as a runtime surprise and fold the decision into how you plan any table this size. Before pt-osc ever touches a parent, the child inventory from step 1 should be a required input, captured in the migration’s review — the same discipline your schema version control ledger already applies to ordering and immutability. Codify a rule: rebuild_constraints is the default because it never drops integrity or the parent; drop_swap is an explicit, reviewed exception reserved for the case where a child table is large enough that rebuilding it inside your lock budget is impossible, and only inside a genuine maintenance window with a short lock_wait_timeout so a blocked swap fails fast instead of queueing traffic.

For the hardest tables — a parent with several large children where neither method is comfortable — the deeper answer is to avoid the FK dance entirely by making the change through the expand-and-contract methodology instead of a single rewriting ALTER: add the new column, backfill it, cut reads over, and drop the old column in a later step, so no whole-parent rename is needed. Where a rewrite is genuinely unavoidable and the database server emits a ROW-format binlog, reconsider the tool itself — gh-ost refuses child-FK tables by default precisely to avoid this class of bug, and the gh-ost vs pt-online-schema-change trade-off may point you away from triggers for this particular table. Whichever route you take, rehearse it first against a production-like copy per your environment parity baseline, because the constraint-name and child-size problems both surface on real data long before they surface in a plan. And remember that MySQL cannot roll back a RENAME TABLE transactionally — the transactional versus non-transactional DDL reality means once the swap lands, the only way back is forward.

rebuild_constraints vs drop_swap on a timeline rebuild_constraints copies the parent, renames it, then runs a sequence of per-child ALTER TABLE blocks each holding its own lock. drop_swap copies, then does a single thin swap band marked FK checks off and parent absent. rebuild_constraints copy rows + triggers parent RENAME ALTER child A ALTER child B ALTER child C each child holds its own lock — scales with child size drop_swap copy rows + triggers swap FK checks off · parent absent one thin band — no child touched, constant time
rebuild_constraints pays a per-child ALTER lock after the rename; drop_swap collapses cutover to one thin band that runs with FK checks off and the parent briefly absent.

Verification Checklist

Frequently Asked Questions

Why does pt-online-schema-change refuse to run when foreign keys reference my table? Because its cutover is a RENAME TABLE, and renaming the parent silently drags every child constraint along to the renamed _old copy, which pt-osc then drops — leaving the children pointing at a table that no longer exists. Rather than guess, the tool stops and demands you pick --alter-foreign-keys-method so you consciously decide how the children get re-attached. Passing the flag is mandatory whenever child foreign keys exist; there is no safe default it can assume for you.

What is the practical difference between rebuild_constraints and drop_swap? rebuild_constraints runs an ALTER TABLE on each child after the swap to re-point its foreign key at the new parent — integrity is never disabled and the parent is never missing, but each child is rebuilt under its own lock, so large children make it slow. drop_swap disables foreign_key_checks, drops the old parent, and renames the shadow into place without touching any child — near-instant regardless of child size, but it opens a brief window where the parent is absent and unvalidated writes can slip through. Use rebuild_constraints by default; reserve drop_swap for large children inside a real maintenance window.

My migration finished but child inserts now fail with a foreign key error — what happened? A run completed with the constraints still pointing at the dropped _<table>_old copy, usually because --alter-foreign-keys-method=none was used or a rebuild_constraints step failed partway. Run SHOW CREATE TABLE on the child; if you see REFERENCES _orders_old, drop that constraint and re-add one referencing the live parent, one child at a time. Verify afterward that a normal child insert succeeds and a deliberately invalid one is still rejected, proving integrity is genuinely restored rather than merely quiet.

Up one level: Online Schema Change Tools

Related Articles