In this tutorial, we will learn what is ON DELETE CASCADE, how to use it, and some more concepts related to it.
Did you ever fall into a situation where you wanted to delete all the matching records in the child table when one of the records in the parent table gets deleted? If yes, then you might have come across the MySQL cascade term.
Along with simplified theory, we will also go through some straightforward examples to understand the ON DELETE CASCADE on a better level.
What is ON DELETE CASCADE?
In MySQL, when you want to delete records in the child table linked to records in the parent table by a foreign key, you can use a nested query with the DELETE clause. However, MySQL provides you with a more efficient way to perform this using ON DELETE CASCADE referential action for the foreign key. Using ON DELETE CASCADE, all the related records in the child table get automatically deleted when the parent table data is deleted.
The ON DELETE CASCADE helps you avoid writing nested queries for deleting the child table data respective to parent table data. Let’s take a simple example. Suppose you have two tables table. The first table consists of a students’ basic data such as id, name, and gender.
The second table consists of the students’ hobbies of the student table. Now, if you have to delete a record of a particular student from the student’s table, all hobbies from the hobby table must be deleted. To achieve this, you can use MySQL ON DELETE CASCADE feature.
Example of MySQL ON DELETE CASCADE
Let’s take an example to demonstrate how ON DELETE CASCADE works.
We will start by creating two tables that will be linked using a foreign key. Then we will insert data in those tables and write ON DELETE CASCADE action. Here we go!
First, let’s create a student table that will consist of attributes such as auto-incremented id, student name, and gender.
CREATE TABLE students(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(50),
sgender VARCHAR(1)
);Code language: SQL (Structured Query Language) (sql)Now, insert some data in it.
INSERT INTO STUDENTS(sname,sgender)VALUES
("John", "m"), ("Lilly", "f"),("Kumi", "f"),
("Yuji" ,"m"),("Jenos","m"),("Mikasa","f");Code language: SQL (Structured Query Language) (sql)Display the table data to check if inserted correctly.
SELECT * FROM students;Code language: SQL (Structured Query Language) (sql)
Set Up Another Table With MySQL ON DELETE CASCADE
Now create a hobbies table to store the hobbies of the students.
CREATE TABLE hobbies(
hid INT PRIMARY KEY AUTO_INCREMENT,
sid INT,
hname VARCHAR(50) NOT NULL,
FOREIGN KEY(sid) REFERENCES students(sid)
ON DELETE CASCADE
);Code language: SQL (Structured Query Language) (sql)Here, we have defined “sid” as a foreign key that references the “sid” in the students table. Right after the foreign-key statement, we have written the ON DELETE CASCADE clause. This will specify, whenever the data in the parent table (students) is deleted, all the related data in the child table (hobbies) should automatically be deleted.
Now, insert some values and display the data to check whether or not all the data is inserted correctly.
INSERT INTO hobbies(sid,hname) VALUES
(1,"swimming"),(1,"reading"),(2,"treking"),
(2,"coding"),(2,"reading"),(1,"boxing"),
(4,"chess"),(5,"travelling"),(4,"drawing"),
(3,"cooking"),(3,"baking");Code language: SQL (Structured Query Language) (sql)SELECT * FROM hobbies;Code language: SQL (Structured Query Language) (sql)
Now, let’s delete a student data from the students table of sid=5 and check if the records in the hobbies table with student id 5 are deleted.
DELETE FROM students where sid=5;Code language: SQL (Structured Query Language) (sql)
The query result shows OK, 1 row affected. Let’s display the data of the hobbies table and find a record of sid=5.
SELECT * FROM hobbies;Code language: SQL (Structured Query Language) (sql)
As you can see, the hobbies table doesn’t contain a record of sid 5 anymore. This means the ON DELETE CASCADE clause worked successfully.
Now, what if you want to find which tables are affected by ON DELETE CASCADE action?
Find affected tables by ON DELETE CASCADE clause
If you are working on someone else’s code or similar circumstances and want to check which tables are affected by the ON DELETE CASCADE clause, you can find that with simple statements. This information can be found by running the following query against the information schema database’s referential constraints table.
USE information_schema;
SELECT table_name FROM referential_constraints
WHERE constraint_schema = 'cascades'
AND referenced_table_name = 'students'
AND delete_rule="cascade";Code language: SQL (Structured Query Language) (sql)Here, ‘cascades’ is a database name and ‘students‘ is a table name. This query will give you the result of tables related to students tables with the ON DELETE CASCADE.
As you can see, the hobbies table is associated with a students table with an ON DELETE CASCADE.
Conclusion
We have learned about the MySQL ON DELETE CASCADE clause or a referential action. It is widely used to automate the deleting task. You are not restricted to writing only one ON DELETE CASCADE clause. You can write multiple DELETE CASCADE actions for the same table or multiple tables. You can try different approaches and use them in real-time applications. See you in the next tutorial!
References
MySQL official documentation of referential actions.