13-05-2023
Суррога́тный ключ — понятие теории реляционных баз данных.
Это дополнительное служебное поле, добавленное к уже имеющимся информационным полям таблицы, единственное предназначение которого — служить первичным ключом. Значение этого поля не образуется на основе каких-либо других данных из БД, а генерируется искусственно.
Содержание |
Пусть у нас есть две таблицы — «Люди» и «Квитанции». Человек идентифицируется четырьмя полями — фамилия, имя, отчество, дата рождения. В таблице «Квитанции» указано, кому именно она адресована.
person name1 | name2 | name3 | birth_date | address --------------------------------------------------------- Иванов | Иван | Иванович | 1 янв 1971 | ул. Википедии, 1 bill person_name1 | person_name2 | person_name3 | person_birth_date | date | amount | is_paid ------------------------------------------------------------------------------------------------ Иванов | Иван | Иванович | 1 янв 1971 | 1 фев 2011 | 2000.00 | да Иванов | Иван | Иванович | 1 янв 1971 | 1 мар 2011 | 1000.00 | нет
Добавив к обеим таблицам техническое числовое поле (часто называемое «id»), получаем такую базу.
person id | name1 | name2 | name3 | birth_date | address ----------------------------------------------------------------- 12345 | Иванов | Иван | Иванович | 1 янв 1971 | ул. Википедии, 1 bill id | person_id | date | amount | is_paid -------------------------------------------------- 56789 | 12345 | 1 фев 2011 | 2000.00 | да 67890 | 12345 | 1 мар 2011 | 1000.00 | нет
Теперь квитанции ссылаются не на «Иванова Ивана Ивановича, родившегося 1 января 1971 года», а на «человека № 12345».
Как правило, суррогатный ключ — это просто числовое поле, в которое заносятся значения из возрастающей числовой последовательности. Это может делаться при помощи триггеров или последовательностей. В ряде СУБД (например, PostgreSQL, Sybase, MySQL[1] или SQL Server[2]) существует специальный тип данных для таких полей — числовое поле, в которое при добавлении записи в таблицу автоматически записывается уникальное для этой таблицы числовое значение — т. н. «автоинкремент» (англ. autoincrement) или serial в терминологии PostgreSQL. В последнее время появилась тенденция использования в качестве суррогатных первичных ключей значений UUID в той или иной форме.
Неизменность. Главное достоинство суррогатного ключа состоит в том, что он практически никогда не меняется, поскольку не несёт никакой информации из предметной области и, следовательно, в минимальной степени зависит от изменений, происходящих в ней. Для изменения суррогатного ключа обычно требуются экстраординарные обстоятельства (см. ниже причину «гибкость»).
Атрибуты естественного ключа время от времени могут меняться — например, человек может изменить имя или фамилию, получить новый паспорт взамен потерянного. В этом случае возникает необходимость так называемых «каскадных изменений» — при изменении значения естественного ключа для сохранения ссылочной целостности система должна внести соответствующие изменения во все значения внешних ключей, ссылающихся на изменяемый. В больших базах данных это может приводить к существенным накладным расходам.
Гарантированная уникальность. Далеко не всегда можно гарантировать то, что уникальность естественного ключа не будет скомпрометирована с течением времени. Различные внешние факторы могут приводить к тому, что естественный ключ, ранее уникальный, в новых обстоятельствах может уникальность утратить. Суррогатный ключ свободен от этого недостатка.
Гибкость. Поскольку суррогатный ключ неинформативен, его можно свободно заменять. Допустим, сливаются две фирмы со сходной структурой БД; сотрудник идентифицируется сетевым логином. Чтобы в полученной БД ключ оставался уникальным, приходится добавлять в него дополнительное поле — «из какой фирмы пришёл». В случае с суррогатными ключами достаточно выдать сотрудникам одной из фирм новые ключи.
Эффективность. Как показано в примере выше, ссылки удобнее хранить в виде целых чисел, чем в виде громоздких естественных ключей. К тому же запрос
SELECT * FROM person INNER JOIN bill ON person.id = bill.person_id;
компактнее и быстрее, чем
SELECT * FROM person AS p INNER JOIN bill AS b ON p.name1 = b.person_name1 AND p.name2 = b.person_name2 AND p.name3 = b.person_name3 AND p.birth_date = b.person_birth_date;
Возможность работы с ORM. Реляционно-объектные отображения (ORM) рассчитаны на то, что ссылки на объект являются числами или GUID’ами. Это сильно упрощает разработку ORM.
Уязвимости генераторов ключей. Например, по номерам ключей можно узнать, сколько записей появилось в БД за некоторый период.
Неинформативность. Усложняется ручная проверка БД, появляются INNER JOIN
в местах, в которых без них можно обойтись. По этой причине в полях перечисляемого типа часто используют ключи в виде коротких строк.
athlete country id | name1 | name2 | country_id id | name ---+----------+-------+----------- ----+------- A1 | Иванов | Иван | RUS RUS | Россия А2 | Петренко | Пётр | UKR UKR | Украина A3 | Смит | Джон | USA USA | США
Иногда данные по своей природе подлежат переносу из базы в базу (например, между локальной и централизованной БД, экспериментальным и рабочим вариантом). Принимая новые данные, СУБД должна сгенерировать для них свои суррогатные ключи.
Склоняет администратора пропустить нормализацию. Добавить суррогатные ключи проще, чем правильно, с учётом дублирования и соотношений «1:∞» разбить БД на таблицы и проставить уникальные индексы.
Вопросы оптимизации. СУБД приходится поддерживать два индекса, суррогатный и естественный. Как сказано выше, могут появляться INNER JOIN
там, где они не нужны.
Невольная привязка разработчика к поведению генератора ключей в конкретной СУБД. Например, разработчик может предполагать, что сообщение с меньшим ключом появилось раньше.
Базы данных | |
---|---|
Концепции |
Модель данных • Реляционная (модель • алгебра • Нормальная форма • Ссылочная целостность • БД • СУБД) • Иерархическая модель • Сетевая (модель • СУБД) • Объектно-ориентированная (БД • СУБД) • Транзакция • Журнализация • Секционирование |
Объекты | |
Ключи |
Потенциальный • Первичный • Внешний • Естественный • Суррогатный (искусственный) • Суперключ |
SQL | |
СУБД |
IMS • DB2 • Informix • Oracle Database • Microsoft SQL Server • Adaptive Server Enterprise • Teradata Database • Firebird • PostgreSQL • MySQL • SQLite • Microsoft Access • Visual FoxPro • ЛИНТЕР • CouchDB • MongoDB |
Компоненты |
Суррогатный ключ.