问题描述
我试图为两列创建UNIQUE INDEX约束，但是仅当另一列包含值1时才如此.例如，仅当active = 1时，column_1和column_2应该是UNIQUE.任何包含active = 0的行都可以与另一行共享column_1和column_2的值，而与active的另一行的值无关.但是active = 1所在的行不能与具有active = 1的另一行共享column_1或column_2的值.
I'm trying to create a UNIQUE INDEX constraint for two columns, but only when another column contains the value 1. For example, column_1 and column_2 should be UNIQUE only when active = 1. Any rows that contain active = 0 can share values for column_1 and column_2 with another row, regardless of what the other row's value for active is. But rows where active = 1 cannot share values of column_1 or column_2 with another row that has active = 1.
共享"的意思是在同一列中具有相同值的两行.示例:row1.a = row2.a AND row1.b =row2.b.仅当row1中的两列都与row2中的其他两列相匹配时，才会共享值.
What I mean by "share" is two rows having the same value(s) in the same column(s). Example: row1.a = row2.a AND row1.b = row2.b. Values would be shared only if both columns in row1 matched the other two columns in row2.
我希望我能说清楚. :\
I hope I made myself clear. :\
推荐答案
您可以尝试使用column_1，colum_2和active创建多列UNIQUE索引，然后为不需要唯一性的行设置active = NULL.另外，您可以使用触发器(请参见MySQL触发器语法)并检查每个插入/更新的行是否已在表中包含这些值-但我认为这样做会很慢.
You can try to make multi-column UNIQUE index with column_1, column_2 and active, and then set active=NULL for the rows where uniqueness not required. Alternatively, you can use triggers (see MySQL trigger syntax)and check for each inserted/updated row if such values are already in the table - but I think it would be rather slow.
这篇关于唯一约束，仅当字段包含特定值时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！