Раздел 35 · SQLite с нуля

Моделирование связей: один-ко-многим

junior~20 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

В прошлом уроке внешний ключ не давал записать заказ несуществующему гостю. Теперь отойдём на шаг и посмотрим на всю базу «Пенки» сверху. Пять таблиц не лежат кучкой: они связаны. Гость делает заказы, в заказе несколько напитков, один напиток попадает в десятки заказов. Этот урок про то, какие бывают связи между таблицами и как каждую записать в схеме.

Карточки в картотеке

Представь картотеку в старой поликлинике. На каждого пациента заведена одна карточка. А посещений у пациента много, и каждое записано на отдельном листке. Чтобы листок не потерялся, на нём пишут номер карточки. По номеру всегда найдёшь, чьё это посещение, а по карточке соберёшь все её листки.

Листок знает свою карточку, а карточка про листки ничего не пишет. Ей и не нужно: достаточно номера на листке.

Так в базе устроена связь один-ко-многим. Карточка это гость в customers, листки это его заказы в orders.

Один-ко-многим: гость и его заказы

Посмотрим, как это записано в схеме:

sqlite> .schema orders
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  staff_id INTEGER NOT NULL REFERENCES staff(id),
  ordered_at TEXT NOT NULL,
  paid TEXT NOT NULL
);

Номер карточки на листке это столбец customer_id. Он хранит id гостя из таблицы customers, а REFERENCES customers(id) объявляет его внешним ключом. Правило простое: внешний ключ всегда лежит на стороне «многих». У заказа один гость, поэтому номер гостя помещается в одну ячейку заказа. У гостя заказов много, и в одну ячейку гостя их не уложить.

Все заказы Маши, у которой id равен 1, находятся по этому столбцу:

sqlite> SELECT id, customer_id, ordered_at FROM orders WHERE customer_id = 1;
╭────┬─────────────┬─────────────────────╮
│ id │ customer_id │     ordered_at      │
╞════╪═════════════╪═════════════════════╡
│  1 │           1 │ 2026-10-01 08:15:00 │
│  4 │           1 │ 2026-10-02 08:20:00 │
│  8 │           1 │ 2026-10-04 08:05:00 │
╰────┴─────────────┴─────────────────────╯

Три заказа, у каждого в customer_id единица. Обрати внимание: у customer_id нет NOT NULL. Это нарочно. Гость без карты лояльности тоже покупает кофе, и у его заказа ссылки на гостя нет вовсе. Связь, которой может и не быть, называют необязательной. А вот бариста у заказа есть всегда, поэтому staff_id объявлен с NOT NULL. Та же связь один-ко-многим, но обязательная.

Почему не сделать наоборот и не хранить у гостя список заказов строкой вроде '1,4,8'? Потому что такую строку SQL не умеет ни проверить, ни соединить с другой таблицей. Каждый новый заказ пришлось бы дописывать в чужую ячейку, а удалённый вырезать оттуда. Одна ячейка, одно значение: это первое правило нормализации.

Многие-ко-многим: заказы и напитки

Теперь связь посложнее. В одном заказе бывает несколько напитков: капучино и эспрессо. И один напиток бывает в многих заказах: капучино брали все утро. Это связь многие-ко-многим.

Внешний ключ на одной из сторон тут не поможет. Положить в заказ drink_id нельзя: напитков в заказе несколько. Положить в напиток order_id тоже нельзя: заказов у напитка много. Поэтому заводят третью таблицу, связующую. В «Пенке» это order_items:

sqlite> .schema order_items
CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(id),
  drink_id INTEGER NOT NULL REFERENCES drinks(id),
  qty INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY (order_id, drink_id)
);

Каждая строка говорит: «в заказе таком-то был напиток такой-то, столько штук». Два внешних ключа смотрят в обе стороны. Одна связь многие-ко-многим превратилась в две связи один-ко-многим: у заказа много строк в order_items, и у напитка тоже.

Вот что входило в заказ номер 5:

