Database.Middle.What are surrogate keys ?

Surrogate keys are artificial, system-generated keys used to uniquely identify rows in a database table. They are not derived from application data and have no business meaning — typically auto-increment integers or UUIDs.

---

🧠 Definition

A surrogate key is a standalone identifier created solely for the purpose of being a primary key, usually implemented as an ID column.

---

✅ Example

Table: users

id (surrogate key)	email	username
1	john@example.com	johnny
2	alice@example.com	alice88

Here, id is a surrogate key — it’s:

• Unique
• System-generated
• Has no meaning to the user

---

🆚 Surrogate Key vs Natural Key

Feature	Surrogate Key	Natural Key
Based on data?	❌ No (synthetic)	✅ Yes (e.g. email, SSN, VIN)
Stability	✅ Very stable	❌ Can change over time
Performance	✅ Fast joins/indexes	❌ Slower if long or complex
Human meaning	❌ None	✅ Usually has semantic meaning
Example	user_id SERIAL	email, SSN, username

---

🧰 Why Use Surrogate Keys?

• ✅ Immutable: won’t change even if business data does
• ✅ Uniform type: predictable size (e.g., INT, UUID)
• ✅ Simpler relationships: easier to define foreign keys
• ✅ Avoid composite keys: simplifies joins and indexing

---

⚠️ When Not to Use

• When the natural key is compact and immutable (e.g., ISO country codes, VINs, UUIDs)
• When you want to prevent duplicates naturally with meaningful columns

---

🧾 Example in SQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,      -- surrogate key
  email VARCHAR(100) UNIQUE,  -- natural candidate key
  name TEXT
);

Or with UUID:

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id INT,
  total_amount NUMERIC
);

🧠 Summary

Term	Meaning
Surrogate Key	Artificial primary key with no business meaning
Purpose	Stable, consistent, unique row identifier
Common Forms	SERIAL, BIGSERIAL, UUID
Best Use Case	General-purpose primary key in most tables