Первичный и внешний ключ в SQL: что это и как связаны таблицы

Коротко

Первичный ключ (PRIMARY KEY) — колонка или набор колонок, значение которых уникально и не пусто в каждой строке. Внешний ключ (FOREIGN KEY) — колонка, значение которой должно совпадать с первичным или уникальным ключом другой таблицы.

• Первичный ключ в таблице один. Он запрещает повторы и NULL, а PostgreSQL сам строит под него уникальный индекс.
• Внешних ключей может быть сколько угодно. Значения в них повторяются: у одного пользователя много платежей.
• Внешний ключ не даёт сослаться на несуществующую строку и решает, что будет со ссылками при удалении: запретить, удалить каскадом или обнулить.
• Для аналитика ключи — это прогноз JOIN. Соединение «от многих к одному» сохраняет число строк, «от одного к многим» — умножает.
• В аналитических хранилищах ключи часто не проверяются. Тогда уникальность и «сирот» проверяют запросом до того, как строить на таблице отчёт.

Первичный ключ: что делает строку единственной

Первичный ключ — это ответ на вопрос «как отличить эту строку от всех остальных». Для таблицы пользователей это номер пользователя, для таблицы платежей — номер платежа. Два условия обязательны: значение не повторяется и не бывает пустым. По сути PRIMARY KEY — это UNIQUE и NOT NULL вместе, плюс пометка «это главный идентификатор таблицы».

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

Первичный ключ в таблице может быть только один. Если объявить два, PostgreSQL ответит multiple primary keys for table "…" are not allowed. Другие колонки, которые тоже должны быть уникальными, например email, объявляют через UNIQUE.

Внешний ключ: как платёж знает, чей он

В таблице платежей нет канала, страны и даты регистрации. Там есть user_id — ссылка на строку в users. Это и есть внешний ключ: колонка в одной таблице, значения которой берутся из первичного ключа другой. Таблицу со ссылкой называют дочерней, таблицу, на которую ссылаются, — родительской.

В отличие от первичного ключа, внешний повторяется. В payments 1 251 строка, а различных user_id — 912: кто-то платил дважды или трижды. В events 35 341 строка и 4 613 различных user_id — у каждого пользователя есть хотя бы одно событие.

Ссылаться внешний ключ может только на колонку, уникальность которой объявлена: первичный ключ или UNIQUE. Попытка сослаться на обычную колонку, например на users.channel, в PostgreSQL заканчивается ошибкой there is no unique constraint matching given keys for referenced table "users".

NULL во внешнем ключе разрешён, если колонку не объявили NOT NULL. Такая строка просто ни на что не ссылается. Для платежей это обычно ошибка данных, поэтому внешний ключ часто объявляют вместе с NOT NULL.

Чем первичный ключ отличается от внешнего

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

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

Ключ из одной колонки называют простым, из нескольких — составным. Составной ключ нужен, когда уникальна только комбинация значений. В таблице участников эксперимента один человек может попасть в несколько разных экспериментов, но в один и тот же — только однажды. Значит, уникальна пара (experiment_name, user_id). В учебной базе эксперимент один, и пар 3 068 — столько же, сколько строк.

Второе деление — по происхождению значения. Естественный ключ взят из самих данных: email, номер паспорта, код страны. Суррогатный придуман базой: порядковый номер или UUID, который ничего не значит, кроме «эта строка». Все пять ключей учебной базы суррогатные.

Суррогатный ключ обычно удобнее. Email меняется, у двух систем он может быть записан в разном регистре, а номер строки остаётся прежним. В PostgreSQL его генерируют через GENERATED ALWAYS AS IDENTITY или старый serial, а UUID — встроенной функцией gen_random_uuid(). Естественный ключ при этом полезно всё равно защитить ограничением UNIQUE, иначе один человек зарегистрируется дважды под двумя номерами.

Будьте осторожны с «ключами по совпадению». В учебной базе пара (user_id, event_time) в событиях тоже уникальна: 35 341 пара на 35 341 строку. Но это свойство конкретной выгрузки, а не правило. В живом потоке событий повторная доставка даёт две одинаковые строки, и строить на такой паре дедупликацию без проверки нельзя.

Связи один-к-одному, один-ко-многим и многие-ко-многим

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

Один-ко-многим — самая частая связь. Один пользователь, много платежей: 622 плательщика заплатили один раз, 241 — дважды, 49 — трижды. Один пользователь, много событий: от 1 до 22, медиана 7. Внешний ключ стоит на стороне «многих».

