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,
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
This works, but feels very kludgy. Is there a better way I can do this?
(I am using MySQL)