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

CREATE TABLE parent (
    a INTEGER,
    b INTEGER,
    PRIMARY KEY (a, b)
);

CREATE TABLE child (
    x INTEGER,
    y INTEGER,
    FOREIGN KEY (x, y) REFERENCES parent(a, b)
);

INSERT INTO parent VALUES (1, 2);
INSERT INTO child VALUES (1, NULL);

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

Да, такая вставка обычно допустима: при стандартном режиме MATCH SIMPLE составной внешний ключ не проверяется, если хотя бы один его столбец равен NULL. Это означает, что строка child(1, NULL) не обязана ссылаться на строку parent(1, NULL) или на любую другую строку родительской таблицы.

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

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

Режим MATCH SIMPLE трактует частично заполненную ссылку как неполную ссылку и не требует поиска соответствия. Это позволяет хранить необязательную связь, когда отсутствие одного из компонентов означает отсутствие ссылки целиком.

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

В примере пара (1, NULL) не является полной ссылкой на составной ключ (a, b). Если разработчик ожидает, что внешний ключ будет проверять только известную часть и потребует parent(1, ...), такое ожидание ошибочно: ссылочная целостность проверяет составной ключ как целое.

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

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

При MATCH SIMPLE, используемом по умолчанию, проверка внешнего ключа выполняется только тогда, когда все столбцы внешнего ключа имеют не-NULL значения. Поэтому:

• (1, 2) требует существования parent(1, 2);
• (1, NULL) проходит проверку внешнего ключа;
• (NULL, NULL) также проходит проверку внешнего ключа.

Это не означает, что NULL равен NULL или что в родительской таблице существует подходящая строка. Проверка просто не выполняется для неполной ссылки.

Если нужно запретить частичные ссылки, применяют NOT NULL ко всем столбцам внешнего ключа:

CREATE TABLE child_strict (
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    FOREIGN KEY (x, y) REFERENCES parent(a, b)
);

Другой вариант — MATCH FULL, если его поддерживает конкретная СУБД. При этом либо все компоненты внешнего ключа должны быть NULL, либо все должны быть заполнены и полностью соответствовать родительскому ключу. Частичное значение вроде (1, NULL) будет запрещено.

Следует учитывать совместимость СУБД: поддержка режимов MATCH и детали их реализации различаются. Поэтому для переносимого и явно выраженного правила часто используют NOT NULL и отдельно проверяют, действительно ли модель допускает отсутствие всей связи.

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

В системе заказов составной ключ (warehouse_id, product_id) идентифицирует остаток товара на складе. В таблице резервов разработчик оставил оба столбца внешнего ключа nullable, потому что резерв может быть создан до выбора склада.

Вариант с MATCH SIMPLE позволяет вставить (NULL, 42) и тем самым сохранить незавершённый резерв, но также пропускает ошибочное состояние (7, NULL). Вариант с NOT NULL не допускает ни один незавершённый резерв, зато гарантирует полную ссылку.

Вариант с MATCH FULL разрешает либо полностью отсутствующую связь (NULL, NULL), либо корректную пару (7, 42), но запрещает частичное заполнение. Если бизнес-правило допускает только эти два состояния, выбранное решение — MATCH FULL при наличии поддержки или эквивалентная комбинация ограничений CHECK и внешнего ключа. Это предотвращает появление частично заполненных идентификаторов и сохраняет смысл данных.

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

1. Означает ли прохождение внешнего ключа для (1, NULL), что такая строка существует в родительской таблице?

   Нет. Внешний ключ не устанавливает соответствие по известной части составного ключа. При MATCH SIMPLE наличие хотя бы одного NULL отключает проверку всей ссылки, поэтому родительская строка для (1, NULL) не требуется.

2. Что изменится при MATCH FULL для значений (NULL, NULL) и (1, NULL)?

   (NULL, NULL) считается полностью неопределённой ссылкой и допускается. (1, NULL) является частичной ссылкой и нарушает правило MATCH FULL, поскольку компоненты должны быть либо все NULL, либо все заполнены и соответствовать составному ключу.

3. Почему замена NULL на специальное значение вроде 0 не является полноценным решением?

   0 становится обычным значением и потребует реальной родительской строки с таким ключом либо отдельного исключения в ограничениях. Это смешивает отсутствие связи с бизнес-данными, усложняет запросы и может привести к ложным ссылкам. Для отсутствия значения следует использовать NULL, а допустимость частичной или полной ссылки выражать ограничениями схемы.