时间戳

首页 > 解决方案 > 两种方式都使用 ON DELETE，具有 1-1 关系，其中只有表具有外键

mysql - 两种方式都使用 ON DELETE，具有 1-1 关系，其中只有表具有外键

问题描述

假设我有两张桌子，a并且b. 表a包含对 的可选FOREIGN KEY引用b。Tableb 没有对 table的FOREIGN KEY引用a，也不应该。这样做的原因是 tablea不是唯一可能引用 table 中的行的 table b：tablesx并且y可能还引用b，并且将来可能会添加更多的表，这些表可能会引用来自 table 的行b。

因此，表中的每一行b都有一个“所有者行”，它可能属于a、x、y或可能属于许多其他表中的任何一个。

现在，如果表中的一行b被删除，我希望它将对自身的外键引用从a、x或y（如果有的话）设置为 null。我知道我可以ON DELETE SET NULL在外键约束a和其他表中使用来完成此操作，因此可以解决此问题。

但是，如果“所有者行”被删除，无论该行是否位于a, x,y或其他任何位置，我都希望相应的行b也被删除。这是我不知道该怎么做。

简而言之：

  1. a参考资料b。这是一个可选引用，并非所有as 都会有b.
    • ( x, y, 和其他表也有类似的关系b)
  2. b不会也不应该参考a或任何其他表格。
  3. 如果我从中删除a并且给定a的有相应b的 ，b则应将其删除。
  4. 如果我从 中删除b，并且任何其他表包含对它的引用b，则该引用应设置为 null。

我将如何做到这一点？

标签： mysqlforeign-keys

解决方案

create table a
( ...
, b_id  bigint default null comment 'fk ref b.id'
, constraint a_b_id foreign key (b_id) reference b (id) on delete set null 
, ...
) engine=innodb

表x和y定义类似，具有可为空的外键列

create table x 
( ...
, b_id  bigint default null comment 'fk ref b.id'
, constraint x_b_id foreign key (b_id) references b (id) on delete set null 
, ...
) engine=innodb

和

create table y 
( ...
, b_id  bigint default null comment 'fk ref b.id'
, constraint y_b_id foreign key (b_id) references b (id) on delete set null 
, ...
) engine=innodb

当从中删除一行时，三个表中任何一个列的b任何值都b_id引用了删除的行，这些值将更改为 NULL。

MySQL 中没有声明性约束可以完成 3。

“如果我从 a 中删除，并且给定的 a 有相应的 b，则应该删除该 b。”

我们也许可以使用 TRIGGER 来完成此操作，但我们遇到了一些问题，即触发器中的语句可以引用哪些表。这可能会更好地使用应用程序逻辑来处理，而不是数据库规则或触发器。

如果我要尝试触发，那么类似

 DELIMITER $$

 CREATE TRIGGER a_ad
 AFTER DELETE ON a
 FOR EACH ROW
 BEGIN
    DELETE FROM b WHERE b.id = OLD.b_id ;
 END$$

 DELIMITER ;

（我不确定这是否被允许，或者是否会引发错误......考虑

table b
---------
row id=42

和

table a
-------
row id=2  b_id=42
row id=3  b_id=42

考虑这个 SQL 语句

DELETE FROM a WHERE a.id IN (2,3);

行的删除id=2将触发“删除后”触发器；这将在 上执行 DELETE b，外键将找到行id=3引用，并尝试将b_id列设置为 NULL ...但该行可能已经被初始 DELETE 语句锁定...我只是不确定是什么在这种情况下会发生；我们在触发器上遇到了一些限制和限制（比如反对修改在触发触发器的语句中引用的表中的行，）

推荐阅读