I was looking in this page but also on stackoverflow and google because I though this may be a quite common question but could not find anything related... I may not be using the right terminology here.

The question is quite straight forward.

Is it a good practice having nested unique keys (or mix of primary and unique keys) inside another unique key?


I have a table with columns A, B, C, D, E, F and G Primary or UNIQUE Key would be (A, B) and UNIQUE KEYS (A, B, C) and (A, B, D).

I would like to know whether or not this is kind of things are a good practice in general but also for mysql.

Thank you very much

  • 2
    What does nested mean in that context?
    – eckes
    May 14, 2017 at 1:18
  • @eckes By a key being nested in another they mean the former's column set is a proper/smaller subset of the latter's. Here (A, B) is nested in each of the other two. PS When you have a FK to a proper superset of a PK/UNIQUE you have to explicitly declare the superkey even though every superset of a unique key is a unique key
    – philipxy
    Jul 25, 2017 at 10:06

1 Answer 1

  • Every table should have a PRIMARY KEY. In MySQL this implies two things: a UNIQUEness constraint, and an index.
  • Any kind of index can be "composite" ("compound"), that is be composed of more than one column.
  • It is rarely useful to have two UNIQUE keys in a single table. (Remember: PRIMARY counts as UNIQUE.
  • A common exception is when you are 'normalizing' a long string and 'mapping' it to a short INT.
  • If you already have a unique key (a), it is unreasonable and unnecessary to have also have UNIQUE(a,b) or UNIQUE(b,a). On the other hand, it may be useful to have a non-unique INDEX(a,b) and/or INDEX(b,a). Think how the uniqueness of (a) implies the uniqueness of the other two.
  • With INDEX(a,b), there is virtually no reason to also have INDEX(a) (Note: I am not talking about UNIQUE in this bullet item.)

If I have not addressed your specific question (perhaps indirectly), please rephrase your question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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