Моделирование связи 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