In relational database design, what is the difference between a primary key and a unique key constraint?

Introduction / Context:
Understanding the difference between primary key and unique key constraints is central to relational database modeling. Both constraints enforce uniqueness, but they play slightly different roles in schema design and have different rules regarding NULL values and the number of such constraints allowed per table. Interviewers use this question to verify that candidates can design tables that uniquely identify rows and enforce additional uniqueness requirements where needed.

Given Data / Assumptions:
• The database is a standard relational system such as SQL Server, Oracle, MySQL, or PostgreSQL. • We assume that primary keys are used as main row identifiers and that unique keys enforce additional uniqueness. • The question asks for differences in behavior, especially regarding NULLs and count per table. • No numeric computation is needed; only conceptual understanding is required.

Concept / Approach:
A primary key is a special constraint whose main purpose is to uniquely identify each row in a table. It automatically enforces uniqueness and disallows NULL values. Most database systems allow only one primary key per table, though it may consist of multiple columns. Unique key constraints also enforce uniqueness but are more flexible: a table can have multiple unique keys, and many implementations permit unique keys to contain a single NULL value (or sometimes multiple NULLs depending on the database). Foreign keys typically refer to primary keys, but they can also reference unique keys in some systems. The correct answer must highlight uniqueness, NULL handling, and the allowed number of constraints per table.

Step-by-Step Solution:
Step 1: Recall that a primary key uniquely identifies each row and therefore cannot be NULL. Step 2: Remember that a table has at most one primary key constraint, though it may consist of several columns. Step 3: Recognize that a unique key also enforces uniqueness across its column or column set. Step 4: Observe that a table can have multiple unique keys, and in many implementations they can contain NULLs. Step 5: Choose the option that clearly expresses these differences between primary key and unique key.

Verification / Alternative check:
Imagine a Customer table. The CustomerId column might be defined as the primary key, guaranteeing a non NULL unique identifier for each row. At the same time, you might create a unique key on Email to ensure no two customers share the same email address, but still allow NULL if a customer chooses not to provide one. The table now has one primary key and one unique key. This example reflects the behavior described in option a and demonstrates that primary keys and unique keys are similar but not identical in their constraints and intended usage.

Why Other Options Are Wrong:
Option b reverses the meaning of the constraints, incorrectly stating that primary keys allow duplicates and NULLs. Option c claims that primary and unique keys are exactly identical and that every table must have multiple such constraints, which is false. Option d invents a difference based on indexed versus non indexed columns, which does not reflect how these constraints are defined in real systems. Option e oversimplifies by claiming primary keys are used only for foreign key relationships and unique keys only for performance, ignoring their integrity roles.

Common Pitfalls:
A frequent mistake is assuming that any unique key can serve as a primary key without considering NULL behavior and semantic meaning. Another pitfall is forgetting that foreign keys may sometimes reference unique keys, not just primary keys, in databases that allow this. Developers also occasionally create multiple unique keys when one composite primary key would better reflect the model. Understanding the subtle differences between primary and unique keys leads to clearer schema design and more reliable data integrity rules.

Final Answer:
The correct choice is A primary key uniquely identifies each row and cannot contain NULL values, and there can be only one per table; a unique key also enforces uniqueness but can allow a single NULL and there may be multiple unique keys per table., because it accurately summarizes how the two constraint types behave and how they are typically used in relational schema design.