ON DELETE CASCADE in MySql

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)  
);

You can also try this code with Online MySQL Compiler

Run Code

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;

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  
);

You can also try this code with Online MySQL Compiler

Run Code

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');

You can also try this code with Online MySQL Compiler

Run Code

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

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:

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'

You can also try this code with Online MySQL Compiler

Run Code

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  
);

You can also try this code with Online MySQL Compiler

Run Code

Now, the initial table looks like this.

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.

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

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 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 explored the ON DELETE CASCADE feature in MySQL, understanding its purpose and importance in maintaining data integrity. We also discussed its implementation, syntax, and provided examples to demonstrate how it works in real-world database scenarios, ensuring that related records are properly deleted when a parent record is removed.

You can also refer to the below articles:

• Mysql-introduction
• Mysql-features
• Mysql-installation
• Mysql-connection
• MySQL Case Statement
• Update Query in MySQL