mysql - UNIQUE Constraint, only when a field contains a specific value

Question

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.

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. :

Answer 1

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.