MySQL - On Delete Cascade

---

---

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