Перевірка цілісності даних

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

В першу чергу розберемо, що саме ми маємо на увазі під "цілісністю даних". Уяви, що у тебе є таблиця із замовленнями (orders) і таблиця з клієнтами (customers). Якщо у замовлення є клієнт, якого немає в таблиці клієнтів, це порушення цілісності. Важливо, щоб всі дані у зв'язаних таблицях були логічно узгоджені.

Цілісність даних означає:

• Ніяких "порожніх" посилань: якщо ми посилаємось на щось в іншій таблиці, то це "щось" завжди існує.
• Стійкість до помилок модифікації: якщо видаляємо з таблиці значення, на яке посилаються інші записи, база даних має нас попередити або коректно обробити цю ситуацію.

Саме для цього в PostgreSQL використовуються зовнішні ключі.

Як зовнішні ключі забезпечують цілісність даних?

Коли в таблиці створюється зовнішній ключ, PostgreSQL автоматично перевіряє:

1. Наявність даних у батьківській таблиці. Перед тим як вставити або оновити запис, PostgreSQL перевіряє, чи існує вказаний зовнішній ключ у зв'язаній таблиці.
2. Видалення або зміна даних. Перед видаленням або оновленням запису в батьківській таблиці, PostgreSQL перевіряє, чи не посилаються на нього записи в дочірній таблиці.

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

Приклад: цілісність даних у таблицях студентів і курсів

Припустимо, у нас є дві таблиці — students і courses. Кожен студент може записатися на кілька курсів. Для відображення цього зв'язку ми використовуємо таблицю enrollments. Розглянемо ситуацію, коли хтось намагається записати студента на неіснуючий курс.

Крок 1. Створимо три зв'язані таблиці:

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 (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id)
);

Тут:

• У таблиці enrollments ми явно вказали зовнішні ключі student_id і course_id, які посилаються на первинні ключі таблиць students і courses.

Типові перевірки цілісності даних

1. Перевірка при вставці даних

Якщо ми спробуємо вставити в таблицю enrollments запис з неіснуючими student_id або course_id, виникне помилка.

Приклад:

INSERT INTO enrollments (student_id, course_id)
VALUES (999, 1); -- Помилка! Студент з ID 999 не існує.

Повідомлення про помилку:

ERROR:  insert or update on table "enrollments" violates foreign key constraint "enrollments_student_id_fkey"
DETAIL:  Key (student_id)=(999) is not present in table "students".

2. Перевірка при видаленні даних

Спробуємо видалити запис з батьківської таблиці, на який є посилання.

Приклад:

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; -- Помилка, бо студент все ще записаний на курс!

Повідомлення про помилку:

ERROR:  update or delete on table "students" violates foreign key constraint "enrollments_student_id_fkey" on table "enrollments"
DETAIL:  Key (student_id)=(1) is still referenced from table "enrollments".

Для коректного видалення записів у таких випадках ми користуємось стратегіями CASCADE, SET NULL або RESTRICT, які обговорювались раніше.

Приклади використання зовнішніх ключів для перевірки цілісності

Приклад 1: Автоматичний захист від некоректних даних

З допомогою зовнішніх ключів PostgreSQL автоматично не дає вставити "неіснуючі" дані:

-- Спробуємо додати неіснуючих студентів у курс:
INSERT INTO enrollments (student_id, course_id)
VALUES (42, 1); -- Помилка! Студент з ID 42 не існує.

Це гарантує, що студент не зможе записатися на курс, якщо його немає в таблиці students.

Приклад 2: Видалення даних з допомогою ON DELETE CASCADE

Якщо зовнішній ключ налаштований на каскадне видалення ON DELETE CASCADE, то при видаленні запису в батьківській таблиці пов'язані дані в дочірній таблиці також будуть видалені.

ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey; -- Видаляємо старий зовнішній ключ

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON DELETE CASCADE;

DELETE FROM students WHERE student_id = 1; -- Тепер видаляться і записи з таблиці enrollments

Приклад 3: Обробка змін з допомогою ON UPDATE

Якщо зовнішній ключ налаштований з ON UPDATE CASCADE, то при зміні значення у батьківській таблиці PostgreSQL автоматично оновить дані у дочірній таблиці.

-- Налаштуємо зовнішній ключ так, щоб зміни батьківського ключа автоматично застосовувались до дочірньої таблиці:
ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey;

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON UPDATE CASCADE;

-- Змінимо ідентифікатор студента:
UPDATE students SET student_id = 10 WHERE student_id = 1;

-- Тепер у таблиці enrollments student_id теж оновиться на 10.

Тестування цілісності даних

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

1. Спробуй вставити дані з неправильним student_id або course_id.
2. Видали дані з students і перевір, як поводиться таблиця enrollments.
3. Зміни дані у таблиці students і переконайся, що пов'язані записи оновлені.

Особливості при роботі з зовнішніми ключами

Іноді виникають ситуації, які можуть збити з пантелику:

• Відсутність індексу. Якщо батьківська таблиця (students, наприклад) не має індексованого стовпця, на який йде посилання, PostgreSQL може "намагатися" працювати повільніше. Тому важливо, щоб первинний ключ у батьківській таблиці завжди був індексом.
• Циклічні посилання. Якщо дві таблиці посилаються одна на одну, це може викликати складнощі при вставці даних. У таких випадках треба підходити до проектування більш уважно.
• Видалення всіх записів. Якщо треба видалити всі записи з каскадним видаленням, потрібно враховувати характер даних у дочірній таблиці, щоб уникнути неочікуваної поведінки.

Щоб уникнути цих проблем, важливо грамотно проектувати таблиці і тестувати правила зв'язків до їх застосування у реальній базі даних.