Superkey, candidate key, primary key, alternate key

В прошлом модуле мы установили: relation — это множество tuples, а в множестве нет дубликатов. Из этого следует, что в любом relation существует способ отличить каждую строку от всех остальных. Этот способ — ключ. Ключи — фундамент всего: на них держится уникальность строк, ссылочная целостность, скорость JOIN, нормализация. Этот модуль — целиком про ключи, и начать надо с точных определений.

Слово «ключ» в разговоре инженеров перегружено: «первичный ключ», «внешний ключ», «составной ключ», «бизнес-ключ». Это разные вещи. Чтобы не путаться, нужна строгая иерархия. Она состоит из четырёх уровней: superkey -> candidate key -> primary key и alternate key. Разберём их по порядку, от самого широкого к самому конкретному.

Superkey: уникально, но, возможно, избыточно

Superkey — это набор атрибутов, значения которых уникально идентифицируют каждый tuple в relation. Формально: если два tuple совпадают по всем атрибутам superkey, то это один и тот же tuple.

Ключевое слово в определении — «набор может содержать лишние атрибуты». Superkey обязан обеспечивать уникальность, но не обязан быть экономным. Возьмём таблицу:

users:
user_id | email             | name | city
--------+-------------------+------+--------
   1    | [email protected]   | Ann  | Moscow
   2    | [email protected]   | Bob  | Berlin
   3    | [email protected]    | Cy   | Moscow

Здесь уникальность строки обеспечивает user_id сам по себе. Но user_id — не единственный superkey. Superkey-ами являются ВСЕ наборы, содержащие user_id:

{user_id}                          -- superkey
{user_id, name}                    -- superkey (name лишний, но уникальность есть)
{user_id, name, city}              -- superkey (два лишних атрибута)
{user_id, email, name, city}       -- superkey (весь набор атрибутов)
{email}                            -- superkey (email тоже уникален)
{email, city}                      -- superkey

Логика проста: если {user_id} уже различает все строки, то добавление любого атрибута уникальность не испортит — она останется. Поэтому superkey-ов в таблице обычно много. Полный набор всех атрибутов — всегда superkey (раз строки в relation различны, полный набор их различает гарантированно).

Superkey — самое широкое понятие. Само по себе оно малополезно: знать, что {user_id, name, city} уникален, не помогает — там два бесполезных атрибута. Нужно понятие поуже.

Candidate key: минимальный superkey

Candidate key — это superkey, из которого нельзя убрать ни одного атрибута, не потеряв уникальность. Иными словами, candidate key — это минимальный superkey.

Слово «минимальный» здесь техническое и важное. Оно не значит «самый короткий из всех» — оно значит «неприводимый»: каждый атрибут в нём необходим. Если убрать любой атрибут candidate key, оставшийся набор перестаёт быть superkey.

Вернёмся к таблице users. Среди множества superkey-ов кандидатами являются:

{user_id}   -- candidate key: одиночный атрибут, убрать нечего, уникален
{email}     -- candidate key: тоже одиночный, тоже уникален

{user_id, name}  -- НЕ candidate key: убрать 'name' -> {user_id} ещё уникален,
                 -- значит 'name' был лишним, минимальности нет
{email, city}    -- НЕ candidate key: убрать 'city' -> {email} ещё уникален

Таблица может иметь несколько candidate keys. У users их два: {user_id} и {email}. Оба минимальны, оба уникально идентифицируют строку. Это нормальная и частая ситуация.

Primary key: выбранный кандидат

Когда candidate keys найдены, проектировщик выбирает один из них главным. Этот выбранный candidate key называется primary key (первичный ключ).

Primary key — это не отдельный математический объект, а роль: «тот candidate key, который мы назначили основным». У таблицы ровно один primary key. На него по умолчанию ссылаются foreign keys из других таблиц, по нему обычно строится основной индекс.

У primary key есть два жёстких требования, добавленных стандартом SQL:

