Lesson 5 — Relationships

Foreign Keys

Foreign keys link tables together and enforce referential integrity — preventing orphaned data that references non-existent records.

What is a Foreign Key?

A foreign key (FK) is a column in one table that references the primary key of another table. It enforces referential integrity: the database prevents you from inserting a row with a FK value that doesn't exist in the referenced table, and (by default) prevents deleting a referenced row.

Example: a placements table has a learner_id column. The foreign key constraint ensures you can never insert a placement for learner_id = 999 if no learner with id 999 exists in the learners table.

SQL — Foreign key 
-- Define FK in CREATE TABLE:
CREATE TABLE placements (
    placement_id  INT          AUTO_INCREMENT PRIMARY KEY,
    learner_id    INT          NOT NULL,
    employer_name VARCHAR(100) NOT NULL,
    position      VARCHAR(100),
    start_date    DATE         NOT NULL,
    monthly_salary DECIMAL(10, 2),
    city          VARCHAR(50),
    is_current    BOOLEAN DEFAULT TRUE,

    -- The foreign key constraint:
    CONSTRAINT fk_placements_learner
        FOREIGN KEY (learner_id)
        REFERENCES learners(learner_id)
        ON DELETE RESTRICT   -- prevents deleting a learner who has placements
        ON UPDATE CASCADE    -- if learner_id changes, update it here too
);

ON DELETE and ON UPDATE Actions

These control what happens when the referenced row is deleted or updated:

ActionOn DELETEOn UPDATE
RESTRICT (default)Error — cannot delete parentError — cannot update PK
CASCADEDelete child rows automaticallyUpdate FK in child rows
SET NULLSet FK to NULL in child rowsSet FK to NULL
NO ACTIONSame as RESTRICT (deferred check)Same as RESTRICT
SQL — FK operations 
-- Adding a FK to an existing table:
ALTER TABLE placements
    ADD CONSTRAINT fk_placements_learner
    FOREIGN KEY (learner_id) REFERENCES learners(learner_id);

-- Dropping a FK constraint:
ALTER TABLE placements
    DROP FOREIGN KEY fk_placements_learner;

-- Test referential integrity:
INSERT INTO placements (learner_id, employer_name, start_date)
VALUES (999, 'Acme Corp', '2025-01-15');
-- ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

Practice Task

Your Turn

Create a marks table with a FK to learners. Add: mark_id (PK), learner_id (FK to learners), assessment_name (VARCHAR), score (DECIMAL 5,2), recorded_date (DATE). Choose ON DELETE and ON UPDATE actions that make sense: if a learner is deleted, should their marks be deleted too?

Common Mistakes

  • Creating the FK table before the referenced table — MySQL will refuse the FK.
  • Not indexing the FK column — joins on unindexed FK columns are extremely slow.
  • ON DELETE CASCADE carelessly — accidentally deleting a learner would cascade-delete all their marks, placements, and enrolments.
  • FK value that doesn't exist in parent — the database rejects it with a constraint violation error.

Professional Tip

The convention for naming FK constraints is fk_childtable_parenttable — e.g. fk_placements_learner. Good constraint names make error messages readable.

Mini Quiz

What does a foreign key constraint prevent?