UNIQUE Constraint, only when a field contains a specific value

mysql unique-constraint
Sam picture Sam · Feb 10, 2011 · Viewed 8.7k times · Source

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

Paul Lysak picture Paul Lysak · Feb 10, 2011

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.

Related questions

How do I specify unique constraint for multiple columns in MySQL?

I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Of course the example is just... an example. …

Read More
MySQL - Make an existing Field Unique

I have an already existing table with a field that should be unique but is not. I only know this because an entry was made into the table that had the same value as another, already existing, entry and this …

Read More
MySQL delete multiple rows in one query conditions unique to each row

So I know in MySQL it's possible to insert multiple rows in one query like so: INSERT INTO table (col1,col2) VALUES (1,2),(3,4),(5,6) I would like to delete multiple rows in a similar way. I know it's possible to delete multiple …

Read More