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