A candidate key is the smallest set of one or more table attributes that uniquely identifies a row. “Smallest” matters: if any attribute can be removed and the remaining attributes still identify every row, the original combination is not a candidate key. Candidate keys give a database designer the options from which one primary key is selected. section 4.3 section 6.2
A candidate key is a minimal superkey. A superkey can uniquely identify a row but may include extra columns; a candidate key has no unnecessary columns. One candidate key is chosen as the table’s primary key, which uniquely identifies rows, helps prevent duplicate data, and supports relationships between tables. section 4.3 section 6.2
To check a possible candidate key, work through these questions:
- Can its value, or combination of values, identify one row and no other row? If not, it is not a superkey. section 6.2
- If it identifies a row, can any included attribute be removed while keeping that uniqueness? If yes, it has unnecessary columns and is not a candidate key. section 4.3 section 6.2
- Compare the available candidate keys and select the best fit as the primary key; if none is suitable, create a new attribute for that role. section 4.3
Testing keys in a user table
Suppose username, email, and phone_number must each be unique for users. Each attribute can identify a user row by itself, so each is a candidate key. The pair username and first_name can also identify a row, but it is not a candidate key: username alone already does the job, making first_name removable. section 4.3
Use the diagram to distinguish the nested categories: superkeys may contain extras, candidate keys are minimal, and one candidate key becomes the primary key. section 6.2
Candidate keys also matter during normalization. They clarify which columns identify a row before selecting a primary key and evaluating table design against normal forms such as BCNF. section 6.2
