Ключи: потенциальные, первичные и внешние

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

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

Зачем нужны ключи

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

Суперключ и потенциальный ключ

Начнём сверху. Суперключ — любой набор атрибутов, который уникально идентифицирует кортеж. В таблице клиентов Clients(id, email, name, phone), где уникальны и id, и email, суперключами будут {id}, {email}, {id, name}, {id, email, phone} и так далее — любой набор, содержащий уникальный атрибут.

Потенциальный ключ — это минимальный суперключ: из него нельзя выкинуть ни одного атрибута, не потеряв уникальность. {id, name} — суперключ, но не потенциальный: name лишний, ведь {id} уже уникален. А вот {id} и {email} — два потенциальных ключа таблицы клиентов: оба минимальны и уникальны.

Потенциальных ключей у отношения может быть несколько. Атрибут, входящий хотя бы в один потенциальный ключ, называется первичным (prime) атрибутом; остальные — непервичными. Это различие нам критически понадобится в нормализации (3NF), так что запомните его.

Ключ — свойство схемы, а не данных

Тонкий, но важный момент: является ли набор атрибутов ключом, определяется смыслом данных, а не их текущим содержимым. Допустим, в таблице клиентов сейчас у всех разные имена. Значит ли это, что name — потенциальный ключ? Нет: завтра придёт второй «Александр Иванов», и уникальность сломается. name не ключ, потому что предметная область допускает тёзок, даже если их пока нет. И наоборот: email — ключ не потому, что сейчас все письма разные, а потому, что система гарантирует уникальность регистрации. Это то же различение «схема против состояния» из первого раздела: ключ — утверждение о всех возможных состояниях. Поэтому выводить ключи «по данным» («сейчас уникально, значит, ключ») — методическая ошибка; ключи диктует смысл.

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

Из потенциальных ключей проектировщик выбирает один главный — первичный ключ (primary key). Именно на него будут ссылаться другие таблицы, и именно он по умолчанию обязан быть уникальным и не содержать NULL (целостность сущностей — следующий урок). Остальные потенциальные ключи становятся альтернативными. В таблице клиентов мы можем назначить первичным id, а email оставить альтернативным (его уникальность всё равно стоит обеспечить ограничением UNIQUE).

Как выбирать? Хорошая практика — предпочитать короткий, стабильный, никогда не меняющийся ключ. email формально уникален, но человек может его сменить, а на ключ ссылаются другие таблицы — менять его дорого. Поэтому часто вводят синтетический (суррогатный) ключ id, который не несёт смысла и потому никогда не меняется.

Зачем вообще нужен первичный ключ

Может возникнуть вопрос: если отношение и так не содержит дубликатов, зачем явно объявлять первичный ключ? Причин несколько, и они практические. Во-первых, «нет полных дубликатов строк» и «есть набор атрибутов, по которому можно адресовать строку» — разные вещи; первичный ключ гарантирует именно второе, давая стабильную «ручку» для обновления и удаления конкретной строки. Во-вторых, на первичный ключ ссылаются внешние ключи — без объявленного ключа не на что ссылаться. В-третьих, СУБД автоматически обеспечивает его уникальность и обычно строит по нему индекс, ускоряя поиск. Наконец, первичный ключ документирует намерение проектировщика: «вот что в этой таблице делает строку уникальной». Поэтому таблица без первичного ключа — почти всегда признак недоработанного проекта.

Внешний ключ

Внешний ключ (foreign key) — это атрибут (или набор), который ссылается на первичный ключ другого (или того же) отношения. Именно так реляционная модель выражает связи. В таблице orders столбец client_id — внешний ключ, ссылающийся на clients.id. Он связывает заказ с клиентом не указателем, а совпадением значений: заказ принадлежит тому клиенту, чей id равен client_id заказа.

CREATE TABLE clients (
  id    INTEGER PRIMARY KEY,
  email TEXT UNIQUE,            -- альтернативный (потенциальный) ключ
  name  TEXT NOT NULL
);
CREATE TABLE orders (
  id        INTEGER PRIMARY KEY,
  client_id INTEGER NOT NULL REFERENCES clients(id),  -- внешний ключ
  summa     INTEGER NOT NULL
);

INSERT INTO clients VALUES (1, '[email protected]', 'Анна'), (2, '[email protected]', 'Борис');
INSERT INTO orders  VALUES (10, 1, 1500), (11, 2, 2300);

SELECT c.name, o.id AS zakaz, o.summa
FROM orders o JOIN clients c ON c.id = o.client_id;

Внешний ключ может ссылаться и на свою же таблицу — так выражают иерархии. Например, в таблице сотрудников столбец manager_id ссылается на id той же таблицы: у каждого сотрудника есть руководитель — тоже сотрудник. Запустите пример самоссылки.

CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT,
  manager_id INTEGER REFERENCES employees(id)   -- ссылка на ту же таблицу
);
INSERT INTO employees VALUES (1,'Анна',NULL), (2,'Борис',1), (3,'Вера',2);

-- найдём для каждого сотрудника его руководителя
SELECT e.name AS sotrudnik, m.name AS rukovoditel
FROM employees e LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

Вывод: «Анна — (нет)», «Борис — Анна», «Вера — Борис». У Анны manager_id равен NULL (она на вершине), поэтому LEFT JOIN подставил пусто. Так одним внешним ключом на саму таблицу выражается целая иерархия подчинения.

Составные ключи

Ключ не обязан быть одним атрибутом. Составной ключ состоит из нескольких атрибутов, которые уникальны только вместе. Классика — связующая таблица «заказ-товар»: ни order_id, ни product_id по отдельности не уникальны (заказ содержит много товаров, товар встречается во многих заказах), а вот пара {order_id, product_id} уникальна и образует первичный ключ. Минимальность для составного ключа означает, что убрать любой из его атрибутов нельзя без потери уникальности. Составные ключи — не исключение, а обычное дело в нормализованных схемах, и именно с ними связана вторая нормальная форма, к которой мы придём в разделе про нормализацию.

Суррогатные и естественные ключи

Ключи делят ещё по одному признаку. Естественный ключ — это атрибут, имеющий смысл в предметной области и уже уникальный сам по себе: ИНН, email, ISBN книги. Суррогатный ключ — искусственный идентификатор без смысла, который вводят специально (автоинкрементный id). У каждого подхода своя цена. Естественный ключ нагляден и не требует лишнего столбца, но рискован: смысл данных меняется (email сменили, ISBN переиздали), а ключ должен быть вечен. Суррогатный ключ стабилен и компактен (целое число — удобная ссылка), но не несёт смысла и не защищает от дублей по естественному признаку — поэтому на естественные уникальные поля всё равно ставят UNIQUE. На практике чаще берут суррогатный первичный ключ плюс уникальные ограничения на естественные ключи — это сочетание стабильности ссылок и защиты от дублей.

Сводная иерархия

Типичные ошибки проектирования

• Первичный ключ из изменяемого атрибута. Сделали ключом email или номер телефона — и при их смене приходится каскадно править ссылки во всех связанных таблицах. Берите стабильный (часто суррогатный) ключ.
• Путают суперключ и потенциальный ключ. Любой потенциальный ключ — суперключ, но не наоборот: суперключ может содержать лишние атрибуты.
• Забыли ограничение на альтернативный ключ. Назначили id первичным, а про уникальность email забыли — и в базе появляются два клиента с одинаковой почтой.
• Внешний ключ без поддержки целостности. Сослались на клиента, но не объявили это связью — и в заказах оказываются ссылки на несуществующих клиентов.

Итог

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