Суррогатный ключ

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

Определение

Существует как минимум два определения суррогата:

Суррогатная мать (1) – Холл, Оулетт и Тодд (1976)

Суррогат представляет собой сущность во внешнем мире. Заместитель генерируется системой внутри, но, тем не менее, виден пользователю или приложению.

Суррогатное материнство (2) – Виринга и Де Йонге (1991)

Суррогат представляет объект в самой базе данных. Заместитель генерируется системой внутри и невидим для пользователя или приложения.

Определение «Суррогат (1)» относится к модели данных, а не к модели хранения , и используется на протяжении всей статьи. См. Дату (1998 г.).

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

Хотя Холл и др. (1976) об этом ничего не говорят, другие утверждали, что суррогатная мать должна обладать следующими характеристиками:

• значение никогда не используется повторно
• значение генерируется системой
• значение не может манипулироваться пользователем или приложением
• значение не содержит смыслового значения
• значение не видно пользователю или приложению
• значение не состоит из нескольких значений из разных доменов.

на

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

Суррогатный ключ часто представляет собой последовательный номер (например, «столбец идентификации» Sybase или SQL Server , столбец PostgreSQL или Informix). serial, Oracle или SQL-сервер SEQUENCE или столбец, определенный с помощью AUTO_INCREMENT в MySQL ). Некоторые базы данных предоставляют UUID / GUID в качестве возможного типа данных для суррогатных ключей (например, PostgreSQL UUID или SQL-сервер UNIQUEIDENTIFIER).

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

Во временной базе данных необходимо различать суррогатный ключ и бизнес-ключ . Каждая строка будет иметь как бизнес-ключ, так и суррогатный ключ. Суррогатный ключ идентифицирует одну уникальную строку в базе данных, бизнес-ключ идентифицирует одну уникальную сущность моделируемого мира. Одна строка таблицы представляет собой интервал времени, содержащий все атрибуты объекта за определенный промежуток времени. Эти срезы отражают весь жизненный цикл одного предприятия. Например, таблица «СотрудникиКонтракты» может содержать временную информацию для отслеживания рабочего времени по контракту. Бизнес-ключ для одного контракта будет одинаковым (неуникальным) в обеих строках, однако суррогатный ключ для каждой строки уникален.

Суррогатный ключ	БизнесКей	Имя сотрудника	Рабочих часов в неделю	Строкавалидот	Роувалидто
1	БОС0120	Джон Смит	40	2000-01-01	2000-12-31
56	P0000123	Боб Браун	25	1999-01-01	2011-12-31
234	БОС0120	Джон Смит	35	2001-01-01	2009-12-31

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

Некоторые из альтернативных названий («генерируемый системой ключ») описывают способ генерации новых суррогатных значений, а не природу суррогатной концепции.

Подходы к созданию суррогатов включают в себя:

• Универсально уникальные идентификаторы (UUID)
• Глобальные уникальные идентификаторы (GUID)
• Идентификаторы объектов (OID)
• Sybase или SQL Server Столбец идентификаторов IDENTITY ИЛИ IDENTITY(n,n)
• Оракул SEQUENCE, или GENERATED AS IDENTITY (начиная с версии 12.1)
• SQL-сервер SEQUENCE (начиная с SQL Server 2012)
• PostgreSQL или IBM Informix Серийный номер
• MySQL AUTO_INCREMENT
• SQLite INTEGER PRIMARY KEY (если AUTOINCREMENT используется, это предотвратит повторное использование номеров, которые уже использовались, но доступны)
• Тип данных AutoNumber в Microsoft Access
• AS IDENTITY GENERATED BY DEFAULT в IBM Db2 и PostgreSQL .
• Столбец идентификаторов (реализован в DDL ) в Teradata
• Таблица последовательности, когда последовательность рассчитывается с помощью процедуры и таблицы последовательности с полями: id, последовательностьName, последовательностьValue и приращение значения.

Преимущества

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

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

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

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

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

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

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

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

Производительность

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

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

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

Единообразие

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

Проверка

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

Недостатки

Диссоциация

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

Чтобы обнаружить такие ошибки, необходимо выполнить запрос, который использует левое внешнее соединение между таблицей с внешним ключом и таблицей с первичным ключом, показывая оба ключевых поля в дополнение к любым полям, необходимым для различения записи; все недопустимые значения внешнего ключа будут иметь столбец первичного ключа со значением NULL. Необходимость выполнения такой проверки настолько распространена, что Microsoft Access фактически предоставляет мастер «Найти несоответствующий запрос», который генерирует соответствующий SQL после прохождения пользователя через диалоговое окно. (Однако такие запросы не так уж и сложно составить вручную.) Запросы «Найти несовпадающие» обычно используются как часть процесса очистки данных при наследовании устаревших данных.

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

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

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

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

Однако эти дополнительные индексы будут занимать дисковое пространство и замедлять вставку и удаление.

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

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

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

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

Непреднамеренное разглашение

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

Есть несколько способов преодолеть эту проблему:

• увеличить порядковый номер на случайную величину;
• сгенерируйте случайный ключ, например UUID .

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

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

См. также

• Естественный ключ
• Идентификатор объекта
• Постоянный идентификатор объекта

Ссылки

Источники

• Нейссен, GM (1976). Моделирование в системах управления базами данных . Паб Северной Голландии. Ко. ISBN  0-7204-0459-2 .
• Энглс, Р.В.: (1972), Учебное пособие по организации базы данных , Ежегодный обзор автоматического программирования, Том 7, Часть 1, Pergamon Press, Оксфорд, стр. 1–64.
• Лангефорс, Б. (1968). Элементарные файлы и записи элементарных файлов , Материалы файла 68, Международного семинара IFIP/IAG по организации файлов, Амстердам, ноябрь, стр. 89–96.
• Виринга, Роэл; де Йонге, Вибрен (1991). Идентификация объектов и ролей - Еще раз об идентификаторах объектов (PDF) (Технический отчет). Технический отчет / Факультет математики и информатики. Том. ИР-267. Амстердам: Факультет математики и информатики, Свободный университет. CiteSeerX   10.1.1.16.3195 . Проверено 2 декабря 2022 г.
• Дата, CJ (1998). «Главы 11 и 12». Написания по реляционным базам данных, 1994–1997 гг . Аддисон-Уэсли. ISBN  0201398141 .
• Картер, Брек. «Интеллектуальные и суррогатные ключи» . Проверено 3 декабря 2006 г.
• Ричардсон, Ли. «Создайте катастрофу данных: избегайте уникальных индексов – (ошибка 3 из 10)» . Архивировано из оригинала 30 января 2008 г. Проверено 19 января 2008 г.
• Беркус, Джош. «Суп из базы данных: первичный кейвил, часть I» . Проверено 3 декабря 2006 г.