SQL Academy · Урок

FOREIGN KEY и ссылочные действия

Связывайте таблицы с помощью FOREIGN KEY и выбирайте подходящее поведение ON DELETE CASCADE / SET NULL / RESTRICT

«FOREIGN KEY и ссылочные действия» — бесплатный урок SQL Academy на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Что делает FOREIGN KEY

FOREIGN KEY заставляет базу данных отклонять строки, которые ссылаются на несуществующих родителей:

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total NUMERIC(10,2) NOT NULL
);

-- Inserting an order with user_id=999 fails if no such user exists.

Зачем использовать FOREIGN KEY?

FK обеспечивают ссылочную целостность:

Нет строк-сирот
Ошибки приложения обнаруживаются во время вставки
Диаграммы ER остаются точными
Поддерживается автоматическое каскадное поведение

Действия при нарушении ссылочной целостности

Что должно произойти при удалении или обновлении родительской строки? Есть пять вариантов:

NO ACTION — ошибка (по умолчанию, допускает отложенную проверку)
RESTRICT — ошибка (отложенная проверка невозможна)
CASCADE — также удалить или обновить дочернюю строку
SET NULL — установить NULL в дочернем FK
SET DEFAULT — установить DEFAULT для дочернего FK

ON DELETE CASCADE

«Когда пользователь удалён, удалите также его заказы»:

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL
    REFERENCES users(id) ON DELETE CASCADE,
  total NUMERIC(10,2) NOT NULL
);

ON DELETE SET NULL

«Когда руководитель удалён, сотрудники остаются, но теряют своего руководителя»:

CREATE TABLE employees (
  id BIGSERIAL PRIMARY KEY,
  full_name TEXT NOT NULL,
  manager_id BIGINT REFERENCES employees(id) ON DELETE SET NULL
);

По умолчанию: как RESTRICT

Без действия для ссылочной целостности попытка удалить родителя, у которого есть дочерние строки, завершается ошибкой:

ERROR:  update or delete on table "users" violates foreign key constraint "orders_user_id_fkey" on table "orders"
DETAIL:  Key (id)=(42) is still referenced from table "orders".

Всегда индексируйте столбец FK

PostgreSQL НЕ создаёт индекс на стороне дочерних строк автоматически. Добавьте его — без индекса удаление родителя вызовет полное сканирование каждой дочерней таблицы:

CREATE INDEX orders_user_id_idx ON orders(user_id);

Составные FOREIGN KEY

Сослаться на составной первичный ключ:

CREATE TABLE order_items (
  order_id BIGINT,
  line_no INT,
  PRIMARY KEY (order_id, line_no)
);
CREATE TABLE shipments (
  order_id BIGINT,
  line_no INT,
  FOREIGN KEY (order_id, line_no)
    REFERENCES order_items(order_id, line_no)
);

Отложенная проверка FOREIGN KEY

Иногда нужно вставить взаимозависимую пару, в которой одна строка ссылается на другую. Объявите FK как DEFERRABLE INITIALLY DEFERRED:

ALTER TABLE orders
  ALTER CONSTRAINT orders_user_id_fkey DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  INSERT INTO orders ... ;     -- FK temporarily not checked
  INSERT INTO users ... ;
COMMIT;                         -- checked here

Добавление FOREIGN KEY без остановки работы

Для больших таблиц используйте NOT VALID + VALIDATE, чтобы избежать длительной блокировки:

ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

Осторожно с CASCADE

Удаления с CASCADE распространяются дальше. Удаление пользователя может стереть заказы, платежи и журналы аудита. Убедитесь, что каскад соответствует замыслу вашей предметной области.

Итоги

FOREIGN KEY — это защитные ограждения.

REFERENCES + действие объявляет связь и поведение
Индексируйте столбец FK в дочерней таблице
Используйте NOT VALID + VALIDATE для добавления ограничений без остановки работы

Быстрая проверка

Какое действие для ссылочной целостности нужно использовать, чтобы AUTOMATICALLY удалить заказ при удалении его родительского пользователя?

Часто задаваемые вопросы

Урок «FOREIGN KEY и ссылочные действия» бесплатный?

Да — полный текст урока «FOREIGN KEY и ссылочные действия» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «FOREIGN KEY и ссылочные действия»?

Связывайте таблицы с помощью FOREIGN KEY и выбирайте подходящее поведение ON DELETE CASCADE / SET NULL / RESTRICT Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «FOREIGN KEY и ссылочные действия»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

← Назад к SQL Academy