Внешние ключи SQL Server: индексы и доверие к ограничениям

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

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

Проверить включение и доверие отдельно

Запустите запрос в нужной базе. Видимость метаданных зависит от прав, поэтому пустой список под ограниченной учетной записью не доказывает отсутствие проблемных ограничений.

SELECT
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS ChildSchema,
    OBJECT_NAME(fk.parent_object_id) AS ChildTable,
    fk.name,
    fk.is_disabled,
    fk.is_not_trusted,
    fk.delete_referential_action_desc
FROM sys.foreign_keys AS fk
WHERE fk.is_disabled = 1 OR fk.is_not_trusted = 1;

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

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

Сначала исправить данные, затем подтвердить связь

Следующие команды представляют адаптируемый образец для существующих учебных таблиц dbo.Orders и dbo.Customers. Имена нужно заменить после изучения реальной связи. Anti-join находит ненулевые идентификаторы клиентов без родителя. Он не решает, следует ли удалять заказы, исправлять ключи или восстанавливать отсутствующих клиентов.

SELECT o.CustomerId, COUNT_BIG(*) AS OrphanRows
FROM dbo.Orders AS o
LEFT JOIN dbo.Customers AS c ON c.CustomerId=o.CustomerId
WHERE o.CustomerId IS NOT NULL AND c.CustomerId IS NULL
GROUP BY o.CustomerId;

CREATE INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId);

ALTER TABLE dbo.Orders
WITH CHECK CHECK CONSTRAINT FK_Orders_Customers;

Два CHECK имеют разные функции. WITH CHECK проверяет существующие данные, CHECK CONSTRAINT включает ограничение. Проверка может прочитать значительный объем и потребовать блокировок. Оцените длительность и затем повторно проверьте is_disabled и is_not_trusted. Состояние каталога надежнее уверенности оператора, что команда включения уже выполнялась.

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

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

Рассмотреть оба направления доступа

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

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

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

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

Техническая документация: Microsoft Learn: Foreign keys · Microsoft Learn: Constraint trust.