Для составного внешнего ключа PostgreSQL нужен подходящий уникальный ключ, охватывающий столбцы, на которые он ссылается. Первичный ключ только по id не подходит для ссылки на (id, edition), хотя логически эта пара и так уникальна. PostgreSQL: CREATE TABLE

Это руководство поможет разработчику, работающему в Терминале на Mac, диагностировать это несоответствие, выбрать подходящий тип связи и проверить результат с помощью node-postgres. Во всех примерах используются вымышленные данные о статьях. SQL рассчитан на PostgreSQL 18 и внешние ключи с обычным сравнением на равенство; в документации PostgreSQL 18 отдельно описаны правила для временных ссылок с использованием PERIOD. PostgreSQL: CREATE TABLE

Почему PostgreSQL отклоняет составную ссылку?

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

Предполагаемая связь задается следующим фрагментом ограничения таблицы:

FOREIGN KEY (article_id, edition)
  REFERENCES demo_articles (id, edition)

Эта декларация просит PostgreSQL проверять оба значения вместе по одной родительской строке. Согласно документации, в качестве целевых столбцов должны выступать столбцы ограничения UNIQUE или PRIMARY KEY, которое не является отложенным, либо неполного уникального индекса. Существующий первичный ключ охватывает только id, а в ссылке указаны (id, edition). PostgreSQL: CREATE TABLE

Математическая логика возражения понятна: если никакие две родительские строки не могут иметь одинаковый id, то у них не может совпадать и пара (id, edition). Однако этот вывод не заменяет соответствующий объявленный целевой ключ, требуемый определением внешнего ключа. Для этой схемы его обеспечивает добавление UNIQUE (id, edition). Дополнительное ограничение логически избыточно с точки зрения уникальности, но при этом служит конкретной цели — задает целевые столбцы составной ссылки.

Рассмотрим эти условные строки родительской таблицы:

id edition title
101 morning Paper Shapes
102 evening Color Notes

Для дочерней пары (101, 'morning') есть соответствующая строка. Для пары (101, 'evening') — нет: оба значения присутствуют в таблице, но в разных строках. Поэтому составную связь стоит использовать, когда оба сохраненных значения должны относиться к одной и той же статье.

Следует добавить уникальный ключ или упростить внешний ключ?

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

Требуемое поведение Подходящая схема Следствие
Дочерняя таблица идентифицирует статью и показывает ее текущий выпуск Хранить article_id; ссылаться на родительский id Получать выпуск из родительской таблицы при выполнении запроса
Дочерняя таблица хранит выпуск, который должен соответствовать статье Оставить составную ссылку; добавить родительское ограничение UNIQUE (id, edition) PostgreSQL проверяет, что сохраненная пара существует целиком
Один и тот же номер статьи может соответствовать нескольким строкам выпусков Задать идентификатор родительской записи как (id, edition) Один только первичный ключ по id не позволит создать такие родительские строки

Последняя строка описывает другую модель данных. Не удаляйте первичный ключ по одному столбцу просто ради устранения ошибки: сначала решите, действительно ли в родительской таблице должны быть повторяющиеся номера статей. PostgreSQL поддерживает составной первичный ключ, но у таблицы может быть только один первичный ключ. PostgreSQL: Constraints

В приведенном ниже исполняемом примере идентификаторы статей остаются глобально уникальными. В дочерней таблице намеренно хранится также выпуск, поэтому в родительской таблице остается PRIMARY KEY (id) и добавляется дополнительная уникальная пара.

Как воспроизвести и исправить ошибку на Mac?

Используйте уже установленную локальную базу данных PostgreSQL 18 и работающую установку Node.js; в этом руководстве предполагается, что они доступны. Откройте Terminal и создайте каталог нового проекта. Установите драйвер командой из документации. node-postgres: Welcome

mkdir composite-fk-demo
cd composite-fk-demo
npm init -y
npm install pg

Сохраните следующую полную программу как demo.mjs. Она создает временные таблицы, демонстрирует отклоненную схему, добавляет подходящий ключ и проверяет допустимые и недопустимые пары статей. Все операторы используют один клиент, поскольку node-postgres требует выполнять транзакцию в одном и том же экземпляре клиента. node-postgres: Transactions

Для каждой ожидаемой ошибки вспомогательная функция использует точку сохранения, благодаря чему транзакция может продолжиться. В документации PostgreSQL сказано, что ROLLBACK TO SAVEPOINT отменяет команды после этой точки сохранения и позволяет продолжить работу. PostgreSQL: ROLLBACK TO SAVEPOINT

import pg from 'pg';
const { Client } = pg;
const client = new Client();

const childDDL = `CREATE TEMP TABLE demo_article_links (
  link_id integer PRIMARY KEY,
  article_id integer NOT NULL,
  edition text NOT NULL,
  FOREIGN KEY (article_id, edition)
    REFERENCES demo_articles (id, edition)
)`;

async function expectError(sql, values, code) {
  await client.query('SAVEPOINT expected_error');
  try {
    await client.query(sql, values);
  } catch (error) {
    await client.query('ROLLBACK TO SAVEPOINT expected_error');
    await client.query('RELEASE SAVEPOINT expected_error');
    if (error.code !== code) throw error;
    console.log(`Expected error: ${code}`);
    return;
  }
  await client.query('ROLLBACK TO SAVEPOINT expected_error');
  await client.query('RELEASE SAVEPOINT expected_error');
  throw new Error(`Expected SQLSTATE ${code}, but query succeeded`);
}

