MySQL 9.6 Changes Foreign Key Constraints and Cascade Handling

"Historically, MySQL enforced foreign key constraints and cascades at the storage engine layer. All cascaded operations were executed internally by InnoDB. Because these changes were hidden from the SQL engine and binary logs, downstream systems, such as CDC pipelines and analytics platforms could miss them. This could lead to inconsistent data, unreliable analytics, and replication issues."

"For example, the SQL layer issues a single DELETE statement to InnoDB, and InnoDB might automatically delete rows in a child table due to cascade rules. These additional deletions are currently handled entirely within the storage engine and are not recorded in the binary log, which captures only the original DELETE on the parent table, not the changes made to the child table."

"Extensive benchmarking across common transactional workloads confirms that SQL engine based foreign key enforcement and cascade performs nearly identically to the InnoDB approach. The cost of foreign key checks and cascades remains minimal."

MySQL 9.6 introduces a significant architectural change by shifting foreign key constraint enforcement and cascade operations from the InnoDB storage engine to the SQL layer. Previously, cascading deletions and updates were handled internally by InnoDB without being recorded in the binary log, causing CDC pipelines and analytics platforms to miss these changes and resulting in data inconsistencies. The new approach ensures all cascaded operations are captured in the binary log and visible to the SQL engine, improving change tracking accuracy and replication reliability. Benchmarking demonstrates that SQL-layer-based foreign key enforcement performs nearly identically to the previous InnoDB approach, addressing performance concerns.

Read at InfoQ