Database keys and indexing context In database design, which statement best describes a secondary key?

Introduction / Context:
Keys define how rows are identified and accessed. Beyond the primary key, designers often add additional access paths to speed up lookups or support alternate query predicates. This question checks whether you can distinguish a secondary key from a primary or surrogate key and from storage/physical concerns.

Given Data / Assumptions:

• A table already has a primary key that uniquely identifies rows.
• Applications run queries filtering on non-PK columns (e.g., email, status, date).
• Secondary keys may or may not be unique; they frequently are not.

Concept / Approach:

A secondary key is any non-primary-key attribute (or attribute set) that is indexed to support alternate access to rows. It does not have to be unique. Its purpose is performance and flexible retrieval, not entity identity. Think of searching customers by last_name or orders by order_date: these are classic secondary keys.

Step-by-Step Solution:

Verification / Alternative check:

Database texts distinguish: primary key (uniqueness + identity), candidate/surrogate keys (alternative unique identifiers), and secondary keys (additional indexed attributes, not necessarily unique).

Why Other Options Are Wrong:

• Primary key: by definition unique and not “secondary.”
• “Useful only for denormalization”: secondary keys aid queries regardless of normalization.
• Tablespace choice: a physical storage concern, not key semantics.
• “Generated by file system”: keys are logical DB constructs, not filesystem artifacts.

Common Pitfalls:

• Assuming every indexed column must be unique—many secondary keys are not.
• Confusing candidate keys (unique) with secondary keys (may be nonunique).

Final Answer:

Nonunique key used for data retrieval and alternative access paths.