try {
  await client.connect();
  await client.query('BEGIN');
  await client.query(`CREATE TEMP TABLE demo_articles (
    id integer PRIMARY KEY,
    edition text NOT NULL,
    title text NOT NULL
  )`);

  await expectError(childDDL, [], '42830');

  await client.query(`ALTER TABLE demo_articles
    ADD CONSTRAINT demo_articles_id_edition_key
    UNIQUE (id, edition)`);
  await client.query(childDDL);

  await client.query(`INSERT INTO demo_articles (id, edition, title)
    VALUES ($1, $2, $3), ($4, $5, $6)`,
    [101, 'morning', 'Paper Shapes', 102, 'evening', 'Color Notes']);

  await client.query(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [1, 101, 'morning']);

  await expectError(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [2, 101, 'evening'], '23503');

  const result = await client.query(`SELECT l.link_id, a.title, a.edition
    FROM demo_article_links AS l
    JOIN demo_articles AS a
      ON a.id = l.article_id AND a.edition = l.edition
    ORDER BY l.link_id`);
  console.table(result.rows);
} catch (error) {
  console.error(error.code ?? error.message);
  process.exitCode = 1;
} finally {
  try { await client.query('ROLLBACK'); }
  finally { await client.end(); }
}

Программа передает значения статей отдельно через $1, $2 и последующие заполнители, следуя API параметризованных запросов драйвера. Имена таблиц и столбцов остаются фиксированными в SQL: параметры PostgreSQL не могут заменять идентификаторы. node-postgres: Queries

Запустите программу с параметрами подключения к существующей локальной базе данных. В этой команде предполагается, что база называется article_demo, сервер прослушивает порт 5432, а существующие локальные параметры подключения по умолчанию работают. При необходимости измените имя базы данных или порт. node-postgres считывает переменные окружения для подключения к PostgreSQL. node-postgres: Connecting

PGHOST=localhost PGPORT=5432 PGDATABASE=article_demo node demo.mjs

Во время подготовки этой статьи JavaScript был проверен на синтаксические ошибки. Поведение базы данных проверялось по документации; здесь программа не запускалась на сервере PostgreSQL.

Что показывают две ошибки и итоговый запрос?

При указанных предварительных условиях ожидаются такие сообщения:

Ожидаемая ошибка: 42830
Ожидаемая ошибка: 23503

Первая ошибка возникает при создании дочерней таблицы, до вставки каких-либо строк статей. PostgreSQL указывает, что 42830 — это invalid_foreign_key. После добавления подходящего уникального ключа в родительскую таблицу то же определение дочерней таблицы становится допустимым. PostgreSQL: Error Codes

Вторая ошибка возникает при вставке (101, 'evening'). Теперь схема допустима, но для указанной пары нет родительской строки. PostgreSQL указывает, что 23503 — это foreign_key_violation. Проверка кода позволяет отличить это несоответствие строки от предыдущего несоответствия схемы без зависимости от локализованного текста ошибки. PostgreSQL: Error Codes

Итоговый запрос должен вернуть одну строку: ссылка 1, заголовок Paper Shapes, выпуск morning. Условие соединения включает оба компонента связи. Поэтому запрос описывает ту же пару, которую проверяет ограничение.

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

Итоговый оператор ROLLBACK намеренно удаляет все созданное в демонстрации, включая определения таблиц. Чтобы перенести эту схему на постоянные таблицы, внесите изменения в рамках обычной процедуры миграции; копирование программы с временными таблицами не создаст долговременную схему приложения.

Что проверить перед изменением существующей схемы?

Начните со списка столбцов, на которые идет ссылка. Определите родительский ключ, который действительно охватывает этот список, а не полагайтесь на имя индекса или на то, что текущие строки случайно различаются. В этом примере нужно добавить ограничение с именем UNIQUE (id, edition), показанное в программе.

Добавление ограничения UNIQUE создает уникальный индекс B-tree. Учитывайте появление этого дополнительного индекса при выборе между составной схемой и хранением только article_id. При объявлении внешнего ключа индекс на дочерних столбцах автоматически не создается. PostgreSQL: Constraints

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

Рассматривайте проверку существующих строк отдельно от пригодности целевого ключа. NOT VALID не добавляет отсутствующий родительский ключ. Это также не позволяет добавить ограничение UNIQUE без подтверждения уникальности: в PostgreSQL 18 этот вариант доступен только для ограничений внешнего ключа, проверки и отсутствия значения NULL. PostgreSQL: ALTER TABLE

В каких случаях результат может отличаться?

Частичный уникальный индекс не подходит в качестве цели обычного внешнего ключа, описанного здесь. Аналогично, пометка ссылочного ограничения UNIQUE как отложенного не удовлетворяет задокументированному требованию о неотложенном ограничении. В этом примере используйте стандартное значение UNIQUE (id, edition). PostgreSQL: CREATE TABLE

Важна и обработка NULL. При поведении по умолчанию для составного внешнего ключа совпадение в родительской таблице не требуется, если какой-либо компонент ссылки равен NULL. В примере оба компонента дочерней ссылки отмечены как NOT NULL, поэтому каждая сохраненная пара должна соответствовать родительской строке. Если связь должна быть необязательной, MATCH FULL разрешает ссылку, в которой все компоненты равны NULL, но отклоняет ссылку с частичным NULL. PostgreSQL: Constraints

Проверьте саму пару: может ли дочерняя запись указать идентификатор статьи и выпуск, которые относятся к разным строкам? Если это должно приводить к ошибке, оставьте составную связь и добавьте подходящий ключ в родительскую таблицу. Если дочерней таблице нужно только определить статью, укажите ссылку на id и получайте выпуск статьи при выполнении запроса.

Источники