Khóa học miễn phí MySQL – On Delete Cascade nhận dự án làm có lương

MySQL – On Delete Cascade

Table of content


The MySQL ON DELETE CASCADE Constraint

The MySQL ON DELETE CASCADE constraint ensures that when a row in the parent table is deleted, all related rows in the child table are automatically deleted as well. This constraint helps maintain referential integrity between two tables that are connected through a foreign key relationship.

If we do not use this constraint, the database server will, by default, prevent us from deleting data in a table if it is referenced by other tables.

Example

Creating the Parent Table −

First, let us create the parent table with the name PERSONS using the following query −

CREATE TABLE PERSONS(
   P_ID int primary key,
   P_NAME varchar(40),
   P_AGE int
);

Now, let us insert some values into the above created table using the INSERT statement as shown below −

INSERT INTO PERSONS VALUES 
(1, "Priya", 29),
(2, "Sarah", 20),
(3, "Varun", 26),
(4, "Dev", 25),
(5, "Ram", 31),
(6, "Aarohi", 34);

The PERSONS table obtained is as shown below −

P_ID P_NAME P_AGE
1 Priya 29
2 Sarah 20
3 Varun 26
4 Dev 25
5 Ram 31
6 Aarohi 34

Creating the Child Table −

Now, let us create a child table named Films_watched with the ON DELETE CASCADE constraint. In this table, the P_ID column is a foreign key referencing the P_ID column in the Persons table −

CREATE TABLE Films_watched (
   P_ID INT,
   F_NO INT,
   F_NAME varchar(40),
   PRIMARY KEY(P_ID,F_NO),
   FOREIGN KEY(P_ID)
   REFERENCES PERSONS(P_ID)
   ON DELETE CASCADE
);

Now, we are inserting rows into the Films_watched table −

INSERT INTO Films_watched VALUES 
(1, 130, "RRR"),
(2, 131, "Bahubali"),
(3, 132, "Pushpa"),
(3, 133, "KGF"),
(3, 134, "Salaar"),
(6, 135, "Karthikeya");

The Films_watched table produced is as follows −

P_ID F_NO F_NAME
1 130 RRR
2 131 Bahubali
3 132 Pushpa
3 133 KGF
3 134 Salaar
6 135 Karthikeya

Deleting a Record from the Parent Table −

As we can see in the above table, we have three films that has been watched by the person with P_ID = 3. Here, we are deleting the person with P_ID = 3 from the PERSONS (parent) table −

DELETE FROM PERSONS WHERE P_ID = 3;

Following is the output obtained −

Query OK, 1 row affected (0.01 sec)

After this deletion, let us check the data in both the Persons and Films_watched tables.

Querying Data from the Persons Table −

To see the remaining records in the Persons table, use the following SELECT query −

SELECT * FROM PERSONS;

We can see in the table below, the row with P_ID = 3 is deleted −

P_ID P_NAME P_AGE
1 Priya 29
2 Sarah 20
4 Dev 25
5 Ram 31
6 Aarohi 34

Querying Data from the Films_watched Table −

Lastly, you can check the data in the Films_watched table −

SELECT * FROM Films_watched;

Output

We can see in the output below that all related records with P_ID = 3 have been automatically deleted −

P_ID F_NO F_NAME
1 130 RRR
2 131 Bahubali
6 135 Karthikeya

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc