MySQL的删除级联

时间:2019-11-20 08:52:07  来源:igfitidea点击:

简介:在本教程中,您将学习如何对外键使用MySQL ON DELETE CASCADE引用动作从多个相关表中删除数据。

在上一教程中,您学习了如何使用单个DELETE语句从多个相关表中删除数据。
但是,MySQL为外键提供了一种更有效的方法,称为ON DELETE CASCADE引用操作,当您从父表中删除数据时,该操作允许您自动从子表中删除数据。

MySQL ON DELETE CASCADE示例

让我们看一个使用MySQL ON DELETE CASCADE的示例。

假设我们有两个表:建筑物和房间。
在此数据库模型中,每个建筑物都有一个或多个房间。
但是,每个房间仅属于一个建筑物。
没有建筑物,就不会有房间。

建筑物和房间表之间的关系是一对多(1:N),如下数据库图所示:

从建筑物表中删除一行时,您还希望删除房间表中所有引用建筑物表中该行的行。
例如,当您删除建筑物编号为2.在建筑物表中按以下查询:

DELETE FROM buildings 
WHERE building_no = 2;

您还希望房间表中指向2号楼的行也将被删除。

以下是演示ON DELETE CASCADE引用动作如何工作的步骤。

步骤1.创建建筑表:

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.将行插入建筑表中:

INSERT INTO buildings(building_name,address)
VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),
      ('ACME Sales','5000 North 1st Street CA 95134');

步骤4.从建筑物表中查询数据:

SELECT * FROM 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;

我们有3个属于1号楼的房间和2个属于2号楼的房间。

步骤7.删除带有编号的建筑物。
2:

DELETE FROM buildings 
WHERE building_no = 2;

步骤8.从rooms表查询数据:

SELECT * FROM rooms;

如您所见,所有引用building_no 2的行均被自动删除。

请注意,ON DELETE CASCADE仅适用于具有支持外键的存储引擎的表,例如InnoDB。

某些表类型不支持外键,例如MyISAM,因此应为计划使用MySQL ON DELETE CASCADE引用操作的表选择适当的存储引擎。

查找受MySQL ON DELETE CASCADE操作影响的表的提示

有时,从表中删除数据时了解哪个表受ON DELETE CASCADE引用动作影响非常有用。
您可以从information_schema数据库中的referential_constraints查询此数据,如下所示:

USE information_schema;

SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'database_name'
        AND referenced_table_name = 'parent_table'
        AND delete_rule = 'CASCADE'

例如,要在classicmodels数据库中使用CASCADE删除规则查找与建筑物表关联的表,请使用以下查询:

USE information_schema;

SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'classicmodels'
        AND referenced_table_name = 'buildings'
        AND delete_rule = 'CASCADE'

在本教程中,您学习了如何在从父表中删除数据时使用MySQL ON DELETE CASCADE的外键引用操作从子表中自动删除数据。