StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A super key is any combination of columns that uniquely identifies a row in a table. A candidate key is a super key which cannot have any columns removed from it without losing the unique identification property. This property is sometimes known as minimality or (better) irreducibility.</p> <p>A super key ≠ a primary key in general. The primary key is simply a candidate key chosen to be the main key. However, in dependency theory, candidate keys are important and the primary key is not more important than any of the other candidate keys. Non-primary candidate keys are also known as alternative keys. </p> <p>Consider this table of Elements:</p> <pre><code>CREATE TABLE elements ( atomic_number INTEGER NOT NULL PRIMARY KEY CHECK (atomic_number > 0 AND atomic_number < 120), symbol CHAR(3) NOT NULL UNIQUE, name CHAR(20) NOT NULL UNIQUE, atomic_weight DECIMAL(8,4) NOT NULL, period SMALLINT NOT NULL CHECK (period BETWEEN 1 AND 7), group CHAR(2) NOT NULL -- 'L' for Lanthanoids, 'A' for Actinoids CHECK (group IN ('1', '2', 'L', 'A', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18')), stable CHAR(1) DEFAULT 'Y' NOT NULL CHECK (stable IN ('Y', 'N')) ); </code></pre> <p>It has three unique identifiers - atomic number, element name, and symbol. Each of these, therefore, is a candidate key. Further, unless you are dealing with a table that can only ever hold one row of data (in which case the empty set (of columns) is a candidate key), you cannot have a smaller-than-one-column candidate key, so the candidate keys are irreducible.</p> <p>Consider a key made up of { atomic number, element name, symbol }. If you supply a consistent set of values for these three fields (say { 6, Carbon, C }), then you uniquely identify the entry for an element - Carbon. However, this is very much a super key that is not a candidate key because it is not irreducible; you can eliminate any two of the three fields without losing the unique identification property.</p> <p>As another example, consider a key made up of { atomic number, period, group }. Again, this is a unique identifier for a row; { 6, 2, 14 } identifies Carbon (again). If it were not for the Lanthanoids and Actinoids, then the combination of { period, group } would be unique, but because of them, it is not. However, as before, atomic number on its own is sufficient to uniquely identify an element, so this is a super key and not a candidate key.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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