Запропонувати правкуПокращити цю статтюДопрацюйте відповідь до «Що таке referential actions в SQL?». Ваші зміни проходять модерацію перед публікацією.Потрібне підтвердженняКонтентЩо ви змінюєте🇺🇸EN🇺🇦UAПереглядЗаголовок (UA)Коротка відповідь (UA)**Referential actions** - це правила, що визначають, що база даних автоматично робить з дочірніми рядками при видаленні або оновленні батьківського рядка. CASCADE видаляє або оновлює дочірні рядки; RESTRICT блокує операцію якщо вони є; SET NULL очищає foreign key; SET DEFAULT скидає до значення за замовчуванням. **Головне:** CASCADE коли дочірні рядки не мають сенсу без батька.Показується над повною відповіддю для швидкого нагадування.Відповідь (UA)Зображення**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' ``` Стаття залишається. Вона просто втрачає прив'язку до автора. Цей патерн підходить для контенту, який має самостійну цінність після видалення його творця.Для рев’юераПримітка для модератора (необов’язково)Бачить лише модератор. Прискорює рев’ю.