Choosing identifiers: Which characteristic best defines a good identifier (primary key or candidate key) for an entity in a database design?

Introduction / Context:
Identifiers (keys) uniquely distinguish rows in a table. A well-chosen key simplifies joins, ensures integrity, and stabilizes references across the schema. Key selection is a crucial step in logical and physical design.

Given Data / Assumptions:

• The identifier should work for all current and future rows.
• It must support referential integrity when used as a target of foreign keys.
• Stability and uniqueness are primary concerns.

Concept / Approach:
A good identifier is globally unique within the table and stable over time. Surrogate keys (for example, sequences, UUIDs) are common because they do not change with business rules. Natural keys are acceptable if they are truly stable and unique. Allowing NULL or volatility undermines the key's purpose.

Step-by-Step Solution:

Verification / Alternative check:
Referential integrity and indexing strategies depend on consistent, unique keys; widely accepted best practices prioritize uniqueness and stability.

Why Other Options Are Wrong:

• Change over time: breaks references and causes cascading updates.
• NULL allowed: a key cannot be NULL by definition.
• Intelligent codes: risk of changing business semantics; often brittle.
• Long descriptive keys: harm performance and storage without guaranteeing uniqueness.

Common Pitfalls:
Choosing business-meaningful identifiers that later require changes, causing widespread ripple effects and data migration challenges.

Final Answer:
It is unique for each entity instance.