Краткий ответ

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

Исторический контекст

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

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

Постановка проблемы

Рассмотрим счёт, идентифицируемый парой код страны и номер счёта. В таблице операций эта пара должна ссылаться на тот же счёт в том же порядке.

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

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

Подробное решение

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

CREATE TABLE account (
    country_code CHAR(2) NOT NULL,
    account_no VARCHAR(20) NOT NULL,
    PRIMARY KEY (country_code, account_no)
);

CREATE TABLE transfer (
    transfer_id INTEGER PRIMARY KEY,
    src_country CHAR(2),
    src_account VARCHAR(20),
    FOREIGN KEY (src_country, src_account)
        REFERENCES account (country_code, account_no)
);

Внешний ключ проверяет существование родительской строки с совпадающей парой значений. Он не сопоставляет столбцы по именам src_country и country_code; имена могут быть совершенно разными.

Целевой набор столбцов должен идентифицировать родительскую строку однозначно — обычно это первичный ключ или подходящее уникальное ограничение. Одного совпадения типов недостаточно: порядок, количество столбцов и их смысл должны соответствовать целевому ключу, а конкретные требования к совместимости типов зависят от СУБД.

Если целевой ключ — (country_code, account_no), запись REFERENCES account (account_no, country_code) не означает автоматическую перестановку. Как правило, такая ссылка невозможна без уникального ключа именно в указанном порядке. Если обратный уникальный ключ добавлен, ссылка будет проверять уже пару (account_no, country_code), что является другой моделью данных.

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

Ситуация из практики

В системе складского учёта ячейка определяется парой идентификатор склада и код ячейки. Таблица перемещений содержит оба значения, но слой отображения данных однажды сформировал внешний ключ в обратном порядке.

Рассматривались три варианта:

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

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

Что кандидаты часто упускают

1. Может ли внешний ключ ссылаться только на часть составного первичного ключа?

Только если эта часть сама является уникальным ключом родительской таблицы. Например, ссылка только на country_code недопустима, если в одной стране может быть много счетов: значение не определяет единственную родительскую строку. Для такой ссылки нужно отдельное уникальное ограничение, но добавлять его можно лишь если бизнес-правило действительно требует уникальности страны.

2. Достаточно ли одинаковых типов данных в соответствующих столбцах?

Нет. Необходима совместимая структура ссылки: одинаковое количество позиций, корректное соответствие целевому ключу и приемлемая для конкретной СУБД совместимость типов. Даже технически совместимые типы не гарантируют правильную модель: числовой идентификатор склада и числовой код ячейки могут сравниваться базой, но их перестановка всё равно будет семантической ошибкой.

3. Что произойдёт, если в родительской таблице есть уникальные ключи в обоих порядках?

Оба варианта могут быть допустимыми с точки зрения ссылочной целостности, но они выражают разные соответствия. Внешний ключ (x, y) REFERENCES parent (a, b) проверяет x = a и y = b; при ссылке на (b, a) проверка становится x = b и y = a. Поэтому наличие технически подходящего уникального ключа не доказывает корректность схемы — нужно проверить смысл каждой позиции и не допустить случайного дублирования альтернативных идентификаторов.