How to create a unique index with only certain values required to be unique?

Question

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)

Answer 1

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 a UNIQUE constraint:

CREATE TABLE codes 
 ( product_id INT NOT NULL,
 code INT NOT NULL,
 ucode BIT AS (CASE WHEN code = 11 THEN b'1' ELSE NULL END) 
 VIRTUAL,
 -- PERSISTENT, -- for persistent storage of the value in MariaDB
 -- STORED, -- for persistent storage of the value in MySQL 
 CONSTRAINT code_11_product_id_unique
 UNIQUE (ucode, product_id)
 ) ;

Test at dbfiddle.uk:

insert into codes
 (product_id, code)
values 
 (1, 11),
 (1, 222),
 (1, 222),
 (1, 333),
 (2, 11),
 (2, 222),
 (2, 222);

✓

select * from codes;

product_id | code | ucode
---------: | ---: | :----
 1 | 11 | 1 
 1 | 222 | null 
 1 | 222 | null 
 1 | 333 | null 
 2 | 11 | 1 
 2 | 222 | null 
 2 | 222 | null 

insert into codes -- should fail
 (product_id, code)
values 
 (2, 11) ;

Duplicate entry '\x01-2' for key 'code_11_product_id_unique'

select * from codes;

product_id | code | ucode
---------: | ---: | :----
 1 | 11 | 1 
 1 | 222 | null 
 1 | 222 | null 
 1 | 333 | null 
 2 | 11 | 1 
 2 | 222 | null 
 2 | 222 | null 

• Nicolas Gramlich: Oh my god, this should be the accepted answer for several dozen of "compound unique keys with NULL values" searches. Thank you so much!