Foreign key ON UPDATE behavior in SQL

Introduction

Foreign key ON UPDATE behavior controls what happens to related data when the original data changes. It helps keep data connected and consistent.

When you want to change a primary key value and keep related records updated automatically.

When you want to prevent changes to a primary key if related records exist.

When you want to set related records to null or default if the primary key changes.

When you want to maintain data integrity between tables during updates.

Syntax

SQL

FOREIGN KEY (child_column) REFERENCES parent_table(parent_column) ON UPDATE {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}

CASCADE updates child rows automatically when parent changes.

RESTRICT or NO ACTION prevent update if child rows exist.

Examples

If a department's id changes, all related employee records update their department_id automatically.

SQL

FOREIGN KEY (department_id) REFERENCES departments(id) ON UPDATE CASCADE

If a category id changes, the related product's category_id is set to NULL.

SQL

FOREIGN KEY (category_id) REFERENCES categories(id) ON UPDATE SET NULL

Prevents changing a user id if orders exist for that user.

SQL

FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE RESTRICT

Sample Program

This example shows how updating the department id in the departments table automatically updates the department_id in employees because of ON UPDATE CASCADE.

SQL

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id) ON UPDATE CASCADE
);

INSERT INTO departments VALUES (1, 'Sales');
INSERT INTO employees VALUES (101, 'Alice', 1);

-- Update department id from 1 to 10
UPDATE departments SET id = 10 WHERE id = 1;

-- Check employees table
SELECT * FROM employees;

Important Notes

ON UPDATE behavior only works if the database supports it and the foreign key is defined with it.

CASCADE is useful but can cause unexpected changes if not used carefully.

RESTRICT and NO ACTION are similar; they stop updates if related rows exist.

Summary

ON UPDATE controls what happens to child rows when parent keys change.

Common options: CASCADE, SET NULL, RESTRICT, NO ACTION.

Use ON UPDATE to keep data consistent automatically.