Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Mar 27, 2024
Difficulty: Medium

ON DELETE CASCADE in MySql

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

In this article, we will learn about On Delete Cascade in MySql and its explanation. We will also see how to use On delete cascade and its syntax, implementation, and some examples of it.

ON DELETE CASCADE in MySql

What is the ON DELETE CASCADE Option?

In database management, specifically in relational databases like MySQL, PostgreSQL, or SQLite, the ON DELETE CASCADE option is a referential action that can be applied to foreign key constraints. When a foreign key constraint is created with ON DELETE CASCADE, it means that if a record in the parent table (referenced table) is deleted, then all corresponding records in the child table (referencing table) will also be automatically deleted.

In simpler terms, when you delete a row from a parent table, the CASCADE option ensures that all related rows in the child table are also deleted, maintaining referential integrity within the database.

For example, consider two tables: orders and order_items. If order_items has a foreign key referencing the orders table, with ON DELETE CASCADE option applied, deleting a record from the orders table will automatically delete all corresponding records in the order_items table.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

MySQL ON DELETE CASCADE

In MySQL, the ON DELETE CASCADE option is used when defining foreign key constraints to specify the action to take when a referenced row in the parent table is deleted.

Note:  If the ON DELETE CASCADE is defined for one of the FOREIGN KEY clauses only, then the cascading operations will result in an error.

Example of ON DELETE CASCADE in MySQL

Now, we will understand how you can use ON DELETE CASCADE in the MySQL table. First, we will create two tables named Student and Fees. Both the tables are related through a foreign key with the “on delete cascade” operation. Here, the Student is the parent table, and the Fees are the child table.

Table: Student

The below query will create the Student table:

CREATE TABLE Student (  
  stu_id int(10) NOT NULL,  
  name varchar(40) NOT NULL,  
  birthdate date NOT NULL,  
  gender varchar(10) NOT NULL,  
  admission_date date NOT NULL,  
  PRIMARY KEY (stu_id)  
);


Now we will run an insert query to fill the data in the table.

INSERT INTO Student (stu_id, name, birthdate, gender, admission_date) VALUES
(101, 'Ram', '2001-08-12', 'M', '2015-08-26'),  
(102, 'Tisha', '2002-05-12', 'F', '2014-10-21'),  
(103, 'Raghav', '1999-10-13', 'M', '2017-10-28'),  
(104, 'Mohan', '2000-04-11', 'M', '2006-11-01'),  
(105, 'Garry', '2001-02-11', 'M', '2018-10-12');


Below is the execution of the table using select* from Student;

output

Table: Fees

The below statement creates a table “fees”:

CREATE TABLE fees (  
  fees_id int(10) PRIMARY KEY NOT NULL,  
  stu_id int(10) NOT NULL,  
  amount float NOT NULL,  
  fees_date date NOT NULL,  
  FOREIGN KEY (stu_id) REFERENCES Student (stu_id) ON DELETE CASCADE  
);


Now we will run an insert query to fill the data in the table.

INSERT INTO fees (fees_id, stu_id, amount, fees_date) VALUES   
(501, 101, 1400, '2015-09-15'),  
(502, 101, 1230, '2015-09-30'),  
(503, 101, 1540, '2015-10-15'),  
(504, 101, 3500, '2015-10-30'),  
(505, 102, 1400, '2015-09-15'),  
(506, 102, 1200, '2015-09-30');

 

Below is the execution of the table using select* from fees;

output

Now we will delete data from the parent table Student. For this, we will execute the following statement:

mysql> DELETE FROM Student WHERE stu_id = 102;

Now, we can verify this using the SELECT command that will provide the following output:

output

Above, we can easily see that the rows referencing stu_id = 102 were deleted automatically from both tables.

Determining the Affected Table with ON DELETE CASCADE Action

You can use the following MySQL command to find the affected table:-

USE information_schema;

SELECT table_name FROM referential_constraints
WHERE referenced_table_name = 'Student'
       AND delete_rule = 'CASCADE'

 

Output:

command output

‘information_schema’ is a database within a MySQL instance, which stores metadata about all the databases that the server maintains. It has many read-only table views. 

In the query above, table names of all the tables that reference the ‘Student’ table and have on delete cascade rule are selected. ‘fees' table name is displayed as it references the ‘Student’ table and its delete rule is set to cascade.

MySQL ON UPDATE CASCADE

In MySQL, ON UPDATE CASCADE is a referential integrity constraint that ensures that when the value of a referenced column in the parent table is updated, the corresponding values in the child table are automatically updated to match the new values. It helps maintain consistency between related tables in a database by propagating changes from the parent table to the child table.

Let's understand it better by implementing it in the above example.

First, instead of the ON DELETE CASCADE constraint while creating the table, add the ON UPDATE CASCADE constraint. This can be done by using the below statement to create a table and keeping the rest of the statements the same.

