Сурогатний ключ також штучний ключ ідентифікатор сутності згенерований ключ послідовний номер непідтверджений ключ техні

Сурогатний ключ (також штучний ключ, ідентифікатор сутності, згенерований ключ, послідовний номер, непідтверджений ключ, технічний ключ або довільний унікальний ідентифікатор) у базах даних — унікальний ідентифікатор сутності модельованого світу чи об'єкта бази даних. Сурогатний ключ не отримується з даних застосунку, на відміну від природного (чи бізнес) ключа.

Визначення

Існують принаймні два визначення сурогату:

Сурогат (1) — Галл, Оулетт і Тодд (1976): Сурогат представляє сутність у зовнішньому світі. Сурогат генерується всередині системи, але все ж є видимим для користувача чи застосунку.
Сурогат (2) — Вайрінга та Де Йонге (1991): Сурогат представляє об'єкт у базі даних, яким він є. Сурогат генерується всередині системи і не є видимим для користувача чи застосунку.

Визначення сурогата (1) стосується радше моделі даних, а ніж ^[en] і використовується далі в цій статті.

Важлива різниця між сурогатним і первинним ключем залежить від того, якою є база даних: поточною чи хронологічною. Оскільки поточна база даних зберігає лише на даний момент валідні дані, то в ній наявна точна відповідність між сурогатом у модельованому світі та первинним ключем бази даних. У цьому випадку сурогат може бути використано як первинний ключ, породжуючи термін сурогатний ключ. У хронологічній базі даних, однак, існує відношення багато-до-одного між первинними ключами та сурогатом. Оскільки кожному сурогату можуть відповідати декілька об'єктів бази даних, то він не може використовуватися як первинний ключ; а отже, необхідний інший атрибут, який би разом із сурогатом унікально ідентифікував би кожний об'єкт.

Хоча Галл та ін. (1976) нічого про це не каже, решта стверджують, що сурогати повинні мати такі характеристики:

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

Сурогати на практиці

У поточній базі даних сурогатний ключ може бути первинним, що згенерований системою керування базами даних і не отримується з будь-яких даних застосунку в базі. Єдиним призначенням сурогатного ключа є діяти як первинний. Також є можливим існування сурогатного ключа на додачу до згенерованого базою даних UUID (наприклад, HR-номер кожного працівника, що відрізняється від його UUID).

Сурогатний ключ часто є послідовним числом (наприклад, у ^[en] чи SQL Server — «стовпчик-ідентифікатор», у PostgreSQL чи Informix — serial, в Oracle чи SQL Server — SEQUENCE, чи стовпчик, визначений з AUTO_INCREMENT у MySQL). Деякі бази даних надають UUID/GUID як можливий тип даних для сурогатних ключів (наприклад, UUID у PostgreSQL або UNIQUEIDENTIFIER в SQL Server).

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

У хронологічній базі даних важливо розрізняти сурогатні та бізнес-ключі. Кожен рядок матиме і бізнес-ключ, і сурогатний ключ. Сурогатний ключ ідентифікує один унікальний рядок у базі даних, а бізнес-ключ — одну унікальну сутність модельованого світу. Один рядок таблиці подає частку часу, що має всі атрибути сутності у визначений проміжок часу. Ці частки зображують увесь проміжок часу однієї бізнес-сутності. Наприклад, таблиця EmployeeContracts може мати хронологічну інформацію для відстеження контрактних робочих годин. Бізнес-ключ для одного контракту буде ідентичним (неунікальним) в обох рядках, але сурогатний ключ для кожного рядка унікальний.

SurrogateKey	BusinessKey	EmployeeName	WorkingHoursPerWeek	RowValidFrom	RowValidTo
1	BOS0120	Джон Сміт	40	2000-01-01	2000-12-31
56	P0000123	Боб Браун	25	1999-01-01	2011-12-31
234	BOS0120	Джон Сміт	35	2001-01-01	2009-12-31

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

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

Підходи до генерації сурогатів включають:

Universally Unique Identifiers (UUID)
Globally Unique Identifiers (GUID)
Ідентифікатори об'єкта (OID)
Стовпчик-ідентифікатор в ^[en] чи SQL Server IDENTITY чи IDENTITY(n, n)
SEQUENCE чи GENERATED AS IDENTITY в Oracle, починаючи з версії 12.1
SEQUENCE в SQL Server, починаючи з версії 2012
SERIAL у PostgreSQL або IBM Informix
AUTO_INCREMENT у MySQL
AUTOINCREMENT у SQLite
Тип даних «Лічильник» (англ. AutoNumber, рос. Счётчик) у Microsoft Access
AS IDENTITY GENERATED BY DEFAULT в IBM DB2
Стовпчик-ідентифікатор (реалізований у DDL) у Teradata
Послідовність таблиці, коли послідовність обчислюється процедурою та таблицею послідовності з полями: id, sequenceName, sequenceValue та incrementValue

Переваги

Незмінність

Сурогатні ключі не змінюються, поки існує рядок. Це має наступні переваги:

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

Зміни вимог

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

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

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

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

Продуктивність

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

Сумісність

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

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

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

Валідація

