Внешние ключи (CASCADE/SET NULL)

В Brew пришёл запрос «удалите мой аккаунт». Разработчик выполнил DELETE FROM customers WHERE id = 42 — и приложение посыпалось в трёх местах сразу. Заказы этого клиента остались висеть со ссылкой на несуществующего владельца; страница «мои отзывы» падала, потому что отзыв ссылался в пустоту; а попытка заодно подчистить меню упёрлась в ошибку, потому что на напиток ещё ссылались позиции старых заказов. Все три проблемы — об одном: что происходит со ссылающимися строками, когда исчезает та, на которую они ссылались.

Цель юнита — переложить эти правила в схему через внешний ключ (FOREIGN KEY) и его политику ON DELETE. FK делает две вещи. Во-первых, не даёт сослаться на несуществующего родителя — «висящая» ссылка отбивается с SQLSTATE 23503. Во-вторых, через ON DELETE задаёт судьбу детей при удалении родителя: CASCADE — удалить их вместе с ним, SET NULL — обнулить ссылку (ребёнок остаётся), а дефолт NO ACTION (≈ RESTRICT) — вовсе запретить удаление, пока ссылки есть. Это бизнес-решения, и место им — в DDL.

FK как страж ссылочной целостности

customer_id BIGINT NOT NULL REFERENCES fk_customer(id) — это обещание базы: в customer_id нельзя положить значение, которого нет в fk_customer.id. Попытка вставить заказ с customer_id = 999, когда такого клиента нет, отбивается с foreign_key_violation (23503). Так же FK ловит и удаление родителя, на которого ещё ссылаются (если политика это запрещает). Целостность гарантирует схема — её не обойти гонкой между «проверил, что клиент есть» и «вставил заказ».

ON DELETE: CASCADE, SET NULL, RESTRICT

Когда удаляют родителя, у каждого ссылающегося FK есть своя политика:

ON DELETE CASCADE — удалить детей вместе с родителем. Подходит для «слабых» сущностей, не имеющих смысла без владельца: заказ без клиента — мусор, пусть уходит каскадом.
ON DELETE SET NULL — оставить ребёнка, но обнулить ссылку. Подходит, когда ребёнок ценен сам по себе: отзыв о кофе остаётся (его текст полезен), просто становится анонимным. Требует, чтобы FK-колонка была NULLABLE — иначе SET NULL нарушит NOT NULL.
дефолт NO ACTION / RESTRICT — запретить удаление родителя, пока на него ссылаются. Это защита по умолчанию: не дать случайно выбить из-под живых заказов напиток из меню. Хочешь удалить — сначала разберись со ссылками.

Родитель и дети: три политики

Одна команда DELETE родителя — три разные судьбы у ссылающихся детей, и решает их ON DELETE:

plaintext

                       DELETE родителя
                              │
        ┌─────────────────────┼─────────────────────┐
        ▼                     ▼                     ▼
     CASCADE              SET NULL          NO ACTION / RESTRICT
        │                     │                     │
   дети уходят          дети живут,         удаление родителя
   каскадом вместе      ссылка := NULL      запрещено, пока есть
   с родителем          (FK NULLABLE)       дети → 23503

Политика	Что делает с детьми	Когда брать
`CASCADE`	удаляет вместе с родителем	«слабые» сущности, бессмысленные без владельца (заказ без клиента)
`SET NULL`	оставляет ребёнка, обнуляет ссылку (FK должен быть NULLABLE)	самоценные данные (текст отзыва полезен и анонимным)
`NO ACTION` / `RESTRICT` (дефолт)	запрещает удаление родителя при живых ссылках (`23503`)	защита по умолчанию: не выбить строку из-под живых ссылок

Что показывает наш код

Один родитель (fk_customer) и трое детей с разными политиками; плюс пара fk_drink ← fk_orderitem с дефолтным FK (DDL в schema.sql):

sql

customer_id BIGINT NOT NULL REFERENCES fk_customer (id) ON DELETE CASCADE   -- fk_order
customer_id BIGINT          REFERENCES fk_customer (id) ON DELETE SET NULL  -- fk_review (NULLABLE!)
drink_id    BIGINT NOT NULL REFERENCES fk_drink (id)                        -- fk_orderitem: дефолт NO ACTION

