Primary Keys

The primary key is the most important concept in database design. Every table must have one, and choosing it correctly prevents a cascade of data quality problems.

What is a Primary Key?

A primary key (PK) is a column or combination of columns that uniquely identifies every row in a table. Three requirements:

1. Unique — no two rows may have the same PK value.
2. Not null — every row must have a PK value.
3. Stable — PK values should not change after creation. Other tables reference them.

Surrogate vs Natural Keys

A surrogate key is a system-generated value (usually an auto-increment integer) with no business meaning. A natural key is derived from real-world data (like a South African ID number or an email address).

For most cases, use a surrogate key. Natural keys seem convenient until: an ID number has a typo, an email changes, a composite natural key gets unwieldy. Auto-increment integers are fast, stable, and simple.

-- Auto-increment syntax by database:

-- MySQL:
CREATE TABLE learners (
    learner_id INT AUTO_INCREMENT,
    full_name  VARCHAR(100) NOT NULL,
    CONSTRAINT pk_learners PRIMARY KEY (learner_id)
);
-- Or inline:
CREATE TABLE learners (
    learner_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL
);

-- PostgreSQL:
CREATE TABLE learners (
    learner_id SERIAL PRIMARY KEY,  -- SERIAL = INT AUTO_INCREMENT
    full_name  VARCHAR(100) NOT NULL
);
-- Or (modern PostgreSQL):
CREATE TABLE learners (
    learner_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL
);

-- SQL Server:
CREATE TABLE learners (
    learner_id INT IDENTITY(1,1) PRIMARY KEY,  -- starts at 1, increments by 1
    full_name  NVARCHAR(100) NOT NULL
);

-- SQLite:
CREATE TABLE learners (
    learner_id INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name  TEXT NOT NULL
);

Composite Primary Keys

Sometimes uniqueness requires two columns together. A classic example: an enrolments table where a learner can enrol in many courses and a course can have many learners, but a learner can only be in a course once.

-- Composite PK — the combination of learner_id + course_id is unique:
CREATE TABLE enrolments (
    learner_id INT  NOT NULL,
    course_id  INT  NOT NULL,
    enrol_date DATE NOT NULL,
    mark       DECIMAL(5, 2),

    CONSTRAINT pk_enrolments PRIMARY KEY (learner_id, course_id),  -- composite
    CONSTRAINT fk_enrol_learner FOREIGN KEY (learner_id) REFERENCES learners(learner_id),
    CONSTRAINT fk_enrol_course  FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

UUID as Primary Key

In distributed systems and APIs, you may see UUIDs (Universally Unique Identifiers) as primary keys. They are globally unique — useful when merging databases or generating IDs in client code before inserting.

-- UUID primary key in PostgreSQL:
CREATE TABLE sessions (
    session_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    learner_id INT  NOT NULL REFERENCES learners(learner_id),
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Practice Task

Common Mistakes

• Using name as PK — names are not unique and change.
• Using email as PK — emails change and are long (bad for indexes and foreign keys).
• No PK at all — you cannot reliably reference or update individual rows.
• Composite PK when a surrogate would be simpler and cleaner.
• UUID for internal systems — the storage cost (16 bytes vs 4) and reduced index performance may not be worth it.