MySQL ON DELETE CASCADE Constraint (original) (raw)

Last Updated : 08 Feb, 2021

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.