MySQL ON DELETE CASCADE

If your Interested to learn about the MySQL Common Table Expression(CTE)

ON DELETE CASCADE clause in MySQL is used to automatically remove the matching records from the child table when we delete the rows from the parent table. It is a kind of referential action related to the foreign key. Suppose we have created two tables with a FOREIGN KEY in a foreign key relationship, making both tables a parent and child. Next, we define an ON DELETE CASCADE clause for one FOREIGN KEY that must be set for the other to succeed in the cascading operations. If the ON DELETE CASCADE is defined for one FOREIGN KEY clause only, then cascading operations will throw an error.

What does on delete cascade?

Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.

Syntax

We use the following syntax for the MySQL ON DELETE CASCADEfor foreign key using CREATE TABLE:

CREATE TABLE ChildTable_Name(Col1 Data_Type [NOT NULL | PRIMARY KEY | AUTO_INCREMENT],.., ColNDate_Type [NOT NULL | NULL] FOREIGN KEY (Col1_Child, …, ColN_Child)
REFERENCES ParentTable_Name (Col1_Parent, …., ColN_Parent)
ON DELETE CASCADE);

Here, the terms used are explained below:

• A foreign key for a MySQL ON DELETE CASCADE can be generated with the help of either using MySQL CREATE TABLE or MySQL ALTER TABLE statements.
• ChildTable_Name: It denotes the name of the child table related to the parent table.
• Col1,..,ColN: These are the column names that we want to create with the respective data type in the table. It has a default value as NULL when we do not provide any data type but we need to add some data type for the proper execution of query either NULL or NOT NULL.
• Col1_Child,…,ColN_Child: These are the columns of the child table which has reference to Primary key in the parental table.
• ParentTable_Name: This is the name of the parent table to be used in the query statement whose primary keys are linked to being used in the child table.
• Col1_Parent,…..,ColN_Parent: These are the parent table columns which holds Primary keys to which the foreign key will be associated
• ON DELETE CASCADE: This MySQL Keyword is responsible to make changes on the child table when the parent table is affected.
• CASCADE: It denotes that when the parent data is affected then, the child data is also altered accordingly since it is used in aggregation to ON DELETE or ON UPDATE.

How ON DELETE CASCADE works in MySQL?

• In MySQL, the parent table comprises the original key values in a table whereas the child table is defined as the linked table that refers to the main key values in the parental table in a database.
• Supposing we have created two MySQL tables person(ID, Name, Address) and Employee(EmpID, ID, Profile, Contact). In the MySQL database structure model, each person occupies either single or multiple employee profiles. However, every employee profile belongs to just one single person in the table relation. An employee profile will not be existent lacking a person.
• Hence, we can conclude that the relation between the person and employee tables is distinct to various i.e. (1:N)
• Again, assume that when you cancel a row in the person table then, you also desire to remove the rows from the employee table that is associated with the Primary key record in the person table.
• For instance, if you remove the person with id 1, the employee profiles that reference to this id will also be needed to remove so, in this case, we will apply with DELETE statement the MySQL ON DELETE CASCADE for a foreign key.
• An important thing is to be noticed that MySQL ON DELETE CASCADE works with the storage engines that support foreign keys such as InnoDB but it may not work in MyISAM type engine.

MySQL ON DELETE CASCADE Example

Let us understand how we can use the ON DELETE CASCADE clause in the MySQL table. First, we are going to create two tables named Employee and Payment. Both tables are related through a foreign key with on delete cascade operation. Here, an Employee is the parent table, and Payment is the child table. The following scripts create both tables along with their records.

Table: Employee

The following statement creates a table Employee:

CREATE TABLE Employee (  
  emp_id int(10) NOT NULL,  
  name varchar(40) NOT NULL,  
  birthdate date NOT NULL,  
  gender varchar(10) NOT NULL,  
  hire_date date NOT NULL,  
  PRIMARY KEY (emp_id)  
);  

Next, execute the insert query to fill the records.

INSERT INTO Employee (emp_id, name, birthdate, gender, hire_date) VALUES  
(101, 'Bryan', '1988-08-12', 'M', '2015-08-26'),  
(102, 'Joseph', '1978-05-12', 'M', '2014-10-21'),  
(103, 'Mike', '1984-10-13', 'M', '2017-10-28'),  
(104, 'Daren', '1979-04-11', 'M', '2006-11-01'),  
(105, 'Marie', '1990-02-11', 'F', '2018-10-12');  

Execute the SELECT query to verify the data into a table, which can be shown below:

Table: Payment

The below statement creates a table Payment:

CREATE TABLE Payment (  
  payment_id int(10) PRIMARY KEY NOT NULL,  
  emp_id int(10) NOT NULL,  
  amount float NOT NULL,  
  payment_date date NOT NULL,  
  FOREIGN KEY (emp_id) REFERENCES Employee (emp_id) ON DELETE CASCADE  
);  

Next, execute the insert statement to fill the records into a table.

