Natural, surrogate and composite keys.
WHAT A SURROGATE KEY IS
A meaningless identifier generated by the database.
WHAT A NATURAL KEY IS
A value with real-world meaning that happens to be unique.
WHAT EXAMPLES OF NATURAL KEYS LOOK LIKE
A registration number An email address A product code
WHY THEY ARE TEMPTING
No extra column, and the key is meaningful.
WHY THEY CAUSE TROUBLE
Real-world values change, and changing a key changes every reference.
WHAT AN EMAIL ADDRESS AS A KEY PRODUCES
An enormous update when someone changes address.
WHAT TO PREFER
A surrogate key, with a unique constraint on the natural value.
WHAT THAT GIVES YOU
Stable references, and enforced uniqueness.
WHAT A COMPOSITE KEY IS
Several columns together forming the key.
WHERE IT IS CORRECT
Joining tables, where the pair is the identity.
WHAT IT COSTS
Wider foreign keys, and larger indexes everywhere referencing it.
WHAT TO CONSIDER ABOUT KEY SIZE
Every secondary index includes the primary key.
WHY THAT MATTERS
A large key enlarges every index on the table.
WHAT TO PREFER
The smallest stable key that works.
WHAT TO AVOID
Changing a primary key value, ever.
WHY
It is the one thing everything else depends on.
