Choosing Primary Keys

George Clarke does not want another number to remember. “Use the phone,” he says.

Amelia Hart opens last week's card. Sophie Walsh had 07700 900 123. Then she changed it. If MemberID had been the old phone, every loan pointing at her would now point at a number she no longer uses.

George tries again. “Then use the name.” Amelia points at the two Sophie Walshes.

Each idea is a candidate key: a field, or combination of fields, that might uniquely identify a row. The one you choose is the primary key. The candidates you do not choose are alternate keys.

Before you pick, ask three questions:

• 1Could two rows ever share this value?
• 2Could it be missing?
• 3Will we want to change it later?

A value the library issues — 1, 2, 3, 8 — passes all three. That is a generated key. Database software can issue the next unused integer automatically.

A natural key comes from the real world: a passport number, an ISBN for a book edition. Natural keys can work, but they often change, go missing, or carry privacy rules. At Riverside Public Library, MemberID is generated on purpose.

Phone. Sophie Walsh already changed hers. Two people can share a mobile.

Name. The two Sophie Walshes are the whole argument.

NIC. Sensitive, easy to mistype, and not everyone has one.

Name and city together. People move. A second Sophie Walsh could join in York.

Sometimes no single field is unique, but two fields together are. That pair is a composite key.

For loans, MemberID + BookID might identify one borrow — unless the same person borrows the same title twice. A simple LoanID is often easier.