Пошук уроків, статей та іншого контенту
Порівняйте природні та сурогатні ключі й визначте вимоги до стабільності, унікальності та продуктивності.
Первинний ключ — це стовпець або набір стовпців, які однозначно ідентифікують кожен рядок таблиці.
Первинний ключ має такі властивості:
значення унікальне;
значення не може бути NULL;
значення стабільне протягом життя запису;
зазвичай ключ складається з мінімально необхідної кількості стовпців.
У PostgreSQL первинний ключ автоматично створює унікальний індекс. Це допомагає швидко знаходити рядки за ключем і перевіряти унікальність значень.
CREATE TABLE users (
id bigint PRIMARY KEY,
email text NOT NULL
);У цьому прикладі id — первинний ключ. PostgreSQL не дозволить створити двох користувачів з однаковим id або записом без id.
Природний ключ складається з даних предметної області. Він уже існує в реальному об'єкті або має зрозумілий бізнес-сенс.
Приклади:
код країни UA;
ISBN книги;
номер паспорта;
артикул товару;
код валюти USD.
Наприклад, для таблиці валют трисимвольний код може бути природним ключем:
CREATE TABLE currencies (
code char(3) PRIMARY KEY,
name text NOT NULL
);
INSERT INTO currencies (code, name)
VALUES
('USD', 'Долар США'),
('EUR', 'Євро'),
('UAH', 'Українська гривня');У цьому випадку code має зрозуміле значення і природно ідентифікує валюту.
не потрібно створювати додатковий ідентифікатор;
ключ має бізнес-сенс;
за значенням ключа можна зрозуміти, який об'єкт він представляє;
не потрібне окреме обмеження UNIQUE для самого ключа.
Природний ключ може бути непридатним, якщо його значення:
може змінитися;
залежить від зовнішньої системи;
має складний формат;
є довгим;
не гарантує унікальність у всій базі даних;
може повторно використовуватися після видалення запису.
Наприклад, електронна адреса часто здається хорошим ключем користувача. Але користувач може змінити адресу. Якщо email є первинним ключем, зміна адреси потребує оновлення всіх зовнішніх ключів, які на нього посилаються.
Також правила унікальності email можуть змінюватися. Наприклад, система може почати обробляти великі й малі літери або адреси з різними доменами за новими правилами.
Сурогатний ключ — це штучний ідентифікатор, який не має бізнес-сенсу. Він створюється спеціально для бази даних.
Найчастіше як сурогатний ключ використовують:
ціле число, яке генерує база даних;
UUID.
Приклад таблиці із числовим сурогатним ключем:
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
display_name text NOT NULL
);id не описує користувача і не залежить від його властивостей. Він лише ідентифікує рядок.
Поле email у цьому прикладі не є первинним ключем, але має обмеження UNIQUE. Це означає:
користувачі ідентифікуються через стабільний id;
email не може повторюватися;
email можна змінити, не змінюючи ідентифікатор користувача.
Розглянемо таблиці користувачів і замовлень:
DROP TABLE IF EXISTS customer_orders;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
full_name text NOT NULL
);
CREATE TABLE customer_orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id),
total_cents integer NOT NULL CHECK (total_cents >= 0)
);
INSERT INTO customers (email, full_name)
VALUES ('olena@example.com', 'Олена Коваль');
INSERT INTO customer_orders (customer_id, total_cents)
SELECT id, 12500
FROM customers
WHERE email = 'olena@example.com';
-- Email змінився, але зв'язок із замовленням залишився дійсним
UPDATE customers
SET email = 'olena.koval@example.com'
WHERE id = 1;
SELECT
customers.id AS customer_id,
customers.email,
customer_orders.total_cents
FROM customers
JOIN customer_orders
ON customer_orders.customer_id = customers.id;Зв'язок таблиць побудований через customer_id, який посилається на customers.id. Зміна email не впливає на замовлення, оскільки зовнішній ключ використовує стабільний ідентифікатор.
У реальному застосунку не варто покладатися на те, що перший вставлений користувач завжди має id = 1. Значення ідентифікатора отримують через INSERT ... RETURNING, параметри застосунку або інший контрольований механізм.
Для нових таблиць PostgreSQL рекомендується використовувати колонку ідентичності:
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);Тепер PostgreSQL сам генерує значення id:
INSERT INTO products (name)
VALUES ('Клавіатура'), ('Миша')
RETURNING id, name;Для поля GENERATED ALWAYS AS IDENTITY база зазвичай не дозволяє явно вказувати значення id. Це зменшує ризик випадкового конфлікту з автоматичною генерацією.
Згенеровані числові ключі не гарантують послідовність без пропусків. Пропуски можуть виникати через скасовані транзакції, помилки або інші особливості генерації значень. Первинний ключ має бути унікальним, але не зобов'язаний бути лічильником без пропусків.
Числовий ключ на основі bigint компактний і зручний:
CREATE TABLE invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount_cents integer NOT NULL
);Переваги:
займає мало місця;
ефективний для індексів і з'єднань;
простий для налагодження;
добре підходить для більшості внутрішніх таблиць.
UUID корисний, коли ідентифікатори потрібно створювати незалежно в різних системах або не потрібно розкривати послідовні числові значення:
CREATE TABLE api_tokens (
id uuid PRIMARY KEY,
token_hash text NOT NULL UNIQUE
);Значення UUID має більший розмір, ніж bigint, тому індекси та зовнішні ключі можуть займати більше місця. Вибір UUID має бути обґрунтований вимогами системи, а не лише популярністю цього формату.
Перед використанням природного ключа перевірте його за такими питаннями:
Чи гарантована унікальність значення?
Чи може значення змінитися?
Чи є ця унікальність гарантованою бізнес-правилами, а не лише поточним станом даних?
Чи коротке й просте значення ключа?
Чи не залежить ключ від зовнішньої системи?
Чи буде таке саме правило унікальності актуальним через кілька років?
Наприклад, артикул товару може бути природним ключем, якщо бізнес гарантує, що:
артикул завжди унікальний;
артикул ніколи не змінюється;
артикул не перевидається для іншого товару.
Якщо хоча б одна з цих умов сумнівна, безпечніше використати сурогатний ключ, а артикул зробити окремим полем із UNIQUE.
Іноді унікальність визначається кількома стовпцями. Наприклад, одна книга може мати різні ціни в різних магазинах:
CREATE TABLE store_prices (
store_id bigint NOT NULL,
product_id bigint NOT NULL,
price_cents integer NOT NULL CHECK (price_cents >= 0),
PRIMARY KEY (store_id, product_id)
);Тут пара (store_id, product_id) є складеним природним ключем для таблиці цін.
Складений ключ доречний, коли сама комбінація значень є природним і стабільним ідентифікатором зв'язку. Якщо ж ключ має багато стовпців або часто використовується в інших таблицях, він ускладнює зовнішні ключі та запити. У такій ситуації можна додати сурогатний id, а комбінацію зберегти як окреме обмеження UNIQUE.
Первинний ключ використовується в індексах, пошуку та зовнішніх ключах. Тому важливі його розмір і структура.
Зазвичай:
bigint компактніший за UUID;
коротші ключі зменшують розмір індексів;
менші індекси краще поміщаються в пам'яті;
прості числові ключі зручні для з'єднань таблиць;
довгий текстовий ключ може збільшити розмір усіх індексів і зовнішніх ключів.
Для невеликої або звичайної прикладної системи bigint GENERATED ALWAYS AS IDENTITY часто є простим і ефективним вибором. Але продуктивність не повинна бути єдиним критерієм: стабільність і правильність ідентифікації важливіші.
Для більшості таблиць можна використовувати таку стратегію:
Додайте стабільний сурогатний первинний ключ, наприклад bigint з ідентичністю.
Для бізнес-полів, які мають бути унікальними, додайте UNIQUE.
Використовуйте природний ключ як первинний лише тоді, коли він короткий, стабільний і гарантовано унікальний.
Не використовуйте як ключ значення, яке користувач може змінити.
Не додавайте складений ключ, якщо він зробить зовнішні зв'язки надто складними.
Вибирайте UUID, коли його переваги потрібні архітектурі системи.
Приклад поєднання сурогатного ключа та бізнес-унікальності:
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku text NOT NULL UNIQUE,
name text NOT NULL
);id є технічним ідентифікатором, а sku — унікальним бізнесовим значенням. Якщо формат або правила SKU зміняться, зв'язки між таблицями все одно залишаться прив'язаними до id.
Email може змінитися, бути введений з помилкою або оброблятися за іншими правилами після зміни вимог.
Краще використовувати окремий id, а для email додати UNIQUE.
UNIQUEЯкщо поле повинно бути унікальним лише за задумом розробника, але не має обмеження в базі, помилка в застосунку може створити дублікати.
Правило унікальності потрібно зафіксувати в схемі:
CREATE TABLE coupons (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code text NOT NULL UNIQUE
);Номер телефону, назва компанії, адреса або код, який контролює зовнішня система, можуть змінитися. Такі поля не варто використовувати як єдиний ідентифікатор без перевірки вимог стабільності.
Значення id можуть мати пропуски. Це нормально для первинного ключа. Якщо потрібна окрема послідовна нумерація документів, її треба проєктувати як окреме бізнес-правило, а не плутати з ідентифікатором рядка.
Складений ключ із кількох текстових стовпців збільшує зовнішні ключі та ускладнює запити. Якщо він не є природним і стабільним для самої сутності, варто розглянути сурогатний ключ.
Первинний ключ має бути унікальним, непорожнім і стабільним.
Природний ключ походить із бізнес-даних.
Сурогатний ключ створюється базою даних і не має бізнес-сенсу.
Природний ключ підходить лише тоді, коли його унікальність і незмінність гарантовані.
Для більшості сутностей зручно використовувати bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY.
Бізнесову унікальність можна зберегти окремим обмеженням UNIQUE.
Чим коротший ключ, тим компактніші індекси та зовнішні ключі.
UUID варто вибирати тоді, коли система справді потребує незалежної генерації ідентифікаторів або іншої властивості UUID.