main.go сначала пробует висящую ссылку (customer_id = 999 → 23503), затем заводит клиента с двумя заказами и одним отзывом и удаляет его — и считает, что осталось:

queries.DeleteCustomer(ctx, custID)        // запускает политики детей
orders, _ := queries.CountOrders(ctx)      // CASCADE → 0
rev, _ := queries.CountReviews(ctx)        // SET NULL → отзыв жив, customer_id IS NULL

В конце — дефолтная политика: на напиток ссылается позиция заказа, и DeleteDrink отбивается с 23503. Все ошибки печатаем как SQLSTATE (код детерминирован, текст — нет).

Запуск

docker compose up -d
make lecture L=02-schema-and-constraints/02-03-foreign-keys T=db-reset
make lecture L=02-schema-and-constraints/02-03-foreign-keys

Вывод:

plaintext

1) FK блокирует «висящую» ссылку:
   заказ с customer_id = 999 (нет такого клиента) → отклонён: SQLSTATE 23503 (foreign_key_violation)
2) Завели клиента id=1: его заказов (ON DELETE CASCADE) = 2, отзывов (ON DELETE SET NULL) = 1
3) DELETE клиента id=1:
   ON DELETE CASCADE → заказы удалены каскадом: осталось 0
   ON DELETE SET NULL → отзыв жив, ссылка обнулена: отзывов 1, из них customer_id IS NULL: 1
4) ON DELETE по умолчанию (NO ACTION / RESTRICT):
   пока на напиток id=1 ссылается позиция заказа, DELETE напитка → отклонён: SQLSTATE 23503

Висящая ссылка отбита (23503). После удаления клиента два его заказа ушли каскадом (осталось 0), а отзыв остался — но уже без автора (customer_id IS NULL). Тот же 23503 защитил меню: напиток, на который ссылается живая позиция заказа, удалить нельзя. Три строчки DDL заменили три ветки ручной логики в приложении.

Заборчик

Что мы упростили: CASCADE выглядит удобно — «удалил клиента, всё связанное само подчистилось», — но в проде это обоюдоострый инструмент, за которым следит твой DBA:

Каскад может тихо снести гораздо больше, чем ты ожидал (удаление одной строки потянуло за собой десятки тысяч в дочерних таблицах — долгая блокировка и распухший WAL), а ещё он бьёт по аудиту: данные исчезают без следа.
Часто безопаснее RESTRICT + явное «мягкое удаление» (deleted_at) в приложении, чтобы удаление было осознанным и обратимым.
С SET NULL помни про NULLABLE-колонку и про то, что теперь приложение должно уметь жить с «осиротевшей» ссылкой.
FK не бесплатен — он проверяется при каждой вставке/удалении и требует индекс на ссылающейся стороне, иначе удаление родителя устроит seq scan по детям (про индексы под FK — модуль 06).

Правило: каждая политика ON DELETE — это записанное бизнес-решение; выбирай её осознанно, а не по привычке ставить CASCADE везде.

Что забрать с собой

FOREIGN KEY ... REFERENCES не даёт сослаться на несуществующего родителя — висящая ссылка → SQLSTATE 23503.
ON DELETE CASCADE — удалить детей с родителем (для сущностей, бессмысленных без владельца).
ON DELETE SET NULL — оставить ребёнка, обнулить ссылку (для самоценных данных); FK-колонка должна быть NULLABLE.
дефолт NO ACTION/RESTRICT — запретить удаление родителя при живых ссылках; это безопасный дефолт.
CASCADE удобен, но опасен (тихо сносит много, ломает аудит) — часто лучше RESTRICT + soft-delete; FK требует индекс на дочерней стороне.

Дальше — юнит 02-04 «UNIQUE и CHECK (NULLS NOT DISTINCT)»: ещё два декларативных ограничения — уникальность (и коварство NULL в ней, плюс PG-фишка NULLS NOT DISTINCT) и проверки значений через CHECK.