InnoDB enforces foreign key constraints using internal metadata structures, indexes, and validation checks that run during INSERT, UPDATE, and DELETE operations. These mechanisms ensure referential integrity between parent and child tables.

1. Internal Storage of Foreign Keys

1
Foreign key definitions are stored in InnoDB internal system tables.
2
InnoDB maintains mappings of child → parent columns.
3
Every foreign key requires an index on the child column; InnoDB auto-creates it if missing.

2. How InnoDB Validates INSERT / UPDATE on Child Table

1
On insert/update, InnoDB checks if the referenced parent record exists.
2
It uses the index on the parent table for fast lookups.
3
If no matching parent exists, the statement fails with a foreign key error.

3. How InnoDB Validates DELETE / UPDATE on Parent Table

1
On delete/update of a parent row, InnoDB checks for dependent child rows.
2
Rules determine the behavior:
3
• CASCADE → delete/update child rows automatically
4
• SET NULL → set FK column to NULL
5
• RESTRICT / NO ACTION → block operation if children exist

4. Locking Behavior During Constraint Enforcement

1
InnoDB uses row-level locking to maintain consistency.
2
Shared locks (S-locks) are placed on parent rows during validation.
3
Cascading operations acquire locks on child rows to prevent race conditions.

5. InnoDB Internal Metadata Tables

1
INNODB_SYS_FOREIGN → foreign key definitions
2
INNODB_SYS_FOREIGN_COLS → column mappings
3
INNODB_SYS_INDEXES → indexes supporting FK checks
4
INNODB_SYS_TABLES → table metadata

6. Summary

1
Foreign keys rely heavily on indexing.
2
InnoDB validates constraints during all DML operations.
3
Locking prevents inconsistent states.
4
CASCADE and SET NULL trigger internal updates/deletes.
5
All metadata and enforcement are handled inside InnoDB engine.