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

Связываем таблицы внешними ключами и управляем тем, что происходит при удалении родителя.

Внешний ключ (FOREIGN KEY) — это столбец, значения которого обязаны существовать в другой таблице; он связывает строки и не даёт появиться «висячим» ссылкам.

Зачем связи

Реляционная модель строится на связях. У заказа есть клиент, у комментария — автор, у товара — категория. Хранить всё в одной таблице — значит дублировать данные. Вместо этого мы разносим сущности по таблицам и связываем их внешними ключами.

Объявление внешнего ключа

Внешний ключ ссылается на первичный ключ другой таблицы. Соберём две связанные таблицы в песочнице.

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 REFERENCES authors(id)
);

INSERT INTO authors (id, name) VALUES (1, 'Толстой'), (2, 'Чехов');
INSERT INTO books (id, title, author_id) VALUES
    (1, 'Война и мир', 1),
    (2, 'Каштанка', 2),
    (3, 'Анна Каренина', 1);

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

Вывод:

Анна Каренина|Толстой
Война и мир|Толстой
Каштанка|Чехов

Столбец author_id в books ссылается через REFERENCES authors(id). Теперь нельзя вставить книгу с author_id = 99, если автора с таким id нет — база отвергнет такую строку. Это и есть ссылочная целостность.

Что делать при удалении родителя

Главный вопрос внешних ключей: что произойдёт, если удалить автора, у которого есть книги? Поведение задают ON DELETE и ON UPDATE.

Действие	Что происходит при удалении родителя
`RESTRICT` / `NO ACTION`	запретить удаление, пока есть зависимые строки (по умолчанию)
`CASCADE`	удалить вместе с родителем и все зависимые строки
`SET NULL`	обнулить ссылку в зависимых строках
`SET DEFAULT`	поставить в ссылку значение по умолчанию

Выбор стратегии

CREATE TABLE comments (
    id      SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    author  INTEGER REFERENCES users(id) ON DELETE SET NULL,
    body    TEXT NOT NULL
);

Логика подобрана по смыслу: если удалить пост, его комментарии теряют смысл — поэтому ON DELETE CASCADE уносит их вместе с постом. А если удалить пользователя, сам комментарий стоит сохранить, просто потеряв автора — отсюда ON DELETE SET NULL. Выбор всегда диктуется предметной областью.

По умолчанию (без явного указания) действует NO ACTION — база не даст удалить автора, пока есть его книги. Это безопасный вариант, который защищает от случайной потери данных.

Итог

Внешний ключ REFERENCES связывает таблицы и запрещает ссылки на несуществующие строки.
ON DELETE CASCADE удаляет зависимые строки вместе с родителем; SET NULL обнуляет ссылку.
По умолчанию действует NO ACTION/RESTRICT — удаление родителя с зависимыми строками запрещено.