The Relational Model and Keys

The Catalog Number That Connects Everything

Walk into a university library. Every book on every shelf has a unique catalog number printed on its spine — something like QA76.9.D3 C67 2021. That number isn't just a label. It's used to locate the book, to reference it in the checkout system, to link it back to its author record, and to track every time it was ever borrowed.

Remove that number and replace it with just the title, and chaos follows. Two books named Introduction to Computing become indistinguishable. A checkout record that says "borrowed Introduction to Computing" no longer points to a specific book. The whole system unravels.

That catalog number is a primary key. This lesson explains why keys are the foundation of every relational database.

The Vocabulary of the Relational Model

Edgar Codd's relational model uses precise mathematical language. Modern databases translate it to practical terms.

A table in the relational model is not an ordered list — it's a mathematical set. Sets have no duplicates and no inherent order. SQL tables add ordering and allow duplicates by default, but the relational model forbids both. That's why primary keys exist: to enforce the set property.

Types of Keys

Candidate Key

A candidate key is any column (or combination of columns) that could uniquely identify every row in a table. A table can have multiple candidate keys.

Example: In a Students table, both StudentID and Email could uniquely identify each student. Both are candidate keys.

Primary Key

The primary key (PK) is the candidate key the designer chooses as the official row identifier. Rules:

• Must be unique across all rows
• Must be NOT NULL — a row with no identifier is unlocatable
• Should be stable — changing a PK cascades pain across every table that references it
• Should be minimal — use fewer columns rather than more

Surrogate vs Natural Keys

A natural key is a real-world value that happens to be unique (Social Security Number, ISBN, email address). A surrogate key is an artificial ID created purely for the database (auto-incremented integer, UUID).

Surrogate keys are usually preferred because natural keys change (people change their email) while surrogate keys never need to.

Foreign Key

A foreign key (FK) is a column in one table that references the primary key of another table. It creates the link between tables.

students.dept_id  →  departments.dept_id

The table holding the FK is the child table. The table being referenced is the parent table.

Composite Key

A composite key is a primary key made of two or more columns. Common in junction tables.

Example: In an Enrollments table, neither student_id alone nor course_id alone is unique — a student enrolls in many courses. But the combination (student_id, course_id) is unique.

Alternate Key

Any candidate key not chosen as the primary key becomes an alternate key. These are typically enforced with a UNIQUE constraint.

Key Reference Table

Constraints: The Rules That Protect Data

Constraints are database-level rules that reject invalid data before it can be stored. They run every time data is inserted or updated.

NOT NULL

The column must have a value. Used for required fields.

first_name VARCHAR(50) NOT NULL

UNIQUE

All values in the column must be different. NULLs are typically allowed and not compared to each other.

email VARCHAR(100) UNIQUE

PRIMARY KEY

Combines NOT NULL + UNIQUE. Only one per table.

student_id INT PRIMARY KEY

FOREIGN KEY

Value must match an existing value in the referenced column, or be NULL.

FOREIGN KEY (dept_id) REFERENCES departments(dept_id)

CHECK

Value must satisfy a boolean expression.

gpa DECIMAL(3,2) CHECK (gpa >= 0.0 AND gpa <= 4.0)

DEFAULT

If no value is provided on INSERT, use this value.

enrollment_date DATE DEFAULT CURRENT_DATE

Referential Integrity and CASCADE Behavior

When a parent row is updated or deleted, what happens to child rows that reference it? The database needs a rule.

Scenario: Student with dept_id = 5 is enrolled. Department 5 is deleted.

Choosing the right action:

• Use CASCADE when children cannot exist without the parent (OrderItems when an Order is deleted)
• Use SET NULL when the child can exist independently (a Book's author_id when an Author is deleted)
• Use RESTRICT when you want to force manual cleanup before deletion

A Working Schema: Students, Courses, Enrollments

CREATE TABLE departments (
    dept_id    INT          PRIMARY KEY AUTO_INCREMENT,
    dept_name  VARCHAR(100) NOT NULL UNIQUE,
    building   VARCHAR(50)
);

CREATE TABLE students (
    student_id      INT           PRIMARY KEY AUTO_INCREMENT,
    first_name      VARCHAR(50)   NOT NULL,
    last_name       VARCHAR(50)   NOT NULL,
    email           VARCHAR(100)  NOT NULL UNIQUE,
    gpa             DECIMAL(3,2)  DEFAULT 0.00
                                  CHECK (gpa >= 0.00 AND gpa <= 4.00),
    dept_id         INT,
    enrollment_date DATE          DEFAULT (CURRENT_DATE),
    FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE courses (
    course_id   INT           PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(10)   NOT NULL UNIQUE,
    course_name VARCHAR(200)  NOT NULL,
    credits     INT           NOT NULL CHECK (credits BETWEEN 1 AND 6),
    dept_id     INT           NOT NULL,
    FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
        ON DELETE RESTRICT
);

CREATE TABLE enrollments (
    student_id      INT  NOT NULL,
    course_id       INT  NOT NULL,
    enrollment_date DATE DEFAULT (CURRENT_DATE),
    grade           CHAR(2),
    PRIMARY KEY (student_id, course_id),   -- composite primary key
    FOREIGN KEY (student_id)
        REFERENCES students(student_id)
        ON DELETE CASCADE,
    FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
        ON DELETE RESTRICT
);

Notice how every design decision maps to a concept from this lesson:

• AUTO_INCREMENT surrogate keys on students, departments, courses
• NOT NULL on all truly required fields
• UNIQUE on email and course_code (alternate keys)
• CHECK on gpa and credits to reject bad values
• DEFAULT for dates that default to today
• CASCADE on enrollments: if a student is deleted, their enrollments disappear too
• Composite PK on enrollments: each student-course pair is unique

The Foundation Is Set

Keys and constraints are not administrative overhead. They are the database doing your validation for you — consistently, every time, from every application that touches the data. No matter which programming language inserts a row, the database enforces the same rules.

Every layer built on top of this — queries, joins, indexes, transactions — depends on the integrity that keys and constraints provide.

Next lesson: Writing the DDL commands that create, modify, and drop these structures in SQL.