MySQL ON DELETE CASCADE
简介:在本教程中,您将学习如何 对外键使用MySQL ON DELETE CASCADE引用操作来从多个相关表中删除数据。
在上一个教程中,您学习了如何使用单个外键的ON DELETE CASCADE引用操作,允许您在从父表中删除数据时自动从子表中删除数据。
MySQL ON DELETE CASCADE 实例
我们来看一个使用MySQL的例子ON DELETE CASCADE。
假设我们有两个表:buildings和rooms。在此数据库模型中,每个建筑物都有一个或多个房间。但是,每个房间只属于一栋建筑。没有建筑物就不会有房间。
buildings 和rooms表之间的关系是一对多(1:N),如下面的数据库图所示:
当我们从buildings表中删除一行时,我们还想删除rooms 表中引用buildings表中行的行。例如,当我们删除一个没有建筑物的行时。2在buildings 表作为以下查询:
DELETE FROM buildings
WHERE
building_no = 2;
我们希望rooms表中引用建筑物编号2的行也将被删除。
以下是演示MySQL ON DELETE CASCADE 引用操作如何 工作的步骤。
第1步,创建buildings表:
CREATE TABLE buildings (
building_no INT PRIMARY KEY AUTO_INCREMENT,
building_name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL
);
第2步,创建rooms表:
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
);
请注意,我们ON DELETE CASCADE 在外键约束定义的末尾添加了子句。
第3步,将数据插入buildings表中:
INSERT INTO buildings(building_name,address)
VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),
('ACME Sales','5000 North 1st Street CA 95134');
第4步。从buildings表中查询数据:
SELECT * FROM buildings;
运行结果:
+-------------+------------------+--------------------------------+
| building_no | building_name | address |
+-------------+------------------+--------------------------------+
| 1 | ACME Headquaters | 3950 North 1st Street CA 95134 |
| 2 | ACME Sales | 5000 North 1st Street CA 95134 |
+-------------+------------------+--------------------------------+
2 rows in set (0.00 sec)
我们在buildings表中有两行。
第5步,将数据插入rooms表中:
INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
('War Room',1),
('Office of CEO',1),
('Marketing',2),
('Showroom',2);
第6步,从rooms表中查询数据:
SELECT * FROM rooms;
运行结果:
+---------+---------------+-------------+
| room_no | room_name | building_no |
+---------+---------------+-------------+
| 1 | Amazon | 1 |
| 2 | War Room | 1 |
| 3 | Office of CEO | 1 |
| 4 | Marketing | 2 |
| 5 | Showroom | 2 |
+---------+---------------+-------------+
5 rows in set (0.01 sec)
我们有3个房间属于建筑1和2个房间属于建筑2。
第7步,删除建筑物号码的建筑物。2:
DELETE FROM buildings
WHERE
building_no = 2;
第8步,从rooms表中查询数据:
SELECT * FROM rooms;
运行结果:
+---------+---------------+-------------+
| room_no | room_name | building_no |
+---------+---------------+-------------+
| 1 | Amazon | 1 |
| 2 | War Room | 1 |
| 3 | Office of CEO | 1 |
+---------+---------------+-------------+
3 rows in set (0.00 sec)
如您所见,所有引用building_no2 的行都 被删除了。
注意:ON DELETE CASCADE 仅适用于存储引擎支持外键的表, 例如InnoDB。某些表类型不支持MyISAM等外键,因此您应为计划使用MySQL ON DELETE CASCADE 引用操作的表选择适当的存储引擎 。
查找受MySQL ON DELETE CASCADE操作影响的表的提示
有时,ON DELETE CASCADE 当您从表中删除数据时,了解哪个表受MySQL 参照操作影响很有用 。您可以从查询数据referential_constraints的information_schema 数据库,如下所示:
USE information_schema;
SELECT
table_name
FROM
referential_constraints
WHERE
constraint_schema = 'database_name'
AND referenced_table_name = 'parent_table'
AND delete_rule = 'CASCADE'
例如,要查找与数据库mysqldemo中具有CASCADE 删除规则的表关联的表buildings,请使用以下查询:
USE information_schema;
SELECT
table_name
FROM
referential_constraints
WHERE
constraint_schema = 'classicmodels'
AND referenced_table_name = 'buildings'
AND delete_rule = 'CASCADE'
运行结果:
+------------+
| table_name |
+------------+
| rooms |
+------------+
1 row in set (0.12 sec)
在本教程中,我们逐步向您展示了如何在ON DELETE CASCADE 从父表中删除数据时,使用外部键的MySQL 引用操作从子表中自动删除数据。