Is (STREET_ADDRESS, CITY, STATE, ZIP) an ideal choice for a primary key, or is such a multi-attribute natural key typically long, mutable, and therefore not recommended?

Introduction / Context:
Primary keys should be compact and stable. Address components are neither: they are verbose, prone to change (street renames, ZIP updates, unit additions), and are hard to standardize across locales. This question evaluates whether a composite address makes an ideal primary key.

Given Data / Assumptions:

• Address attributes: STREET_ADDRESS, CITY, STATE, ZIP.
• Addresses can change and be formatted inconsistently.
• The PK will be used in foreign keys and indexes.

Concept / Approach:
An ideal PK is short, stable, and unique. Address fields are lengthy, error-prone (abbreviations, punctuation, international variability), and may change over time. Using them as a composite PK inflates indexes and slows joins. The recommended approach is a surrogate key (e.g., address_id) with uniqueness and validation enforced on normalized address attributes via separate constraints and cleansing processes.

Step-by-Step Solution:

Verification / Alternative check:
Compare index size and join performance using a composite address PK versus a surrogate integer PK; observe the overhead of the composite approach.

Why Other Options Are Wrong:

• “Correct” contradicts best practices.
• “Ideal only for warehouses” still suffers from mutability and standardization problems.
• “Ideal with a surrogate” is contradictory—if you have a surrogate PK, the address should not be the PK.
• Country variations do not make addresses more suitable as keys.

Common Pitfalls:
Using meaningful but unstable attributes as keys; failing to cleanse and standardize addresses; ignoring apartment/unit numbers leading to false duplicates.

Final Answer:
Incorrect