A surrogate key is a new, artificial value used as a table’s primary key. It identifies each row uniquely without depending on business data such as a review’s text, a customer’s email address, or a phone number. section 4.3 section 8.5
Use one when no reliable candidate key is available, or when natural attributes can change. An auto-incrementing numeric ID, such as review_id or customer_id, gives every record its own stable identifier. Numeric keys are smaller, searchable in memory, and more standardized than a key made from several attributes. section 4.3 section 8.5
A surrogate key solves row identification, but not every data-quality problem:
- It uniquely identifies a row so it can be updated or deleted correctly. section 4.3
- It does not stop two identical rows from being inserted. section 4.3 section 8.5
- Add unique constraints to natural attributes when duplicate values must be prevented, such as customer email_address and phone_number. section 8.5
- Allow duplicates when they represent acceptable separate records, such as transaction records or payment methods held by different users. section 4.3
Customer identity with changeable contact details
A customer may update an email address or phone number, so neither is a dependable primary key. Define customer_id as an INT primary key that increments automatically, then place separate unique constraints on email_address and phone_number to prevent duplicate registrations using the same details. section 8.5
