mysql alert on delete cascade_MySQL ON DELETE CASCADE级联删除多张表的数据

Summary: in this tutorial, you will learn how to use MySQL ON DELETE CASCADE referential action for a foreign key to delete data from child tables when you delete data from a parent table.

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

MySQL ON DELETE CASCADE Example

Let’s take a look at an example of using MySQL ON DELETE CASCADE.

We have two tables named buildingsand rooms. Each building has one or more rooms. However, each room belongs to only one building. A room would not exist without a building.

The relationship between the buildingstable and the roomstable is one-to-many (1:N) as illustrated in the following database diagram:

When we delete a record from the buildingstable, we want the records in the rooms table, which associates with the deleted building record to be removed e.g., when we delete a record with building no. 2 in the buildings table as the following query:DELETE FROM buildings WHERE building_no = 2;

We want the records in the rooms table associated with the building number 2 to be removed as well.

The following are steps that demonstrates how MySQL ON DELETE CASCADE referential action works.

Step 1. Create the buildingstable:CREATE TABLE buildings (   building_no int(11) NOT NULL AUTO_INCREMENT,   building_name varchar(255) NOT NULL,   address varchar(355) NOT NULL,   PRIMARY KEY (building_no) ) ENGINE=InnoDB;

Step 2. Create the roomstable:CREATE TABLE rooms (   room_no int(11) NOT NULL AUTO_INCREMENT,   room_name varchar(255) NOT NULL,   building_no int(11) NOT NULL,   PRIMARY KEY (room_no),   KEY building_no (building_no),   CONSTRAINT rooms_ibfk_1    FOREIGN KEY (building_no)    REFERENCES buildings (building_no)    ON DELETE CASCADE ) ENGINE=InnoDB;

Notice that we put the ON DELETE CASCADE clause at the end of the foreign key constraint definition.

Step 3. Insert data into the buildingstable:INSERT INTO buildings(building_name,address) VALUES('ACME Headquaters','3950 North 1st Street CA 95134'), ('ACME Sales','5000 North 1st Street CA 95134')

Step 4. Query data from the buildingstable:SELECT * FROM buildings;

We have two records in the buildingstable.

Step 5. Insert data into the roomstable:INSERT INTO rooms(room_name,building_no) VALUES('Amazon',1), ('War Room',1), ('Office of CEO',1), ('Marketing',2), ('Showroom',2)

Step 6. Query data from the roomstable:SELECT * FROM rooms

We have 3 rooms that belong to building 1 and 2 rooms that belong to the building 2.

Step 7. Delete the building with building no. 2:DELETE FROM buildings WHERE building_no = 2

Step 8. Query data from roomstable:SELECT * FROM rooms

As you see, all the records that refer to building_no2 were deleted.

Notice that ON DELETE CASCADE works only with tables whose storage engines support foreign keys e.g., InnoDB. 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 referential action.

Tips to find tables affected by MySQL ON DELETE CASCADE action

Sometimes, it is useful to know which table is affect by the MySQL ON DELETE CASCADE referential action when you delete data from the parent table. You can query this data from the referential_constraintsin the information_schema database as follows:USE information_schema; SELECT table_name FROM referential_constraints WHERE constraint_schema = 'database_name' AND       referenced_table_name = 'parent_table' AND       delete_rule = 'CASCADE'

For example, to find tables that associated with the buildingstable with the CASCADE delete rule  in the classicmodelsdatabase, you use the following query:USE information_schema; SELECT table_name FROM referential_constraints WHERE constraint_schema = 'classicmodels' AND       referenced_table_name = 'buildings' AND       delete_rule = 'CASCADE'

In this tutorial, we have shown you step by step how to use the MySQL ON DELETE CASCADE referential action for a foreign key to delete data automatically from the child tables when you delete data from the parent table.