一对多关系中的MySQL循环引用:有没有办法避免它?

Question

请考虑以下示例：

我有一个User表，其中包含用户的信息以及引用Address表的PrimaryAddress列。

Address表包含地址信息以及引用地址所属人员的UserId列。

每个用户可以有多个地址，但只能PrimaryAddress一个地址。因此，User表需要存储对PrimaryAddress的引用以强制实施此规则。在地址表中具有IsPrimary列不会产生类似的效果。

但正如你所知道的，这将在用户表和地址表之间创建循环关系，据我所知，循环依赖关系可能是设计不佳的标志。问题是，有没有办法避免这种情况？如果是这样，如何？

Answer 1

循环引用不一定是"糟糕"的设计。你举了一个具有合法意义的现实世界案例的例子。

我承认管理起来有点复杂。如果需要在创建最终指定为主地址的地址之前创建用户行，则该User.PrimaryAddress必须可为空。

此外，如果需要 SQL 脚本来重新创建数据库，则只能在创建引用的表后添加外键约束。因此，如果您有循环引用，则会遇到先有鸡还是先有蛋的问题。典型的解决方案是创建没有外键的表，然后在创建外键后，使用ALTER TABLE添加外键。

解决方法都牺牲了一些东西。如果添加属性Address.IsPrimary，则必须弄清楚如何确保每个用户只有一个地址是主地址。如果使用第三个表，则必须担心每个用户缺少一行。

循环引用可能是问题最少的解决方案。

Answer 2

不，你会将bridge4表与BEFORE INSERT TRIGGER结合使用

如果一个地址可以有多个用户，这也将有所帮助

那看起来像

CREATE tABLE user (id int PRIMARY KEY)

INSERT INTO user VALUEs(1)

CREATE TABLe address(id int PRIMARY KEY)

INSERt INTO address VALUES(1),(2)

CREATE TABLE user_address ( user_id int, address_id int, Is_primary int
,FOREIGN KEY (user_id)
REFERENCES user(id)
,FOREIGN KEY (address_id)
REFERENCES address(id)
,PRIMARY KEY (user_id,address_id)
)

CREATE TRIGGER before_user_address_insert
BEFORE INSERT
ON user_address FOR EACH ROW
BEGIN
DECLARE rowcount INT;
IF NEW.Is_primary = 1 then
SELECT COUNT(*) 
INTO rowcount
FROM user_address WHERE user_id = NEw.user_id AND Is_primary = 1;

IF rowcount = 1 THEN
set @message_text = CONCAT('userid ',New.user_id ,' has already a primary address');
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @message_text;
END IF; 
end if;
END

INSERT INTO user_address VALUEs(1,1,1)

✓

INSERT INTO user_address VALUEs(1,2,1)

用户 ID 1 已具有主地址

db<>小提琴在这里