Oracle design feature check: “Oracle directly supports the definition of surrogate keys.” Decide whether this statement is accurate, considering identity columns and sequences.
-
AIncorrect
-
BCorrect
-
COnly when using GUID data types
-
DOnly via triggers, not natively
-
EOnly in non-CDB databases
Answer
Correct Answer: Incorrect
Explanation
Introduction / Context: Surrogate keys are system-generated identifiers (for example, numeric IDs) that uniquely identify rows without business meaning. Oracle provides mechanisms to generate such values, but this question asks whether Oracle “directly supports the definition of surrogate keys” as a special modeling construct.
Given Data / Assumptions:
- Oracle offers sequences in all modern releases and identity columns beginning with Oracle 12c.
- Primary keys are defined with constraints; “surrogate” is a modeling term, not an Oracle keyword.
- Surrogate key behavior involves immutability and uniqueness enforced by PRIMARY KEY or UNIQUE constraints.
Concept / Approach: Oracle does not have a special “SURROGATE KEY” keyword or constraint type. Instead, you implement surrogate keys using standard features: define a PRIMARY KEY column and generate its values using a sequence (optionally with a trigger) or by declaring the column as GENERATED {ALWAYS|BY DEFAULT} AS IDENTITY (12c+). These are general-purpose generators; they are not a distinct “surrogate key” construct. Therefore, the statement that Oracle “directly supports the definition of surrogate keys” (as a unique, named, built-in concept) is not accurate; Oracle supports generation, not a special key type.
Step-by-Step Solution:
Create table T(id number primary key, ...).Option A: CREATE SEQUENCE t_seq; insert using t_seq.nextval.Option B (12c+): id NUMBER GENERATED ALWAYS AS IDENTITY.Enforce uniqueness via the PRIMARY KEY constraint.Verification / Alternative check: Oracle documentation lists identity columns and sequences, not a “surrogate key” feature. The modeling term remains conceptual.
Why Other Options Are Wrong:
- Calling it “Correct” confuses value generation with a dedicated surrogate-key construct.
- GUID types and CDB/non-CDB architecture are unrelated to the concept.
- “Only via triggers” is incomplete; 12c identity removes the trigger requirement.
Common Pitfalls: Equating sequences with primary keys; omitting constraints that guarantee uniqueness; assuming “surrogate” is a SQL keyword.
Final Answer: Incorrect