Один-к-одному — внешний ключ, который ещё и уникален. У плательщика одна подписка: 912 строк в subscriptions и 912 различных user_id. Строго говоря, связь «один к нулю или одному»: из 4 613 пользователей подписка есть у 912. В базе такое правило закрепляют ограничением UNIQUE на subscriptions.user_id, и вторая подписка того же человека не запишется.

Многие-ко-многим напрямую не хранится. Её раскладывают на две связи один-ко-многим через промежуточную таблицу. experiment_exposures — такая таблица между пользователями и экспериментами: один человек может участвовать во многих экспериментах, в одном эксперименте много людей. Сейчас эксперимент один, в нём 3 068 человек, а 1 545 пользователей в тест не попали.

SELECT n_payments, count(*) AS payers
FROM (
  SELECT user_id, count(*) AS n_payments
  FROM payments
  GROUP BY user_id
) AS per_user
GROUP BY n_payments
ORDER BY n_payments;

-- 1 → 622, 2 → 241, 3 → 49

PRIMARY KEY и FOREIGN KEY в CREATE TABLE

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

Простой ключ пишут прямо у колонки. Составной — отдельной строкой после колонок: PRIMARY KEY (experiment_name, user_id). Внешний ключ у колонки записывается как REFERENCES users (user_id); если имя колонки в родительской таблице опустить, возьмётся её первичный ключ.

Если таблица уже существует, ключ добавляют через ALTER TABLE … ADD CONSTRAINT. PostgreSQL при этом проверит все старые строки и откажется создавать ограничение, если хотя бы одна строка ссылается в пустоту. Опция NOT VALID пропускает проверку старых строк, но новые проверяются сразу.

CREATE TABLE users (
  user_id     integer PRIMARY KEY,
  signup_date date NOT NULL,
  channel     text
);

CREATE TABLE payments (
  payment_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    integer NOT NULL REFERENCES users (user_id),
  paid_at    date NOT NULL,
  amount     numeric(10, 2) NOT NULL
);

CREATE TABLE subscriptions (
  subscription_id integer PRIMARY KEY,
  user_id         integer NOT NULL UNIQUE REFERENCES users (user_id),
  plan            text NOT NULL
);

CREATE TABLE experiment_exposures (
  experiment_name text    NOT NULL,
  user_id         integer NOT NULL REFERENCES users (user_id),
  variant         text    NOT NULL,
  PRIMARY KEY (experiment_name, user_id)
);

-- если таблицу создали без ключа, его добавляют позже
ALTER TABLE payments
  ADD CONSTRAINT payments_user_fk
  FOREIGN KEY (user_id) REFERENCES users (user_id);

Ограничения внешнего ключа: что происходит при удалении

Внешний ключ проверяет не только вставку, но и удаление родителя. Что делать с платежами, если удалили пользователя, задаёт ON DELETE. По умолчанию действует NO ACTION: удаление запрещено, пока на строку кто-то ссылается. RESTRICT запрещает то же самое, но его проверку нельзя отложить до конца транзакции, даже если ограничение объявлено как DEFERRABLE.

Два других варианта меняют данные молча, и аналитику важно о них знать. CASCADE удаляет дочерние строки вместе с родителем: удалили пользователя — пропали его платежи, и выручка прошлых месяцев в отчёте изменилась задним числом. SET NULL оставляет платежи, но обнуляет в них user_id. Итог по таблице платежей сохраняется, а в разбивке по каналам через JOIN с users эти платежи выпадают, и сумма по каналам перестаёт сходиться с общей.

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

Что ключи значат для аналитика: зерно и JOIN без дублей

Каждый JOIN соединяет таблицы по какой-то паре колонок, и ключи заранее говорят, сколько строк получится. Правило одно. Если идти от внешнего ключа к первичному — от платежа к его пользователю, — у каждой строки слева ровно одна пара справа, и строк не прибавляется. Если идти в обратную сторону — от пользователя к его платежам, — строки умножаются на число платежей.

Хуже всего соединять две «многие» таблицы через общего родителя. Платежи и события обе ссылаются на users, но друг на друга — нет. JOIN по user_id сопоставляет каждый платёж человека с каждым его событием. В учебной базе это 11 667 строк вместо 1 251, и sum(amount) даёт $284 913 вместо $30 639.

Соединение платежей с подписками по тому же user_id безопасно: subscriptions.user_id уникален, у каждого платежа одна пара. Строк остаётся 1 251, сумма — $30 639. Разница между этим JOIN и предыдущим видна только по ключам, синтаксис у них одинаковый.