sqlite> SELECT order_id, drink_id, qty FROM order_items WHERE order_id = 5;
╭──────────┬──────────┬─────╮
│ order_id │ drink_id │ qty │
╞══════════╪══════════╪═════╡
│        5 │        5 │   2 │
│        5 │        6 │   1 │
╰──────────┴──────────┴─────╯

Два раза напиток 5 и один раз напиток 6. Какие это напитки, по номерам пока не видно: имена лежат в drinks. Собрать их в одну таблицу научимся в следующем уроке.

А в какие заказы попал капучино, у которого id равен 3? Тот же столбец, только с другой стороны:

sqlite> SELECT order_id, qty FROM order_items WHERE drink_id = 3;
╭──────────┬─────╮
│ order_id │ qty │
╞══════════╪═════╡
│        1 │   1 │
│        4 │   1 │
│        8 │   1 │
│       12 │   1 │
╰──────────┴─────╯

Составной первичный ключ

У order_items нет своего столбца id. Строку задаёт пара (order_id, drink_id): один напиток упоминается в одном заказе один раз, а сколько чашек, говорит qty. Такой ключ из нескольких столбцов называют составным. Подробнее о ключах есть теоретический урок.

Составной ключ сам ловит дубли. Попробуем ещё раз добавить напиток 5 в заказ 5:

sqlite> INSERT INTO order_items (order_id, drink_id, qty) VALUES (5, 5, 1);
Error near line 1: UNIQUE constraint failed: order_items.order_id, order_items.drink_id

SQLite отказал: пара (5, 5) уже есть. Если гость попросил ещё один раф, правильно не вставлять вторую строку, а увеличить qty в существующей. Так на вопрос «сколько рафа в заказе 5» всегда один ответ.

Один-к-одному

Третий вид связи встречается реже. Допустим, «Пенка» выпускает пластиковые карты лояльности, и у каждого гостя может быть не больше одной карты. Это связь один-к-одному. Её записывают тем же внешним ключом, только запрещают повторы:

CREATE TABLE loyalty_cards (
  customer_id INTEGER PRIMARY KEY REFERENCES customers(id),
  card_number TEXT NOT NULL UNIQUE,
  issued_on TEXT
);

customer_id здесь и первичный ключ, и внешний. Второй карты одному гостю не выдать: первичный ключ не пустит дубль. Зачем отдельная таблица, если можно добавить столбцы в customers? Когда данные есть не у всех и меняются отдельно от гостя: карт мало, у них свои даты и номера, и пустые ячейки у остальных гостей не нужны.

Как выбрать связь

На практике связь находят вопросами в обе стороны. Сколько заказов у гостя? Много. Сколько гостей у заказа? Один. Значит, один-ко-многим, и ключ ставим в orders. Сколько напитков в заказе? Много. Сколько заказов у напитка? Много. Значит, многие-ко-многим и связующая таблица.

СвязьПример в «Пенке»Как записать
один-ко-многимгость и заказывнешний ключ на стороне «многих»
многие-ко-многимзаказы и напиткисвязующая таблица с двумя внешними ключами
один-к-одномугость и картавнешний ключ с PRIMARY KEY или UNIQUE

Так рисуют схему на бумаге до первой строки кода. Сущности и связи, их обозначения и перевод в таблицы подробно разобраны в уроках про ER-модель и про перевод ER в таблицы.

Попробуй сам

В песочнице посмотри на связи «Пенки» своими глазами: все заказы Димы (у него id равен 4), напитки заказа 10 и схему связующей таблицы.

Найди заказы гостей без карты: у них customer_id пустой, а проверять пустоту нужно через IS NULL, как в уроке про NULL. Потом посмотри .schema staff: у сотрудников тоже есть внешний ключ, и он ссылается на ту же таблицу. Это особый случай, ему посвящён урок про самосоединение.

Проверь себя

Задание

Дальше

Связи нарисованы, но смотреть на номера вместо имён неудобно. В следующем уроке соединяем таблицы через JOIN и видим заказы с именами гостей и названиями напитков.

домашка

Домашка