MySQL Foreign Keys: What’s New and What’s Changed

A Foreign Key (FK) is the glue that holds a relational database together. It ensures that a column in a child table references a valid, existing column in a parent table.

For years, MySQL DBAs handled Foreign Keys with a mix of caution and superstition. In older versions, a simple schema change could result in server crashes or corrupted metadata. However, the architecture backing these constraints has undergone a massive overhaul.

What was once fragile and file-based in MySQL 5.7 became transactional and atomic in 8.0, and is now stricter and more standard-compliant in MySQL 8.4 LTS.

Here is a look at what changed, why it matters, and how to adapt.

1. Atomic DDL: No More "Zombie" Files

The biggest leap from MySQL 5.7 to 8.0 was the removal of file-based metadata.

Before MySQL 8.0

In MySQL 5.7, table metadata was stored in .frm files on the disk. Because the file system and the database storage engine were separate entities, DDL operations (like adding a Foreign Key) were not atomic.

If you tried to add a Foreign Key to a large table and the server crashed during the process, or if the connection was lost, MySQL often left behind temporary "garbage" files. The metadata would become inconsistent, and you would find orphaned files in your data directory.

The MySQL 5.7 Crash Scenario:

‍Imagine trying to add a constraint to big_child_new. If the server crashes mid-operation:

mysql> show create table big_child\G
*************************** 1. row ***************************
       Table: big_child
Create Table: CREATE TABLE `big_child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `payload` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51553265 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table big_child_new\G
*************************** 1. row ***************************
       Table: big_child_new
Create Table: CREATE TABLE `big_child_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `payload` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25000001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE big_child_new
ADD CONSTRAINT fk_parent_link_new
FOREIGN KEY (parent_id) REFERENCES parent_data(id);
ERROR 2013 (HY000): Lost connection to MySQL server during query

If you looked at the disk after this error, you would see this:

rw-r----- 1 mysql mysql 1.4G Dec 17 22:00  big_child_new.ibd
-rw-r----- 1 mysql mysql 8.5K Dec 17 22:02 '#sql-200349_2.frm'
-rw-r----- 1 mysql mysql  48M Dec 17 22:02 '#sql-200349_2.ibd'
root@warehouse-b2b-db-playground-none-8815631:/var/lib/mysql/test# 

The operation failed, but the cleanup didn't happen. You were left with junk files consuming space and potentially blocking future DDL operations.

MySQL 8.0: The Transactional Data Dictionary

MySQL 8.0 introduced a centralized Transactional Data Dictionary. Metadata is no longer stored in .frm files; it resides inside InnoDB tables.

This enables Atomic DDL.

Either the entire DDL succeeds — or nothing is committed.

If the foreign key creation fails , MySQL simply rolls back the transaction internally. It doesn't leave orphaned files on your disk because the changes were never "committed" to the data dictionary.

mysql> ALTER TABLE big_child_new ADD CONSTRAINT fk_child_to_child FOREIGN KEY (parent_id) REFERENCES big_child(id);
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR: 
Can't connect to the server

When the server comes back up, the state is clean. No manual cleanup is required.

2. The Evolution of Error Messages

From the mysql 8.0 we could see that server is able to provide the error where exactly the error occurred.

If you have debugged Foreign Key errors in MySQL 5.7, you know the frustration. The error messages were often cryptic, referencing internal temporary table names rather than your actual tables.

Mysql 5.7 Error:

