Table of Contents Link to heading

• Introduction
• Keep The Primary Key As Small As Possible
• Pick Primary Keys From Attributes That Are Stable
• Where Possible, Small Natural Keys May Improve Data Readability
• Still Have Not Got Any Good Primary Keys? - Use A Surrogate Key

Introduction Link to heading

Picking a good Primary Key from a list of available Candidate Keys might be an unappealing process, but try to stick to these rules to make your life easier.

Keep The Primary Key As Small As Possible Link to heading

Imagine that the PK will be distributed among other relations.

• 1000s of copies of that PK value will appear in the database, which will overload the system.

That is, the smaller they are, the fewer bytes the DBMS has to load into RAM to find related records.

E.g. Picking studentName + birthDate + address is bad as it is large! Try to keep it below 3 attributes.

Pick Primary Keys From Attributes That Are Stable Link to heading

You will definitely not want the DBMS to constantly update Foreign Key values that reference changing PK values!

Thus, pick a column that has no inherent meaning for the entity or does not change.

E.g. studentID and productID are good choices.

Picking studentName + address or mobilePhone is bad since these can change over time.

Where Possible, Small Natural Keys May Improve Data Readability Link to heading

Abbreviated names can be excellent: st for street, rd for road, ave for avenue.

Surrogate Keys have no meaning (e.g., streetTypeID = 1, streetType = ‘Road’).

Still Have Not Got Any Good Primary Keys? - Use A Surrogate Key Link to heading

Nonetheless, keep in mind that this is more data to manage which has no meaning or relationship to the business data.