Моделювання зв'язку ONE-TO-MANY між таблицями

Тепер настав час прокачати наші скіли й створити один із найчастіших зв'язків у реляційних базах даних — зв'язок ONE-TO-MANY.

Уяви собі невеличку компанію. Один співробітник може працювати лише в одному відділі, але в одному відділі можуть працювати десятки таких співробітників. У нас є два об'єкти реального світу: співробітники та відділи. Зв'язок між ними можна описати як "один відділ може включати багато співробітників", або формально "один до багатьох" (ONE-TO-MANY).

Точно так само відносини ONE-TO-MANY зустрічаються всюди:

• один клієнт може зробити багато замовлень;
• один автор може написати купу книжок;
• один вчитель може викладати кільком студентам.

У реляційній базі даних зв'язок ONE-TO-MANY реалізується через використання зовнішнього ключа (FOREIGN KEY). Один зі стовпців таблиці "багато" (MANY) вказує на первинний ключ із таблиці "один" (ONE).

Як створити зв'язок ONE-TO-MANY

Давай розглянемо класичний приклад: зв'язок між замовниками та замовленнями. Один замовник може зробити багато замовлень, але кожне замовлення прив'язане лише до одного замовника. Ми створимо дві таблиці: customers (замовники) і orders (замовлення).

Таблиця customers

Це наша таблиця "один". Вона зберігає інформацію про замовників.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- Унікальний ідентифікатор замовника
    name TEXT NOT NULL              -- Ім'я замовника
);

Таблиця orders

Ця таблиця "багато". Вона зберігає замовлення, де кожному замовленню відповідає зовнішній ключ customer_id, який вказує на customer_id із таблиці customers.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,           -- Унікальний ідентифікатор замовлення
    order_date DATE NOT NULL,              -- Дата замовлення
    customer_id INT REFERENCES customers(customer_id) -- Зовнішній ключ
);

Практичне застосування

Вставка даних

Тепер додамо в наші таблиці трохи даних, щоб перевірити, як працює зв'язок.

Додаємо замовників у таблицю customers:

INSERT INTO customers (name)
VALUES
    ('Ada Lovelace'),
    ('Grace Hopper'),
    ('Linus Torvalds');

Результат:

Додаємо замовлення в таблицю orders:

INSERT INTO orders (order_date, customer_id)
VALUES
    ('2023-10-01', 1),  -- Замовлення Ади
    ('2023-10-02', 2),  -- Замовлення Грейс
    ('2023-10-03', 1);  -- Ще одне замовлення Ади

Таблиця orders:

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

Перевірка зв'язку

Тепер подивимось, як наші таблиці пов'язані. Наприклад, спитаємо: які замовлення зробила Ada Lovelace?

SELECT orders.order_id, orders.order_date, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.name = 'Ada Lovelace';

Результат:

Тут ми використали команду JOIN, щоб об'єднати дві таблиці на основі зовнішнього ключа. Зручно, красиво — і ніяких дубльованих даних!

Навіщо це потрібно

Зв'язок ONE-TO-MANY неймовірно поширений і корисний у реальному житті. Уяви інтернет-магазин, де є тисячі замовників і мільйони замовлень. Замість того, щоб дублювати інформацію про замовника в кожному записі замовлення, ми зберігаємо дані про унікальних замовників в одній таблиці, а замовлення — в іншій. Це зменшує обсяг даних і робить базу більш організованою.

Крім того, можливість зв'язувати дані дозволяє створювати потужні запити для аналітики. Наприклад, можна спитати: "Скільки замовлень зробив кожен замовник?" або "Які замовники зробили замовлення за останній місяць?".

Складнощі та підводні камені

Ось на чому новачки зазвичай "спотикаються":

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

Спроба видалити запис із таблиці "один". Наприклад, якщо ти видалиш замовника з customers, то його замовлення в orders стануть "завислими". Щоб цього уникнути, можна використати ON DELETE CASCADE, щоб разом із замовником автоматично видалялися його замовлення.

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

Тепер, якщо ти видалиш замовника:

DELETE FROM customers WHERE customer_id = 1;

Усі його замовлення теж видаляться. База буде чистою, як свіжа чашка кави.

Помилки вставки даних. Якщо ти спробуєш вставити замовлення з неіснуючим customer_id, то отримаєш помилку типу:

ERROR:  insert or update on table "orders" violates foreign key constraint