Связи между таблицами в SQL: один-к-одному, один-ко-многим, многие-ко-многим

Правильно спроектированные связи между таблицами — основа надёжной базы данных. Выбор неверного типа связи приводит к дублированию данных, сложным запросам и трудноуловимым ошибкам. Разберём все три типа на конкретных примерах.

Один-к-одному (One-to-One)

Каждой записи в таблице A соответствует не более одной записи в таблице B, и наоборот.

Когда использовать:

• Данные логически относятся к одной сущности, но редко нужны вместе.
• Часть данных конфиденциальна (пароли, персональные данные) — разделение по таблицам упрощает управление правами.
• Большая часть записей не имеет дополнительных данных (разрежённые атрибуты).

Пример: пользователь и его профиль

CREATE TABLE users (
  user_id    SERIAL PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE user_profiles (
  user_id     INT PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
  full_name   TEXT,
  avatar_url  TEXT,
  bio         TEXT,
  birth_date  DATE
);

Ключевой момент: в таблице user_profiles первичный ключ одновременно является внешним ключом. Это гарантирует уникальность — у каждого пользователя не может быть двух профилей.

-- Получить пользователя с профилем
SELECT u.email, p.full_name, p.bio
FROM users u
LEFT JOIN user_profiles p ON u.user_id = p.user_id
WHERE u.user_id = 42;

Один-ко-многим (One-to-Many)

Одной записи в таблице A соответствует много записей в таблице B. Это самый распространённый тип связи.

Пример: пользователь и его заказы

CREATE TABLE orders (
  order_id   SERIAL PRIMARY KEY,
  user_id    INT NOT NULL REFERENCES users(user_id) ON DELETE RESTRICT,
  status     TEXT NOT NULL DEFAULT 'pending',
  total      NUMERIC(12,2),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Индекс на внешнем ключе — обязателен для производительности
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Все заказы пользователя
SELECT order_id, status, total, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

-- Количество заказов и сумма по каждому пользователю
SELECT
  u.email,
  COUNT(o.order_id)  AS orders_count,
  SUM(o.total)       AS total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.email
ORDER BY total_spent DESC NULLS LAST;

Многие-ко-многим (Many-to-Many)

Одной записи в таблице A соответствует много записей в таблице B, и наоборот. Реализуется через промежуточную таблицу (junction table, associative table, bridge table).

Пример: заказы и товары

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name       TEXT    NOT NULL,
  price      NUMERIC(10,2) NOT NULL
);

-- Промежуточная таблица
CREATE TABLE order_items (
  order_id   INT REFERENCES orders(order_id)     ON DELETE CASCADE,
  product_id INT REFERENCES products(product_id) ON DELETE RESTRICT,
  quantity   INT     NOT NULL DEFAULT 1,
  unit_price NUMERIC(10,2) NOT NULL, -- цена на момент покупки
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX idx_order_items_product_id ON order_items(product_id);

Поле unit_price в промежуточной таблице — намеренная денормализация: фиксируем цену на момент покупки, чтобы историческая отчётность оставалась корректной.

-- Состав конкретного заказа
SELECT
  p.name,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS line_total
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE oi.order_id = 101;

-- Самые популярные товары (по числу заказов)
SELECT
  p.name,
  COUNT(DISTINCT oi.order_id) AS times_ordered,
  SUM(oi.quantity)            AS units_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY times_ordered DESC
LIMIT 10;

Поведение при удалении: ON DELETE

При объявлении внешнего ключа нужно явно выбрать, что происходит со связанными записями при удалении родительской:

-- Каскадное удаление: удаляем пользователя → удаляются все его сессии
CREATE TABLE user_sessions (
  session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    INT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  expires_at TIMESTAMPTZ NOT NULL
);

-- SET NULL: удаляем менеджера → его клиенты становятся «без менеджера»
CREATE TABLE clients (
  client_id  SERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id INT REFERENCES employees(employee_id) ON DELETE SET NULL
);

Практическое правило: используйте CASCADE с осторожностью. Случайное удаление родительской записи может удалить много дочерних данных. RESTRICT — более безопасный выбор по умолчанию; удаление потребует явной очистки дочерних записей.

Индексы на внешних ключах

PostgreSQL автоматически создаёт индекс только на первичном ключе и уникальных ограничениях. На столбцах с внешними ключами индексы нужно создавать вручную.

Без индекса поиск «всех заказов пользователя» выполняет Seq Scan по всей таблице заказов. С индексом — Index Scan по idx_orders_user_id.

-- Проверить: какие FK не имеют индекса, где первый столбец FK совпадает
-- с первым столбцом индекса (покрывает обычный случай FK из одного столбца,
-- как в примерах этой статьи; для составных FK проверка сложнее)
SELECT
  conrelid::regclass AS table_name,
  conname            AS constraint_name,
  pg_get_constraintdef(oid) AS constraint_def
FROM pg_constraint
WHERE contype = 'f'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_index i
    WHERE i.indrelid = conrelid
      AND i.indkey[0] = conkey[1]
  );

Итог

• 1:1 — разделение таблицы для изоляции редко используемых или чувствительных данных.
• 1:M — стандартная связь «владелец → принадлежащие объекты».
• M:M — всегда реализуется через промежуточную таблицу; в ней можно хранить атрибуты самой связи.
• Всегда создавайте индексы на столбцах внешних ключей.
• Явно указывайте ON DELETE поведение — не оставляйте его на усмотрение базы данных.

Потренируйтесь

Спроектируйте связи для системы бронирования отелей: hotels (у отеля много номеров), rooms, guests, bookings (гость бронирует номер на конкретные даты) и удобства номера — wifi, кондиционер, вид на море (у номера их несколько, и одно и то же удобство есть у многих номеров). Какие типы связей понадобятся и как реализовать бронирования?

CREATE TABLE bookings (
  booking_id SERIAL PRIMARY KEY,
  room_id    INT NOT NULL REFERENCES rooms(room_id),
  guest_id   INT NOT NULL REFERENCES guests(guest_id),
  check_in   DATE NOT NULL,
  check_out  DATE NOT NULL,
  CHECK (check_out > check_in)
);

CREATE INDEX idx_bookings_room_id  ON bookings(room_id);
CREATE INDEX idx_bookings_guest_id ON bookings(guest_id);

Вопросы для самопроверки

1. Почему в примере 1:1 первичный ключ таблицы user_profiles одновременно является внешним ключом?
2. Почему промежуточная таблица order_items может использовать составной PK (order_id, product_id), а таблица бронирований bookings из задания выше — нет?
3. В чём разница между RESTRICT и NO ACTION, если оба формально «запрещают» удаление?
4. Почему на столбце внешнего ключа обязательно нужен отдельный индекс, если PostgreSQL и так проверяет ссылочную целостность при вставке?
5. Когда денормализация вроде unit_price в order_items оправдана, а когда — нет?

Закрепите знания о связях на практических задачах в нашем тренажёре SQL.