MySQL Foreign Keys: Evolution from 5.7 to 8.4

A payments team running MySQL 5.7 reached out after noticing that their nightly order reconciliation job had ballooned from 4 minutes to 47 minutes over six months. The culprit was not their query logic — it was the cascade of foreign key checks firing on every DELETE across three joined tables, each holding tens of millions of rows. They had designed a textbook-correct relational schema, and MySQL was enforcing every constraint exactly as specified, at a cost they had not anticipated when the tables were small. Foreign keys in MySQL are not free, and their behavior, metadata visibility, and DDL mechanics have changed meaningfully across 5.7, 8.0, and 8.4 LTS — in ways that directly affect how senior engineers should design and operate production schemas.

Foreign Key Basics: Syntax and Referential Actions

A foreign key constraint tells InnoDB to verify, on every write, that a value in a child column exists in the referenced parent column. The basic syntax has not changed across versions:

CREATE TABLE orders (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id BIGINT UNSIGNED NOT NULL,
    status      ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_customer_id (customer_id),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB;

The ON DELETE and ON UPDATE clauses accept four actions. Each has different write amplification consequences:

• RESTRICT / NO ACTION — Blocks the parent DELETE or UPDATE if any matching child row exists. Zero additional writes; one additional index read per statement. The safe default.
• CASCADE — Propagates the DELETE or UPDATE to all matching child rows automatically. Write amplification is unbounded: deleting one parent row can cascade through thousands of child rows and trigger their own FK checks on grandchild tables.
• SET NULL — Writes NULL to the child foreign key column. Requires the child column to be nullable. Causes one UPDATE per matching child row.
• SET DEFAULT — Accepted by the parser in MySQL 5.7 and 8.0 but rejected at runtime by InnoDB with ER_FK_CANNOT_USE_DEFAULT. Effectively unusable; avoid it.

You can inspect all FK constraints in the database without relying on SHOW CREATE TABLE:

SELECT
    rc.CONSTRAINT_NAME,
    rc.TABLE_NAME          AS child_table,
    rc.REFERENCED_TABLE_NAME AS parent_table,
    rc.UPDATE_RULE,
    rc.DELETE_RULE,
    kcu.COLUMN_NAME        AS child_column,
    kcu.REFERENCED_COLUMN_NAME AS parent_column
FROM information_schema.REFERENTIAL_CONSTRAINTS rc
JOIN information_schema.KEY_COLUMN_USAGE kcu
    ON kcu.CONSTRAINT_NAME   = rc.CONSTRAINT_NAME
   AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
WHERE rc.CONSTRAINT_SCHEMA = DATABASE()
ORDER BY rc.TABLE_NAME, rc.CONSTRAINT_NAME;

MySQL 5.7: What the Limitations Actually Cost You

No Atomic DDL

In MySQL 5.7, DDL operations write to two separate locations: the InnoDB data dictionary (inside .ibd files and the internal SYS tables) and the server-level .frm file in the filesystem. An ALTER TABLE that adds or drops a foreign key is not atomic. If the server crashes mid-operation, you can end up with an InnoDB dictionary that references a constraint that no .frm file acknowledges — or vice versa. Recovering from this state requires manual intervention with innodb_force_recovery and is genuinely painful on large tables.

FK Checks Are Not Parallelized

MySQL 5.7's InnoDB enforces FK checks serially, holding row locks during the check-and-write sequence. On a table with 50 million rows and a CASCADE DELETE originating from a single parent, the engine walks child rows one at a time, acquiring locks as it goes. This creates long lock chains that block concurrent writers and can trigger lock wait timeouts across the application even when those writers are touching completely unrelated rows.

FK Metadata Is Incomplete

In MySQL 5.7, information_schema.REFERENTIAL_CONSTRAINTS exists but is populated from the .frm file parser, not from a structured data dictionary. This means that in some edge cases — particularly after failed DDL operations or replica inconsistencies — the information_schema view and the actual InnoDB enforcement diverge. DBAs who relied on information_schema queries to audit FK coverage sometimes had a false picture of which constraints were actually being enforced.

Disabling FK Checks for Bulk Loads

The session variable foreign_key_checks disables FK enforcement for the current session. This is the standard approach for bulk data imports:

-- Disable for bulk load (session-scoped)
SET SESSION foreign_key_checks = 0;

LOAD DATA INFILE '/var/lib/mysql-files/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, customer_id, status, created_at);

SET SESSION foreign_key_checks = 1;

MySQL 8.0: Atomic DDL, Better Metadata, and Instant DDL

Atomic DDL

MySQL 8.0 introduced a transactional data dictionary stored in InnoDB system tables, replacing the .frm file architecture entirely. All DDL operations — including ALTER TABLE ... ADD CONSTRAINT, ALTER TABLE ... DROP FOREIGN KEY, and CREATE TABLE with FK definitions — are now atomic. A crash mid-DDL rolls back completely. The server restarts clean. The data dictionary and the enforcement layer are always in sync.

This eliminates an entire category of corruption recovery scenarios that 5.7 DBAs encountered after unexpected restarts during maintenance windows.

Improved FK Metadata in information_schema

In MySQL 8.0, information_schema views are backed by the transactional data dictionary directly. The FK metadata you read from REFERENTIAL_CONSTRAINTS and KEY_COLUMN_USAGE is authoritative — it reflects exactly what InnoDB enforces. The divergence problem from 5.7 is gone.

You can also query the mysql schema's internal dictionary tables directly for even more detail:

-- MySQL 8.0+ only: query the internal data dictionary
SELECT
    f.NAME                   AS constraint_name,
    f.FOR_NAME               AS child_table,
    f.REF_NAME               AS parent_table,
    f.DELETE_RULE,
    f.UPDATE_RULE,
    fe.FOR_COL_NAME          AS child_column,
    fe.REF_COL_NAME          AS parent_column
FROM mysql.foreign_keys f
JOIN mysql.foreign_key_column_usage fe
    ON fe.FK_ID = f.ID
ORDER BY f.FOR_NAME;

Instant DDL and FK Interactions

MySQL 8.0 (specifically 8.0.12+) introduced ALGORITHM=INSTANT for a subset of ALTER TABLE operations, allowing column additions without rebuilding the table. However, adding or dropping a foreign key constraint does not qualify for instant DDL — it always requires at minimum a metadata lock and in most cases a full table rebuild depending on the change. Use ALGORITHM=INPLACE where possible to avoid a full copy:

-- Adding an FK in 8.0: INPLACE avoids table copy in most cases
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_product
    FOREIGN KEY (product_id) REFERENCES products (id)
    ON DELETE RESTRICT,
    ALGORITHM=INPLACE,
    LOCK=NONE;

-- Dropping an FK: also supports INPLACE
ALTER TABLE orders
    DROP FOREIGN KEY fk_orders_product,
    ALGORITHM=INPLACE,
    LOCK=NONE;

MySQL 8.4 LTS: Tighter Validation and Deprecations

Stricter FK Validation on ALTER TABLE

MySQL 8.4, released as the first Long-Term Support version in the MySQL 8.x series, tightens constraint validation during ALTER TABLE in ways that can break upgrade paths from 5.7 or early 8.0 instances that accumulated technical debt in FK definitions. Specifically, 8.4 rejects ALTER TABLE statements that would leave a foreign key referencing a column with a mismatched character set or collation between parent and child — a mismatch that 5.7 and some 8.0 minor versions silently accepted.

-- This would succeed in 5.7 but fail in 8.4 if collations differ:
-- parent: customers.id is BIGINT UNSIGNED
-- child: orders.customer_id is BIGINT (signed)
-- 8.4 enforces strict type compatibility; audit before upgrading

SELECT
    kcu.TABLE_NAME,
    kcu.COLUMN_NAME,
    col_child.DATA_TYPE      AS child_type,
    col_child.COLUMN_TYPE    AS child_full_type,
    kcu.REFERENCED_TABLE_NAME,
    kcu.REFERENCED_COLUMN_NAME,
    col_parent.DATA_TYPE     AS parent_type,
    col_parent.COLUMN_TYPE   AS parent_full_type
FROM information_schema.KEY_COLUMN_USAGE kcu
JOIN information_schema.COLUMNS col_child
    ON col_child.TABLE_SCHEMA  = kcu.TABLE_SCHEMA
   AND col_child.TABLE_NAME    = kcu.TABLE_NAME
   AND col_child.COLUMN_NAME   = kcu.COLUMN_NAME
JOIN information_schema.COLUMNS col_parent
    ON col_parent.TABLE_SCHEMA = kcu.TABLE_SCHEMA
   AND col_parent.TABLE_NAME   = kcu.REFERENCED_TABLE_NAME
   AND col_parent.COLUMN_NAME  = kcu.REFERENCED_COLUMN_NAME
WHERE kcu.REFERENCED_TABLE_NAME IS NOT NULL
  AND kcu.TABLE_SCHEMA = DATABASE()
  AND col_child.COLUMN_TYPE != col_parent.COLUMN_TYPE
ORDER BY kcu.TABLE_NAME;

Run this query against your 5.7 or 8.0 instance before upgrading to 8.4. Any rows returned represent FK definitions that 8.4 may reject or behave differently on.

Deprecation of Implicit FK Index Management

In 8.4, MySQL formally deprecates the behavior where dropping an FK constraint also silently drops the supporting index when no other constraint or query relies on it. In earlier versions this was a convenient side effect; in 8.4 it emits a deprecation warning and in a future major version will require explicit DROP INDEX. Audit your maintenance scripts accordingly.

Performance Implications in Production

Write Overhead Per Statement

On every INSERT into a child table, InnoDB performs an index lookup on the parent table to verify the referenced value exists. On every DELETE from a parent table with RESTRICT, InnoDB performs an index lookup on the child table. With CASCADE, it performs additional writes. The overhead is proportional to the number of FK constraints on the table and the size of the indexes being checked. On a table receiving 50,000 inserts per second with two FK constraints, this can translate to 100,000 additional index reads per second that your buffer pool must serve.

Lock Contention with CASCADE

Cascaded deletes acquire shared locks on child rows during the lookup phase and exclusive locks during the write phase. On high-concurrency workloads, this is a deadlock generator. InnoDB's deadlock detector will resolve these, but each resolution kills a transaction that your application must retry. Monitor SHOW ENGINE INNODB STATUS\G for the LATEST DETECTED DEADLOCK section if you use CASCADE on high-write tables.

Gotcha: Circular Foreign Keys

MySQL allows circular FK references across tables — table A references table B, which references table A. InnoDB detects these during enforcement and will raise ER_CANNOT_ADD_FOREIGN in most cases, but certain construction orders at schema creation time can slip through. If they do, you will find that deleting any row in either table requires disabling foreign_key_checks, which defeats the purpose of the constraints entirely. Do not create circular FK graphs.

Gotcha: FKs on Partitioned Tables

This is a hard limitation in every MySQL release including 8.4: a partitioned table cannot be the child or parent in a foreign key constraint. Attempting it raises ER_FOREIGN_KEY_ON_PARTITIONED. If your schema uses partitioning for retention or performance and also needs referential integrity, you must enforce that integrity at the application layer.

When to Skip Foreign Keys Entirely

Foreign key constraints are the right choice for many schemas. They are the wrong choice in several well-defined scenarios that come up repeatedly in production environments:

High-Throughput OLTP

Any workload processing more than 10,000–20,000 writes per second on FK-constrained tables will see FK check overhead in profiling. At 100,000+ writes per second, it becomes a dominant cost. Teams running order ingestion pipelines, event logging, or financial transaction tables at this scale routinely disable FK constraints and rely on application-layer validation and periodic reconciliation queries to catch drift.

Sharded Databases

Foreign key enforcement requires both the parent and child row to be visible to the same InnoDB instance. In a sharded architecture, a customer row on shard 3 and an order row on shard 7 cannot have a database-level FK between them. Sharding and FK constraints are architecturally incompatible. Application-layer or service-layer integrity checks are mandatory.

Microservices with Separate Databases

A foreign key relationship implies that two entities belong in the same database. If your Order Service and Customer Service each own their own database — which is the correct design in a microservices architecture — there is no FK to add. Cross-service referential integrity is maintained through eventual consistency patterns: event-driven reconciliation, saga patterns, or scheduled integrity checks.

Comparison: FK Behavior Across MySQL Versions

Working with JusDB on MySQL Schema Design and Upgrades

JusDB manages MySQL for engineering teams who need production-grade reliability without the operational overhead of running their own DBA practice. Whether you are auditing an existing schema's FK coverage before a 5.7 to 8.4 LTS upgrade, redesigning a high-throughput write path to remove FK bottlenecks, or investigating lock contention from cascading deletes, our DBAs have done this work at scale across payments, logistics, and SaaS platforms.

We handle the full lifecycle: schema review, upgrade planning and execution, query optimization, replication health, and 24/7 incident response — so your team ships features instead of debugging InnoDB lock waits at 2 AM.

Explore JusDB MySQL Consulting →  |  Talk to a MySQL DBA

Related reading:

• MySQL InnoDB Locking Deep Dive: Row Locks, Gap Locks, and Deadlocks
• MySQL 8.0 Upgrade Guide: Production Checklist for 5.7 Migrations
• MySQL Table Partitioning Guide: RANGE, HASH, and LIST Strategies