Внешние ключи и связи таблиц

В прошлом уроке ограничения охраняли одну таблицу. Но данные магазина разложены по нескольким: авторы отдельно, книги отдельно, отзывы отдельно. Между таблицами тоже нужны правила. Книга не может ссылаться на автора, которого нет, а удаление автора не должно оставлять книги, ссылающиеся в пустоту.

Номерок в гардеробе

Сдаёшь пальто в гардероб и получаешь номерок 42. Номерок сам по себе ничего не значит, это ссылка на крючок с номером 42. Гардеробщик следит за двумя вещами: не выдаёт номерок на несуществующий крючок и не отдаёт пальто с крючка другому, пока по номерку его не забрали.

В базе так же. В таблице books у книги хранится не имя автора, а его номер, author_id. Это номерок. А крючки висят в таблице authors. Внешний ключ делает базу гардеробщиком: ссылка обязана вести на существующую строку.

Почему автор хранится отдельно, а не текстом в каждой книге, разобрано в теории про перевод ER-модели в таблицы. Здесь смотрим на практику.

REFERENCES

CREATE TABLE authors (
  id      integer PRIMARY KEY,
  name    text    NOT NULL,
  country text    NOT NULL
);

CREATE TABLE books (
  id        integer PRIMARY KEY,
  title     text    NOT NULL,
  author_id integer NOT NULL REFERENCES authors (id)
);

INSERT INTO authors VALUES (1, 'Булгаков', 'Россия'), (2, 'Стругацкие', 'Россия');
INSERT INTO books VALUES
  (1, 'Мастер и Маргарита', 1),
  (2, 'Собачье сердце', 1),
  (3, 'Пикник на обочине', 2);

REFERENCES authors (id) читается так: значение author_id должно найтись в колонке id таблицы authors. Таблицу, на которую ссылаются, называют родительской, а таблицу со ссылкой дочерней. Здесь authors родитель, books ребёнок. Родителя создают первым: сослаться на таблицу, которой ещё нет, нельзя.

Ссылаться можно только на первичный ключ или колонку с UNIQUE. Иначе непонятно, на какую из одинаковых строк указывает номерок.

Ссылка в пустоту

Книга несуществующего автора:

INSERT INTO books VALUES (4, 'Хоббит', 99);

ERROR:  insert or update on table "books" violates foreign key constraint "books_author_id_fkey"
DETAIL:  Key (author_id)=(99) is not present in table "authors".

Автора с номером 99 нет, книга не вставилась. Имя ограничения books_author_id_fkey PostgreSQL придумал сам: таблица, колонка, fkey.

Теперь с другой стороны. Удалим Булгакова, у которого две книги:

DELETE FROM authors WHERE id = 1;

ERROR:  update or delete on table "authors" violates foreign key constraint "books_author_id_fkey" on table "books"
DETAIL:  Key (id)=(1) is still referenced from table "books".

Гардеробщик не отдаёт крючок, пока на нём висит пальто. Сменить автору номер через UPDATE тоже не выйдет, ошибка будет та же.

Связанные строки достают запросом с JOIN. Подробно соединения разберём в уроке 14, а пока посмотри, как номерок превращается обратно в имя:

SELECT b.title, a.name
FROM books b
JOIN authors a ON a.id = b.author_id
ORDER BY b.id;

       title        |    name
--------------------+------------
 Мастер и Маргарита | Булгаков
 Собачье сердце     | Булгаков
 Пикник на обочине  | Стругацкие
(3 rows)