Primary key design guidance: is an ideal primary key short in length, numeric (or compact), and very stable (seldom changing over time)?

Introduction / Context:
Primary keys influence performance, indexing, and data integrity. Good keys simplify joins and minimize maintenance. This question asks whether “short, numeric, and stable” captures the key qualities of an ideal primary key.

Given Data / Assumptions:

• We consider general OLTP and many analytics scenarios.
• Keys are used in joins, indexes, and foreign key relationships.
• Stability avoids cascades and churn.

Concept / Approach:
Short keys reduce index size and I/O. Numeric or compact keys are faster to compare and sort than long strings. Stability prevents costly cascades and fragmentation. Surrogate integers (or UUIDs in some contexts) often satisfy these criteria, although UUIDs are larger and have ordering concerns unless using sequential variants. Therefore, the statement is correct as general guidance.

Step-by-Step Solution:

Verification / Alternative check:
Benchmark joins and index scans with long string PKs versus integer PKs; observe space and speed differences. Analyze downstream FK tables to see space savings with compact keys.

Why Other Options Are Wrong:

• “Incorrect” contradicts widely accepted design guidance.
• “Only for OLTP” is too narrow; the benefits are broad.
• “Text keys are preferred” increases size/volatility.
• “Only when no surrogate exists” is unrelated; surrogates are one way to achieve ideal traits.

Common Pitfalls:
Picking business attributes that change as PKs; using concatenated multi-column natural keys that bloat indexes; using random UUIDs without considering index locality.

Final Answer:
Correct