2learn

Ключи и связи

Что это такое

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

Почему это важно

Выбор ключа фактически необратим. Смена первичного ключа после того, как таблица выросла, означает перезапись и самой таблицы, и каждой ссылки на неё, обычно с простоем. Естественный ключ вроде адреса электронной почты или номера заказа выглядит удобным, пока значение не придётся изменить; однако тогда изменение распространяется на каждую таблицу, где оно хранилось. Эти же решения определяют и то, поддерживаются ли связи базой данных или лишь подразумеваются приложением.

Ключевые идеи

Сгенерированный идентификатор — устойчивая идентичность

Произвольное значение, не несущее смысла, нельзя обесценить изменением в бизнесе, поэтому ссылки на него остаются действительными. Целые числа компактны и упорядочены; случайные идентификаторы избегают коллизий между системами и скрывают число записей, но стоят размера и локальности. Оба варианта защитимы, а изменяемое бизнес-значение в роли первичного ключа — нет.

id           bigint generated     -> компактный, последовательный
public_id    uuid generated        -> непрозрачный, безопасен для показа
email        text unique          -> ограничивайте уникальность, не идентифицируйте

В интерфейс выносите безопасный идентификатор

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

У каждой связи есть форма, и форма решает схему

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

один клиент   -> много заказов     orders.customer_id
много заказов <-> много товаров    order_items(order_id, product_id, quantity)

Поддержите связь, затем решите, что значит удаление

Объявленный внешний ключ не даёт ссылке указывать на несуществующую строку. То же объявление говорит и о том, что происходит при удалении ссылаемой строки: отказать, удалить зависимые или очистить ссылку. За каждым вариантом стоит свой смысл — строка заказа без заказа бессмысленна, а клиент без заказов осмыслен, — и выбрать это при объявлении яснее, чем обрабатывать в коде.

Естественная уникальность — в ограничении, а не в проверке

Адрес электронной почты должен быть уникальным, и сказать об этом нужно ограничением уникальности, потому что только оно действует при конкурентном доступе. Два потока приложения, которые сначала проверяют, а потом оба вставляют, дадут дубликат, как бы тщательна ни была проверка. Ограничение превращает гонку в ошибку, которую приложение может обработать.

Составные ключи полезны и легко ошибочны

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

Практика

Выберите ключи для трёх таблиц

Спроектируйте ключи для клиента, заказа и строки заказа. Для каждого решите, сгенерирована идентичность или естественна, и нужен ли отдельный публичный идентификатор.

Готово, когда: вы можете назвать значение, которое когда-либо изменится, и подтвердить, что ни одна ссылка от него не зависит.

Выразите связь «многие ко многим»

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

Готово, когда: вставка одной и той же пары товара и тега дважды отклоняется базой, а тег без товаров всё ещё может существовать.

Частые ошибки

  • Изменяемое бизнес-значение в роли первичного ключа — при изменении ломается каждая ссылка; исправить: используйте сгенерированный идентификатор, а бизнес-значение ограничьте уникальностью.
  • Последовательный ключ в публичном интерфейсе — объём раскрыт, записи можно перебрать; исправить: используйте отдельный непрозрачный идентификатор.
  • Хранение связи «многие ко многим» списком в одной строке — запросы и ограничения становятся неудобными; исправить: добавьте таблицу-связку.
  • Проверка уникальности только в коде приложения — конкурентные вставки дают дубликаты; исправить: объявите ограничение уникальности.
  • Связи оставлены неподдерживаемыми — копятся строки-сироты; исправить: объявите внешние ключи и осознанно выберите поведение при удалении.

Что читать дальше

Разобранные примеры

Файлы cookie аналитики ставятся только с вашего согласия. До вашего выбора ничего не загружается и никуда не отправляется.