I have a list of codes being stored in a table, and some codes should be unique, others can be repeated. The codes should be unique-scoped to another field, product_id
.
Assuming the code 11
should only be allowed once per product_id
, and other codes are allowed to repeat, the table would look like:
product_id code
1 11 # Needs to be unique for product_id 1
1 222
1 222
1 333
2 11 # Needs to be unique for product_id 2
2 222
2 444
With MySQL, I took advantage of the fact that you can have multiple NULL values in a unique index, so by adding a "tie breaker"(?) field, ucode
, I was able to hack together a solution:
product_id code ucode
1 11 1 # Code 11 needed to be unique, so 1 for ucode
1 222 NULL # Code 222 can be repeated, so NULL for ucode
1 222 NULL
1 333 NULL
2 11 1
2 222 NULL
2 444 NULL
Unique index was then made on [product_id, code, ucode].
For unique codes, the ucode
field was set to 1
, otherwise NULL
.
This works, but feels very kludgy. Is there a better way I can do this?
(I am using MySQL)
Best Answer
In versions MySQL 5.7 and MariaDB 5.2+ you can use a (generated)
VIRTUAL
column to accomplish this. You defineucode
as a virtual column and then add aUNIQUE
constraint:Test at dbfiddle.uk: