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_id | customer_id | ожидаемый статус |
|---|---|---|
| 101 | 1 | есть покупатель |
| 102 | 2 | есть покупатель |
| 103 | 77 | нет покупателя |
| 104 | NULL | ключ не указан |
| 105 | 88 | нет покупателя |
| 106 | 1 | есть покупатель |
Ошибка: искать отсутствующие связи после 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;
| order_id | customer_id |
|---|---|
| 103 | 77 |
| 105 | 88 |
Получилось ровно два нарушения бизнес-целостности. У заказа 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_id | customer_id | link_state |
|---|---|---|
| 101 | 1 | связь есть |
| 102 | 2 | связь есть |
| 103 | 77 | потерянная связь |
| 104 | NULL | не указан |
| 105 | 88 | потерянная связь |
| 106 | 1 | связь есть |
Сверка: 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;
| table | rowid | parent | fkid |
|---|---|---|---|
| qa11_orders | 2 | qa11_customers | 0 |
Строка с 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 попадёт только в отдельную категорию. Почему увеличение числа заказов не обязано увеличивать число нарушенных ссылок?
Первоисточник: SQLite — Foreign Key Support и PRAGMA foreign_key_check. Синтаксис показан для SQLite WASM; отдельный курс Stepik использует MySQL 8 с другими механизмами настройки FK.
Для тех, кто хочет продолжить практику на MySQL 8: отдельный курс SQL на Stepik. Примеры выше выполнены на SQLite и не требуют курса.