INSERT INTO Payment (payment_id, emp_id, amount, payment_date) VALUES   
(301, 101, 1200, '2015-09-15'),  
(302, 101, 1200, '2015-09-30'),  
(303, 101, 1500, '2015-10-15'),  
(304, 101, 1500, '2015-10-30'),  
(305, 102, 1800, '2015-09-15'),  
(306, 102, 1800, '2015-09-30');  

Execute the SELECT query to verify the data into a table, which can be shown below:

Let us delete data from the parent table Employee. To do this, execute the following statement:

mysql> DELETE FROM Employee WHERE emp_id = 102;  

The above statement will delete the employee records whose emp_id = 102 and referencing data into the child table. We can verify the data using the SELECT statement that will give the following output:

In the above output, we can see that all the rows referencing to emp_id = 102 were automatically deleted from both tables.

How to find the affected table by ON DELETE CASCADE action?

Sometimes, before deleting records from the table, we want to know the affected table by the ON DELETE CASCADE referential action. We can find this information by querying from the referential_constraints in the information_schema database as follows:

USE information_schema;  
  
SELECT table_name FROM referential_constraints  
WHERE constraint_schema = 'database_name'  
        AND referenced_table_name = 'parent_table'  
        AND delete_rule = 'CASCADE'  

The below statement produces the result about the tables associated with the Employee table with the ON DELETE CASCADE rule in the employeedb database:

USE information_schema;  
  
SELECT table_name FROM referential_constraints  
WHERE constraint_schema = 'employeedb'  
        AND referenced_table_name = 'Employee'  
        AND delete_rule = 'CASCADE';  

After executing the above command, we will get the output below:

MySQL ON UPDATE CASCADE

ON UPDATE CASCADE clause in MySQL is used to update the matching records from the child table automatically when we update the rows in the parent table. The following example explains it more clearly. First, we need to use the ALTER TABLE statement to add the ON UPDATE CASCADE clause in the table Payment as below:

ALTER TABLE Payment ADD CONSTRAINT `payment_fk`   
FOREIGN KEY(emp_id) REFERENCES Employee (emp_id) ON UPDATE CASCADE;  

It will give the following output:

In the below script, we will update the id of the employee in the Parent Table, and it will automatically reflect this change in the child table as well:

mysql> UPDATE Employee SET emp_id = 102 WHERE emp_id = 103;  

Verifying the content of the Employee and Payment table, we will see that emp_id column values will be updated successfully.

ON DELETE CASCADE constraint is used in MySQL to delete the rows from the child table automatically, when the rows from the parent table are deleted. For example when a student registers in an online learning platform, then all the details of the student are recorded with their unique number/id. All the courses in these online learning platforms had their own code, title, and name. Students can enroll in any course according to their wishes. 

There is no rule that all students must enroll in all courses, or they have to join the course on the same date. A student can enroll in one or more courses. Suppose you delete a row from the “Student” table, now you will also want to delete all rows in the “Enroll” table that references the row in the “Student” table. For that, we need ON DELETE CASCADE.  Below are the steps that explain how ON DELETE CASCADE referential action works.

Step 1: Create the Student table

CREATE TABLE Student (
    sno INT PRIMARY KEY,
    sname VARCHAR(20),
    age INT

);

Step 2: Insert rows intothe Student table

INSERT INTO Student(sno, sname,age)
 VALUES(1,'Ankit',17),
       (2,'Ramya',18),
       (3,'Ram',16);

Step 3: Executethe SELECT query to check the data in the STUDENT table.

SELECT *
FROM Student;

Output:

Step 4: Create the Course table

CREATE TABLE Course (
    cno INT PRIMARY KEY,
    cname VARCHAR(20)
);

Step 5: Insert rows intothe Course table

INSERT INTO Course(cno, cname)
 VALUES(101,'c'),
       (102,'c++'),
       (103,'DBMS');

Step 6: Executethe SELECT query to check the data in the Course table.

SELECT *
FROM Course;

Output:

Step 7: Create the Enroll table

CREATE TABLE Enroll (
    sno INT,
    cno INT,
    jdate date,
    PRIMARY KEY(sno,cno),
    FOREIGN KEY(sno) 
        REFERENCES Student(sno)
        ON DELETE CASCADE
    FOREIGN KEY(cno) 
        REFERENCES Course(cno)
        ON DELETE CASCADE
);

Step 8: Insert rows intothe Enroll table

INSERT INTO Enroll(sno,cno,jdate)
 VALUES(1, 101, '5-jun-2021'),
       (1, 102, '5-jun-2021'),
       (2, 103, '6-jun-2021');

Step 9: Executethe SELECT query to check the data in the Enroll table.

SELECT *
FROM Enroll;

Output:

Step 10: Here the parent tables are Student and Course whereas the child table is Enroll. If a student drops from the course or a course is removed from the offering list it must affect the child table also. 

DELETE FROM Student
WHERE sname="Ramya";

Step 11: Executethe SELECT query to check the data.

Select * from Student;

Output: 

Select * from Enroll;

Output:

As you delete the contents of sno=2 in the parent table it automatically deletes the details of sno=2 from the child table also. In the same way, if you remove a course from the Course table it automatically deletes the rows of that course in the child table Enroll. This works out because the foreign key constraint ON DELETE CASCADE is specified.