CODEWITHSUNDEEP
mysqlunique
Oskar Persson
Oskar Persson

Reputation: 6753

MySQL: Unique if column = x

Let's say a table has 3 columns can I then make it so that the mix between column 1 & 2 is unique if (and only if) column 3 is equal to x?

That would make this work:

-----------------------------------------------------
|     A     |        B       |           C           |
-----------------------------------------------------
|     1     |        2       |           x           |
-----------------------------------------------------
|     2     |        1       |           x           |
-----------------------------------------------------
|     0     |        0       |           y           |
-----------------------------------------------------
|     0     |        0       |           y           |
-----------------------------------------------------

And this wouldn't:

|     A     |        B       |           C           |
-----------------------------------------------------
|     2     |        2       |           x           |
-----------------------------------------------------
|     2     |        2       |           x           |
-----------------------------------------------------
|     0     |        0       |           y           |
-----------------------------------------------------
|     0     |        0       |           y           |
-----------------------------------------------------

Upvotes: 2

Views: 134

Answers (2)

eggyal
eggyal

Reputation: 125865

You can create a trigger:

DELIMITER ;;

CREATE TRIGGER special_unique_test BEFORE INSERT ON my_table FOR EACH ROW
IF NEW.C = 'x' AND EXISTS (
  SELECT * FROM my_table WHERE A = NEW.A AND B = NEW.B AND C = 'x'
) THEN
  CALL raise_error;
END IF;;

DELIMITER ;

I'd recommend maintaining an index on (A, B, C) to keep this reasonably performant.

However, this won't yield the same benefits with INSERT ... ON DUPLICATE KEY UPDATE, REPLACE, etc. as a genuine UNIQUE key.

Upvotes: 2

user359040
user359040

Reputation:

Try:

select distinct a, b, c
from mytable
where c = 'x'
union all
select a, b, c
from mytable
where c <> 'x'

Upvotes: 0

Related Questions