Databases
StackOverflow2013
Postsdbo
POUnique key vs Candidate key / Superkey (MySQL)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUnique key vs Candidate key / Superkey (MySQL)
    primarykey
    data
    text
    <p>The following SQL code creates two tables, (A) <code>__tableA__</code>, (B) <code>__tableB__</code>. Both tables contain exactly 2 unique keys, named <code>k1</code> and <code>k2</code>.</p> <pre><code>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__; # </code></pre> <p>As shown by the <code>SHOW KEYS</code> command, the sets of attributes (columns) of keys <code>k1</code> and <code>k2</code> are</p> <ul> <li>A: <code>{a}</code> and <code>{a,b}</code>, respectively,</li> <li>B: <code>{a}</code> and <code>{a}</code>, respectively.</li> </ul> <p>This suggests the conclusion that, in MySQL, <b>the concept of <i>unique key</i> does NOT correspond to either of the concepts of <i>candidate key</i> / <i>superkey</i></b>: In case (A), <code>k1</code> and <code>k2</code> cannot be candidate keys because <code>{a} &sub; {a,b}</code> (thus the set of attributes of <code>k2</code> is not minimal). In case (B), <code>k1</code> and <code>k2</code> cannot be superkeys because they are different keys with identical sets of attributes.</p> <p>My <b>question</b>: Is this conclusion correct? If so, which authoritative source does state it clearly?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USpaon
    1. USpaon
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUnique key vs Candidate key / Superkey (MySQL)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUnique key vs Candidate key / Superkey (MySQL)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COTo 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?
      singulars
      1. POUnique key vs Candidate key / Superkey (MySQL)
      1. USSebas
    2. COCandidate Key/Superkey are data-modeling terms that are rarelly related to RDBMS implementation. What do you mean by Superkey?
      singulars
      1. POUnique key vs Candidate key / Superkey (MySQL)
      1. USCarlos Gavidia
    3. COMaybe this question is more apropriate for dba.stackexchange.com
      singulars
      1. POUnique key vs Candidate key / Superkey (MySQL)
      1. USMaxim Krizhanovsky
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload