>_ IzzyLab

Hands-on task

This task is step 20 of the course “SQL with SQLite”

SQLite: foreign key

medium~7 min

The database /root/lib.db already has a lookup table authors(id INTEGER PRIMARY KEY, name TEXT) with authors (id = 1 and 2). Create a table books(id INTEGER PRIMARY KEY, author_id INTEGER) such that the author_id column is a foreign key referencing authors(id). The schema must guarantee: a book with an existing author can be inserted, but a book referencing a non-existent author cannot (when foreign key enforcement is enabled).

What you'll practise:
  • foreign key
  • references
  • create table
  • pragma foreign_keys
  • data integrity

Topic breakdown

A foreign key (FOREIGN KEY) links a column of one table to the key of another and forbids "dangling" references to non-existent records. It is declared in CREATE TABLE — either briefly in the column definition via REFERENCES, or as a separate FOREIGN KEY(...) REFERENCES ... clause. An SQLite quirk: foreign key enforcement is off by default and enabled per connection via PRAGMA foreign_keys = ON — so the constraint itself lives in the schema, but only takes effect in a session where this PRAGMA is on.

What's checked

  1. book with an existing author is inserted
  2. book with a non-existent author is rejected

What it looks like