JIYIK CN >

In MySQL parlance, a constraint is a set of rules enforced on a table to prevent certain anomalies.

These exceptions will result in error records in the table. Constraints can be of various types based on the behavior of the failure.

They are referential integrity, entity constraints, and semantic constraints. These have meaning and usage based on the type of failure and how to avoid inconsistent behavior.

Let us briefly understand the types of constraints that exist.

3. Referential integrity is a constraint applied to other tables to enforce restrictions on data. The most commonly used constraint is the foreign key constraint.

   If there is modification in the parent table, the child table should remain consistent with the parent table. In case of deletion of records, the reference in the child record should not remain dangling 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, the transcript shows the department or course of the student in the parent table student. If referential integrity constraints are applied, then operations in one table will affect the work in the child table.

Under referential integrity constraints on delete cascadeis a referential integrity constraint which automatically deletes the child records if the parent record is deleted. It is a response to a referential action taken from a 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, inttype and primary key as entity constraints for the columns. Other value attributes include name and birthday which have data types of varcharand date.

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);

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

Here on delete cascadethe constraint says that when any deletion occurs to the student id, its entry from the department table will also be deleted. So, after the deletion there will be no entry corresponding to the student of that department.

Let's insert records into the table to see how the operation works here.

First, Studentinsert records into the table.

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 Studentinsert three records into the table.

部门Insert a record into the table .

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 each student ID in the table departments.

Now let us delete a record from the parent table.

DELETE FROM Student WHERE stu_id = 102; 

The end result will be to delete the third record of the table department because the reference to its parent table is deleted, leaving the third record dangling.

Therefore, it collapses the third record using on delete cascadethe attribute. This attribute is a referential integrity constraint attribute that deletes records in the child table when the parent table is affected.