Surrogate Key
A surrogate key is a type of primary key.A synthetic, meaningless identifier
Natural Key or Business Key
A key that has real-world meaning email ,phone number ,country_code, ISO currency code
Default Rule (Production Systems)
Use a surrogate key as the primary key by default.
Why surrogate keys win (in practice)
Stability -
Business values change.
Primary keys should not.
Ex - email changes → FK cascade nightmare
Smaller indexes = faster joins
ORM-friendly - Works best with surrogate IDs
Safer cascades
Changing a natural PK forces:
FK updates
Locks
Long transactions
Surrogate keys never change.
Correct modelling pattern (recommended)
Use both:
Surrogate key → Primary Key
Natural key → Unique constraint
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email TEXT NOT NULL UNIQUE
);
One-line rule you can remember
Primary keys are for databases, not for humans.
UUID vs BIGINT surrogate keys
BIGINT
✔️ Small
✔️ Fast
✔️ Sequential
❌ Not globally unique
UUID
✔️ Globally unique
✔️ Safe for distributed systems
❌ Large index
❌ Random write amplification
Foreign Key
Every value in the child column must reference an existing row in the parent table (or be NULL).
orders(id PRIMARY KEY)
order_items(
id PRIMARY KEY,
order_id BIGINT,
FOREIGN KEY (order_id) REFERENCES orders(id)
)
Meaning:
You cannot insert an order_item with order_id = 123
unless orders(id = 123) already exists
Every FK column must have an index <Tatooable></Tatooable>
Foreign keys do NOT automatically create indexes on the child table
If order_items(order_id) is not indexed:
DELETE on orders
Causes full scan of order_items
Takes row locks
Spikes CPU
Blocks concurrent writes
Cascades
FOREIGN KEY (order_id)
REFERENCES orders(id)
ON DELETE CASCADE
DELETE order →
DB automatically deletes all order_items
Index Working
Most common is B Tree. Works for =, <>,BETWEEN, ORDER BY, prefix LIKE ‘abc%’
Index - Faster reads, slower writes
On INSERT:
Write table row
Write to each index
On UPDATE:
Possibly update index entries
On DELETE:
Remove index entries
