Enforcing Integrity with FOREIGN KEY

This lesson covers maintaining consistency between tables with FOREIGN KEY, so a row can't reference data that doesn't exist. It's for anyone searching "SQL FOREIGN KEY tutorial."

Declaring FOREIGN KEY(column) REFERENCES other_table(column) restricts that column to values that genuinely exist in the referenced table — preventing a mistake like creating a loan record that points to a nonexistent book_id.

The example adds FOREIGN KEY(book_id) REFERENCES books(id) to the loans table, after first enabling foreign key enforcement with PRAGMA foreign_keys = ON;. Try creating a loan record with a nonexistent book ID, and the database rejects it outright.

A common early mistake is forgetting that SQLite has foreign key enforcement disabled by default — without running PRAGMA foreign_keys = ON;, a defined FOREIGN KEY won't actually be checked, so remembering that setting matters. This mechanism, called a "foreign key constraint," lets the database itself guarantee that relationships between tables stay valid — a genuinely important safety net that catches inconsistencies even when the application code has a bug.

PRAGMA foreign_keys = ON;

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE loans (
  id INTEGER PRIMARY KEY,
  book_id INTEGER,
  FOREIGN KEY(book_id) REFERENCES books(id)
);

INSERT INTO books VALUES (1, 'Intro to SQL');
INSERT INTO loans VALUES (1, 1);

SELECT * FROM loans;

OUTPUT

💡 The SQL engine may take a few seconds to load the first time you run code.