2

The following SQL code creates two tables, (A) __tableA__, (B) __tableB__. Both tables contain exactly 2 unique keys, named k1 and k2.

SHOW VARIABLES WHERE Variable_name = 'version';    # 5.0.51b

CREATE TABLE __tableA__ (`a` INT, `b` INT,
  UNIQUE KEY `k1`(`a`),
  UNIQUE KEY `k2`(`a`,`b`)
);                                       # Key_name      Column_name
                                         # -------------------------
SHOW KEYS FROM __tableA__;               # k1            a
                                         # k2            a
                                         # k2            b
CREATE TABLE __tableB__ (`a` INT,
  UNIQUE KEY `k1`(`a`),
  UNIQUE KEY `k2`(`a`)
);                                       # Key_name      Column_name
                                         # -------------------------
SHOW KEYS FROM __tableB__;               # k1            a
                                         # k2            a

DROP TABLE __tableA__;     # cleanup
DROP TABLE __tableB__;     #

As shown by the SHOW KEYS command, the sets of attributes (columns) of keys k1 and k2 are

  • A: {a} and {a,b}, respectively,
  • B: {a} and {a}, respectively.

This suggests the conclusion that, in MySQL, the concept of unique key does NOT correspond to either of the concepts of candidate key / superkey: In case (A), k1 and k2 cannot be candidate keys because {a} ⊂ {a,b} (thus the set of attributes of k2 is not minimal). In case (B), k1 and k2 cannot be superkeys because they are different keys with identical sets of attributes.

My question: Is this conclusion correct? If so, which authoritative source does state it clearly?

paon
  • 378
  • 3
  • 12
  • To be honest I never heard about these 2 concepts, so in the first place thank you for making me improve. Then, after reading a little bit about it, my question is the following: what is, for you, the reason that a unique index is not a superkey? – Sebas May 16 '12 at 20:21
  • Candidate Key/Superkey are data-modeling terms that are rarelly related to RDBMS implementation. What do you mean by Superkey? – Carlos Gavidia-Calderon May 16 '12 at 20:46
  • 1
    Maybe this question is more apropriate for dba.stackexchange.com – Maxim Krizhanovsky May 16 '12 at 20:59
  • related: http://stackoverflow.com/questions/3087654/superkey-vs-candidate-key – Songo May 17 '12 at 11:27
  • @Songo: _Superkey vs Candidate key_ is no problem, it is well documented. – paon May 17 '12 at 11:39
  • @Darhazer: There seem to be more related questions on [stackoverflow.com](http://stackoverflow.com/search?q=unique%20key%20candidate%20key%20superkey) than on [dba.stackexchange.com](http://dba.stackexchange.com/search?q=unique%20key%20candidate%20key%20superkey). – paon May 17 '12 at 11:49
  • You're asking something weird ;P `UNIQUE KEY `k2`(`a`,`b`)` is a candidate key because you can't have two records with the same `a`, `b` pair. Same in case (b). Also, it's not clear why you say they "aren't superkeys because they are different keys". They __are__ different keys, you can't consider them as one superkey... – meze May 17 '12 at 12:06
  • @meze: By definition, `k2` cannot be a candidate key because its set of attributes is not minimal - it contains the set of attributes of `k1`. In case (B), it follows from the definition of a superkey that a table with exactly one attribute (column) `a` always contains exactly one superkey - namely the singleton set `{a}`. Thus, it cannot contain 2 distinct superkeys. – paon May 17 '12 at 12:40
  • @paon Still don't get. How can you know it's not minimal if you don't what data you store. If the k2 has (favorite_color, SSN) then it's not a candidate key. If it has (country, zip) then it is a candidate key. That's how i understand it. – meze May 17 '12 at 12:44
  • @meze: By definition, the minimality is taken w.r.t. sets of attributes. Whether a set of attributes forms a candidate key or not cannot be, in general, derived from the (current) set of table rows. A candidate key is something that has to be put as a constraint for possible table states. – paon May 17 '12 at 12:59

1 Answers1

1

You are right. In SQL, the set of columns defined in a uniqueness constraint is not necessarily a key (minimal superkey) and certainly isn't if some proper subset of those columns also has a uniqueness constraint on it.

Unlike standard SQL a peculiarity of MySQL syntax is that it uses "KEY" as a synonym for "INDEX", which seems to suggest that the authors of MySQL either don't know or don't care what a key is!

nvogel
  • 24,981
  • 1
  • 44
  • 82