MySQL价值的唯一性
我有一个包含列的表：
employer_id
contact_id
primary
前两个是整数，最后一个是 1/0 的tinyint。一个雇主可以加入多个联系人，反之亦然。但是，我想强制规定，对于每个特定雇主来说，primary = 1 的情况只能有一个。可以有很多primary = 0。
有没有办法通过在MySQL中使用约束/键来做到这一点？
谢谢
I have a table that contains the columns:
employer_id
contact_id
primary
First two are integers, last is a tinyint that's either 1/0. An employer can be joined to multiple contacts and vice versa. However, I want to enforce that there can only be one instance where primary = 1 for each a particular employer. There can be many where primary = 0.
Is there any way to do this through the use of constraints/keys in MySQL?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不是通过键，但您可以设置插入/更新触发器来检查
(count(*) where Primary=1 and雇主_id=?) <= 1。编辑：实际上，我认为有一种方法：您创建第二个表，其中
employer_id作为主键，并为其主要联系人创建第二个字段。因此，您将拥有这 2 个表：不再有
primary列。由于employer_id是第二个表中的键，因此您最多可以有一个条目。您通过左外连接获取主要联系人，它返回主要联系人或
null。Not through keys, but you can set an insertion/update trigger to check that
(count(*) where primary=1 and employer_id=?) <= 1.Edit: Actually, I think there is a way: you make a second table with
employer_idas the primary key and a second field for his primary contact. So you'd have these 2 tables:No more
primarycolumn. Sinceemployer_idis a key in the second table, you can have at most one entry.You get the primary contact through a left outer join and it returns either the primary contact or
null.几天前我遇到了类似的情况，我所做的是再添加一列“created_at”，这样我就可以检索“primary”（最后插入的列并且primary = 1）；
I had a similar case a few days ago, and what I did was add one more columns "created_at", this way I can retrieve the "primary" (the last one inserted and with primary = 1);