MySQL Cascade Delete

In MySQL language, the constraint refers to the set of rules that get enforced on the table to avoid some anomalies.

These anomalies lead to faulty records in the table. The constraints can be of various types based on the behavior of fault.

These are referential integrity, entity constraint, and semantic constraint. These have meaning and usage based on the type of fault and how inconsistent behavior can get avoided.

Let us understand in brief the type of constraints that are present.

1. Entity Constraint is a constraint that applies over entities and attributes present in the table.

   It maintains the consistency within the values presented within a column of a table. It is often known as first-level validation, where the integrity of the column is maintained.

   Example:

   create table student (stu_id int primary key, stu_name varchar2(50), stu_dept varchar2(15));
   Insert into student values(111, 'ABC', 'Chemical');
   

   The primary key is the entity integrity constraint that internally means that column stu_id is a unique and non-null value. No duplicity is allowed in the stu_id column, and a not null value is not allowed.

2. The Semantic constraint gets enforced over a column to show similar data type values present in the table. It generally gets enforced over a column to make the values consistent and remain of a particular type.

   Example:

   Stu_name varchar2(50);
   

   In the above example, varchar2 is the datatype with the capacity of 50 bytes that hold the student name.

   The column keeps only character type of values, and no double and integer values are allowed. Hence it is a type of semantic constraint.

3. Referential integrity is the constraint that applies to other tables to enforce restrictions over data. The most used constraint is the foreign key constraint.

   If there is a modification in the parent table, the child table should remain consistent with the parent table. If it is a case of dropping the record, then the reference in the child record should not stay to dangle and is a referential integrity constraint.

   Example:

   create table report_card (stu_roll_number int, stu_marks int, stu_course varchar2(30) references student);
   

   In the above example, a report card shows the student’s department or course from the parent table student. If referential integrity constraint gets applied, then manipulations in one table will affect the work in child tables.

Under the referential integrity constraint, on delete cascade is a referential integrity constraint that automatically deletes child records if the parent record gets deleted. It is in response to the reference action taken from the foreign key.

Example:

CREATE TABLE Student (stu_id int(10) NOT NULL, name varchar(10) NOT NULL, birthdate date NOT NULL, PRIMARY KEY (stu_id));

The above query creates a table student with attributes or columns as student id with int type and primary key as an entity constraint over the column. Other values attributes include name and birthday with varchar and date as its datatypes.

CREATE TABLE Department (dept_id int(20) PRIMARY KEY NOT NULL,stu_id int(10) NOT NULL, dept_name varchar(20) NOT NULL, FOREIGN KEY (stu_id) REFERENCES Student (stu_id) ON DELETE CASCADE);

Here the above query, a department table is formed with attributes as id, department name, and student id. Also, the student id will act as a reference or foreign key from the parent table.

Here on delete cascade constraint says, when any deletion in the student id happens, its entry from the department table will also get deleted. Hence there will not be any entry corresponding to the student in a department after deletion.

Let’s insert the records in the tables and see how the manipulation works here.

First, insert the records in table Student.

INSERT INTO Student (stu_id, name, birthdate) VALUES  
(101, 'John', '1995-11-12'),  
(102, 'Jian', '1983-07-19'),  
(103, 'Jackey', '1965-10-23');

The above query will insert three records in table Student.

Inserting the records in table Department.

INSERT INTO Department (dept_id, stu_id, dept_name) VALUES   
(301, 101, 'Physics'),  
(302, 101, 'Science'),  
(303, 102,  'Maths');

The above query will insert three departments for respective students id’s in the table department.

Now let’s remove a record in the parent table.

DELETE FROM Student WHERE stu_id = 102; 

The final result will remove the third record from the table department as the reference from its parent table gets removed, making the third record dangling.

Hence it collapses the third record using the on delete cascade property. This property is referential integrity constraint property which removes records in the child table when the parent table is affected.