от Mike Aubert

Добро пожаловать в 13-ую статью серии «Изучи администрирование SQL Server за 15 минут в неделю!». В прошлой статье мы рассматривали ACID-свойства и ссылочную целостность. Статья этой недели освещает проверку ограничений (check constraint). Темы этой статьи:

• Логические выражения
• Создание проверки ограничений

Логические выражения

Проще говоря, проверка ограничения - это булево выражение, принимающее значение TRUE либо FALSE (вариант с NULL упомяну чуть позже).

Когда добавляется либо обновляется строка таблицы, выполняется проверка на соответствие данных всем созданным ограничениям. Если какая-либо из проверок ограничений возвращает значение FALSE, происходит откат транзакции. Если все ограничения возвращают значения, отличные от FALSE, транзакция продолжается. Я говорю "отличные от FALSE", поскольку значение NULL несет с собой трехвариантную логику. NULL или неизвестное значение не повлечет отката транзакции - это случится только  в результате возврата проверкой ограничения значения FALSE.

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

miles <= 100

Это выражение будет возвращать  TRUE, если значение в miles (имя столбца) меньше или равно 100. Знак <= называется оператором сравнения и должен быть знаком каждому, кто знаком с основами программирования. SQL Server поддерживает нижеследующие операторы сравнения, которые можно применять ко всем типам данных, кроме text, ntext, и image:

* Эти операторы сравнения не являются стандартными для SQL, но реализованы в SQL Server.

Другим типом допустимых операторов являются логические операторы, такие как AND и OR. К примеру, мы можем добавить больше ограничений на число миль с помощью оператора AND. Вот как можно добавить условие, чтобы каждая поездка была длиной больше чем 0 миль:

miles > 0 AND miles <= 100

Теперь наше новое ограничение проверки позволяет вводить мили в диапазоне от 1 до 100.

Итак, мы рассмотрели проверку ограничения уровня столбца, поскольку эти ограничения затрагивают только один столбец. Вдобавок к этому, мы можем создавать ограничения, затрагивающие несколько столбцов одной и той же таблицы и одной и той же строки – известные как проверка ограничения уровня таблицы. Выделение слов "одной и той же" не случайно, потому что проверка ограничений не может ссылаться на столбцы других таблиц, кроме той, в которой они созданы. Кроме того, они могут ссылаться только на текущую строку этой таблицы.

К примеру, имеется таблица для хранения адресов клиентов, содержащая столбцы country и region. Мы хотим добавить ограничение, по которому, при указании в столбце country значения "USA", region должен хранить двухбуквенное сокращение имени штата. Вместо того чтобы писать выражение с оператором OR для каждого из названий штатов, используем оператор IN. (список сокращенных названий укорочен для читабельности).

country <> 'USA' OR region IN ('AL','FL', 'GA')

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

Хотя понимание основ работы логических выражений важно, их написаниеявляется в большей степени проблемой программирования базы данных, а не ее администрирования. Кроме упомянутых, существует еще множество операторов, которые можно использовать в проверке ограничений, поэтому, если вы не обладаете большим опытом написания логических выражений в SQL Server и хотите узнать больше, я рекомендую поискать в справочной системе Books Online тему, посвященную приоритету операторов. Порядок выполнения операторов и обработка значений NULL могут повлечь неожиданные результаты, даже если у вас уже есть немалый опыт работы.

Создание проверки ограничений

Итак, как мы можем добавить проверку ограничения к таблице или столбцу? Практически, как и во всем, что касается SQL Server, мы можем использовать для этой цели как операторы SQL, так и Диспетчер предприятия (Enterprise Manager). Я собираюсь показать, как добавить проверку ограничения, используя Диспетчер предприятия, а вы можете посмотреть также справку по синтаксису операторов CREATE TABLE и ALTER TABLE в Books Online.

То, что мы собираемся сделать – это добавить к таблице Customers в базе данных Northwind проверку ограничения, похожую на рассмотренное выше ограничение для столбцов country/state. Откройте ваш Диспетчер предприятия, выберите свой сервер, раскройте папку Databases (базы данных), затем базу Northwind и выберите Tables (таблицы). Затем щелкните правой клавишей таблицу Customers и выберите пункт Design Table (разработка таблицы). Откроется соответствующее окно:

Затем щелкните на значке Manage Constraints (управление ограничениями) на панели инструментов. Вот как он выглядит:

Когда появится окно Properties, щелкните кнопку  New и введите следующее выражение в поле ввода Constraint expression (и опять, данный список является укороченным):

Country <> 'USA' OR Region IN ('AL', 'AK', 'CA', 'FL', 'GA', 'ID', 'MT', 'NM', 'OR', 'WA', 'WY')

И последнее, в поле Constraint name (имя ограничения) введите CK_Customers_USA_Region. Вот что получится:

Прежде чем закрыть это окно, рассмотрим, зачем нужны три опции внизу окна:

- Check existing data on creation (проверять существующие данные при создании): будет проверять все существующие в таблице данные, чтобы обнаружить строки, не удовлетворяющие новому ограничению. Заметим однако, что проверка не выполнится, пока таблица не будет сохранена, и если обнаружатся конфликтные строки, то появится сообщение об ошибке, а изменения в таблице не сохранятся. Можно оставить эту опцию выключенной, чтобы ограничение применялось только к вновь создаваемым, либо обновляемым строкам.

- Enforce constraint for replication (проверять ограничение при репликации): будет отслеживать выполнение этого ограничения в случае репликации таблицы в другую базу данных. О репликации мы еще будем говорить в последующих статьях этой серии.

- Enforce constraint for INSERT and UPDATE (поверять ограничение для операторов INSERT и UPDATE): будет выполнять ограничение для любых операций INSERT и UPDATE применительно к этой таблице.

Закройте окно Properties и сохраните таблицу. Если вы получили сообщение об ошибке "Unable to add constraint 'CK_Customers_USA_Region' (невозможно добавить ограничение 'CK_Customers_USA_Region')", возможно вы имеете в таблице строки, не являющиеся частью исходной базы данных Northwind, для которых Country="USA" и сокращение в столбце Region не удовлетворяет созданному ограничению.

Еще одна вещь, которую надо иметь в виду, создавая проверку ограничения в Диспетчере предприятия. Единственное место, где вы можете создать их – вкладка окна свойств таблицы (см. выше). Другими словами, в Диспетчере предприятия нет способа указать, что ограничение должно быть привязано к столбцу, а не к таблице, как это можно сделать в операторе CREATE TABLE. Но есть и хорошая новость: Диспетчер предприятия способен на основе введенного ограничения автоматически решать, на какой уровень его добавить  – уровень столбца или таблицы.

Вот и все на этой неделе. В следующей статье мы займемся кое-чем интересным, тем, что может поднять либо убить производительность вашей базы – индексами! Так что, как обычно, если у вас есть технические вопросы – задавайте их на доске объявлений 2000Trainers.com SQL message board. Все нетехнические вопросы, комментарии, отзывы – на мой адрес электронной почты. Я надеюсь, вы находите эти статьи полезными и буду рад узнать ваше мнение.

Mike Aubert, MCSE, MCDBA,  MCSD.

(перевод Дианы Нигматуллиной)