Primary keys, foreign keys and relationships

Primary keys

A primary key is a column (or set of columns) whose value uniquely identifies each row. It must be unique and never NULL. In our students table, id is the primary key: two students can share a name or a city, but never an id.

Most tables use a generated number or an identifier as the primary key rather than real-world data such as an email address, because real-world data can change. In PostgreSQL you can let the database generate it:

CREATE TABLE courses (
    id     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title  VARCHAR(150) NOT NULL,
    hours  INTEGER NOT NULL CHECK (hours > 0)
);

Why not one big table?

Imagine storing everything about enrolments in a single table:

Asha's city and the hours of "SQL Essentials" are repeated. If the course length changes, every copy must be updated, and missing one leaves the data inconsistent. Relational design avoids this by storing each fact once and linking tables with keys. This process is called normalisation.

Foreign keys

A foreign key is a column that holds the primary key of a row in another table. It creates a relationship and lets the database protect it:

CREATE TABLE enrollments (
    student_id  INTEGER NOT NULL REFERENCES students (id),
    course_id   INTEGER NOT NULL REFERENCES courses (id),
    enrolled_on DATE    NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);

With these foreign keys the database refuses to insert an enrolment for a student or course that doesn't exist, and refuses to delete a student who still has enrolments (unless you specify otherwise). This guarantee is called referential integrity.

The primary key (student_id, course_id) is a composite key: the pair must be unique, so a student cannot be enrolled in the same course twice.

Kinds of relationships

Reading related data

You'll learn to combine tables with JOIN in a later course, but here is a preview of how keys make it possible:

SELECT s.name, c.title
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses  c ON c.id = e.course_id;

The ON conditions match each enrolment's foreign keys to the primary keys they point to.