CREATE TABLE fees (  
 fees_id int(10) PRIMARY KEY NOT NULL,  
 stu_id int(10) NOT NULL,  
 amount float NOT NULL,  
 fees_date date NOT NULL,  
 FOREIGN KEY (stu_id) REFERENCES Student (stu_id) ON DELETE CASCADE ON UPDATE CASCADE  
);

 

Now, the initial table looks like this.

table before updating

Run the following statement to update some data in the first table.

UPDATE Student SET stu_id = 201 WHERE stu_id = 101;

 

Now, we can see the data is also updated in the second table.

after updating

Purpose of ON DELETE CASCADE

The ON DELETE CASCADE in SQL is used for automatically deleting matching records from the child table when they are removed from the parent table. In the above examples, when a student is deleted from the database, the corresponding tuple is removed from the Fees table. This ensures that the programmer doesn't have to manually write logic for tbe removal of all related records from a database when parent record is deleted.

Does ON DELETE CASCADE Always Help? Corner Cases

While ON DELETE CASCADE can be a powerful tool for maintaining referential integrity in a database, there are some corner cases to consider:

  • Unintended Deletion: In complex database schemas, cascading deletions can inadvertently delete large portions of data if not carefully managed. This can lead to data loss and unexpected behavior.
  • Circular Dependencies: If there are circular dependencies between tables, where Table A references Table B and Table B references Table A, using ON DELETE CASCADE can cause a cascade of deletions that might not be desirable.
  • Performance Impact: Cascading deletions can have a performance impact, especially on large tables, due to the additional processing required to delete related records.
  • Data Recovery: Once records are deleted due to ON DELETE CASCADE, they cannot be easily recovered. Careful consideration should be given to implementing cascading deletions, especially in critical database systems.

Tips for Using ON DELETE CASCADE Safely and Effectively

Here are some tips for using ON DELETE CASCADE in your database schema:

  • Understand Your Data Model: Fully understand the relationships between tables in your database schema before implementing ON DELETE CASCADE.
  • Test Thoroughly: Test cascading deletions in a development environment to ensure they behave as expected before deploying changes to production.
  • Use Constraints Wisely: Apply foreign key constraints selectively, considering the impact of cascading deletions on your data model.
  • Implement Data Backup Strategies: Regularly backup your database to mitigate the risk of data loss from unintended cascading deletions.
  • Avoid Circular Dependencies: Design your database schema to avoid circular dependencies between tables to prevent unexpected cascading deletions.
  • Consider Alternative Actions: Evaluate alternatives to ON DELETE CASCADE, such as setting foreign key columns to NULL or using triggers, depending on your specific use case.

Frequently Asked Questions

Is it good to use on delete cascade?

Using ON DELETE CASCADE in a database is beneficial when you want related records to be automatically deleted when a parent record is removed, simplifying data integrity management.

What is the difference between on delete cascade and set null?

ON DELETE CASCADE automatically deletes related records when a parent record is deleted, ensuring data integrity. SET NULL sets foreign keys in related records to NULL, breaking the relationship but preserving the records.

Which direction does the delete cascade go?

DELETE CASCADE works in a downward direction. When you delete a parent record, it automatically deletes associated child records, ensuring that child records are removed when the parent is deleted.

What is on delete cascade code?

The ON DELETE CASCADE keyword is used when a child table is defined in a database. This allows the DBMS to automatically delete records from the child table when referenced records are deleted from the parent table.

What is the difference between on delete cascade and on delete?

ON DELETE CASCADE automatically marks dependent rows for deletion when a reference row is deleted. ON DELETE is used along with other keywords such as RESTRICT, SET NULL, etc., for specifying other behaviours when referenced rows are deleted. 

What does cascade mean in SQL?

In SQL, CASCADE helps delete or update a record in both child and parent tables together. It's like a tool when writing ON DELETE or ON UPDATE queries.

Conclusion

In this article, we have learned about the On delete cascade in Mysql. We have also seen the implementation and syntax of on delete cascade with examples.

You can also refer to the below articles:


You can learn the basics of Java and data structures and algorithms in Java on Coding Ninjas. Refer to our guided path on code studio to learn more about DSA Competitive Programming, Javascript System Design, etc. Enroll in our courses and refer to the mock test and problems available. Also, look at the interview experiences for placement preparations.

Happy Learning, Ninjas.

Topics covered
1.
Introduction
2.
What is the ON DELETE CASCADE Option?
3.
MySQL ON DELETE CASCADE
4.
Example of ON DELETE CASCADE in MySQL
5.
Determining the Affected Table with ON DELETE CASCADE Action
6.
MySQL ON UPDATE CASCADE
7.
Purpose of ON DELETE CASCADE
8.
Does ON DELETE CASCADE Always Help? Corner Cases
9.
Tips for Using ON DELETE CASCADE Safely and Effectively
10.
Frequently Asked Questions
10.1.
Is it good to use on delete cascade?
10.2.
What is the difference between on delete cascade and set null?
10.3.
Which direction does the delete cascade go?
10.4.
What is on delete cascade code?
10.5.
What is the difference between on delete cascade and on delete?
10.6.
What does cascade mean in SQL?
11.
Conclusion