When a parent table’s key (usually a PRIMARY KEY or UNIQUE key) is updated, MySQL enforces foreign key rules to maintain referential integrity. The behavior depends on the ON UPDATE action defined in the foreign key constraint.

1. Allowed ON UPDATE Actions

1
ON UPDATE CASCADE → Automatically updates the child table’s foreign key values.
2
ON UPDATE SET NULL → Sets the child foreign key column to NULL (only allowed if the column is nullable).
3
ON UPDATE RESTRICT → Prevents the update if child rows exist.
4
ON UPDATE NO ACTION → Same as RESTRICT in MySQL (checks after the statement).

2. How MySQL Enforces These Rules Internally

1
MySQL checks all referencing child rows before modifying the parent key.
2
If CASCADE is used, MySQL updates child rows in the same transaction.
3
If RESTRICT/NO ACTION is used, the update fails if any child rows depend on the parent.
4
All updates involve index lookups, since foreign keys require indexed columns.

3. Example: ON UPDATE CASCADE

4. Important Notes

1
Parent key updates are rare because primary keys are usually stable identifiers.
2
CASCADE can cause large update operations if many child rows depend on the parent.
3
Using BIG, complex primary keys increases the cost of cascading updates.
4
MySQL requires that referenced keys be indexed to ensure fast update propagation.

In summary, MySQL ensures referential integrity during parent key updates by applying ON UPDATE rules—automatically propagating changes with CASCADE, nullifying child references with SET NULL, or blocking the operation entirely when RESTRICT or NO ACTION is used.