SQL from Zero › Module 2: DDL: building the structure
Foreign keys
Link tables together so every book points to a real author.
Lesson 11 of 33 · about 14 minutes
Video: Pointing to another shelfThe narrated video for this lesson is coming soon. The lesson below covers the same ideas.
The lesson
In Module 1 you saw that each book stores its author's id instead of the author's name. That link is a foreign key: a column whose values must match the primary key of another table.
Your practice database already has an authors table and a customers table. Now link a new table to one of them:
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER REFERENCES authors(id)
);
REFERENCES authors(id) means: "every value in author_id must be an id that exists in the authors table."
What this buys you:
- No orphans. You can't add a book by author 999 if there's no author 999. The database refuses.
- Safe deletes. You can't delete an author while books still point at them, unless you decide what should happen to those books.
You'll often see the longer form, which does the same thing:
FOREIGN KEY (author_id) REFERENCES authors(id)
Note: SQLite only enforces foreign keys after PRAGMA foreign_keys = ON;. Your practice database has it switched on. Most other databases enforce them automatically.
Key ideas
- A foreign key links a column to another table's primary key.
- Write it as column_name INTEGER REFERENCES other_table(id).
- The database then refuses values that don't exist in the other table.
Practise
Loading practice…