Що таке referential actions в SQL?

Загальні питання~3 хв читання

Referential actions - це правила, які повідомляють базі даних, що автоматично робити з дочірніми рядками при видаленні або оновленні батьківського рядка через зв'язок foreign key.

Теорія

TL;DR

Уяви систему бронювання ресторану: видали клієнта - і система або скасовує всі його бронювання (CASCADE), блокує видалення поки бронювання не очищено (RESTRICT), або ставить "без клієнта" (SET NULL)
П'ять типів: CASCADE, RESTRICT, NO ACTION, SET NULL, SET DEFAULT
Тісно пов'язані дані (замовлення належать користувачу) - CASCADE. Незалежні дані (коментарі, логи) - RESTRICT або SET NULL

Швидкий приклад

sql

CREATE TABLE users (
  id INT PRIMARY KEY
);

CREATE TABLE posts (
  id INT PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE
);

INSERT INTO users VALUES (1);
INSERT INTO posts VALUES (1, 1);

DELETE FROM users WHERE id = 1;
-- таблиця posts тепер порожня; рядок видалено автоматично

CASCADE поширює видалення вниз. Без нього DELETE FROM users завершиться помилкою порушення foreign key.

П'ять типів дій

CASCADE копіює операцію на дочірні рядки. Видалив батька - видалились всі діти. Оновив ID батька - оновились всі посилання. Підходить коли дочірні рядки без батька не мають сенсу.

RESTRICT блокує операцію, якщо є хоч один дочірній рядок. Спочатку треба вручну видалити дітей. Добре захищає від випадкової втрати даних.

NO ACTION поводиться як RESTRICT у MySQL та PostgreSQL при стандартному використанні. У PostgreSQL з відкладеними обмеженнями (deferred constraints) може дозволяти тимчасові порушення в межах транзакції - перевірка відбувається при commit. Якщо потрібна однакова поведінка в різних БД, використовуй RESTRICT.

SET NULL записує NULL у колонку foreign key при видаленні батька. Дочірній рядок залишається, але посилання зникає. Працює тільки якщо колонка допускає NULL.

SET DEFAULT скидає foreign key до значення за замовчуванням. Наприклад, замовлення видаленого користувача отримують user_id = 0 (гостьовий акаунт). Рідко зустрічається, але корисно для soft delete і запасного власника.

Коли що використовувати

Користувач видаляє акаунт, і всі його замовлення мають зникнути: ON DELETE CASCADE
Коментар залишається після видалення автора, просто без прив'язки: ON DELETE SET NULL на user_id
Захист від випадкового видалення користувача з активними замовленнями: ON DELETE RESTRICT
Видалений продукт переходить до категорії за замовчуванням: ON DELETE SET DEFAULT
Audit log не повинен втрачати посилання на батьківський рядок: ON DELETE NO ACTION (deferred у PostgreSQL)

Як це працює всередині

При DELETE або UPDATE рушій сканує індекс на колонці foreign key в дочірній таблиці, знаходить відповідні рядки і застосовує дію: блокує, рекурсивно видаляє або записує NULL/default. Рядкові блокування запобігають перегонам. У PostgreSQL відкладені обмеження переносять перевірку на moment commit, що дозволяє тимчасово порушити цілісність всередині транзакції без негайної помилки.

Типові помилки

SET NULL на колонці з NOT NULL. Транзакція відкочується з помилкою порушення обмеження. Завжди перевіряй, що колонка допускає NULL, перш ніж додавати цю дію.

sql

-- Завершиться помилкою, якщо user_id NOT NULL
ALTER TABLE orders
  ADD CONSTRAINT fk_user FOREIGN KEY (user_id)
  REFERENCES users(id) ON DELETE SET NULL;

Припускати, що CASCADE зачіпає тільки прямих дітей. Я бачив, як це видаляло тисячі рядків, яких ніхто не планував видаляти. Якщо у orders є order_items з власним CASCADE, видалення одного користувача знищить всі три таблиці в одному statement. Спочатку перевір кількість рядків: SELECT COUNT(*) FROM orders WHERE user_id = 1.

Забути про ON UPDATE. За замовчуванням для оновлень стоїть NO ACTION. Зміниш ID користувача, поки orders ще посилаються на старий - операція впаде. Додавай ON UPDATE CASCADE явно, якщо ID батьківської таблиці може змінюватись.

Вважати NO ACTION і RESTRICT однаковими в усіх базах. MySQL обробляє їх однаково. PostgreSQL дозволяє відкласти NO ACTION. Перевіряй поведінку в своїй цільовій БД.

Де зустрічається в реальних проектах

PostgreSQL / Supabase: CASCADE на auth.users -> profiles при видаленні акаунту
MySQL / WooCommerce: RESTRICT на orders.user_id, щоб не видалити активного покупця
SQL Server / AdventureWorks: SET NULL на sales.customer_id для soft delete
Oracle: NO ACTION на таблицях аудиту, щоб заблокувати будь-які зміни батька

Follow-up питання

Q: В чому різниця між RESTRICT і NO ACTION?
A: У MySQL та стандартному PostgreSQL вони поводяться однаково: перевірка відбувається одразу після statement. NO ACTION можна відкласти (deferred) в PostgreSQL, тоді перевірка відбувається при commit транзакції.

Q: Чи може CASCADE спричинити нескінченний цикл?
A: Так, якщо дві таблиці посилаються одна на одну з CASCADE. PostgreSQL 12+ виявляє цикли через рекурсивне відстеження і видає помилку. Самопосилальні таблиці, як employees з manager_id, каскадуються через рівні, але зупиняються на корені.

Q: Як вимкнути перевірку foreign key під час міграції?
A: MySQL: SET FOREIGN_KEY_CHECKS=0. PostgreSQL: SET session_replication_role = replica. Одразу після міграції вмикай назад і перевіряй цілісність даних.

Q: Як referential actions працюють у шардованій базі типу CockroachDB?
A: Перевірка відбувається локально в межах шарду. Крос-шардові foreign key потребують логіки на рівні застосунку або gateway-шару на кшталт Vitess. Розподілені транзакції додають затримку - це варто згадати на senior-рівні.

Приклади

Базовий: видалення з CASCADE

sql

CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50));
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  total DECIMAL(10,2)
);

INSERT INTO users (name) VALUES ('Alice');           -- id = 1
INSERT INTO orders (user_id, total) VALUES (1, 99.99), (1, 49.99);

DELETE FROM users WHERE id = 1;
-- Обидва замовлення видалено автоматично
SELECT * FROM orders; -- повертає 0 рядків

Акаунт Alice видалено. Обидва її замовлення зникають в тому ж statement. Жодних зайвих запитів.

Середній: SET NULL для незалежних дочірніх рядків

sql

CREATE TABLE authors (id SERIAL PRIMARY KEY, name VARCHAR(50));
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  author_id INT REFERENCES authors(id) ON DELETE SET NULL,
  title VARCHAR(100)
);

INSERT INTO authors (name) VALUES ('Bob');            -- id = 1
INSERT INTO articles (author_id, title) VALUES (1, 'SQL Deep Dive');

DELETE FROM authors WHERE id = 1;
-- Стаття залишилась, author_id став NULL
SELECT * FROM articles;
-- id=1, author_id=NULL, title='SQL Deep Dive'

Стаття залишається. Вона просто втрачає прив'язку до автора. Цей патерн підходить для контенту, який має самостійну цінність після видалення його творця.

Коротка відповідь

Для співбесіди

Premium

Коротка відповідь допоможе вам впевнено відповідати на цю тему під час співбесіди.

Дочитали статтю?