Суррогатные ключи в базах данных: преимущества и применение

Введение

Уникальный идентификатор для сущности в моделируемом мире или объекта в базе данных.

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

Стабильность

Заместительные ключи обычно не изменяются, пока существует запись. Это дает следующие преимущества:

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

Изменения требований

Атрибуты, однозначно идентифицирующие сущность, могут измениться, что может сделать естественные ключи непригодными. Рассмотрим следующий пример:

Имя пользователя в сети сотрудника выбрано в качестве естественного ключа. При слиянии с другой компанией необходимо добавить новых сотрудников. Некоторые из новых имен пользователей приводят к конфликтам, поскольку они были сгенерированы независимо (когда компании были разделены). В таких случаях обычно требуется добавить новый атрибут к естественному ключу (например, столбец, указывающий исходную компанию). При использовании суррогатного ключа необходимо изменить только таблицу, определяющую этот ключ. При использовании естественных ключей придется изменять все таблицы (и, возможно, другое связанное программное обеспечение), использующие этот ключ. В некоторых предметных областях сложно однозначно определить подходящий естественный ключ. Суррогатные ключи позволяют избежать выбора естественного ключа, который может оказаться некорректным.

Выступление

Заместительные ключи обычно имеют компактный тип данных, например, четырехбайтовое целое число. Это позволяет базе данных быстрее выполнять запросы к одному столбцу ключа, чем к нескольким столбцам. Более того, не избыточное распределение ключей обеспечивает полную сбалансированность результирующего B-дерева. Объединение по заместительным ключам также обходится дешевле (требуется сравнение меньшего числа столбцов), чем объединение по составному ключу.

Совместимость

При работе с несколькими системами разработки приложений баз данных, драйверами и системами объектно-реляционного отображения, такими как Ruby on Rails или Hibernate, значительно проще использовать целочисленные или GUID-суррогатные ключи для каждой таблицы вместо естественных ключей, чтобы обеспечить независимость от конкретной системы управления базами данных и сопоставление объектов с записями.

Однородность

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

Валидация

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

Диссоциация

Значения генерируемых суррогатных ключей не имеют отношения к смысловому содержанию данных, хранящихся в строке. При проверке строки, содержащей внешний ключ, ссылающийся на другую таблицу, с использованием суррогатного ключа, смысл строки, соответствующей суррогатному ключу, нельзя определить, исходя из самого ключа. Каждый внешний ключ необходимо соединить, чтобы увидеть связанные с ним данные. Если соответствующие ограничения базы данных не установлены или данные импортированы из устаревшей системы, где не поддерживалась ссылочная целостность, то может существовать значение внешнего ключа, которое не соответствует значению первичного ключа и, следовательно, является недействительным. (В связи с этим, К. Дж. Дейт рассматривает бессмысленность суррогатных ключей как преимущество.) Для обнаружения таких ошибок необходимо выполнить запрос с использованием левого внешнего соединения между таблицей с внешним ключом и таблицей с первичным ключом, отображая оба поля ключа, а также любые поля, необходимые для идентификации записи; все недействительные значения внешнего ключа будут иметь значение NULL в столбце первичного ключа. Необходимость выполнения такой проверки настолько велика, что Microsoft Access фактически предоставляет мастер "Найти несоответствующие записи", который генерирует соответствующий SQL-код после проведения пользователя через диалоговое окно. (Однако, составить такие запросы вручную не слишком сложно.) Запросы "Найти несоответствующие записи" обычно используются в процессе очистки данных при работе с унаследованными данными. Суррогатные ключи не подходят для данных, которые экспортируются и передаются. Особую сложность представляет тот факт, что таблицы из двух идентичных схем (например, тестовой и рабочей) могут содержать записи, эквивалентные с точки зрения бизнеса, но имеющие разные ключи. Это можно смягчить, не экспортируя суррогатные ключи, за исключением временных данных (например, при выполнении приложений, имеющих "живое" подключение к базе данных). Когда суррогатные ключи заменяют естественные ключи, нарушается специфическая ссылочная целостность предметной области. Например, в главной таблице клиентов один и тот же клиент может иметь несколько записей с разными идентификаторами, даже если естественный ключ (комбинация имени клиента, даты рождения и адреса электронной почты) будет уникальным. Чтобы предотвратить это, естественный ключ таблицы не должен быть заменен: он должен быть сохранен в виде уникального ограничения, реализованного как уникальный индекс по полям естественного ключа.

