# compouned (composite) unique keys

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?

example:

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

• What does nested mean in that context? 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 Jul 25, 2017 at 10:06

• Every table should have a `PRIMARY KEY`. In MySQL this implies two things: a `UNIQUEness` constraint, and an index.
• 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.)