2

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)

4

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 
  • Oh my god, this should be the accepted answer for several dozen of "compound unique keys with NULL values" searches. Thank you so much! – Nicolas Gramlich Jun 1 at 0:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.