MySQL ON DELETE and ON UPDATE Cascade Example

In this article, we will learn about MySQL cascade. We use cascading operation when two tables are dependent on each other.

We apply this MySQL cascade on foreign keys. If any data modify from the parent table then this referenced table data will also modify.

The two most important cascade type are:

1. ON DELETE cascade
2. ON UPDATE cascade

ON DELETE cascade

We use ON DELETE cascade when we want that all referenced entities will automatically delete if we delete any parent entity.

Take below Example:

Suppose I have Two Tables

1. person Table
2. orders Table

person table structure is like below:

create table person(personId bigint(12) primary key,
name varchar(20),
age int(3));

orders table structure is like below :

create table orders(orderId bigint(12) primary key,
orderName varchar(20),
personId bigint(20),
foreign key(personId) references person(personId)
ON DELETE CASCADE );

If you see in orders table we use ON DELETE CASCADE. It means if we delete row from person table than row from orders table automatically delete which have same personId.

Take below Example to understand this

1. Insert some data in person table.
2. Insert some data in orders table.

insert into person values(1,"vasu",26);
insert into person values(2,"Ayush",26);
insert into person values(3,"sushmits",25);

insert into orders values(1,"Shoes", 1);
insert into orders values(2,"Jeans", 1);
insert into orders values(3,"Tshirt", 3);
insert into orders values(4,"Jacket",2 );

Now Our person and ordes table will look like this.

Now try to delete some data from person table.

delete from person where personId=1;

After execute above query check both the tables data and see the difference.

ON UPDATE cascade

This is the same as ON DELETE cascade. The difference is that instead of delete referenced table data it will update the data.

So we follow the same steps as ON DELETE cascade.

1. Create person table.
2. Create orders table with ON UPDATE cascade.
3. Insert data in both the tables.
4. Now update data from person table.
5. Now select data from both the tables and see the differnece.

Follow the above steps:

person Table

create table person(personId bigint(12) primary key,
name varchar(20),
age int(3));

2- orders Table

create table orders(orderId bigint(12) primary key, orderName varchar(20),
personId bigint(20),
foreign key(personId) references person(personId)
ON UPDATE CASCADE );

3- Insert Data in both tables.

insert into person values(1,"vasu",26);
insert into person values(2,"Ayush",26);
insert into person values(3,"sushmits",25);
 
insert into orders values(1,"Shoes", 1);
insert into orders values(2,"Jeans", 1);
insert into orders values(3,"Tshirt", 3);
insert into orders values(4,"Jacket",2 );

4- Update persionId from person table.

update person set personId = 4 where personId = 1;

5- Now see the difference by making select query on both the tables.

There are 5 options for ON DELETE cascade and they are as below.

1. ON DELETE CASCADE : This is the default cascade as we used in our example.
2. ON DELETE SET NULL : This cascade is used to set referenced entities value null if we delete the parent table entity.
3. ON DELETE SET DEFAULT : This cascade is used to set referenced entities value default if we delete the parent table entity.
4. ON DELETE RESTRICT: we can not delete the row from the parent table if it has any referenced row with the same id.
5. ON DELETE NO ACTION: In this cascade, there is no referential delete action performed.

To use the above cascade in orders table the structure is like below:

ON DELETE CASCADE

create table orders(orderId bigint(12) primary key,
orderName varchar(20),
personId bigint(20),
foreign key(personId) references person(personId)
ON DELETE CASCADE );

ON DELETE SET NULL

create table orders(orderId bigint(12) primary key,
orderName varchar(20),
personId bigint(20),
foreign key(personId) references person(personId)
ON DELETE SET NULL );

ON DELETE SET DEFAULT

create table orders(orderId bigint(12) primary key,
orderName varchar(20),
personId bigint(20),
foreign key(personId) references person(personId)
ON DELETE SET DEFAULT );

ON DELETE RESTRICT

create table orders(orderId bigint(12) primary key,
orderName varchar(20),
personId bigint(20),
foreign key(personId) references person(personId)
ON DELETE RESTRICT );

ON DELETE NO ACTION

create table orders(orderId bigint(12) primary key,
orderName varchar(20),
personId bigint(20),
foreign key(personId) references person(personId)
ON DELETE RESTRICT );

The same Cascade is also applicable for ON UPDATE cascade just replace DELETE to UPDATE and remain syntax will be same.

You may also like:

MySQL CASE function Example

MySQL Dates and Times

Database backup in MySql

Reference

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html