MySQL UNIQUE 条件约束
我正在尝试为两列（id_1 和 id_2）创建唯一索引约束，条件如下： 如果两个不同的行在 id_2 列中具有相同的值，则它们在 id_1 列中的值也必须相同。
是否可以？
谢谢。
I'm trying to create a unique index constraint for two columns (id_1 and id_2) with the following condition:
If two different rows have the same value in the id_2 column, their values in the id_1 column must also be the same.
Is it possible?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
没有声明性约束来支持这种限制。您描述的场景不满足唯一约束的要求。 （您可以创建约束，但无法添加 id_1 和 id_2 具有相同值的多于一行。
如果您的目的是拒绝基于此限制的插入或更新，您也许能够完成这与行级触发器。
There is no declarative constraint to support such a restriction. The scenario you describe does not satisfy the requirements for a unique constraint. (You can create the constraint, but you won't be able to add more than one row with identical values for id_1 and id_2.
If your intent is to reject an insert or an update based on this restriction, you might be able to accomplish this with a row-level trigger.
您可以通过构建一个检查这些行的查询并将其集成到触发器中来做到这一点。您将无法通过约束来做到这一点，当然也不能通过唯一约束来做到这一点。唯一约束强制行具有唯一值。这意味着对于约束中的一个或多个字段，没有两行可以具有相同的值。
You could do that by building a query that checks these rows and integrate it in a trigger. You won't be able to do that with a constraint and certainly not with a unique constraint. A unique constraint forces rows to have unique values. That means that for the field or fields in the constraint no two rows can have the same value.
那是不可能的。对
id_1和id_2的唯一约束将强制每一行具有不同（唯一）的id_1和id_2。这与你所描述的完全相反。您可以通过使用触发器来强制执行您的要求，但考虑到您提供的少量信息，我不能说这是否是最好的解决方案。
复合外键也可能是解决方案，但我不知道
id_1和id_2指的是什么，所以真的很难说。That is not possible. A unique constraint on
id_1andid_2will force each row to have a different (unique) combination ofid_1andid_2. That's the exact opposite of what you are describing.You could possibly enforce your requirement by using a trigger, but given the small amount of information provided by you, I can't say if it would be the best solution.
A composite foreign key could also be the solution, but I don't know what
id_1andid_2refer to, so it's hard to say, really.