MySQL Cascade Update Guide

1 year ago
Liam
2 minutes

MySQL can achieve cascading updates by using foreign key constraints and ON UPDATE CASCADE.

Firstly, when creating a table, it is necessary to set up foreign key constraints and set ON UPDATE CASCADE. For example, if there are two tables, orders and customers, the customer_id column in the orders table is a foreign key that links to the id column in the customers table.

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

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_number VARCHAR(50),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE
);

In the above example, the foreign key constraint specifies that when the id value in the customers table is updated, the customer_id value in the orders table is updated simultaneously.

For example, if you want to update the record with the id of 1 in the customers table and also update the records with the customer_id of 1 in the orders table, you can execute the following SQL statement:

UPDATE customers SET id = 2 WHERE id = 1;

In this way, the value of customer_id in the record in the orders table with customer_id of 1 will also be automatically updated to 2.

Caution: Using cascading updates may lead to unexpected results and performance issues, so use with caution.

bannerAds