Search code examples
mysqlschemaunique-constraintunique-keyunique-index

Can I add a compound unique key if one of the fields is already unique

In MySQL, does following statement make sense?

CREATE TABLE `sku_classification` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sku` int(10) unsigned NOT NULL,
  `business_classification_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_SKU_BUSINESS_CLASSIFICATION` (`sku`,`business_classification_id`),
  UNIQUE KEY `sku` (`sku`)
)

Is it an unnecessary overkill to add a unique key on a combination of fields (sku,business_classification_id), one of which (sku) already has unique index on it? Or is it not, and there is indeed some reason for such duplicate unique index?

Solution

  • Yes, you can. But it does not make sense. But, let's analyze what is going on.

    An INDEX (UNIQUE or not) is a BTree that facilitates lookups in the table.

    A UNIQUE index is both an index and a "constraint" saying that there shall not be any duplicates.

    You have already said UNIQUE(sku). This provides both an index an a uniqueness constraint.

    Adding UNIQUE(sku, x) in that order:

    • Does not provide any additional uniqueness constraint,
    • Does not provide any additional indexing capability, except...
    • Does provide a "covering" index that could be useful if the only two columns mentioned in a SELECT were sku and x. Even so, you may as make it an INDEX not a UNIQUE, because...
    • Every INSERT must do some extra effort to prevent "duplicate key". (OK, the INSERT code is not smart enough to see that you have UNIQUE(sku).)

    If that is your complete table, there is no good reason to have the id AUTO_INCREMENT; you may as well promote sku to be the PRIMARY KEY. (A PK is a UNIQUE KEY.)

    Furthermore... If, on the other hand, you were suggesting UNIQUE(x, sku), then there is one slight difference. This provides you a way to efficiently lookup by x -- a range of x, or x=constant AND sku BETWEEN ..., or certain other thing that are not provided by (sku, x). Order matters in an index. But, again, it may as well be INDEX(x, sku), not UNIQUE.

    So, the optimal set of indexes for the table as presented is not 3 indexes, but 1:

    PRIMARY KEY(sku)

    One more note: With InnoDB, the PK is "clustered" in BTree with the data. That is, looking up by the PK is very efficient. When you need to go through a "secondary index", there are two steps: first drill down the secondary index's BTree to find the PK, then drill down the PK's BTree.