当无涯教程从父表中删除行时,MySQL中的ON DELETE CASCADE子句用于从子表中自动删除匹配记录。这是与外键相关的一种参照动作。
假设创建了两个带有外键关系的FOREIGN KEY的表,使这两个表成为父级和子级。接下来,为一个FOREIGN KEY定义一个ON DELETE CASCADE子句,必须将另一个FOREIGN KEY设置为成功进行级联操作。如果仅为一个FOREIGN KEY子句定义ON DELETE CASCADE,则级联操作将引发错误。
让无涯教程了解如何在MySQL表中使用ON DELETE CASCADE子句。首先,将创建两个名为 Employee和Payment 的表。这两个表都通过带有删除级联操作的外键关联。在这里,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
下面的语句创建一个付款表:
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引用操作来了解受影响的表。可以通过在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 数据库中的ON DELETE CASCADE规则生成与Employee表关联的表的结果:
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子句用于自动更新子表中的匹配记录表。以下示例对其进行了更清晰的说明。
首先,需要使用 ALTER TABLE 语句在"Payment"表中添加ON UPDATE CASCADE子句,如下所示:
ALTER TABLE Payment ADD CONSTRAINT `payment_fk` FOREIGN KEY(emp_id) REFERENCES Employee (emp_id) ON UPDATE CASCADE;
它将给出以下输出:
在下面的脚本中,将更新"Employee"中员工的ID,它也会自动在子表中反映此更改:
mysql> UPDATE Employee SET emp_id = 102 WHERE emp_id = 103;
验证Employee and Payment表的内容,无涯教程将看到 emp_id 列值将成功更新。
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)