Заказ есть, покупателя нет: диагностика внешних ключей

Два заказа исчезли из отчёта после импорта. Покажем, как найти потерянные связи и не перепутать их с пустым customer_id.

SQL Studio / Разборы SQL / Внешние ключи

После импорта в отчёте стало на два заказа меньше. В выгрузке лежат строки с customer_id = 77 и 88, но в справочнике покупателей этих ID нет. Обычный INNER JOIN молча исключает оба заказа. Для поиска таких связей начните с дочерней таблицы: WHERE customer_id IS NOT NULL AND NOT EXISTS (...). Отдельно обработайте NULL: неизвестный покупатель и ссылка на несуществующего покупателя — разные проблемы.

Исходные строки: шесть заказов, три покупателя

Мини-набор встроен в каждый запрос через WITH ... VALUES, поэтому его можно выполнить в бесплатной SQLite-песочнице SQL Studio без импорта таблиц. Покупатели: 1 — Анна, 2 — Борис, 3 — Вера. Заказы 101, 102 и 106 связаны правильно; 103 ссылается на 77, 105 — на 88; у 104 ключ вообще не указан. Это намеренно неконсистентный импорт, а не данные из штатной базы тренажёра.

Контрольная выборка импорта
order_idcustomer_idожидаемый статус
1011есть покупатель
1022есть покупатель
10377нет покупателя
104NULLключ не указан
10588нет покупателя
1061есть покупатель

Ошибка: искать отсутствующие связи после INNER JOIN

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

WITH customers(customer_id, name) AS (
  VALUES (1,'Анна'), (2,'Борис'), (3,'Вера')
),
imported_orders(order_id, customer_id) AS (
  VALUES (101,1), (102,2), (103,77),
         (104,NULL), (105,88), (106,1)
)
SELECT COUNT(*) AS suspected_orphans
FROM imported_orders AS o
INNER JOIN customers AS c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
Неправильный результат
suspected_orphans
0

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

Исправление: NOT EXISTS по ненулевому внешнему ключу

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

WITH customers(customer_id, name) AS (
  VALUES (1,'Анна'), (2,'Борис'), (3,'Вера')
),
imported_orders(order_id, customer_id) AS (
  VALUES (101,1), (102,2), (103,77),
         (104,NULL), (105,88), (106,1)
)
SELECT o.order_id, o.customer_id
FROM imported_orders AS o
WHERE o.customer_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM customers AS c
    WHERE c.customer_id = o.customer_id
  )
ORDER BY o.order_id;
Потерянные ссылки: реально выполнено на SQLite
order_idcustomer_id
10377
10588

Получилось ровно два нарушения бизнес-целостности. У заказа 104 customer_id IS NULL; он не включён специально. В SQL сравнение NULL = c.customer_id не истинно. Без фильтра IS NOT NULL запрос NOT EXISTS также вернул бы 104, но смешал бы две причины отсутствия связи.

Три состояния вместо одного флага ошибки

Для приёмки импорта полезнее сохранить все исходные заказы и пометить причину. Здесь LEFT JOIN не удаляет строку из источника; проверяем именно первичный ключ правой таблицы, который не может быть NULL у существующего покупателя:

WITH customers(customer_id, name) AS (
  VALUES (1,'Анна'), (2,'Борис'), (3,'Вера')
),
imported_orders(order_id, customer_id) AS (
  VALUES (101,1), (102,2), (103,77),
         (104,NULL), (105,88), (106,1)
)
SELECT o.order_id, o.customer_id,
       CASE WHEN o.customer_id IS NULL THEN 'не указан'
            WHEN c.customer_id IS NULL THEN 'потерянная связь'
            ELSE 'связь есть' END AS link_state
FROM imported_orders AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY o.order_id;
Полная классификация заказов
order_idcustomer_idlink_state
1011связь есть
1022связь есть
10377потерянная связь
104NULLне указан
10588потерянная связь
1061связь есть

Сверка: 3 связанных + 2 потерянных + 1 неопределённый = 6 входных строк. Такое равенство проверяет, не пропали ли записи при диагностике. Для nullable-поля NULL может быть допустим по схеме; для обязательного покупателя это уже отдельное нарушение контракта.

Что контролирует SQLite — и почему импорт всё же может сломаться

Наличие REFERENCES в схеме ещё не доказывает, что каждая вставка проверялась. В SQLite проверка внешних ключей настраивается **для каждого соединения** командой PRAGMA foreign_keys = ON вне транзакции; не следует предполагать значение по умолчанию. Старые выгрузки, импорт с выключенной проверкой или неправильно заданные ограничения могут оставить потерянные связи. Следующий блок создаёт отдельные демонстрационные таблицы: намеренно отключает контроль при вставке, затем снова включает его и запускает диагностический PRAGMA. Включение проверки не исправляет уже вставленные строки и не отменяет необходимость аудита. Выполняйте опыт только в отдельной учебной базе, не в рабочей.

PRAGMA foreign_keys = OFF;
CREATE TABLE qa11_customers (
  customer_id INTEGER PRIMARY KEY
);
CREATE TABLE qa11_orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES qa11_customers(customer_id)
);
INSERT INTO qa11_customers VALUES (1);
INSERT INTO qa11_orders VALUES (1,1), (2,777), (3,NULL);
-- Восстановим проверку для следующих операций в этом соединении.
-- Она не исправит уже внесённые некорректные строки.
PRAGMA foreign_keys = ON;
PRAGMA foreign_key_check;
Результат PRAGMA foreign_key_check
tablerowidparentfkid
qa11_orders2qa11_customers0

Строка с order_id = 2 нарушает объявленный FK. У order_id = 3 значение NULL, а для обычного nullable FK оно разрешено. foreign_key_check проверяет объявленные в схеме ограничения; для CTE без REFERENCES он ничего не проверит. PRAGMA integrity_check не заменяет проверку FK. На реальном проекте сначала изучите PRAGMA foreign_key_list(orders) и повторите аудит с нужной таблицей.

Самостоятельная проверка

Добавьте заказ (107,3) и заказ (108,NULL). Сколько строк вернёт поиск через NOT EXISTS с фильтром IS NOT NULL? Ожидается, что два прежних нарушения останутся, а новый NULL попадёт только в отдельную категорию. Почему увеличение числа заказов не обязано увеличивать число нарушенных ссылок?

Продолжить: почему NOT IN ломается на NULL · как WHERE меняет LEFT JOIN · почему JOIN меняет количество строк.

Первоисточник: SQLite — Foreign Key Support и PRAGMA foreign_key_check. Синтаксис показан для SQLite WASM; отдельный курс Stepik использует MySQL 8 с другими механизмами настройки FK.


Для тех, кто хочет продолжить практику на MySQL 8: отдельный курс SQL на Stepik. Примеры выше выполнены на SQLite и не требуют курса.