外键约束中的mysql循环依赖 [英] mysql circular dependency in foreign key constraints

问题描述

给定模式：

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

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

但是，当我尝试添加这个引用与 ON DELETE NO ACTION ON更新无操作，MySQL说

＃1452 - 无法添加或更新子行：外键约束失败（ yap 。＃sql-a3b_1bf ，CONSTRAINT ＃sql-a3b_1bf_ibfk_1 FOREIGN KEY（ belongs_to ）参考用户（ id 和维护引用完整性）？

解决方案

解决方案的唯一方法（至少与限制MySQL的功能）允许两个FK列中的 NULL 值。创建一个具有主要身份的新用户可以看起来像这样：

 插入用户（id，primary_identity）
值（1，null）; 
 
插入身份（id，name，belongs_to）
值（1，'foobar'，1）; 
 
更新用户
 set primary_identity = 1 
其中id = 1; 
 
提交; 
  

此解决方案的唯一缺点是您无法强制用户具有主要身份（因为列需要为空）

^{另一个选择是更改为支持延迟约束的DBMS，那么你可以只需插入两行，约束只会在提交时检查。或使用DBMS，您可以使用部分索引，然后您可以使用 is_primary 列}

Given the schema:

What I need is having every user_identities.belongs_to reference an users.id.

At the same time, every users has a primary_identity as shown in the picture.

However when I try to add this reference with ON DELETE NO ACTION ON UPDATE NO ACTION, MySQL says

#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)

I suspect this is due to the circular dependency, but how could I solve it (and maintain referential integrity)?

解决方案

The only way to solve this (at least with the limited capabilities of MySQL) to allow NULL values in both FK columns. Creating a new user with a primary identity would then look something like this:

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;

The only drawback of this solution is that you cannot force that a user has a primary identity (because the column needs to be nullable).

^{Another option would be to change to a DBMS that supports deferred constraints, then you can just insert the two rows and the constraint will only be checked at commit time. Or use a DBMS where you can have a partial index, then you could use the solution with an is_primary column}

这篇关于外键约束中的mysql循环依赖的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！