In relational database design, what does a PRIMARY KEY constraint guarantee when defined on a column or a set of columns in Oracle?

Introduction / Context:
This question examines understanding of the PRIMARY KEY constraint, one of the most important concepts in relational database design. A primary key uniquely identifies each row in a table and is central to normalization, indexing, and referential integrity. Oracle enforces specific rules for columns that are part of a PRIMARY KEY constraint.

Given Data / Assumptions:

- The question is about the behavior of a PRIMARY KEY constraint in Oracle or similar relational systems.
- Primary keys are defined on one or more columns in a table.
- The goal is to choose the option that correctly describes uniqueness and nullability.

Concept / Approach:
A PRIMARY KEY constraint enforces two conditions: the key values must be unique across all rows, and none of the key columns may contain null values. Oracle implements this by combining a NOT NULL constraint and a UNIQUE constraint on the key columns. The primary key can be a single column or a composite of multiple columns, but together they must uniquely identify each row.

Step-by-Step Solution:
Step 1: Recall that the primary key is the main identifier for each row and must be both unique and present. Step 2: Evaluate option A, which states that the key values are unique and not null, uniquely identifying each row, which matches the definition. Step 3: Option B claims only non null values are enforced, but primary keys also require uniqueness, so this is incomplete. Step 4: Option C claims only uniqueness is enforced, but primary keys also disallow nulls, so this is also incomplete. Step 5: Option D suggests that a primary key prevents updates or deletes, which is not true; it only controls the values allowed in the key columns.

Verification / Alternative check:
You can verify by creating a table with a primary key and attempting to insert two rows with the same key value or a row with a null key value. Oracle will raise constraint violation errors in both cases. However, updating non key columns or even changing the key value to a new unique non null value will succeed, showing that the constraint focuses on uniqueness and non null conditions.

Why Other Options Are Wrong:
Option B ignores the uniqueness requirement of primary keys and thus allows duplicates, which contradicts relational theory. Option C allows nulls in the primary key, which violates the requirement that every row must have a valid identifier. Option D misinterprets the effect of primary keys and confuses them with access control or table locking mechanisms.

Common Pitfalls:
A typical mistake is to define a primary key on a column that may legitimately be null or duplicated, leading to errors later in the application. Another pitfall is to forget to index a natural key and instead create a surrogate key, without carefully managing the relationships. Oracle automatically creates an index for the primary key, which helps performance, but designers must still pick an appropriate key that uniquely represents each row.

Final Answer:
A PRIMARY KEY constraint guarantees that key values are both unique and not null, so that each row in the table is uniquely identifiable.