聊聊从多个具有外键约束的表中删除数据行的问题

数据表

首先创建数据库：

create database images_zt;
use images_zt;

比如有如下两个数据表（以下为主表）：

create table list
(
    zt_id int unsigned not null auto_increment,
    title varchar(255) not null,
    created_at datetime not null,
    updated_at datetime not null,
    primary key(zt_id)
)ENGINE = InnoDB;

接下来是从表：

create table resource
(
    img_id int unsigned not null auto_increment,
    zt_id int unsigned not null,
    img_url text not null,
    created_at datetime not null,
    updated_at datetime not null,
    primary key(img_id),
    index (img_url),
    foreign key(zt_id) references list(zt_id)
)ENGINE = InnoDB;

通过创建的SQL语句，我们可以看到，在resource表中，将List表的zt_id设置为了外键。《MYsql外键约束》

MySQL外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

这时候，如果我们仅仅从主表中删除一行：

 DELETE FROM list WHERE zt_id = 1;

这个时候一定会报错：

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`images_zt（这个为数据库）`.`resource`, CONSTRAINT `resource_ibfk_1` FOREIGN KEY (`zt_id`) REFERENCES `list` (`zt_id`))

大致翻译过来的意思就是：

无法删除或更新父行：外键约束失败（`images`.`resource`，约束`resource`外键（`zt_id`）引用`list`（`zt_ id`）

从《MYsql外键约束》文章中我们知道，关于外键的创建，我们在另一篇文章再详细讨论：

主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

所以问题原因就是表关联生成的强制约束问题，在删除的时候回检查表之间的关联关系，从而导致无法删除。

解决方法：临时关闭外键验证

在PHP中，我们可以通过执行sql语句先关闭外键验证，待执行删除后，再开启外键的验证。

MySQL的环境变量中存在一个foreign_key_checks，这是默认检查外键的配置项，如果将其设置为0，则表示不检查外键约束。查看foreign_key_checks的值：

show VARIABLES like "foreign%";

使用PHP执行：

// 先设置外键约束检查关闭
$sql_close_foreign_key_checks = "SET foreign_key_checks = 0";

// 删除主表的行或者表，如果要删除视图，也是如此
$sql_del = "DELETE FROM list WHERE zt_id = 1";

// 开启外键约束检查，以保持表结构完整性
$sql_open_foreign_key_checks = "SET foreign_key_checks = 1";

// DB::handle为数据库假想操作函数，执行sql语句
DB::handle($sql_close_foreign_key_checks);
$resDB = DB::handle($sql_del);
// 获取受影响行数,这里务必根据实际情况先保存一下，否则有可能被后面的操作覆盖结果
$del_ok = $resDB->rowCount();
// 再开启外键约束检查，以保持表结构完整性
DB::handle($sql_open_foreign_key_checks);
if($del_ok) {....}

解决方法：先删除从表中的行，再删主表的行

// 先删除从表中的行
DELETE FROM resource WHERE zt_id = 1;
// 当从表的行删除，这时删除主表的行就不会报错了
DELETE FROM list WHERE zt_id = 1;

解决方法：设计表时，添加on delete cascade

使用这种方法删除，叫做级联删除，在创建resource表的时候，需要做如下修改：

create table resource
(
    img_id int unsigned not null auto_increment,
    zt_id int unsigned not null,
    ...
    foreign key(zt_id) references list(zt_id) on delete cascade
)ENGINE = InnoDB;

级联删除（on delete set null 、  on delete cascade）属于外键关联的一种

这时候，直接删除主表中的数据，发现从表中的数据也被删除了。

关于多表删除，使用级联删除是一种方法，同时也可以使用存储例程、触发器方法去实现。

根据个人百度搜索得到的结果，一般是不推荐用数据库的级联的 在程序中用事务控制的删除好一些，也容易跟踪问题你用数据库级联，出了问题确实很难跟踪到。现在的趋势是 数据库的关系尽量的简单，尽量不要设外键之类的，所有的逻辑全部在代码中完成。

使用alter修改表：

alter table resource add foreign key(zt_id) references list(zt_id) on delete cascade;

不建议使用外键关系

阿里的JAVA规范中也有下面这一条：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。每次做DELETE 或者UPDATE都必须考虑外键约束，会导致开发的时候很痛苦,测试数据极为不方便。

更多的详情参见这篇文章：为什么不建议使用外键关系

相关资料