Foreign keys and referential integrity

Guarantee consistency between your tables with REFERENCES and deliberately choose the ON DELETE behaviour.

8 min · 3 questions

A foreign key declares that a column points to the primary key of another table. The database then refuses any orphan value: you cannot create a book whose author does not exist. Beware, in SQLite this check is disabled by default: enable it with a PRAGMA at the top of your script.

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL
    REFERENCES authors(id)
    ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES
  (1, 'Ursula K. Le Guin'),
  (2, 'Ted Chiang');

INSERT INTO books (id, title, author_id) VALUES
  (1, 'A Wizard of Earthsea', 1),
  (2, 'The Dispossessed', 1),
  (3, 'Exhalation', 2);

Test the protection: insert an orphan book, then delete an author. The first statement is refused; the second takes its books with it thanks to ON DELETE CASCADE.

INSERT INTO books (id, title, author_id)
VALUES (4, 'Orphan', 99);

DELETE FROM authors WHERE id = 1;

SELECT id, title FROM books;

ON DELETE defines the fate of the child rows when the parent disappears. CASCADE deletes them along with it. SET NULL keeps the row but clears the reference - so the column must accept NULL. Without a clause, SQLite applies NO ACTION: deleting the parent is refused as long as children remain, a behaviour close to RESTRICT.

CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  reviewer_id INTEGER
    REFERENCES authors(id)
    ON DELETE SET NULL
);

Knowledge check

Make sure you remember the key points of this lesson.

1. With ON DELETE CASCADE on books.author_id, what does DELETE FROM authors WHERE id = 1 cause?
   • An error as long as the author has books
   • The deletion of the author and of all their books
   • Setting author_id to NULL in their books
2. Why run PRAGMA foreign_keys = ON; in SQLite?
   • To create the foreign keys
   • Because foreign key checking is disabled by default, connection by connection
   • To speed up joins
3. What condition must the reviewer_id column meet to use ON DELETE SET NULL?
   • Be declared UNIQUE
   • Be of type INTEGER
   • Accept NULL, so not carry NOT NULL