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

Introduction / Context:
In relational database design, candidates are often asked to explain the difference between a primary key and a unique index or unique constraint. Both concepts are related to enforcing uniqueness, but they are not identical. Understanding how they differ in purpose, rules, and usage is essential for designing robust schemas in systems such as DB2, Oracle, SQL Server, and other SQL based products.

Given Data / Assumptions:

• We are working with a relational database that supports primary key constraints and unique indexes or unique constraints.
• Every table should usually have a primary key that uniquely identifies each row.
• Additional uniqueness requirements may exist on other columns that are not the primary key.

Concept / Approach:
A primary key is a logical constraint that enforces entity integrity. It identifies each row uniquely and is typically used as the main reference in foreign keys. Most systems restrict a table to one primary key, and the columns in the primary key cannot contain null values. A unique index or unique constraint is also used to enforce uniqueness, but it is more flexible. A table can have multiple unique indexes, and depending on the database, they may allow null values and are not necessarily used as the main identifier of the row. Internally, primary keys are often backed by unique indexes, but their semantic roles differ.

Step-by-Step Solution:
Step 1: Identify the purpose of a primary key. It is defined at the table level and ensures that each row can be uniquely identified by the primary key columns. Step 2: Note that most database systems enforce a single primary key per table, and the primary key columns are defined as not null, which prevents missing identifiers. Step 3: Understand that when you create a primary key, the database usually creates a corresponding unique index to enforce the uniqueness rule physically. Step 4: Consider unique indexes or unique constraints. These enforce that no two rows share the same value combination in the indexed columns, but a table can have several such unique definitions. Step 5: Recognise that unique indexes are often used to enforce business rules such as unique email addresses or unique employee codes that are not the primary key, and some systems allow null values within unique indexes, depending on implementation.

Verification / Alternative check:
You can verify the difference by creating a sample table with a primary key and an additional unique constraint. The table will accept only one primary key declaration but can include several unique constraints. If you attempt to insert null values into a primary key column, the database will reject them, while unique indexed columns often accept a single or multiple null values depending on the vendor. System catalogs usually show primary keys separately from other unique constraints, confirming that the concepts are distinct at the schema level.

Why Other Options Are Wrong:
Option B is incorrect because physical storage order is usually defined by clustering or index organisation, not by whether a key is primary or unique. Option C is wrong because both primary keys and unique indexes can be defined on character, numeric, or composite columns; there is no such restriction. Option D is incorrect because although primary keys are typically implemented with unique indexes, they carry different semantic meaning, nullability rules, and limitations on how many can exist per table.

Common Pitfalls:
A common mistake is assuming that any unique index can replace a primary key. While uniqueness is enforced, foreign keys, tools, and design conventions rely on the explicit primary key definition. Another pitfall is forgetting that a table may need additional unique indexes to enforce business rules beyond the primary key. Designers should choose the primary key carefully and then add unique indexes where necessary to reflect real world uniqueness requirements.

Final Answer:
The main difference is that a primary key enforces entity integrity as the single, not null unique identifier for each row in a table, whereas a unique index enforces uniqueness on one or more columns and can usually allow null values and multiple such definitions per table.