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

i'm trying create unique index constraint 2 columns, when column contains value 1. example, column_1 , column_2 should unique when active = 1. rows contain active = 0 can share values column_1 , column_2 row, regardless of other row's value active is. rows active = 1 cannot share values of column_1 or column_2 row has active = 1.

what mean "share" 2 rows having same value(s) in same column(s). example: row1.a = row2.a , row1.b = row2.b. values shared if both columns in row1 matched other 2 columns in row2.

i hope made myself clear. :\

you can try make multi-column unique index column_1, column_2 , active, , set active=null rows uniqueness not required. alternatively, can use triggers (see mysql trigger syntax) , check each inserted/updated row if such values in table - think rather slow.