MySQL ON DELETE CASCADE子句

MySQL中的ON DELETE CASCADE子句用于在从父表中删除行时自动从子表中删除匹配的记录。它是与外键相关的引用操作的一种形式。

假设我们已经在外键关系中创建了两个表，使两个表成为父表和子表。接下来，我们为其中一个FOREIGN KEY定义了一个ON DELETE CASCADE子句，这个子句必须为其他子句设置以使级联操作成功。如果只为一个FOREIGN KEY子句定义了ON DELETE CASCADE，则级联操作将抛出错误。

MySQL ON DELETE CASCADE示例

让我们了解如何在MySQL表中使用ON DELETE CASCADE子句。首先，我们将创建两个名为”Employee”和”Payment”的表。这两个表通过一个具有on delete cascade操作的外键相关联。在这里，Employee是父表，Payment是子表。以下脚本创建了这两个表以及它们的记录。

表：Employee

下面的语句创建了一个名为Employee的表：

CREATE TABLE Employee (
  emp_id int(10) NOT NULL,
  name varchar(40) NOT NULL,
  birthdate date NOT NULL,
  gender varchar(10) NOT NULL,
  hire_date date NOT NULL,
  PRIMARY KEY (emp_id)
);

接下来，执行插入查询来填充记录。

INSERT INTO Employee (emp_id, name, birthdate, gender, hire_date) VALUES
(101, 'Bryan', '1988-08-12', 'M', '2015-08-26'),
(102, 'Joseph', '1978-05-12', 'M', '2014-10-21'),
(103, 'Mike', '1984-10-13', 'M', '2017-10-28'),
(104, 'Daren', '1979-04-11', 'M', '2006-11-01'),
(105, 'Marie', '1990-02-11', 'F', '2018-10-12');

执行SELECT查询来验证数据是否插入表中，如下所示：

表：Payment

下面的语句创建了一个表 Payment：

CREATE TABLE Payment (
  payment_id int(10) PRIMARY KEY NOT NULL,
  emp_id int(10) NOT NULL,
  amount float NOT NULL,
  payment_date date NOT NULL,
  FOREIGN KEY (emp_id) REFERENCES Employee (emp_id) ON DELETE CASCADE
);

接下来，执行 插入语句 将记录填充到表中。

INSERT INTO Payment (payment_id, emp_id, amount, payment_date) VALUES 
(301, 101, 1200, '2015-09-15'),
(302, 101, 1200, '2015-09-30'),
(303, 101, 1500, '2015-10-15'),
(304, 101, 1500, '2015-10-30'),
(305, 102, 1800, '2015-09-15'),
(306, 102, 1800, '2015-09-30');

执行SELECT查询以验证数据是否进入表中，可以如下所示：

删除父表Employee中的数据。为此，请执行以下语句：

mysql> DELETE FROM Employee WHERE emp_id = 102;

上述语句将删除具有emp_id = 102条件的员工记录以及相关的子表数据。我们可以使用SELECT语句验证数据，将得到以下输出：

在上面的输出中，我们可以看到所有引用emp_id = 102的行都被自动从两个表中删除了。

如何查找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'

以下语句生成关于在 employeedb 数据库中与Employee表关联的具有ON DELETE CASCADE规则的表的结果：

USE information_schema;

SELECT table_name FROM referential_constraints
WHERE constraint_schema = 'employeedb'
        AND referenced_table_name = 'Employee'
        AND delete_rule = 'CASCADE';

执行以上命令后，我们将得到以下输出结果：

MySQL ON UPDATE CASCADE

ON UPDATE CASCADE子句在MySQL中用于在更新父表的行时自动更新子表中的匹配记录。下面的示例更清楚地解释了它。

首先，我们需要使用 ALTER TABLE 语句在Payment表中添加ON UPDATE CASCADE子句，如下所示：

ALTER TABLE Payment ADD CONSTRAINT `payment_fk` 
FOREIGN KEY(emp_id) REFERENCES Employee (emp_id) ON UPDATE CASCADE;

它将会给出以下输出结果：

在下面的脚本中，我们将更新父表中员工的ID，并且这种更改也会自动反映在子表中：

mysql> UPDATE Employee SET emp_id = 102 WHERE emp_id = 103;

验证员工和支付表的内容，我们将看到 emp_id 列的值将被成功更新。