Видалення та зміна даних з урахуванням зовнішніх ключів

Отже, ми вже знаємо, що таке зовнішні ключі (FOREIGN KEY), як вони працюють, і навіть потренувалися у створенні таблиць з їх використанням. Але що робити, якщо настав час видалити дані або змінити пов’язані записи? Ось зараз і розберемося, як видалення та зміна даних працюють у зв’язці із зовнішніми ключами, і які тут є фішки.

Уяви, що база даних — це складний картковий будиночок. Якщо ти витягуєш одну карту, то ризикуєш зруйнувати всю конструкцію. Ось тут і вступають у гру зовнішні ключі, які не дають "зламати" нашу базу при видаленні пов’язаних даних. Давай розберемося, як це працює.

Що станеться, якщо спробувати видалити дані?

Коли в таблиці є зовнішній ключ, СУБД перевіряє, чи пов’язаний запис з іншими таблицями. Якщо так, то спроба видалити запис може викликати помилку цілісності даних. Щоб таких сюрпризів не було, можна заздалегідь задати дії для зовнішнього ключа. Ці дії налаштовуються за допомогою опцій ON DELETE.

Налаштування поведінки з ON DELETE

Ти можеш задати одне з наступних правил для зовнішнього ключа під час його створення:

1. ON DELETE CASCADE: Видалення запису в батьківській таблиці автоматично призводить до видалення всіх пов’язаних записів у дочірній таблиці.
2. ON DELETE SET NULL: Замість видалення пов’язаних записів їх зовнішні ключі встановлюються в NULL.
3. ON DELETE SET DEFAULT: Значення зовнішнього ключа встановлюється у значення за замовчуванням.
4. ON DELETE RESTRICT (поведінка за замовчуванням): Видалення запису неможливе, якщо є пов’язані записи, і буде викинута помилка.
5. ON DELETE NO ACTION: Майже як RESTRICT, але перевірка цілісності відкладається до завершення транзакції.

Приклад: Каскадне видалення ON DELETE CASCADE

-- Таблиця клієнтів
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');

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

-- Перевіряємо, що сталося з таблицею orders
SELECT * FROM orders; -- Жодних записів, вони були видалені каскадно!

Коли ми видаляємо запис з таблиці customers, PostgreSQL автоматично видаляє всі замовлення цього клієнта з таблиці orders.

Приклад: Встановлення посилань у NULL ON DELETE SET NULL

-- Таблиця замовлень з новим правилом поведінки
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- Вставка даних
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

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

-- Перевіряємо таблицю orders_with_null
SELECT * FROM orders_with_null;

Вивід:

З допомогою ON DELETE SET NULL ми можемо зберегти замовлення, але "відв’язати" їх від вже неіснуючого клієнта.

Зміна даних з урахуванням зовнішніх ключів

Окрім видалення, зміни даних у батьківських таблицях теж можуть впливати на пов’язані записи. Наприклад, що буде, якщо клієнт змінює свій customer_id? А ось тут вступає в гру опція ON UPDATE.

Налаштування поведінки з ON UPDATE

Обробити зміни у батьківській таблиці можна за допомогою таких стратегій:

1. ON UPDATE CASCADE: зміна значення зовнішнього ключа у батьківській таблиці автоматично оновлює його у всіх пов’язаних записах.
2. ON UPDATE SET NULL: значення зовнішнього ключа у дочірніх таблицях встановлюється в NULL.
3. ON UPDATE SET DEFAULT: встановлюється значення за замовчуванням.
4. ON UPDATE RESTRICT: зміна значення зовнішнього ключа заборонена, якщо є пов’язані записи.
5. ON UPDATE NO ACTION: перевірка відкладається до кінця транзакції.

Приклад: Каскадне оновлення ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- Вставка даних
INSERT INTO customers_with_cascade (name) VALUES ('Іван Іванов');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- Зміна customer_id
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- Перевіряємо таблицю orders_with_cascade
SELECT * FROM orders_with_cascade;

Вивід:

При зміні customer_id PostgreSQL автоматично оновлює його у таблиці orders_with_cascade.

Практичне закріплення: Таблиця enrollments

Давай згадаємо наш приклад зі студентами students і курсами courses. Ми будемо працювати з таблицею записів на курси enrollments і налаштовувати керування видаленням даних.

-- Таблиця студентів
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Таблиця курсів
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- Проміжна таблиця записів
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- Вставка даних
INSERT INTO students (name) VALUES ('Олексій Петров');
INSERT INTO courses (title) VALUES ('Математика');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- Видаляємо студента
DELETE FROM students WHERE student_id = 1;

-- Перевіряємо таблицю enrollments
SELECT * FROM enrollments; -- Порожньо! Запис був автоматично видалений.

Типові помилки та їх запобігання

Однією з частих помилок є спроба видалити запис із батьківської таблиці, не налаштувавши коректну поведінку зовнішніх ключів. Наприклад, якщо ти не вказав нічого для ON DELETE, за замовчуванням буде застосовано поведінку RESTRICT, що призведе до помилки.

Також важливо пам’ятати, що занадто часте використання каскадних операцій (CASCADE) може призвести до неочікуваних наслідків. Наприклад, ти можеш випадково видалити більше даних, ніж планував.

Щоб уникнути таких проблем, дотримуйся цих порад:

• Завжди ретельно продумуй поведінку ON DELETE та ON UPDATE з огляду на логіку застосунку.
• Для особливо важливих операцій додай перевірочні запити перед виконанням змін або видалень.
• Використовуй транзакції, щоб можна було відкотити зміни у разі помилки.