UNIQUE Constraint, only when a field contains a specific value

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开发者_如何学C 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. :\

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.

I'm trying to create a UNIQUE INDEX constraint for two columns, but only when another column contains the value 1

You can set the value of "another column" to a unique value that does not equal to 1. for example the id of a record.

Then the unique index constraint could be applied to all three columns including the "another column". Let's call the "another column" columnX. Set the value of columnX to 1 if you want to apply the unique constraint to a record. Set the value of columnX to a unique value if you don't want to apply the unique constraint.

Then no extra work/triggers needed. The unique index to all three columns could solve your problem.

I am not sure about MySQL syntax, but it should have pretty much the same thing that SQL Server has:

CREATE UNIQUE INDEX [UNQ_Column1Column2OnActive_MyTable]
  ON dbo.[MyTable]([column1,column2)
  WHERE   ([active] = 1);

This index will make sure if active=1 then column1 and column2 combination is unique across the table.

In SQL Server this could be accomplished with check constraints, however I do not know if MySQL supports anything similar.

What will work on any database, is that you can split the table in two. If the records where active =0 are just history records, and will never become active again, you could just move them to another table, and set a simple unique constraint on the original table.

I am not sure I understand you 100% but lets say you have a table that has a status column and you want to make sure there is only one raw with a status of 'A' (Active). You are OK with many rows with statuses of 'I' or 'Z' or anything else. Only one row is allowed with status of 'A'.

This will do the trick.

  CREATE UNIQUE INDEX [Idx_EvalHeaderOnlyOneActive]
  ON [dbo].[EvalHeader]([Hdr Status])
  WHERE [Hdr Status] = 'A';

indexes are agnostic of external influences. This kind of constraint would have to be implemented outside your database.