Можливо спроектувати ключі-значення, що відповідають загальновідомим шаблонам чи структурам, які можуть бути автоматично верифіковані. Наприклад, ключі, призначені для використання у деяких стовпчиках деяких таблиць, може бути спроектовано «інакше, ніж» ті, які призначені для іншого стовпчика чи таблиці, таким чином спрощуючи виявлення помилок застосунку, за яких ключі можуть бути переплутані. Проте, ця характеристика сурогатних ключів ніколи не має використовуватися для керування будь-якою логікою застосунку, адже вона порушує принципи нормалізації баз даних.

Недоліки

Дизасоціація

Значення згенерованих сурогатних ключів не стосуються реального сенсу даних у рядку. При перевірці рядка з зовнішнім ключем на іншу таблицю за допомогою сурогатного ключа значення останнього не може бути розрізнене на основі самого ключа. Кожен зовнішній ключ повинен бути з'єднаний для отримання пов'язаного елементу даних. Це також ускладнює аудит^[] через неочевидність некоректних даних.

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

Оптимізація запитів

Реляційні бази даних припускають застосування унікального індексу як первинного ключа таблиці. Унікальний індекс слугує двом цілям: (1) забезпеченню цілісності сутностей, оскільки дані первинного ключа повинні бути унікальними по рядках, та (2) швидкому пошуку рядків при запитах. Оскільки сурогатні ключі замінюють атрибути-ідентифікатори таблиці — природні ключі — які є найзапитуванішими, то оптимізатор запитів змушений сканувати всю таблицю при задоволенні подібних запитів. Засобом повного сканування таблиці є застосування індексів на атрибути-ідентифікатори чи їх множини. Коли такі множини є потенційними ключами, індекс може бути унікальним.

Ці додаткові індекси, однак, займають дисковий простір і сповільнюють вставки та вилучення.

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

Сурогатні ключі можуть призвести до повторюваних значень у будь-яких природних ключах. Унеможливлення таких дублікатів є частиною реалізації системи баз даних.

Моделювання бізнес-процесів

Оскільки сурогатні ключі неприродні, то під час моделювання бізнес-вимог можуть з'являтися вади. Бізнес-вимоги, покладаючись на природний ключ, згодом потребують трансляції в сурогатний. Однією зі стратегій є чітке розмежування логічної моделі (в якій немає сурогатних ключів) та її фізичної реалізації для забезпечення того, що логічна модель коректна та достатньо нормалізована, а також того, що фізична модель є коректною реалізацією логічної.

Випадкове розкриття

Власницька інформація може витікати у разі використання генераторів послідовних ключів. Шляхом віднімання попередньо та нещодавно згенерованих послідовних ключів можна з'ясувати кількість вставлених рядків за деякий період часу. Це може викрити, наприклад, кількість транзакцій або нових акаунтів за період. Існують кілька способів подолання цієї проблеми:

збільшення послідовного числа на довільне значення
генерування випадкового ключа на кшталт UUID

Випадкові припущення

Послідовно згенеровані сурогатні ключі можуть припускати, що події з більшим значенням сталися після подій з меншим. Це не обов'язково так, оскільки такі значення не гарантують часову послідовність, адже можливі відмови у вставках і залишення розривів, які можуть бути заповнені пізніше. Якщо хронологія є важливою, то дата й час повинні записуватися окремо.

Див. також

Примітки

https://elib.lntu.edu.ua/sites/default/files/elib_upload/ЕНП_Саварин_Лепкий/teoretic/lec5.html
Галл, П. А. В.; Оулетт, Дж.; Тодд, С. Дж. П. (1976). Relations and Entities. У Ніжссен, Джерард Марія (ред.). Modelling in Data Base Management Systems. Північна Голландія.
Дейт (1998)
UUID. PostgreSQL (англійською) .{{}}: Обслуговування CS1: Сторінки з параметром url-status, але без параметра archive-url ()
UNIQUEIDENTIFIER. MSDN (англійською) .{{}}: Обслуговування CS1: Сторінки з параметром url-status, але без параметра archive-url ()
Create Table. Oracle Database (англійською) . Oracle Corporation.{{}}: Обслуговування CS1: Сторінки з параметром url-status, але без параметра archive-url ()
Create Sequence (Transact-SQL). MSDN (англійською) .{{}}: Обслуговування CS1: Сторінки з параметром url-status, але без параметра archive-url ()

[1] ttps://elib.lntu.edu.ua/sites/default/files/elib_upload/ЕНП_Саварин_Лепкий/teoretic/lec5.html

[2] Галл, П. А. В.; Оулетт, Дж.; Тодд, С. Дж. П. (1976). Relations and Entities. У Ніжссен, Джерард Марія (ред.). Modelling in Data Base Management Systems. Північна Голландія.

[date-1998-3] Дейт (1998)

[postgresql-uuid-4] UUID. PostgreSQL (англійською) .{{}}: Обслуговування CS1: Сторінки з параметром url-status, але без параметра archive-url ()

[uniqueidentifier-5] UNIQUEIDENTIFIER. MSDN (англійською) .{{}}: Обслуговування CS1: Сторінки з параметром url-status, але без параметра archive-url ()

[oracle-6] Create Table. Oracle Database (англійською) . Oracle Corporation.{{}}: Обслуговування CS1: Сторінки з параметром url-status, але без параметра archive-url ()

[sql-server-2012-7] Create Sequence (Transact-SQL). MSDN (англійською) .{{}}: Обслуговування CS1: Сторінки з параметром url-status, але без параметра archive-url ()