Первичные и прочие ключи в базе данных

Первичные ключи в базе данныхКлюч — один из главных элементов реляционной модели. Без него все ваши таблицы превращаются в нагромождение строк на перекрестье столбцов.

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

Требование к минимальности означает, что из данного набора (множества) атрибутов следует отсеять те, чьи значения в совокупности не влияют на однозначность определения кортежа.

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

Первичный ключ — один из ключей, выбранный в качестве основного.

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

Много копий сломано в дискуссиях о «правильном» выборе первичных ключей. Действительно, выбор неподходящего набора атрибутов или даже их типов на стадии проектирования принесёт дополнительные проблемы в реализации и сопровождении. Остановимся на нескольких важных моментах.

  1. Выделение первичного ключа сущности — нетривиальная задача. В реальном мире только искусственно созданные объекты имеют простой и однозначный идентификатор, который можно принять за ключ. Например, номер телефона, почтовый индекс или штрих-код товара. В отношении объектов, созданных природой задача не столь проста и не всегда разрешима в принципе. Поэтому на практике таким объектам присваивают искусственный идентификатор, в простейшем случае представляющий собой просто порядковый номер определённого формата. Например, люди в системе социального страхования имеют генерируемый по определённому алгоритму уникальный номер.
  2. Существующие ограничения СУБД накладывают на проектировщика обязанности по предварительной проверке реализуемости создаваемых схем. Если в теории вы можете считать первичным ключом совокупность всех атрибутов отношения, то на практике такой ключ в большинстве случаев не может быть даже создан из-за лимита длины своего значения.
  3. Тип и количество атрибутов в первичном ключе также непосредственно влияют на производительность, размер хранимых данных и стоимость внесения изменений. Например, ключ на основе строкового типа в общем случае будет медленнее такового, на базе числового типа даже при одинаковом размере хранения, потому что при сравнении строк учитывается используемая кодировка, регистр и порядок следования символов данной кодовой страницы. Если же ключ содержит более одного атрибута, то во всех связанных таблицах придётся эти атрибуты дублировать (о внешних ключах мы поговорим ниже).
  4. При реализации приложений унификация типа первичного ключа всех таблиц позволяет существенно сократить время разработки за счёт создания типовых модулей обобщения процедур ввода и обработки данных, проверки связей, обработки ошибок, локализации и т. п.

В итоге, наиболее универсальным вариантом для проектировщика транзакционного приложения является выбор искусственного идентификатора числового типа. В простейшем варианте, 32-разрядный целый тип со знаком позволяет иметь 231 = 2 1 47 483 648 уникальных неотрицательных значений для каждой таблицы, что является достаточным для большинства случаев.

Однако, в условиях распределенной БД, имеющей два и более узла, для подобных ключей возникает проблема генерации глобально-уникальных значений, ведь, например, созданный в БД подразделения №1 клиент с внутренним номером «125» не тот же самый клиент с номером «125», созданным в подразделении №2. На практике эта проблема решается по- разному, вот возможные варианты:

  • для каждой БД выделяется непересекающееся множество значений первичного ключа, например, БД 1 генерирует номера 1, 5, 10 и т. д., а БД 2 - 2, 6, 11...;
  • к генерируемому значению в качестве старших разрядов числа добавляется номер БД, например, 125 в БД 1 превращается в 10000000125 и 20000000125, что несколько увеличивает размер хранимых данных;
  • вместо 32-разрядного целого числа используется так называемый UUID, который по своей внутренней структуре представляет собой и, как правило, хранится в виде 128-разрядного целого числа (подробнее о методе хранения UUID следует смотреть в документации по конкретной СУБД);
  • идентификатор остаётся локально-уникальным, используется таблица соответствий вида «идентификатор в локальной БД — идентификатор в удалённой БД»;
  • смешанные подходы, например, локальный идентификатор для внутренних связей и глобальный, как идентификатор строки при обмене данными.

Каждое из приведённых решений имеет свои преимущества и недостатки. Наиболее общим подходом является использование UUID, однако есть как минимум две причины, по которым такое решение не любят администраторы баз данных:

  • значения трудно читаемы человеком, что затрудняет работу с интерактивными запросами, их выводящими. Сравните, например, ключи «201568» и «2344C060-C338-11E3-9C1A-0800200X9B66»;
  • размер каждого значения ключа — 128 бит, что в 4 раза больше 32-­разрядного целого. Соответственно, для БД с объёмами в миллионы строк увеличивается размер файлов хранения, как и размер индексов, привязанных к значениям кластерного ключа (подробнее см. «Физическая организация памяти»). Для обхода этой проблемы можно объявить ключ на базе UUID некластерным и ввести в таблицу автоинкрементную целочисленную колонку, играющую по сути роль идентификатора строки, создав по ней уникальный кластерный индекс.

Пора подвести некоторые итоги по теме ключей.

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

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

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

Вас заинтересует / Intresting for you:

Что такое базы данных? Назначе...
Что такое базы данных? Назначе... 12810 просмотров Ирина Светлова Mon, 28 Oct 2019, 05:41:34
Перенос корпоративных баз данн...
Перенос корпоративных баз данн... 2775 просмотров Дэн Fri, 27 Sep 2019, 07:52:18
Оптимизация структур баз данны...
Оптимизация структур баз данны... 1652 просмотров Ирина Светлова Sun, 24 Mar 2019, 06:25:41
База данных как объект правово...
База данных как объект правово... 1567 просмотров Денис Wed, 27 Mar 2019, 03:16:24
Войдите чтобы комментировать

apv аватар
apv ответил в теме #9197 5 года 6 мес. назад
Коротенько и по делу. Порадовала картинка - действительно многое объясняет!!!)))))
Doc аватар
Doc ответил в теме #9192 5 года 6 мес. назад
Первичный ключ, первичный ключ... он так силен, он так могуч!
)))