35

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

Sam
  • 6,414
  • 11
  • 46
  • 61

6 Answers6

21

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.

Paul Lysak
  • 1,284
  • 1
  • 14
  • 18
  • I'd recommend the NULL solution - it's easy, NULL sort of equals FALSE (just don't say that to a fundamentalist SQLer), and best of all it makes your database do all the work for you. – Xavier Holt Feb 10 '11 at 23:16
  • 1
    In the NULL solution, how does the database distinguish the uniqueness of 2 rows in the situation when they are identical? Does mysql avoid constraint check at that time? – decached Jun 19 '14 at 11:04
  • @A.K. It is not opinionated in case of Null i.e NULL is not a value for MySQL. As Active='YES' (as opposed to Active=NULL) is a value, it is able to establish uniqueness. – veritas Aug 26 '22 at 11:03
5

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.

Lewis Z
  • 498
  • 7
  • 16
3

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.

Ibo
  • 4,081
  • 6
  • 45
  • 65
  • Thanks for the example this was a game changer for us. To help others research this, these indexes are called Filtered Indexes. Here's the official link from Microsoft: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes – Airn5475 Oct 07 '22 at 12:36
0

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.

SWeko
  • 30,434
  • 10
  • 71
  • 106
  • I got a similar suggestion on IRC; create a active table and an inactive table. But they way you put it makes more sense; have an historical table and a active table. However, this table has a lot of fields and it would seem like duplicating those fields into two tables would be the wrong thing to do. How would you suggest I avoid this type of redundancy? – Sam Feb 10 '11 at 10:23
  • A little denormalization can go a long way, so don't worry about the duplicate definitions. Of course, you'll have to make any changes to the original table to the historical table too, so that's some overhead, but, IMHO it's well worth the clean data. – SWeko Feb 10 '11 at 10:33
  • Hmm, what if I were to separate only the data that needs unique indexing? The tables: mydata, mydata_active, mydata_inactive. This way mydata contains the Primary Key and data columns. And, mydata_active and mydata_inactive will only contain the columns that need to be uniquely indexed and a Foreign Key which references mydata. This is one other way, but it requires an extra (third) table. – Sam Feb 10 '11 at 10:43
  • That would work too, but it might be too much overhead, as any query that touches those tables will have to have an extra join. If the historic data is seldom used, you should basically stash them in a corner, and forget about them. – SWeko Feb 10 '11 at 11:17
  • Quite late to the party, but adding to the answer of @SWeko : if you're using Microsoft SQL Server, also see Filtered Indexes as suggested here: https://stackoverflow.com/a/5149263/3766034 – Jirajha Oct 06 '20 at 13:05
0

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';
Dharman
  • 30,962
  • 25
  • 85
  • 135
Jorge Serrano
  • 1
-2

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

Ian Wood
  • 6,515
  • 5
  • 34
  • 73