Використання ON DELETE CASCADE та ON UPDATE RESTRICT

Уяви, що ти створив базу даних, де є таблиця клієнтів (customers) і пов'язані з ними замовлення (orders). Але в якийсь момент виникає питання: що робити, якщо клієнта видаляють з таблиці customers? Чи повинні замовлення цього клієнта теж видалятись, чи вони залишаться "сиротами", посилаючись на неіснуючого клієнта? А що, якщо ти вирішиш змінити ID клієнта? Ось тут і з'являються каскадні операції (CASCADE) та обмеження (RESTRICT) для керування поведінкою бази даних.

ON DELETE CASCADE — це механізм, який автоматично видаляє пов'язані записи при видаленні запису з батьківської таблиці. Іншими словами, якщо ти видалив клієнта, то всі пов'язані з ним замовлення теж видаляться.

Це працює так: коли ти додаєш ON DELETE CASCADE у визначення зовнішнього ключа, база даних "розуміє", що пов'язаний запис треба видалити автоматично.

Приклад

Припустимо, у нас є дві таблиці: customers і orders. Клієнти customers можуть зробити кілька замовлень orders, що відповідає зв'язку ONE-TO-MANY. Ми хочемо, щоб при видаленні клієнта всі його замовлення також видалялися.

-- Створюємо таблицю клієнтів
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Створюємо таблицю замовлень із зовнішнім ключем, що посилається на таблицю клієнтів
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

Вставляємо дані в таблиці

-- Вставляємо дані в таблицю клієнтів
INSERT INTO customers (name) VALUES ('Іван'), ('Анна');

-- Вставляємо дані в таблицю замовлень
INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-10-01'),
(1, '2023-10-02'),
(2, '2023-10-03');

Таблиця orders:

Видаляємо клієнта і дивимось, що буде

-- Видаляємо клієнта з ID 1
DELETE FROM customers WHERE customer_id = 1;

-- Перевіряємо, що залишилось у таблиці замовлень
SELECT * FROM orders;

Як бачиш, замовлення, пов'язані з видаленим клієнтом, теж були видалені.

Обмеження змін: ON UPDATE RESTRICT

ON UPDATE RESTRICT дозволяє запобігти зміні значення в батьківській таблиці, якщо запис у дочірній таблиці посилається на це значення. Це такий собі "захисний бар'єр", який не дає зробити зміни, що можуть порушити цілісність даних.

Як це працює? Коли ти додаєш ON UPDATE RESTRICT, база даних не дозволить оновити ключ у батьківській таблиці, якщо на нього посилаються записи в дочірній таблиці.

Приклад

Беремо ті ж таблиці customers і orders, але додамо до зовнішнього ключа обмеження на зміну.

-- Перестворюємо таблицю замовлень з обмеженням на оновлення
DROP TABLE orders;

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON UPDATE RESTRICT,
    order_date DATE NOT NULL
);

Спробуємо оновити ID клієнта

-- Спроба змінити ID клієнта з 2 на 5
UPDATE customers 
SET customer_id = 5 
WHERE customer_id = 2;

Результат:

ERROR:  update or delete on table "customers" violates foreign key constraint
DETAIL:  Key (customer_id)=(2) is still referenced from table "orders".

Як бачиш, база даних викинула помилку, бо зміна ключа порушила б зв'язок між таблицями.

Більше про UPDATE і нюанси його роботи я розповім на наступному рівні :P

Комбінування ON DELETE CASCADE та ON UPDATE RESTRICT

Звісно, каскадні операції (CASCADE) і обмеження (RESTRICT) можна поєднувати. Наприклад, ти можеш налаштувати автоматичне видалення пов'язаних даних при видаленні батьківського запису (ON DELETE CASCADE), але при цьому заборонити зміну його ID (ON UPDATE RESTRICT), щоб уникнути небажаних наслідків.

Приклад

Створимо ще раз таблицю замовлень, використовуючи обидва механізми:

DROP TABLE orders;

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE RESTRICT,
    order_date DATE NOT NULL
);

Тепер:

• Якщо ти видалиш клієнта, всі його замовлення видаляться.
• Якщо ти спробуєш змінити ID клієнта, отримаєш помилку.

Чому це важливо в реальних проектах?

Використання CASCADE і RESTRICT дуже важливе для великих систем із багатьма пов'язаними таблицями. Наприклад:

В інтернет-магазині клієнт може мати замовлення. Якщо клієнт вирішить видалити свій профіль, ти не хочеш залишати в базі замовлення, які вже ні на що не посилаються. Тут допоможе ON DELETE CASCADE.

Водночас ти можеш захотіти запобігти випадковим змінам унікальних ключів, щоб не зламати зв'язки між таблицями. Для цього стане у пригоді ON UPDATE RESTRICT.