Primary & Foreign Keys

The last lesson left recipes.user_id as an informal link to users.id — just an INTEGER column, with nothing actually stopping it from pointing at a user that doesn't exist. This lesson makes that relationship real, and covers a SQLite-specific gotcha that trips up nearly everyone the first time they rely on it.

INTEGER PRIMARY KEY Is Special in SQLite

Every SQLite table (unless explicitly created WITHOUT ROWID) has a hidden 64-bit row identifier called rowid. When a table's primary key is declared as exactly INTEGER PRIMARY KEY (a single column, that exact type), SQLite makes that column an alias for the rowid itself, rather than a separate value it has to track:

CREATE TABLE users (
  id       INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE
);

-- Leave id out entirely and SQLite assigns the next available rowid automatically:
INSERT INTO users (username) VALUES ('chefsonu');
SELECT last_insert_rowid();  -- the new user's id

This is why nearly every table in this track is declared with plain id INTEGER PRIMARY KEY — it gives you free, automatic, unique row numbering without needing anything extra.

AUTOINCREMENT: Real, But Rarely Needed

SQLite has a genuine AUTOINCREMENT keyword (spelled as one word, unlike MySQL's AUTO_INCREMENT), but it does something narrower than its name suggests. Plain INTEGER PRIMARY KEY may reuse the rowid of a previously deleted row; adding AUTOINCREMENT guarantees ids are never reused, at the cost of a little extra bookkeeping overhead:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  total REAL NOT NULL
);

Declaring Foreign Keys

Now the real relationship. RecipeBox's reviews table links a user to a recipe with a rating, so it references both users and recipes:

CREATE TABLE recipes (
  id       INTEGER PRIMARY KEY,
  user_id  INTEGER NOT NULL,
  title    TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE reviews (
  id         INTEGER PRIMARY KEY,
  recipe_id  INTEGER NOT NULL,
  user_id    INTEGER NOT NULL,
  rating     INTEGER NOT NULL,
  comment    TEXT,
  FOREIGN KEY (recipe_id) REFERENCES recipes (id),
  FOREIGN KEY (user_id) REFERENCES users (id)
);

Each review now points at exactly one recipe and exactly one user — the same pattern described back in the track's home page: "a review links a user to a recipe with a rating."

The Gotcha: Foreign Keys Are OFF By Default

Here is the single most commonly missed detail in all of SQLite: writing FOREIGN KEY ... REFERENCES in your schema does not, by itself, make SQLite enforce that relationship. For backward-compatibility reasons, foreign key enforcement is disabled by default. Without turning it on, SQLite will silently accept a review pointing at a recipe_id that doesn't exist:

-- Fresh connection, foreign keys NOT yet turned on for it:
INSERT INTO reviews (recipe_id, user_id, rating) VALUES (9999, 1, 5);
-- Succeeds! Even though recipe 9999 doesn't exist. The FOREIGN KEY was declared but never enforced.

The fix is a single pragma, run once per connection, ideally immediately after opening it:

PRAGMA foreign_keys = ON;

-- Now the same insert correctly fails:
INSERT INTO reviews (recipe_id, user_id, rating) VALUES (9999, 1, 5);
-- Error: FOREIGN KEY constraint failed