On Delete Cascade MySQL

There is a constraint called on delete cascade, which is used in MySQL to automatically delete the rows from the child table when the rows from the parent's table are deleted. Let us have a look n how this can happen.

For example, we will create tables and use this method to delete the child's row from the parents.

Step 1: Creating Employee Table.

CREATE TABLE employee (
    eno INT PRIMARY KEY,
    ename VARCHAR(20),
    age INT

); 

Let us insert some rows into the employee table.

INSERT INTO employee(eno, ename,age) 
 VALUES(1,'Akash',22),
       (2,'Tharun',25),
       (3,'Deva',40);

So our employee table is now ready, which looks something like this.

Step 2: let us create a salary details table.

 CREATE TABLE saldetails (
    no INT PRIMARY KEY,
    sname VARCHAR(20)
);

Let us insert some rows into the saldetails table.

INSERT INTO saldetails(sno, sname) 
 VALUES(101,'20000'),
       (102,'1000'),
       (103,'30000');

Here's the saldetails table.

Step 3: let us create a joining date table.

CREATE TABLE Enroll (
    eno INT,
    sno INT,
    jdate date,
    PRIMARY KEY(eno,sno),
    FOREIGN KEY(eno) 
        REFERENCES employee(eno)
        ON DELETE CASCADE,
    FOREIGN KEY(sno) 
        REFERENCES saldetails(sno)
        ON DELETE CASCADE
);

Here we have created a table enrolled with references to the previous two tables as foreign keys so that this is referenced and deleted when the parent is deleted.

Let us insert some rows into the Enroll table.

INSERT INTO Enroll(eno,sno,jdate) 
 VALUES(1, 101, '1967-11-14'),
       (2, 102, '1967-11-16'),
       (3, 103, '1967-11-19');

Here's the Enroll table.

Step 4: The parent tables employee, saldetails, and child table Enroll.

If an employee is terminated now, it must also affect the child table.

DELETE FROM employee
WHERE ename="Tharun";

We can see that this constraint works in clearing all records of the data of a child table when specific keys are referenced with on delete cascade.