Foreign Key Actions and Cascade Operations in MySQL

When you design a relational database, creating meaningful relationships between tables is essential. These relationships are established using foreign keys, which link a column in one table to the primary key of another. But what happens when the data in the parent table changes?

This is where foreign key actions come into play. MySQL gives you the power to define how changes—like updates or deletions—affect related data in child tables. These actions ensure your database remains consistent, even when the underlying data evolves.

In this article, we’ll break down the key foreign key actions in MySQL and explore how you can use them effectively to manage relationships in your database.

Why Foreign Key Actions Matter

Imagine you’re managing a database for an e-commerce platform. You have a customers table and an orders table. Each order is linked to a customer through a foreign key relationship. Now, what happens when a customer is deleted? Should their orders be deleted too? Or maybe the customer should just be marked as inactive, leaving their order history intact?

These kinds of decisions are critical for maintaining data integrity, and MySQL’s foreign key actions let you define exactly what happens.

The Key Actions for Foreign Keys

MySQL supports several actions you can apply to foreign keys, each dictating how changes in the parent table cascade to the child table. Let’s look at each action in detail.

1. CASCADE: Automatically Propagate Changes

When you set a foreign key action to CASCADE, any change in the parent table will automatically propagate to the child table.

On DELETE CASCADE: Deletes all related rows in the child table when a row in the parent table is deleted.
On UPDATE CASCADE: Updates all related rows in the child table when a row in the parent table is updated.

For example, if you delete a customer, their orders should also be removed to avoid orphaned records.

USE `mydatabase`;

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

INSERT INTO customers(`id`, `name`) VALUES(1, 'John Doe');
INSERT INTO orders(`id`, `customer_id`) VALUES(1, 1);
INSERT INTO orders(`id`, `customer_id`) VALUES(2, 1);
INSERT INTO orders(`id`, `customer_id`) VALUES(3, 1);

DELETE FROM customers WHERE id = 1;

SELECT * FROM orders;

When you run the example above, you will see that orders table is empty.

Deleting a customer from the customers table will automatically delete all their associated orders from the orders table.

2. SET NULL: Allow the Child Table to Detach

With SET NULL, if a parent row is deleted or updated, the foreign key column in the child table is set to NULL.

On DELETE SET NULL: Sets the foreign key column in the child table to NULL when a parent row is deleted.
On UPDATE SET NULL: Sets the foreign key column in the child table to NULL when a parent row is updated.

For example, if a product in an inventory system is discontinued, you might want to keep the associated sales records but remove the reference to the product.

USE `mydatabase`;

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE sales (
    id INT PRIMARY KEY,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

INSERT INTO products(`id`, `name`) VALUES(1, 'Example product');
INSERT INTO sales(`id`, `product_id`) VALUES(1, 1);
INSERT INTO sales(`id`, `product_id`) VALUES(2, 1);

DELETE FROM products WHERE id = 1;

SELECT * FROM sales;

Here, deleting a product will not delete the corresponding sales records. Instead, the product_id in the sales table will be set to NULL.

3. RESTRICT: Block Changes

The RESTRICT action prevents any operation (delete or update) that would violate the foreign key relationship.

On DELETE RESTRICT: Stops you from deleting a parent row if there are dependent rows in the child table.
On UPDATE RESTRICT: Prevents updates to the parent row if they would invalidate the foreign key relationship.

If deleting a record might break critical business rules, you can use RESTRICT to enforce those rules.

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);

In this scenario, you cannot delete a category if there are still products associated with it.

4. NO ACTION: Similar to RESTRICT

NO ACTION is functionally similar to RESTRICT, but there’s a subtle difference in execution timing. While RESTRICT checks immediately, NO ACTION delays the constraint check until the end of the SQL statement.

It’s rare to use NO ACTION explicitly, but it can be useful in certain transactional scenarios where constraint enforcement order matters.

CREATE TABLE authors (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE NO ACTION
);

Wrapping Up

Foreign key actions are one of MySQL’s most powerful features for managing relationships and ensuring data integrity. By defining how changes in parent tables affect related rows, you can create a database that’s not only robust but also easy to maintain.

Whether you’re cascading deletions, setting values to NULL, or enforcing strict rules with RESTRICT, foreign key actions give you the flexibility to design a database that fits your needs. So the next time you’re working on a database schema, take a moment to think about how your data should behave when things change. A well-thought-out foreign key strategy can save you a lot of headaches down the road.

AUTHOR

Cemil Tokatli

PUBLISHED 2 April 2025

TOPICS

mysql
sql