A2-02-31.DML- MySQL ON DELETE CASCADE: Deleting Data From Multiple Related Tables
转载自：http://www.mysqltutorial.org/mysql-on-delete-cascade/
MySQL ON DELETE CASCADE: Deleting Data From Multiple Related Tables
Summary: in this tutorial, you will learn how to use MySQL ON DELETE CASCADE referential action for a foreign key to delete data from multiple related tables.
In the previous tutorial, you learned how to delete data from multiple related tables 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 .
Suppose we have two tables:buildings and rooms . In this database model, each building has one or more rooms. However, each room belongs to one only one building. A room would not exist without a building.
The relationship between the buildings and rooms tables is one-to-many (1:N) as illustrated in the following database diagram:
When we delete a row from the buildings table, we also want to delete the rows in the rooms table that references to the rows in the buildings table. For example, when we delete a row with building no. 2 in the buildings table as the following query:
|
1
2
3
|
DELETE FROM buildings
WHERE
building_no = 2;
|
We want the rows in the rooms table that refers to building number 2 will be also removed.
The following are steps that demonstrate how MySQL ON DELETE CASCADE referential action works.
Step 1. Create the buildings table:
|
1
2
3
4
5
|
CREATE TABLE buildings (
building_no INT PRIMARY KEY AUTO_INCREMENT,
building_name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL
);
|
Step 2. Create the rooms table:
|
1
2
3
4
5
6
7
8
|
CREATE TABLE rooms (
room_no INT PRIMARY KEY AUTO_INCREMENT,
room_name VARCHAR(255) NOT NULL,
building_no INT NOT NULL,
FOREIGN KEY (building_no)
REFERENCES buildings (building_no)
ON DELETE CASCADE
);
|
Notice that we add the ON DELETE CASCADE clause at the end of the foreign key constraint definition.
Step 3. Insert data into the buildings table:
|
1
2
3
|
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 buildings table:
|
1
|
SELECT * FROM buildings;
|
We have two rows in the buildings table.
Step 5. Insert data into the rooms table:
|
1
2
3
4
5
6
|
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 rooms table:
|
1
|
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:
|
1
2
3
|
DELETE FROM buildings
WHERE
building_no = 2;
|
Step 8. Query data from rooms table:
|
1
|
SELECT * FROM rooms;
|
As you can see, all the rows that reference to building_no 2 were deleted.
Notice that ON DELETE CASCADE works only with tables with the 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 affected by the MySQL ON DELETE CASCADE referential action when you delete data from a table. You can query this data from the referential_constraints in the information_schema database as follows:
|
1
2
3
4
5
6
7
8
9
10
|
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 buildings table with the CASCADE deletion rule in the classicmodels database, you use the following query:
|
1
2
3
4
5
6
7
8
9
10
|
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.
