Ключи в БД

Что такое ключ в базе данных

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

Два ключа, которые нужны каждый день:

• Первичный ключ (PRIMARY KEY) — идентификатор строки внутри своей таблицы.
• Внешний ключ (FOREIGN KEY) — ссылка на строку в другой таблице.

Первичный ключ

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

CREATE TABLE users (
    id         bigserial PRIMARY KEY,     -- первичный ключ
    email      text NOT NULL UNIQUE,      -- потенциальный ключ
    name       text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

Свойства первичного ключа:

• Уникален — СУБД не даст вставить дубль.
• Не NULL — обязателен для каждой строки.
• Один на таблицу — других уникальных колонок может быть сколько угодно, но PRIMARY KEY только один.
• Неизменен — на него ссылаются другие таблицы, менять его значение не следует.
• Автоматически индексируется — под него создаётся индекс, поэтому поиск по нему быстрый.

Внешний ключ

Внешний ключ (foreign key) — колонка, значения которой обязаны существовать в первичном ключе другой таблицы. Это ссылка, за соблюдением которой следит сама база.

CREATE TABLE orders (
    id      bigserial PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    total   numeric(10,2) NOT NULL
);

INSERT INTO orders (user_id, total) VALUES (99999, 100);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- DETAIL: Key (user_id)=(99999) is not present in table "users".

В этом весь смысл: заказ на несуществующего пользователя не появится в базе никогда — ни из-за бага в коде, ни из-за ручного INSERT в консоли, ни из-за гонки между запросами. Проверка в приложении так не работает: она действует только в том коде, где её написали.

Ограничение, которое обеспечивает внешний ключ, называется ссылочной целостностью.

ON DELETE и ON UPDATE

Поведение при удалении строки, на которую ссылаются:

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

Отдельная практическая деталь: PostgreSQL индексирует первичный ключ автоматически, но не создаёт индекс на колонке внешнего ключа. Без него JOIN по orders.user_id и проверка при удалении пользователя пойдут полным сканом — индекс на внешние ключи создают руками.

Виды ключей

Составной ключ

Ключ из нескольких колонок — типичен для таблиц связи «многие ко многим»:

CREATE TABLE order_items (
    order_id   bigint REFERENCES orders(id) ON DELETE CASCADE,
    product_id bigint REFERENCES products(id),
    qty        int NOT NULL CHECK (qty > 0),
    PRIMARY KEY (order_id, product_id)   -- один товар в заказе один раз
);

Суррогатный или естественный

Естественный ключ берётся из предметной области: email пользователя, ИНН компании, артикул товара. Суррогатный — искусственный id, не значащий ничего.

Практика склоняется к суррогатным, и причина простая: «уникальное и неизменное» в реальном мире почти не встречается. Email пользователь меняет, у компании меняется ИНН при реорганизации, артикул переназначают. А поменять естественный ключ — значит обновить его во всех таблицах, которые на него ссылаются.

Выбор между bigserial и uuid: последовательный id компактнее и лучше ложится в B-tree, но предсказуем снаружи и мешает при слиянии данных из разных источников или шардировании. UUID решает это ценой размера и случайной вставки в индекс.

Связанные термины

• Базы данных — где живут ключи и ограничения
• SQL — JOIN работает через ключи
• Индексы в БД — почему ключи быстрые
• Реляционные и NoSQL БД — модель, построенная на ключах
• PostgreSQL — примеры синтаксиса выше
• Миграции БД — как ключи добавляют к живой таблице