How does MySQL handle updates to parent keys that are referenced by foreign keys?

Question

Accepted Answer

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. **ON UPDATE CASCADE** → Automatically updates the child table’s foreign key values. **ON UPDATE SET NULL** → Sets the child foreign key column to NULL (only allowed if the column is nullable). **ON UPDATE RESTRICT** → Prevents the update if child rows exist. **ON UPDATE NO ACTION** → Same as RESTRICT in MySQL (checks after the statement). MySQL checks all referencing child rows before modifying the parent key. If CASCADE is used, MySQL updates child rows in the same transaction. If RESTRICT/NO ACTION is used, the update fails if any child rows depend on the parent. All updates involve index lookups, since foreign keys require indexed columns. CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    dept_id INT,
    FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
        ON UPDATE CASCADE
);

-- Updating parent key:
UPDATE departments SET dept_id = 200 WHERE dept_id = 100;

-- MySQL automatically updates employees.dept_id from 100 → 200. Parent key updates are rare because primary keys are usually stable identifiers. CASCADE can cause large update operations if many child rows depend on the parent. Using BIG, complex primary keys increases the cost of cascading updates. 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.

How does MySQL handle updates to parent keys that are referenced by foreign keys?

How MySQL Handles Updates to Parent Keys Referenced by Foreign Keys

Scenario Questions

Follow-up Questions