Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

UNIQUE Constraint, only when a field contains a specific value

Tags:

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

like image 686
Sam Avatar asked Feb 10 '11 10:02

Sam


People also ask

Can you create unique constraint on a column that has one NULL value?

You can insert NULL values into columns with the UNIQUE constraint because NULL is the absence of a value, so it is never equal to other NULL values and not considered a duplicate value. This means that it's possible to insert rows that appear to be duplicates if one of the values is NULL .

Which SQL constraint do we use to set some value to a field whose value has not been added explicitly?

Which SQL constraint do we use to set some value to a field whose value has not been added explicitly? Explanation: The DEFAULT constraint is used to set a default value for a column which comes into use when a value for a field in that column is not set.

Which constraint used for making unique a column value?

The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint.

What is the difference between unique key and unique constraints?

A unique index ensures that the values in the index key columns are unique. A unique constraint also guarantees that no duplicate values can be inserted into the column(s) on which the constraint is created. When a unique constraint is created a corresponding unique index is automatically created on the column(s).


1 Answers

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.

like image 129
Paul Lysak Avatar answered Oct 27 '22 13:10

Paul Lysak