Oracle sequences and key validity: “Sequences guarantee valid surrogate key values.” Evaluate whether this statement is correct in real Oracle deployments.

Introduction / Context: Oracle sequences generate numeric values, often used to populate surrogate key columns. However, correctness for keys includes uniqueness and referential integrity. This question examines whether using a sequence alone “guarantees valid surrogate key values.”

Given Data / Assumptions:

• A surrogate key must be unique and present (not NULL) for each row.
• Oracle sequences can generate values that are monotonically increasing but may have gaps (for example, due to rollbacks, caching, instance restarts).
• Uniqueness is enforced by constraints, not by the sequence itself.

Concept / Approach: A sequence is just a number generator. It does not check table contents and cannot prevent duplicates if two different sequences or manual inserts generate conflicting values. The validity of a surrogate key is guaranteed by defining a PRIMARY KEY or UNIQUE constraint on the column that receives sequence values. Identity columns (12c+) pair generation with a constraint-friendly design, but uniqueness still comes from constraints. Therefore, the presence of a sequence alone does not guarantee “valid” keys; it merely supplies candidate values.

Step-by-Step Solution:

Verification / Alternative check: Attempt to insert a manual duplicate value; the constraint blocks it. Restart the instance—observe sequence gaps but no impact on uniqueness enforcement.

Why Other Options Are Wrong:

• NOCACHE or identity flavors do not change the fundamental separation of generation from enforcement.
• Deferrable constraints change timing of checks, not the guarantee mechanism.

Common Pitfalls: Assuming sequences enforce uniqueness; not declaring constraints; relying on sequence order for business meaning.

Final Answer: Incorrect