Внешние ключи и ссылочная целостность

Гарантируй согласованность между таблицами с помощью REFERENCES и осознанно выбирай поведение ON DELETE.

8 мин · 3 вопросов

Внешний ключ объявляет, что столбец указывает на первичный ключ другой таблицы. После этого база отклоняет любое осиротевшее значение: ты не сможешь создать книгу, автора которой не существует. Осторожно: в SQLite эта проверка выключена по умолчанию - включи её через PRAGMA в начале скрипта.

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);

Проверь защиту: вставь осиротевшую книгу, затем удали автора. Первая инструкция будет отклонена; вторая заберёт с собой книги благодаря 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 определяет судьбу дочерних строк, когда родитель исчезает. CASCADE удаляет их вместе с ним. SET NULL сохраняет строку, но очищает ссылку - значит, столбец должен допускать NULL. Без явной конструкции SQLite применяет NO ACTION: удаление родителя отклоняется, пока остаются дети, - поведение, близкое к RESTRICT.

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

Проверка знаний

Убедись, что запомнил ключевые моменты этого урока.

1. При ON DELETE CASCADE на books.author_id к чему приведёт DELETE FROM authors WHERE id = 1?
   • К ошибке, пока у автора есть книги
   • К удалению автора и всех его книг
   • К установке author_id в NULL в его книгах
2. Зачем выполнять PRAGMA foreign_keys = ON; в SQLite?
   • Чтобы создать внешние ключи
   • Потому что проверка внешних ключей выключена по умолчанию, для каждого подключения отдельно
   • Чтобы ускорить соединения
3. Какому условию должен отвечать столбец reviewer_id, чтобы использовать ON DELETE SET NULL?
   • Быть объявленным UNIQUE
   • Быть типа INTEGER
   • Допускать NULL, то есть не нести NOT NULL