ysql> ALTER TABLE products  ADD CONSTRAINT fk_product_category  FOREIGN KEY (cat_id)  REFERENCES categories(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-239e50_2`, CONSTRAINT `fk_product_category` FOREIGN KEY (`cat_id`) REFERENCES `categories` (`id`))
mysql> 

Note the reference to #sql-239e50_2. This tells you nothing about which table actually has the bad data.

Mysql 8.0 Error

MySQL 8.0 finally speaks human. It points directly to the table name and, in type mismatch cases, tells you exactly what is incompatible.‍

mysql> INSERT INTO products (product_id, cat_id) VALUES  (106, 6);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`products`, CONSTRAINT `fk_product_category` FOREIGN KEY (`cat_id`) REFERENCES `categories` (`id`))

If the data types don't match, 8.0 is explicit:

mysql> ALTER TABLE products  ADD CONSTRAINT fk_product_category  FOREIGN KEY (cat_id)  REFERENCES categories(id);
ERROR 3780 (HY000): Referencing column 'cat_id' and referenced column 'id' in foreign key constraint 'fk_product_category' are incompatible.
mysql> 

3. MySQL 8.4 LTS: Enforcing the Standard

MySQL 8.4 Long Term Support (LTS) takes relational integrity a step further by deprecating loose behaviors that were previously allowed.

The "Unique Key" Requirement

Strict SQL standards dictate that a Foreign Key must reference a UNIQUE or PRIMARY KEY in the parent table. Historically, MySQL allowed you to reference a non-unique index, which could lead to ambiguous data relationships.

In MySQL 8.4, this is restricted by default.‍

mysql> CREATE TABLE parent_test (     id INT,     INDEX (id)  ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE child_test (
    ->     child_id INT PRIMARY KEY,
    ->     parent_id INT,
    ->     FOREIGN KEY (parent_id) REFERENCES parent_test(id)
    -> ) ENGINE=InnoDB;
ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'child_test_ibfk_1' in the referenced table 'parent_test'
mysql> 

This behavior is controlled by the variable restrict_fk_on_non_standard_key, which defaults to ON.

How to Bypass (Not Recommended):

‍If you are migrating a legacy application that relies on this non-standard behavior, you can temporarily disable the check:

mysql> show global variables like 'restrict_fk_on_non_standard_key';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| restrict_fk_on_non_standard_key | ON    |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> SET SESSION restrict_fk_on_non_standard_key = OFF;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE child_test_bypass (
    ->     child_id INT PRIMARY KEY,
    ->     parent_id INT,
    ->     FOREIGN KEY (parent_id) REFERENCES parent_test(id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show create table child_test_bypass\G
*************************** 1. row ***************************
       Table: child_test_bypass
Create Table: CREATE TABLE `child_test_bypass` (
  `child_id` int NOT NULL,
  `parent_id` int DEFAULT NULL,
  PRIMARY KEY (`child_id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `child_test_bypass_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent_test` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Interactions with Composite Primary Keys

MySQL 8.4 also tightens the rules on Composite Keys (Primary Keys made of multiple columns).

Previously, you could create a Foreign Key that referenced only part of a composite Primary Key in the parent table. In 8.4, unless that specific part has its own UNIQUE constraint, this is forbidden.

The Scenario:

• Parent Table PK: (col1, col2)
• Child Table tries to reference: parent(col1)

MySQL 8.0 Behavior: Allowed.
MySQL 8.4 Behavior:

mysql>  CREATE TABLE parent_composite (
    col1 INT,
    col2 INT,
    PRIMARY KEY (col1, col2)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)


mysql> CREATE TABLE child_composite (     id INT PRIMARY KEY,     ref_col1 INT,     FOREIGN KEY (ref_col1) REFERENCES parent_composite(col1) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

To fix this in 8.4, your Foreign Key must reference the full composite key or a column that has its own unique index.

mysql> CREATE TABLE parent_composite (
    ->     col1 INT,
    ->     col2 INT,
    ->     PRIMARY KEY (col1, col2)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE child_composite (     id INT PRIMARY KEY,     ref_col1 INT,     FOREIGN KEY (ref_col1) REFERENCES parent_composite(col2) ) ENGINE=InnoDB;
ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'child_composite_ibfk_1' in the referenced table 'parent_composite'

mysql> CREATE TABLE child_composite (     id INT PRIMARY KEY,     ref_col1 INT,     ref_col2 INT,    FOREIGN KEY (ref_col1) REFERENCES parent_composite(col1) ) ENGINE=InnoDB;
ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'child_composite_ibfk_1' in the referenced table 'parent_composite'




mysql> CREATE TABLE `child_composite` (
    ->   `id` int NOT NULL,
    ->   `ref_col1` int DEFAULT NULL,
    ->   `ref_col2` int DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `ref_col1` (`ref_col1`,`ref_col2`),
    ->   CONSTRAINT `child_composite_ibfk_1` FOREIGN KEY (`ref_col1`, `ref_col2`) REFERENCES `parent_composite` (`col1`, `col2`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

‍Foreign Key Evolution: MySQL 5.7 vs. 8.0 vs. 8.4 LTS

The evolution of Foreign Keys in MySQL demonstrates a clear path toward stability and strict SQL compliance. While MySQL 8.0 solved the operational headaches of crashes and metadata corruption, MySQL 8.4 ensures your data models are architecturally sound by enforcing strict relational rules.

If you are planning an upgrade to 8.4, audit your schema for Foreign Keys that reference non-unique indexes or partial composite keys to avoid upgrade blockers.

Moving from the fragility of MySQL 5.7 to the strict integrity of 8.4 LTS requires precise schema validation to prevent application downtime. Mydbops provides the audit and database management expertise to keep your foreign keys consistent and your production environment stable during every architectural shift.