In relational database design, what is the main difference between a candidate key and a primary key?

Introduction / Context:
In relational database theory, candidate keys and primary keys are closely related concepts that form the foundation of good table design. Both involve uniqueness and minimality, but they play slightly different roles in a schema. Understanding the distinction helps database designers choose appropriate keys and define clear constraints that enforce data integrity across tables.

Given Data / Assumptions:

• We are working with a relational database model.
• Tables have multiple columns representing attributes of entities.
• Multiple attribute sets may uniquely identify each row.
• The question focuses on the difference between candidate keys and the primary key.
• We assume standard definitions used in database textbooks and exams.

Concept / Approach:
A candidate key is any minimal set of attributes that can uniquely identify a row in a table. Minimal means that if you remove any attribute from the set, it no longer uniquely identifies rows. There can be several candidate keys in a table, for example a numeric identifier and a unique email address. The primary key is the specific candidate key that the database designer chooses to represent the main identifier for the table. The primary key is usually enforced by a primary key constraint and is commonly referenced by foreign keys from other tables.

Step-by-Step Solution:
Step 1: Recall that candidate keys are sets of attributes with the properties of uniqueness and minimality. Step 2: Recognise that there can be multiple candidate keys in a single table, such as an employee number and a tax identification number. Step 3: Understand that the primary key is a single candidate key chosen by the designer to act as the main identifier for the table, often enforced by a primary key constraint. Step 4: Compare this understanding with the answer choices and look for the option that states that a candidate key is any minimal unique set, while the primary key is the chosen candidate key. Step 5: Select the option that clearly expresses this relationship without introducing incorrect ideas about duplicates or null values.

Verification / Alternative check:
Standard database textbooks and certification manuals explain that every primary key is a candidate key but not every candidate key becomes the primary key. This confirms that the relationship is one of subset selection: the primary key is chosen from the set of candidate keys. This cross check reinforces that the key difference lies in selection and usage, not in uniqueness or minimality alone.

Why Other Options Are Wrong:
Option B is incorrect because both candidate keys and primary keys must be unique; primary keys do not allow duplicates. Option C is wrong because neither candidate keys nor primary keys are required to be composite; both can be single column or multi column. Option D misstates null handling; in practice both primary keys and candidate keys are normally defined to disallow null values. Option E is incorrect because keys are defined in the database schema; application code may reference them but does not define them at the conceptual level.

Common Pitfalls:
Students often believe that only one candidate key exists in a table, which is inaccurate. Others think that candidate keys and primary keys differ in uniqueness, when in reality they share that property. Another pitfall is assuming that composite keys cannot be primary keys, which is not true. Remember that the primary key is simply the chosen candidate key used as the main identifier.

Final Answer:
The correct distinction is that a candidate key is any minimal set of attributes that can uniquely identify a row, whereas the primary key is the single candidate key chosen to uniquely identify rows in the table..