MySQL ON DELETE CASCADE
ON DELETE CASCADE clause in MySQL is used to automatically remove the matching records from the child table when we delete the rows from the parent table. It is a kind of referential action related to the foreign key.
Suppose we have created two tables with a FOREIGN KEY in a foreign key relationship, making both tables a parent and child. Next, we define an ON DELETE CASCADE clause for one FOREIGN KEY that must be set for the other to succeed in the cascading operations. If the ON DELETE CASCADE is defined for one FOREIGN KEY clause only, then cascading operations will throw an error.
MySQL ON DELETE CASCADE Example
Let us understand how we can use the ON DELETE CASCADE clause in the MySQL table. First, we are going to create two tables named Employee and Payment. Both tables are related through a foreign key with on delete cascade operation. Here, an Employee is the parent table, and Payment is the child table. The following scripts create both tables along with their records.
Table: Employee
The following statement creates a table Employee:
Next, execute the insert query to fill the records.
Execute the SELECT query to verify the data into a table, which can be shown below:
Table: Payment
The below statement creates a table Payment:
Next, execute the insert statement to fill the records into a table.
Execute the SELECT query to verify the data into a table, which can be shown below:
Let us delete data from the parent table Employee. To do this, execute the following statement:
The above statement will delete the employee records whose emp_id = 102 and referencing data into the child table. We can verify the data using the SELECT statement that will give the following output:
In the above output, we can see that all the rows referencing to emp_id = 102 were automatically deleted from both tables.
How to find the affected table by ON DELETE CASCADE action?
Sometimes, before deleting records from the table, we want to know the affected table by the ON DELETE CASCADE referential action. We can find this information by querying from the referential_constraints in the information_schema database as follows:
The below statement produces the result about the tables associated with the Employee table with the ON DELETE CASCADE rule in the employeedb database:
After executing the above command, we will get the output below:
MySQL ON UPDATE CASCADE
ON UPDATE CASCADE clause in MySQL is used to update the matching records from the child table automatically when we update the rows in the parent table. The following example explains it more clearly.
First, we need to use the ALTER TABLE statement to add the ON UPDATE CASCADE clause in the table Payment as below:
It will give the following output:
In the below script, we will update the id of the employee in the Parent Table, and it will automatically reflect this change in the child table as well:
Verifying the content of the Employee and Payment table, we will see that emp_id column values will be updated successfully.
