Lesson 05 ~30 min read

Keys & Relationships

Keys are what make the relational model relational. Without them, tables would be isolated islands of data. With them, you can model the entire real world — students enrolled in courses, orders containing products, users posting comments.

Why Keys Exist

In a table of students, two students could have the same name. How does the database tell them apart? How does an enrolment record know which student it belongs to? The answer is keys — special columns that uniquely identify rows and connect tables together.

Keys are the backbone of the relational model. They enforce uniqueness, enable lookups, and define the relationships that give relational databases their power.

Primary Keys

A primary key uniquely identifies every row in a table. No two rows can share a primary key value, and it can never be NULL. Every table should have one.

sql — primary key

CREATE TABLE students (
  student_id  INT  PRIMARY KEY AUTO_INCREMENT,
  full_name   VARCHAR(100) NOT NULL,
  email       VARCHAR(150) UNIQUE NOT NULL
);

-- student_id is the PK:
-- • Unique: no two students share it
-- • Not null: every row must have it
-- • Stable: it never changes once assigned
-- • AUTO_INCREMENT: database assigns it automatically

// Natural vs Surrogate Keys

A natural key uses real-world data (e.g. national ID number, email). A surrogate key is an artificial ID the database generates (e.g. auto-increment integer or UUID). Surrogate keys are usually safer — natural data can change (emails get updated), but a surrogate ID never needs to.

Foreign Keys

A foreign key is a column in one table that references the primary key of another table. It is the mechanism that creates relationships between tables.

sql — foreign key

CREATE TABLE enrolments (
  enrolment_id  INT  PRIMARY KEY AUTO_INCREMENT,
  student_id    INT  NOT NULL,
  course_name   VARCHAR(100) NOT NULL,
  enrolled_on   DATE DEFAULT CURRENT_DATE,

  -- This FK links enrolments to students
  FOREIGN KEY (student_id) REFERENCES students(student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- Now this is guaranteed:
-- every enrolment.student_id must exist in students.student_id
-- you cannot enrol a student that doesn't exist

ON DELETE CASCADE

When a student is deleted, all their enrolments are automatically deleted too. Prevents orphaned records.

ON DELETE RESTRICT

Prevents deleting a student if they still have enrolments. Forces you to clean up first.

ON DELETE SET NULL

When a student is deleted, their enrolment's student_id is set to NULL — keeping the enrolment record but removing the link.

Types of Relationships

Foreign keys implement three types of relationships between tables. Understanding these is essential for designing any database.

relationship types

ONE-TO-MANY (most common)
One student → many enrolments
One teacher → many courses
One order → many order items
FK goes on the "many" side

ONE-TO-ONE
One student → one student profile
One user → one password record
FK + UNIQUE constraint on the referencing table

MANY-TO-MANY
Students can enrol in many courses
Courses can have many students
Requires a JUNCTION table with two FKs:
student_courses(student_id, course_id)

Composite Keys & Other Key Types

Composite Key

A primary key made from two or more columns combined. Common in junction tables: PRIMARY KEY (student_id, course_id) — one student can only appear once per course.

Candidate Key

Any column (or combination) that could serve as a primary key — it's unique and not null. A table may have several candidates; you choose one as the primary key.

Unique Key

Like a primary key but allows NULLs and a table can have many of them. Email addresses are typically unique keys — they identify a user but the primary key is a surrogate integer.

// Knowledge Check

A database models students and courses where each student can be enrolled in many courses, and each course can have many students. What type of relationship is this, and how is it implemented?