Textbooks and exams name keys with three words. They describe sets of columns.
Superkey
A is a set of columns whose values are different in every row. Its values identify one row.
categories
| id | name |
|---|---|
| 1 | Plush |
| 2 | Apparel |
| 3 | Stickers |
{id, name} is a superkey: no two rows have the same pair. But name is extra: {id} alone identifies each row.
Candidate key
A is a superkey with no extra column. When you remove any one of its columns, it is not a superkey any more.
| Set | Superkey? | Candidate key? |
|---|---|---|
{id} | yes | yes |
{name} | yes: each category name is unique | yes |
{id, name} | yes | no: name is extra |
categories has two candidate keys.
Primary key
The primary key is the candidate key that the designer chooses. categories uses {id}. The other candidate key, {name}, is still unique. The next lesson makes the database enforce it.
Test a set of columns on the data
SELECT DISTINCT removes duplicate rows (Chapter 3). Compare its row count with the row count of the table:
SELECT DISTINCT dept_name FROM student;The Result tab shows 6 rows. student has 10 rows. So two or more students have the same dept_name, and {dept_name} is not a superkey.
The same count does not prove that a set is a superkey. It shows only that today's rows have no duplicates. The meaning of the data decides: can two rows ever have these values? Two students can have the same name next year, so {name} is not a key, even when today's names are all different.