{"id":2089,"date":"2013-05-06T18:33:03","date_gmt":"2013-05-07T02:33:03","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2089"},"modified":"2020-04-11T02:02:23","modified_gmt":"2020-04-11T09:02:23","slug":"mysql-on-delete-cascade","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-on-delete-cascade\/","title":{"rendered":"MySQL ON DELETE CASCADE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL  ON DELETE CASCADE<\/code> referential action for a foreign key to delete data from multiple related tables.<\/p>\n\n\n\n

In the previous tutorial, you learned how to delete data from multiple related tables using a single DELETE<\/a><\/code> statement. However, MySQL provides a more effective way called ON DELETE CASCADE<\/code> referential action for a foreign key<\/a> that allows you to delete data from child tables automatically when you delete the data from the parent table.<\/p>\n\n\n\n

MySQL ON DELETE CASCADE example<\/h2>\n\n\n\n

Let’s take a look at an example of using MySQL ON DELETE CASCADE<\/code> .<\/p>\n\n\n\n

Suppose that we have two tables:buildings<\/code> and rooms<\/code> . In this database model, each building has one or many rooms. However, each room belongs to one only one building. A room would not exist without a building.<\/p>\n\n\n\n

The relationship between the buildings<\/code> and rooms<\/code> tables is one-to-many (1:N) as illustrated in the following database diagram:<\/p>\n\n\n\n

\"MySQL<\/figure>\n\n\n\n

When you delete a row from the buildings<\/code> table, you also want to delete all rows in the rooms<\/code>  table that references to the row in the buildings<\/code> table. For example, when you delete a row with building no. 2 in the buildings<\/code>  table as the following query:<\/p>\n\n\n

DELETE<\/span> FROM<\/span> buildings \nWHERE<\/span> building_no = 2<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
You also want the rows in the rooms<\/code> table that refers to building number 2 will be also removed.<\/p>\n\n\n\n
The following are steps that demonstrate how the ON DELETE CASCADE<\/code>  referential action works.<\/p>\n\n\n\n
Step 1<\/strong>. Create the buildings<\/code> table:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> buildings (\n    building_no INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n    building_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    address VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2<\/strong>. Create the rooms<\/code> table:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> rooms (\n    room_no INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n    room_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    building_no INT<\/span> NOT<\/span> NULL<\/span>,\n    FOREIGN<\/span> KEY<\/span> (building_no)\n        REFERENCES<\/span> buildings (building_no)\n        ON<\/span> DELETE<\/span> CASCADE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3<\/strong>. Insert rows<\/a> into the buildings<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> buildings(building_name,address)\nVALUES<\/span>('ACME Headquaters'<\/span>,'3950 North 1st Street CA 95134'<\/span>),\n      ('ACME Sales'<\/span>,'5000 North 1st Street CA 95134'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4<\/strong>. Query data from the buildings<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> buildings;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
We have two rows in the buildings<\/code> table.<\/p>\n\n\n\n
Step 5<\/strong>. Insert rows<\/a> into the rooms<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> rooms(room_name,building_no)\nVALUES<\/span>('Amazon'<\/span>,1<\/span>),\n      ('War Room'<\/span>,1<\/span>),\n      ('Office of CEO'<\/span>,1<\/span>),\n      ('Marketing'<\/span>,2<\/span>),\n      ('Showroom'<\/span>,2<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 6<\/strong>. Query data from the rooms<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> rooms;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
We have three rooms that belong to building no 1 and two rooms that belong to the building no 2.<\/p>\n\n\n\n
Step 7<\/strong>. Delete<\/a> the building with building no. 2:<\/p>\n\n\n
DELETE<\/span> FROM<\/span> buildings \nWHERE<\/span> building_no = 2<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 8<\/strong>. Query data from rooms<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> rooms;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
As you can see, all the rows that reference to building_no<\/code> 2 were automatically deleted.<\/p>\n\n\n\n
Notice that ON DELETE CASCADE<\/code>  works only with tables with the storage engines<\/a> that support foreign keys e.g., InnoDB.<\/p>\n\n\n\n
Some table types do not support foreign keys such as MyISAM so you should choose appropriate storage engines for the tables that you plan to use the MySQL ON DELETE CASCADE<\/code>  referential action.<\/p>\n\n\n\n
Tips to find tables affected by MySQL ON DELETE CASCADE action<\/h2>\n\n\n\n
Sometimes, it is useful to know which table is affected by the ON DELETE CASCADE<\/code>  referential action when you delete data from a table. You can query this data from the referential_constraints<\/code> in the information_schema<\/code>  database as follows:<\/p>\n\n\n
USE<\/span> information_schema;\n\nSELECT<\/span> \n    table_name\nFROM<\/span>\n    referential_constraints\nWHERE<\/span>\n    constraint_schema = 'database_name'<\/span>\n        AND<\/span> referenced_table_name = 'parent_table'<\/span>\n        AND<\/span> delete_rule = 'CASCADE'<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, to find tables that associated with the buildings<\/code> table with the CASCADE<\/code>  deletion rule  in the classicmodels<\/code> database, you use the following query:<\/p>\n\n\n
USE<\/span> information_schema;\n\nSELECT<\/span> \n    table_name\nFROM<\/span>\n    referential_constraints\nWHERE<\/span>\n    constraint_schema = 'classicmodels'<\/span>\n        AND<\/span> referenced_table_name = 'buildings'<\/span>\n        AND<\/span> delete_rule = 'CASCADE'<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the MySQL ON DELETE CASCADE<\/code>  referential action for a foreign key to delete data automatically from the child tables when you delete data from the parent table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Notice that the ON DELETE CASCADE<\/code>  clause at the end of the foreign key constraint<\/a> definition.<\/p>\n\n\n\n