When user wants to delete data from child tables, whenever the data is deleted from parent table, on referential action for a foreign key, MYSQL ON DELETE CASCADE is used. To use the DELETE CASCADE, the parent and child should share a 1:n relationship, where a parent table can have 'n' child tables. While creating child tables the REFERENCES with FOREIGN KEY id and ON DELETE CASCADE is added as given below:

CREATE TABLE parent(

   id INT AUTO_INCREMENT, user VARCHAR(20), PRIMARY KEY(id) 

   parent_name varchar(255) NOT NULL) ENGINE=InnoDB;

 

CREATE TABLE child(

   id INT AUTO_INCREMENT, id_child INT, name VARCHAR(20), PRIMARY KEY(id), 

   FOREIGN KEY(parent_name) REFERENCES parent(parent_name) 

   ON DELETE CASCADE) ENGINE=InnoDB;

 

DELETE FROM parent WHERE parent_name='myself';

This syntax deletes all the references of 'myself' from 'parent' table and 'child' table.

The example below creates two tables 'college' which is parent and 'course' which is child. The college has course id, name and fees in its database. The course has the course id, subjects. When a course id is deleted from college, its associated elements are deleted from course table too.

CREATE TABLE college (

   course_no int(11) NOT NULL AUTO_INCREMENT, 

   course_name varchar(255) NOT NULL, fees varchar(355) NOT NULL, 

   PRIMARY KEY (course_no) ) ENGINE=InnoDB;

 

CREATE TABLE course (

   id int(11) NOT NULL AUTO_INCREMENT, 

   course_no int(11) NOT NULL, subject_1 varchar(255) NOT NULL, 

   subject_2 varchar(255) NOT NULL, 

   PRIMARY KEY (id), KEY course_no (course_no), 

   CONSTRAINT course_ibfk_1 FOREIGN KEY (course_no) 

   REFERENCES college (course_no) ON DELETE CASCADE);

 

INSERT INTO college(course_name,fees) VALUES('Sciene','2000'), 

   ('Environment','5000');

 

INSERT INTO course(course_no,subject_1,subject_2) 

   VALUES (1,'Astronomy','Physics'),

   (2,'Environmental studies','Human Pshychology');

 

DELETE FROM college WHERE course_no = 2;