Primary Keys and Unique Records

Lesson 3 of 8 · Database Fundamentals · Beginner

Every table in a relational database needs a way to identify each row uniquely. Without a reliable identifier, you cannot link tables, update the right record, or prevent duplicates. This lesson explains primary keys and why choosing them carefully matters.

What Is a Primary Key?

A primary key is a column (or combination of columns) whose value uniquely identifies each row in a table. No two rows can have the same primary key value, and a primary key column cannot be null.

The database enforces this automatically. If you try to insert a row with a primary key value that already exists, the database rejects it.

Why Unique Identifiers Matter

Consider a participants table. If two participants are both named "Marie Tremblay", how do you tell them apart? If you update a record, how do you know you updated the right one? If another table references a participant, which Marie does it mean?

A unique identifier solves this. Each participant gets a distinct identifier — for example, participant_id 1001 and participant_id 1002 — regardless of their name.

Natural Keys vs Surrogate Keys

A natural key is an identifier that already exists in the real world — an employee number, a licence plate, a product code. Natural keys work well when they are truly unique, stable, and will never change.

A surrogate key is an identifier created by the database purely for identification purposes — typically an auto-incrementing integer (1, 2, 3…) or a generated unique code. Surrogate keys have no business meaning; they exist only to identify rows.

Surrogate keys are often preferred because:

• They never change, even if the real-world attributes of the record change
• They are not tied to sensitive information
• They are compact and efficient for linking tables

Why Names Are Poor Identifiers

Names change. People get married, divorced, or legally change their names. Two people can share the same name. A name is a description of a person, not a stable identifier of a database record.

The same applies to addresses, email addresses, and phone numbers — all of these can change over time and are not reliable as primary keys.

Composite Keys

Sometimes no single column uniquely identifies a row, but a combination of columns does. For example, in a registrations table, the combination of participant_id and program_id might uniquely identify each registration — one participant can only register once per program. This is called a composite key.

Protecting Identifier Meaning

Primary keys are internal database identifiers. They should not be exposed to the public or used as meaningful codes in communications. An auto-incrementing integer reveals how many records exist in your database — information you may not want to share.

Do not use government-issued identifiers (such as social insurance numbers or health card numbers) as primary keys. These are sensitive personal information and should be stored only when legally required, with appropriate access controls.

Key Takeaways

• A primary key uniquely identifies each row in a table and cannot be null.
• Names, addresses, and email addresses are poor primary keys because they change.
• Surrogate keys (auto-generated integers) are stable, compact, and do not expose sensitive information.
• Composite keys use multiple columns together to form a unique identifier.
• Do not use sensitive government identifiers as primary keys.