Отсюда рабочая привычка: перед соединением назвать зерно обеих таблиц и тип связи. Если связь один-ко-многим в сторону «многих», сначала агрегируйте многую сторону до одной строки на ключ, а уже потом соединяйте.

SELECT
  (SELECT count(*) FROM payments)                     AS payment_rows,     -- 1 251
  (SELECT sum(amount) FROM payments)                  AS revenue,          -- 30 639
  (SELECT count(*) FROM payments p
     JOIN events e ON e.user_id = p.user_id)          AS rows_with_events, -- 11 667
  (SELECT sum(p.amount) FROM payments p
     JOIN events e ON e.user_id = p.user_id)          AS inflated_revenue, -- 284 913
  (SELECT sum(p.amount) FROM payments p
     JOIN subscriptions s ON s.user_id = p.user_id)   AS revenue_with_subs;-- 30 639

Если ограничений в базе нет: проверка уникальности и «сирот»

Ключ, объявленный в базе, проверяется при каждой записи. Но в аналитике таблицы часто собирают без ограничений. BigQuery принимает PRIMARY KEY и FOREIGN KEY только с пометкой NOT ENFORCED и в документации прямо предупреждает, что запросы к таблицам с нарушенными ключами могут вернуть неверный результат. В Snowflake на стандартных таблицах проверяется только NOT NULL, а первичный, внешний и уникальный ключи остаются описанием. В ClickHouse PRIMARY KEY задаёт разреженный индекс для чтения и допускает строки с одинаковым значением ключа.

Учебная база устроена так же. Таблицы созданы через CREATE TABLE AS, и запрос SELECT count(*) FROM duckdb_constraints() в песочнице вернёт 0: ни одного объявленного ограничения. Колонки user_id, payment_id и остальные — ключи по смыслу и по данным, но не по схеме.

Поэтому ключи проверяют запросом. Три вопроса: уникален ли первичный ключ, нет ли в нём NULL и у всех ли ссылок есть родитель. Строки дочерней таблицы без родителя называют «сиротами». В учебной базе проверки проходят чисто: дублей нет, NULL нет, сирот ноль во всех четырёх дочерних таблицах. В рабочих данных сироты появляются, когда события приходят раньше, чем пользователь попал в таблицу, или когда пользователя удалили, а события оставили.

SELECT
  -- 1. первичный ключ уникален: разница должна быть 0
  (SELECT count(*) - count(DISTINCT user_id) FROM users)     AS duplicate_ids,  -- 0
  -- 2. в первичном ключе нет NULL
  (SELECT count(*) - count(user_id) FROM users)              AS null_ids,       -- 0
  -- 3. у каждого платежа и события есть пользователь
  (SELECT count(*) FROM payments p
    WHERE NOT EXISTS (SELECT 1 FROM users u
                      WHERE u.user_id = p.user_id))          AS orphan_payments, -- 0
  (SELECT count(*) FROM events e
    WHERE NOT EXISTS (SELECT 1 FROM users u
                      WHERE u.user_id = e.user_id))          AS orphan_events;   -- 0

Частые вопросы

Может ли первичный ключ быть NULL? Нет. PRIMARY KEY включает NOT NULL, и PostgreSQL отвергнет такую строку с ошибкой violates not-null constraint.

Может ли внешний ключ быть NULL? Да, если колонку не объявили NOT NULL. Строка с NULL во внешнем ключе ни на что не ссылается и проверку проходит.

Может ли внешний ключ ссылаться не на первичный ключ? Да, на любую колонку или набор колонок с ограничением UNIQUE. На обычную колонку без уникальности — нет.

Может ли таблица ссылаться сама на себя? Да. Классический пример — manager_id в таблице сотрудников, который ссылается на employee_id той же таблицы.

Создаёт ли PostgreSQL индекс на внешний ключ? Нет, только на первичный ключ и UNIQUE. Индекс на колонке внешнего ключа обычно добавляют вручную: он ускоряет JOIN и удаление родителя.

Поддерживает ли ключи DuckDB? Да, PRIMARY KEY и FOREIGN KEY в DuckDB проверяются. Правила CASCADE и SET NULL для внешних ключей DuckDB 1.5 не поддерживает и отвечает ошибкой при создании таблицы.

Итог

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

Для аналитика из этого следует одно практическое правило: прежде чем соединять таблицы, назовите их ключи и направление связи. Тогда 11 667 строк вместо 1 251 не станут сюрпризом. А если ограничений в базе нет, уникальность и сирот проверяют запросом — три строки SQL перед отчётом. Ключи разбираются в нулевой главе SQL-курса, JOIN без дублей — в четвёртой. Главы 0–2 открыты бесплатно.