首先,如果这是一个骗局,我很抱歉-我怀疑可能是,但我找不到。
假设我有一张公司表:
id | company_name
----+--------------
1 | Someone
2 | Someone else
以及联系人表:
id | company_id | contact_name | is_primary
----+------------+--------------+------------
1 | 1 | Tom | 1
2 | 2 | Dick | 1
3 | 1 | Harry | 0
4 | 1 | Bob | 0
是否可以设置contacts
表,使其要求为每个公共company_id
设置一个且只有一个记录的is_primary
标志?
所以如果我试着做:
UPDATE contacts
SET is_primary = 1
WHERE id = 4
查询将失败,因为Tom
(id = 1
)已经被标记为company_id = 1
的主要联系人。或者更好的是,是否可以构造一个触发器以使查询成功,但Tom
的is_primary
标志将被相同的操作清除?
我并不太在意检查company_id
是否存在于companies
表中,在我进入这个阶段之前,我的PHP代码就已经执行了这个检查(尽管我想,如果有一种方法可以在同一操作中执行,那就太好了)。
当我最初想到这一点时,我想"这很容易,我只需要在company_id
和is_primary
列中添加一个唯一的索引",但显然这行不通,因为它会将我限制为一个主要联系人和一个非主要联系人-任何添加第三个联系人的尝试都会失败。但我忍不住觉得,有一种方法可以配置一个唯一的索引,为我提供所需的最低功能——拒绝添加第二个主要联系人的尝试,或者拒绝离开没有主要联系人的公司的尝试。
我知道我可以在companies
表中添加primary_contact
字段,在contacts
表中添加FK,但感觉很混乱。我不喜欢两个表都有FK的想法——在我看来,一个表应该依赖另一个表,而不是两个表相互依赖。我想我只是觉得随着时间的推移,出问题的可能性会更大。
综上所述:
- 如何限制联系人表,使具有给定
company_id
的一个且只有一个记录设置了is_primary
标志 - 有人想知道两张桌子之间有FK是个好主意还是坏主意吗
表之间的循环引用确实很混乱。请参阅这篇(十年前)文章:SQL By Design:The Circular Reference
做出这种约束的最干净的方法是添加另一个表:
Company_PrimaryContact
----------------------
company_id
contact_id
PRIMARY KEY (company_id)
FOREIGN KEY (company_id, contact_id)
REFERENCES Contact (company_id, id)
这还需要(company_id, id)
上的表Contact
中的UNIQUE
约束
您可以在设置之前进行查询
UPDATE contacts SET is_primary = 0 WHERE company_id = .....
甚至
UPDATE contacts
SET is_primary = IF(id=[USERID],1,0)
WHERE company_id = (
SELECT company_id FROM contacts WHERE id = [USERID]
);
只是提出了一个替代方案——就我个人而言,我可能会选择FK方法,而不是这种类型的变通方法,即在companies表中有一个带有primary_user_id字段的字段。
编辑方法,不依赖联系人。is_pripary字段
替代方法,首先从联系人中删除is_primary。其次,在公司中添加一个"primary_contact_id"INT字段。第三,当更改主要联系人时,只需更改该primary_contact_id,从而防止在任何时候存在多于1个主要联系人的任何可能性,并且所有这些都不需要后台中的触发器等。
该选项在任何引擎中都可以正常工作,因为它只是更新INT字段,任何对FK等的依赖都可以根据需要添加/删除,但最简单的是,它只是更改INT字段值
只要您需要从公司到标记主要的联系人的一个精确的链接,此选项是可行的