Updated May 12, 2023
Introduction to MySQL ON DELETE CASCADE
MySQL ON DELETE CASCADE is a MySQL referential action for a MySQL foreign key that permits to remove of records automatically from the child-related tables when the main parental table data is deleted.
The MySQL ON CASCADE statement query shows the reference method for deleting data from both correlated tables, i.e., the parent table and the child table with Primary and Foreign keys, when executing a query to delete data related to the parent table.
It means that if we delete any data or column values in the parental table, then, with referential action, the related data or column values with a foreign key in the sub-table will also be removed spontaneously.
Syntax
We use the following syntax for the MySQL ON DELETE CASCADEfor foreign key using CREATE TABLE:
CREATE TABLE ChildTable_Name(Col1 Data_Type [NOT NULL | PRIMARY KEY | AUTO_INCREMENT],.., ColNDate_Type [NOT NULL | NULL]
FOREIGN KEY (Col1_Child, …, ColN_Child)
REFERENCES ParentTable_Name (Col1_Parent, …., ColN_Parent)
ON DELETE CASCADE);Let’s explain the terms used below:
- FOREIGN KEY: You can generate a foreign key for a MySQL ON DELETE CASCADE using either the MySQL CREATE TABLE or MySQL ALTER TABLE statements.
- ChildTable_Name: It denotes the name of the child table related to the parent table.
- Col1,..,ColN: We want to create these column names with the respective data type in the table. It has a default value of NULL when we do not provide any data type, but we need to add some data type for the proper execution of the query, either NULL or NOT NULL.
- Col1_Child,…,ColN_Child: These are the child table’s columns with reference to the Primary key in the parental table.
- ParentTable_Name: In the query statement, you should use the name of the parent table whose primary keys are linked to the child table.
- Col1_Parent,…..,ColN_Parent: These are the parent table columns that hold Primary keys to which the foreign key will be associated
- ON DELETE CASCADE: This MySQL Keyword is responsible for changing the child table when the parent table is affected.
- CASCADE: When you use ON DELETE or ON UPDATE in aggregation, any changes made to the parent data will also affect the child data. This means that the child data will be altered accordingly.
How does ON DELETE CASCADE work in MySQL?
- The parent table in MySQL contains the original key values, while the child table is a linked table that refers to the main key values in the parent table within the database.
- Supposing we have created two MySQL tables, the person(ID, Name, Address) and Employee(EmpID, ID, Profile, Contact). In the MySQL database structure model, each person occupies single or multiple employee profiles. However, every employee profile belongs to just one person in the table relation. An employee profile will not existent lacking a person.
- Hence, we can conclude that the relation between the person and employee tables is distinct to various, i.e. (1:N)
- Again, assume that when you cancel a row in the person table, you also desire to remove the rows from the employee table associated with the Primary key record in the person table.
- For instance, if you remove the person with id 1, the employee profiles that reference this id will also be needed to remove, so, in this case, we will apply with the DELETE statement the MySQL ON DELETE CASCADE for a foreign key.
- It is important to note that MySQL ON DELETE CASCADE works with the storage engines that support foreign keys such as InnoDB but may not work in MyISAM type engine.
Examples of MySQL ON DELETE CASCADE
Let us consider the following examples to demonstrate the use of MySQL ON DELETE CASCADEfor a foreign key for referential works using the CREATE, SELECT like Table statements:
Example #1 – Creating a parent table
We are creating a main table with a Primary key using the below query:
CREATE TABLE Building (Build_Num int AUTO_INCREMENT PRIMARY KEY, Build_Name varchar(255) NOT NULL, Build_Address varchar(255) NOT NULL)ENGINE = InnoDB;Example #2 – Creating a child table
We will also create a sub-table for a foreign key referential action and implement the MySQL ON DELETE CASCADE with CREATE TABLE statement below:
CREATE TABLE Room (Room_Num int AUTO_INCREMENT PRIMARY KEY, Room_Name varchar(255) NOT NULL, Build_Num int NOT NULL,
FOREIGN KEY (Build_Num)
REFERENCES Building (Build_Num)
ON DELETE CASCADE) ENGINE = InnoDB;Example #3 – Entering some inputs in the tables created above
INSERT INTO Building(Build_Name, Build_Address) VALUES('Sahara Apartments','Sector62, Noida'), ('South City','Baduan, Bareily');To view data from the Building table:
SELECT * FROM Building;Output:
For Room Table:
INSERT INTO Room(Room_Name, Build_Num) VALUES('PawanVihar','1'), ('Acme Floors','1'),('Mecon Floors','1') ,('Amazon','2'),('Tata Showroom','2');
Display records:
SELECT * FROM Room;Output:
Example #4 – Deleting Build_Num 2 from Building table
DELETE FROM Building WHERE Build_Num=2;
SELECT * FROM Building;Output:
Example #5 – Querying Rows from Room Table
SELECT * FROM Room;Output:
Initially, there were three rooms associated with building number 1, and two rooms were linked to building number 2.. So, now by deleting the Build_Num 2 in the parent table, we can see that the rooms referenced with foreign key Build_Num in Room table are also auto-deleted simultaneously.
Example #6 – Finding Table affected by ON DELETE CASCADE in MySQL
Suppose we want to know the CASCADE affected table by MYSQL ON DELETE CASCADE for the foreign key to old referential action when we delete data from the related table. In that case, we need to apply a query in the information_schema database in the phpMyAdmin or database panel with the syntax as follows:
SQL Query:
USE information_schema;
SELECT Table_Name FROM referential_constraints
WHERE
constraint_schema = 'achu'
AND referenced_Table_Name = 'Building'
AND delete_rule = 'CASCADE'Output:
Conclusion
Using this type of MySQL delete statement helps to automatically remove the associated table data whenever we delete the data in the paternal table to save our time, manage the table records easily, fetch updated rows, and organize the database size as well as improve usage of the referential action for a foreign key.
Recommended Articles
We hope that this EDUCBA information on “MySQL ON DELETE CASCADE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
