Lesson 50 of 61

5 mins read

Python SQL FOREIGN KEY: Referential Integrity & Cascades

In Plain English

A `FOREIGN KEY` creates a referential link between a column in a child table and the `PRIMARY KEY` of a parent table. It prevents "orphan records" by blocking insertions of child rows referencing non-existent parents. Cascade actions (`ON DELETE CASCADE`, `ON DELETE SET NULL`, `ON DELETE RESTRICT`) automate parent-child lifecycle management.

The Mental Model

"A `FOREIGN KEY` is like a child holding hands with a parent in a crowd: the child cannot be registered in the park unless the parent is already verified at the entrance."

Deep Dive: How It Works

Referential Integrity: The engine guarantees child foreign key values match an existing parent primary key.

ON DELETE CASCADE: Automatically deletes child rows when the parent entity is deleted.

ON DELETE SET NULL: Clears the child foreign key column to NULL when parent is deleted.

ON DELETE RESTRICT: Forbids deleting parent rows if active child records exist.

Syntax Blueprint

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Declares foreign key link with cascade actions.

Core Rules to Remember

Prevents Orphan Records: Blocks insertions pointing to deleted or non-existent parent rows.

Cascade Automation: Deletes dependent sub-records automatically when parent entity is destroyed.

Foreign Key Indexing: Always create indexes on foreign key columns to avoid table scans during join operations.

Common Beginner Traps & How to Fix Them

Mistake:Using `ON DELETE CASCADE` on critical financial or historical transaction tables.

Why it happens: Deleting a user would silently destroy all financial audit logs and receipt history.

How to fix: Use `ON DELETE RESTRICT` or soft deletes (`is_deleted = 1`) for critical audit trails.

Pro Tip: In SQLite, foreign keys are disabled by default for legacy backward compatibility; execute `PRAGMA foreign_keys = ON;` on every new connection.

Live Interactive Example

Hit Run Code to see it live

Referential Integrity and Cascading Keys

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: Referential link verified and child records joined with parent authors.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Link Categories and Items via Foreign Key

Create a `categories (id INTEGER PRIMARY KEY, name TEXT)` table and an `items (id INTEGER PRIMARY KEY, item_name TEXT, category_id INT, FOREIGN KEY (category_id) REFERENCES categories(id))` table.
Insert category (1, "Electronics") and item (1, "Monitor", 1). Join and select item_name and category name.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.