Сравнение методов применения ограничений в SQL Server 2008

Быстрый ответ

Команда WITH CHECK ADD CONSTRAINT обеспечивает проверку, согласуются ли с новым ограничением все существующие записи в таблице при его добавлении. Если данные удовлетворяют новым условиям, ограничение вступает в силу и становится обязательным для новых записей.

Напротив, WITH NOCHECK ADD CONSTRAINT добавляет ограничение без проверки уже имеющихся данных. Это удобно, когда известно, что некоторые записи не соответствуют вновь установленным условиям, но исправление данных заложено в планы на будущее, либо важно быстро создать ограничение.

Для приведения ограничения в статус доверенного, после его добавления с WITH NOCHECK, используется команда CHECK CONSTRAINT, которая проводит проверку, и, если данные соответствуют, апгрейдит статус ограничения.

Пример добавления ограничения с большими ожиданиями:

ALTER TABLE dbo.TheTableName
-- Добавляем ограничение, надеясь, что все значения ColumnName больше 0
WITH NOCHECK ADD CONSTRAINT CK_TheTableName_ColumnName CHECK (ColumnName > 0);

ALTER TABLE dbo.TheTableName
-- Теперь, когда данные подготовлены, проверяем и надеемся на лучшее
CHECK CONSTRAINT CK_TheTableName_ColumnName;

На языке разработчиков: WITH CHECK помогает сразу поддерживать целостность данных. WITH NOCHECK отсрочивает проверку на более поздний срок, а CHECK CONSTRAINT позволяет нам доверять ограничению после его проверки.

Особенности работы ограничений

Используя WITH CHECK, SQL Server тщательно проверяет каждую запись на соответствие новому ограничению. Офтен данных можно и нужно предварительно подготовить, показывая цель всего этого действа – это снижает риск ошибок при вводе новых данных.

Если производительность стоит на первом месте или надо работать с устаревшими данными, нарушающими правила, можно использовать WITH NOCHECK. Это позволяет отсрочить принудительное соблюдение правил целостности данных.

Однако не стоит забывать: ограничения, добавленные с помощью WITH NOCHECK, вызывают неуверенность SQL-системы до момента их проверки и считаются недоверенными. Это может затруднить оптимизацию запросов, так как оптимизатор не будет предполагать целостность данных при создании планов выполнения.

Исправление "сиротских" данных

При нахождении некорректных записей важно их исправить перед введением новых ограничений на существующие данные.

DELETE FROM TheTable WHERE NOT EXISTS (
-- Этот запрос удаляет так называемые "сиротские" записи
);

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

SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;
-- Этот запрос помогает отслеживать недоверенные ограничения.

Индивидуальные сценарии

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

Разработка и Производство

В рамках разработки, когда схема базы данных часто изменяется, можно обойтись без явного указания WITH CHECK. Но в производственной среде прямое использование WITH CHECK гарантирует целостность данных в процессе миграции приложений или добавления новых функций.

Визуализация

Процесс можно представить на примере разработки и внедрения новых дорожных правил:

Городской Совет 🏛️
  WITH CHECK ADD CONSTRAINT:   📜 ➡️ 🚧 (Разрабатывается и проверяется проект закона)
  CHECK CONSTRAINT: ✅ 🚦 (Закон проверен и принят)
Полиция 👮‍♂️
  ADD CONSTRAINT: 🚦 ➡️ 🔒 (Закон постоянно действует)

Никакой магии, только логичная последовательность!

Важность контекста

В SQL Server термин CHECK обозначает несколько типов ограничений: CHECK CONSTRAINT, WITH CHECK, WITH NOCHECK. Понимание их отличий критически важно для избежания путаницы в обеспечении целостности данных.

Лучшие практики и рекомендации

• Перед добавлением новых ограничений очистите данные для обеспечения их соответствия.
• Используйте WITH CHECK для ясности кода и очевидности целей ограничения.
• Для поддержания производительности используйте WITH NOCHECK, намечая операцию CHECK CONSTRAINT в периоды простоя.
• Регулярное прослеживание недоверенных ограничений поможет поддерживать спокойствие.

Полезные материалы

1. Stack Exchange – Обсуждения о CHECK CONSTRAINT — Отличное место для дискуссий по вопросам SQL CHECK ограничений.
2. Добавление или удаление ограничения таблицы – Управление SQL командами — Универсальное руководство для работы с ограничениями таблиц и SQL командами.
3. Database Journal – Работа с ограничениями по умолчанию в SQL Server — Глубокий разбор работы с ограничениями по умолчанию.