外键约束中的mysql循环依赖

Question

给定架构:

我需要的是让每个 user_identities.belongs_to 引用一个 users.id。

同时，每个用户都有一个primary_identity，如图所示。

但是，当我尝试使用 ON DELETE NO ACTION ON UPDATE NO ACTION 添加此引用时，MySQL 说

#1452 - Cannot add or update a child row: a foreign key constraint fails (yap.#sql-a3b_1bf, CONSTRAINT #sql-a3b_1bf_ibfk_1 FOREIGN KEY (belongs_to) REFERENCES users (id) ON DELETE NO ACTION ON UPDATE NO ACTION)

我怀疑这是由于循环依赖，但我该如何解决它(和保持参照完整性)？

Answer 1

解决这个问题的唯一方法(至少在 MySQL 的有限能力的情况下)允许两个 FK 列中的 NULL 值。使用主要身份创建新用户将如下所示:

insert into users (id, primary_identity)
values (1, null);

insert into identities (id, name, belongs_to)
values (1, 'foobar', 1);

update users 
  set primary_identity = 1
where id = 1;

commit;

此解决方案的唯一缺点是您不能强制用户具有主要身份(因为该列必须可以为空)。

^{另一种选择是更改为支持延迟约束的 DBMS，然后您可以插入两行，并且只会在提交时检查约束。或者使用可以有部分索引的 DBMS，然后你可以使用带有 is_primary 列的解决方案}