The values of generated surrogate keys have no relationship to the real world meaning of the data held in a row. When inspecting a row holding a foreign key reference to another table using a surrogate key, the meaning of the surrogate key's row cannot be discerned from the key itself. Every foreign key must be joined to see the related data item. If appropriate database constraints have not been set, or data imported from a legacy system where referential integrity was not employed, it is possible to have a foreign key value that does not correspond to a primary key value and is therefore invalid. (In this regard, C. J. Date regards the meaninglessness of surrogate keys as an advantage.) To discover such errors, one must perform a query that uses a left outer join between the table with the foreign key and the table with the primary key, showing both key fields in addition to any fields required to distinguish the record; all invalid foreign key values will have the primary key column as NULL. The need to perform such a check is so common that Microsoft Access actually provides a "Find Unmatched Query" wizard that generates the appropriate SQL after walking the user through a dialog. (It is, however, not too difficult to compose such queries manually.) "Find Unmatched" queries are typically employed as part of a data cleansing process when inheriting legacy data. Surrogate keys are unnatural for data that is exported and shared. A particular difficulty is that tables from two otherwise identical schemas (for example, a test schema and a development schema) can hold records that are equivalent in a business sense, but have different keys. This can be mitigated by not exporting surrogate keys, except as transient data (most obviously, in executing applications that have a "live" connection to the database). When surrogate keys supplant natural keys, then domain specific referential integrity will be compromised. For example, in a customer master table, the same customer may have multiple records under separate customer IDs, even though the natural key (a combination of customer name, date of birth, and e mail address) would be unique. To prevent compromise, the natural key of the table must not be supplanted: it must be preserved as a unique constraint, which is implemented as a unique index on the combination of natural key fields.

Оптимизация запросов

Реляционные базы данных предполагают применение уникального индекса к первичному ключу таблицы. Этот уникальный индекс выполняет две функции: (i) обеспечение целостности сущностей, так как данные первичного ключа должны быть уникальными для каждой строки, и (ii) ускорение поиска строк при выполнении запросов. Поскольку суррогатные ключи заменяют идентифицирующие атрибуты таблицы – естественный ключ, и поскольку именно идентифицирующие атрибуты, скорее всего, будут использоваться в запросах, оптимизатор запросов вынужден выполнять полное сканирование таблицы при обработке типичных запросов. Решением проблемы полного сканирования таблицы является создание индексов по идентифицирующим атрибутам или их комбинациям. Если такие комбинации сами по себе являются кандидатными ключами, индекс может быть уникальным. Однако эти дополнительные индексы потребуют дискового пространства и замедлят операции вставки и удаления данных.

Нормализация

Заместительные ключи могут приводить к появлению дубликатов в естественных ключах. Чтобы предотвратить дублирование, необходимо сохранять роль естественных ключей как уникальных ограничений при определении таблицы с помощью операторов SQL CREATE TABLE или ALTER TABLE ADD CONSTRAINT, если ограничения добавляются позднее.

Моделирование бизнес-процессов

Поскольку суррогатные ключи искусственны, при моделировании бизнес-требований могут возникать недостатки. Бизнес-требования, основанные на естественном ключе, затем необходимо адаптировать к суррогатному ключу. Эффективной стратегией является четкое разделение логической модели (в которой суррогатные ключи не используются) и физической реализации этой модели, чтобы обеспечить корректность и разумную нормализацию логической модели, а также соответствие физической модели логической.

Непреднамеренные предположения

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