• Уникальность — следует из того, что это candidate key.
• NOT NULL — primary key не может содержать NULL ни в одном атрибуте. Логика: NULL означает «значение неизвестно», а ключ обязан идентифицировать строку — нельзя идентифицировать строку «неизвестным». (Помните из модуля про NULL: NULL = NULL даёт UNKNOWN — два NULL-ключа невозможно сравнить, а значит, и гарантировать уникальность.)

Есть и третье требование — не из стандарта, а из практики проектирования: стабильность. Хороший primary key не должен меняться со временем. Если значение ключа меняется, его приходится синхронно менять во всех foreign keys, ссылающихся на него — дорого и рискованно. Этот критерий — одна из главных причин выбирать surrogate key вместо natural key, и ему посвящён следующий урок.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,        -- выбрали {user_id} как primary key
    email   TEXT NOT NULL UNIQUE,       -- {email} остался alternate key -> UNIQUE
    name    TEXT NOT NULL,
    city    TEXT
);

Alternate key: кандидат, не ставший первичным

Раз candidate keys обычно несколько, а primary key назначается ровно один, остальные candidate keys остаются «не у дел». Они называются alternate keys (альтернативные ключи).

Формула простая:

alternate keys = все candidate keys - primary key

В таблице users candidate keys — {user_id} и {email}. Если primary key выбран {user_id}, то {email} — alternate key.

Alternate key — не пустое звание. Это всё ещё candidate key: он по-прежнему уникально идентифицирует строку, и эту уникальность нужно защитить. В SQL это делается ограничением UNIQUE. Если объявить user_id как PRIMARY KEY, но не объявить email как UNIQUE, СУБД позволит вставить двух пользователей с одинаковым email — и email перестанет быть candidate key на уровне реальных данных.

-- Правильно: alternate key защищён через UNIQUE
email TEXT NOT NULL UNIQUE

-- Неправильно: email объявлен кандидатом только "в уме",
-- СУБД не мешает вставить дубликаты email
email TEXT NOT NULL

Практический вывод: каждый candidate key должен быть отражён в схеме — primary key через PRIMARY KEY, все alternate keys через UNIQUE. Иначе уникальность, которую вы определили при моделировании, не будет соблюдаться на данных.

Сводная таблица

Как выбрать, какой кандидат сделать primary

Если candidate keys несколько, какой назначить primary? Три критерия, в порядке важности:

1. Стабильность. Выбирайте кандидата, чьё значение не меняется. user_id стабилен; email пользователь может сменить — поэтому из пары {user_id} и {email} в primary идёт user_id.
2. Простота и узость. Чем уже ключ (меньше байт, меньше столбцов), тем компактнее индекс и быстрее JOIN. Одиночный целочисленный ключ предпочтительнее составного или строкового. Почему именно — детально в последнем уроке модуля.
3. Гарантированное наличие. Primary key обязан быть NOT NULL. Кандидат, который теоретически может отсутствовать, в primary не годится.

Эти три критерия почти всегда указывают на surrogate key — искусственный, узкий, стабильный, всегда заполненный идентификатор. Но это тема следующего урока; пока зафиксируем саму иерархию superkey -> candidate -> primary/alternate.

Попробуй сам

Возьмите таблицу employees со столбцами: employee_id, tax_number (ИНН, уникален), email (уникален), full_name, department, hired_on. На бумаге:

1. Выпишите 4-5 разных superkey этой таблицы (помните: любой набор, содержащий уникальный атрибут, — superkey).
2. Определите все candidate keys. Их здесь несколько — найдите все и для каждого докажите минимальность: убедитесь, что ни один атрибут убрать нельзя.
3. Объясните, почему {full_name, department} не является ни candidate key, ни даже superkey.
4. Выберите primary key из найденных кандидатов. Обоснуйте выбор по трём критериям: стабильность, узость, гарантированное наличие.
5. Назовите оставшиеся alternate keys и напишите фрагмент CREATE TABLE, где primary key объявлен через PRIMARY KEY, а каждый alternate key — через UNIQUE.