Какие из следующих инструкций используются для описания правил целостности

Правила целостности данных

Главная особенность SQL-технологий наличие у сервера СУБД специальных
средств контроля целостности данных, не зависящих от клиентских
программ и привязанных непосредственно к таблицам. Т.е. принципиально
не важно, каким образом осуществляется доступ к базе данных: через
SQL-консоль, через ODBC-драйвера из приложения Windows, через WWW-connector
из Internet-браузера или через DBI-интерфейс Perl. В любом из этих
случаев, за контролем целостности данных следит сервер, и при нарушении
правил целостности данных сервер известит клиента об ошибке.

К структурам контроля целостности данных относятся ограничители
(constraint), которые привязаны к столбцам и триггеры (trigger),
которые могут быть привязаны как к столбцам, так и к строкам в таблице.

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

SQL-серверы, как правило, поддерживают следующие ограничители.

NOT NULL — проверка на непустое значение. NULL — специальное понятие
в СУБД, которое означает «пусто». «Пусто» и «0(ноль)» не равны друг
другу!

UNIQUE — проверка на уникальность. Вставляемое значение должно
быть уникально для данного столбца по всей таблице. Может содержать
пустые значения.

PRIMARY KEY — первичный ключ. Значение в столбце считается первичным
ключом, если оно непустое и уникально в пределах столбца данной
таблицы. Первичный ключ может быть составным и представлять собой
комбинацию столбцов. Тогда чтобы считаться первичным ключом, каждое
из группы значений не должно быть пустыми и формируемые строки значений
первичного ключа должны быть уникальны в пределах таблицы. Первичный
ключ — основа для построения индексов по таблице.

SQL-технология позволяет на уровне столбца задавать домены значений,
т.е. строго определенные наборы или диапазоны значений, для помещаемых
в столбец данных. В частности можно реализовывать ограничения ссылочной
целостности (referential integrity constraint) и проверки фиксированного
условия. Ограничение ссылочной целостности не позволяет значениям
из столбца одной таблицы принимать значения кроме как из присутствующих
в столбце другой таблицы. Это делается при помощи ограничителей
FOREIGN KEY (внешний ключ) и REFERENCES (указатель ссылки). Таблица,
содержащая FOREIGN KEY, считается родительской таблицей. Таблица,
содержащая REFERENCES, считается дочерней таблицей. Внешний ключ
и указатель ссылки могут находиться в одной таблице, т.е. родительская
таблица одновременно является дочерней.

FOREIGN KEY — внешний ключ. Назначает столбец или комбинацию столбцов
в текущей (родительской) таблице в качестве внешнего ключа для ссылки
из других таблиц.

REFERENCES — указатель ссылки (или родительский ключ). Указывает
на столбец (комбинацию столбцов) в родительской таблице, ограничивающую
значения в текущей (дочерней) таблице.

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

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

CHECK — проверка фиксированного условия. В данном ограничителе
явно указывается условие, которое должно выполняться для вставляемого
или модифицируемого значения в столбце. Например: check (user in
‘ALEX’,’JUSTAS’) — в столбце user могут содержаться только значения
‘ALEX’ и ‘JUSTAS’, попытка вставки значения ‘SHTIRLITZ’ будет интерпретирована
как ошибочная , check (user_salary between 1000 and 5000) — столбец
user_salary может принимать целочисленные значения в диапазоне от
1000 до 5000 и т.д. При формировании условий с некоторыми ограничениями
могут использоваться функции, например check (user = upper(user)),
в данном случае имя пользователя должно вводиться только в верхнем
регистре. Есть и ограничения, например, CHECK не может содержать
подзапросы (SELECT).

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

Триггеры — это сохраненная откомпилированная процедура, которая
связана с определенной таблицей. Триггеры, в отличие от ограничителей,
могут выполнять сколь угодно сложные манипуляции над данными. Помимо
операций модификации и вставки, триггеры могут срабатывать и при
удалении данных из таблицы. Можно также задавать порядок срабатывания
триггера относительно операции, т.е. выполниться ли триггер перед
операцией вставки/модификации/удаления значения из столбца (или
всей строки) или непосредственно после такой операции.

Некоторые типовые применения триггеров:

• Прозрачный аудит (не зависящий от клиентских программ и невидимый
  для них) и регистрация событий, связанных с доступом к определенным
  таблицам или столбцам в таблицах.
• Генерация значений в столбцах на основе значений в других столбцах
  при вставке/модификации строки данных.
• Манипуляции над зависимыми таблицами в особенности, если они
  находятся на других узлах распределенной базы данных, чего нельзя
  сделать при помощи ограничителей.

В случае необходимости триггеры можно запрещать, а затем разрешать.
Запрещение триггеров применяется обычно при массовых загрузках данных
в таблицы извне, с целью уменьшения времени загрузки. Понятие триггера
как выполнение кода по событию в том же Oracle используется весьма
широко. В частности, оно является основным при разработке клиентских
программ при помощи SQL*Forms. Триггеры пишутся на процедурных расширениях
SQL.

Обработка данных в многопользовательской СУБД.

Основное требование к многопользовательским СУБД — обеспечение
непротиворечивости данных в системе, при сохранении максимальной
производительности и конкуренции в доступе к данным для пользователей.

Конкуренция в доступе к данным означает, что каждый из пользователей
независим от остальных пользователей в потребности обработки данных.
Система, во избежание порчи данных, самостоятельно устанавливает
очередность работы с данными для пользователей. В случае необходимости
пользователи могут ожидать своей очереди для работы с данными. Одной
из главной целей многопользовательской СУБД является максимальное
уменьшение этого времени ожидания до такой степени, чтобы оно (в
идеале) стало незаметным для пользователя.

Кроме того, сервер СУБД должен предотвращать взаимно разрушающие
манипуляции с данными нескольких пользователей при их одновременной
работе. Например, если система не предусматривает такую возможность,
то менеджеры принимающие заказы от клиентов на поставку товара,
и выполняющие их резервирование на складе, могут зарезервировать
товара больше чем фактически имеется в наличии. В этом случае обеспечен
неприятный разговор с клиентом, заказ которого будет впоследствии
отменен.

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

Контроль нужен также в системах резервирования билетов на транспорте,
чтобы билет на одно и то же место не был продан разными кассирами
разным пассажирам.

Несмотря на различия в реализации, серверы СУБД используют общие
способы управления данными и доступом к ним.

Атомарность SQL-выражений при работе с данными.

Под атомарностью выражения понимается неизменность (фиксация во
времени) набора данных, с которыми это выражение работает на всем
протяжении своего исполнения. Т.е. если мы выполняем оператор UPDATE
над определенной таблицей, то состояние таблицы на момент начала
выполнения операции фиксируется во времени и не изменяется до конца
выполнения оператора. Этот набор данных для текущего выполняемого
выражения не может быть изменен другим пользователем или даже другой
сессией этого же пользователя, которая пытается выполнить операцию
модификации этих же данных в этой же таблице.

Распараллеливание операций.

Типовые операции с таблицей в базе данных состоят из многих однотипных
операций, например оператор UPDATE, который модифицирует 5000 строк
в таблице, по своей сути состоит из 5000 операций, каждая из которых
может быть выполнена независимо. В связи с этим такие операторы
очень хорошо распараллеливаются при использовании многопроцессорных
систем. Это позволяет выровнять нагрузку в системе между разными
процессорами, при том условии что СУБД умеет работать в многопроцессорной
конфигурации, и уменьшить время ответа системы.

Обеспечение максимальной производительности.

С целью сокращения времени различных пользователей на манипуляции
с данными используется ряд следующих методов. Их работа находится
на уровне, скрытом даже от программиста СУБД, но о них стоит упомянуть
т.к. они иллюстрируют серьезные различия с xBase-технологией.

Строго говоря, эта информация справедлива лишь в отношении Oracle,
но другие СУБД используют подобные принципы.

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

Данные приемы позволяют существенно уменьшить время ожидания ответа
системы и увеличить ее производительность.

Транзакции

Многопользовательские системы широко используют понятие транзакций.
Транзакция — это логическая единица работы, которая состоит из одного
или нескольких SQL-выражений. Группа выражений отмеченных как транзакция
рассматривается как единое и неделимое целое. В случае если в одном
из выражений обработки данных происходит ошибка, то транзакция отменяется
целиком. Таким образом, система возвращается в состояние предшествующее
началу транзакции, обеспечивая при этом физическую и логическую
непротиворечивость данных.

Например, мы пытаемся модифицировать таблицу при помощи оператора
UPDATE. В одном из столбцов этим оператором устанавливается недопустимое
значение с точки зрения правил целостности для этой таблицы. Срабатывание
ограничителя приведет к тому, что сервер СУБД не позволит выполнить
такую модификацию и известит нас ошибкой, а механизм контроля транзакций
вызывает отмену всего выполняемого выражения и производит откат
к предыдущему состоянию таблицы, сохраняя, таким образом, целостность
и непротиворечивость данных. В данном примере транзакция работает
с одним SQL-выражением. В случае если выражений несколько, то откатывается
результат работы всех выражений составляющих единую транзакцию.

Чтобы транзакциями можно было пользоваться, в системе должен быть
включен режим регистрации транзакций. После этого система сохраняет
информацию о предыдущих состояниях объектов в системе в так называемых
журналах транзакций. Журналы транзакций — это специальные файлы,
управляемые сервером СУБД, в которых записываются все изменения
произошедшие с момента начала транзакций для всех транзакций в системе.

Если происходит явное сохранение изменений в системе (по команде
COMMIT) или неявное сохранение изменений (по завершению группы SQL-выражений,
формирующих транзакцию или по завершению сеанса пользователя), то
все изменения произошедшие с момента начала транзакции вносятся
в систему, и информация о данной транзакции удаляется из журнала.

Для облегчения управления системой в режиме регистрации транзакций
существует возможность задания так называемых промежуточных точек
сохранения. Промежуточная точка сохранения по команде SAVEPOINT
явно помечает состояние системы и предоставляет
возможность восстановления состояния БД на момент ее сохранения
по команде ROLLBACK. В данном случае ROLLBACK
откатывает систему к указанной точке. Обычно промежуточных точек
сохранения для одного пользователя может быть несколько.

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

Данная схема справедлива для Oracle, где транзакция начинается
с выполнением первого оператора, прочие сервера могут работать по-другому.
Например в Informix DS, транзакция начинается явно, при помощи команды
BEGIN WORK.

В SQL-бочке меда есть своя ложка дегтя. Для всех SQL-серверов использующих
журнальный режим регистрации транзакций существует проблема, так
называемых «длинных» транзакций. Это транзакции, которые затрагивают
очень большой объем данных (сопоставимый с количеством свободного
места на дисках) и в этом случае журналы регистрации транзакций
могут переполниться. Если их рост ничем неограничен, то они могут
израсходовать у ОС всю доступную дисковую память, что не есть хорошо,
т.к. операционная система и сервер СУБД в этом случае остаются в
непредсказуемом состоянии. Если их рост ограничен, то при переполнении
журналов СУБД выдает соответствующую ошибку и операция откатывается.
Чтобы избежать таких ситуаций программист должен разделить длинную
транзакцию на короткие транзакции.

Блокировки.

Для того чтобы пользователи не искажали взаимно используемые данные,
сервер СУБД, при многопользовательской работе, использует механизм
блокировок. Блокировки по аналогии с базами данных на основе файлов
могут быть как разделяемые, так и исключительные. Блокировки могут
устанавливаться как на таблицу целиком, так и на строку в таблице.
Аналогично в xBase-технологиях: блокировки могут устанавливаться
как на xBase-файл, так и на запись в xBase-файле.

Блокировки связаны с транзакциями. Если выполняется отмена транзакции,
то снимаются все связанные с этой транзакцией блокировки.

Многие блокировки выполняются неявно для пользователя, они выставляются,
например, операторами UPDATE, INSERT. Существуют явные операторы
задания блокировок, например, LOCK TABLE или операторы, имеющие
клаузы блокировки, например SELECT : FOR UPDATE. Соответственно
есть операторы и для снятия блокировок.

Многие SQL-серверы имеют специальные способы обнаружения и предотвращения
взаимных блокировок (deadlocks), которые могут занимать ресурсы
СУБД на неопределенное время.

Способы, которыми обеспечиваются блокировки, зависят от реализации
сервера, и описываются в его документации. Виды блокировок также
зависят от используемого сервера. В Informix существуют, т.н. promotional
locks, это означает, что если клиентский процесс не может блокировать
в исключительном режиме ресурс, то такая блокировка ставится в очередь
и ей предоставляется ресурс после снятия текущей исключительной
блокировки другого процесса. Oracle7 так не делает, если он не может
установить исключительную блокировку в течение указанного сервером
времени, то клиент извещается об ошибке.

[ Назад ]
[ Оглавление ]
[ Далее ]

При
создании реляционной БД необходимо
помнить о непротиворечивости и
правильности хранимой информации. Для
этого любая СУБД предусматривает
возможность устанавливать одно или
несколько условий целостности данных.
Эти условия определяют, какие значения
могут быть записаны в базу данных в
результате добавления или обновления
данных. Как правило, можно использовать
следующие условия целостности данных:

• Обязательное
  наличие данных. По сути хранимой
  информации некоторые столбцы в базе
  данных должны содержать значения в
  каждой строке; строки в таких столбцах
  не могут содержать значения NULL. Например,
  в базе данных для каждого объекта
  недвижимости должен существовать
  владелец, сдающий этот объект. Поэтому
  столбец ono
  в таблице property_for_rent
  является обязательным и необходимо
  указать СУБД, что запись значения NULL в
  такой столбец недопустима.

• Условие
  на значение. Каждый столбец в базе
  данных определен на своем домене, т.е.
  имеет тот набор значений, которые
  допускается хранить в данном столбце.
  Можно указать СУБД, что запись значений,
  не входящих в определенный диапазон,
  в такие столбцы недопустима.

• Целостность
  таблицы (сущности). Первичный ключ
  таблицы должен в каждой строке иметь
  уникальное значение, отличное от
  значений во всех остальных строках.
  Можно указать СУБД, чтобы она обеспечивала
  целостность таблиц за счет использования
  первичных ключей.

• Ссылочная
  целостность. Каждая строка таблицы-потомка
  с помощью внешнего ключа связана со
  строкой таблицы-предка, содержащей
  первичный ключ, значение которого равно
  значению внешнего ключа. Значение
  столбца bno
  таблицы staff
  связывает служащего с офисом, в котором
  он работает. Столбец bno
  должен содержать значение из столбца
  bno
  таблицы branch;
  в противном случае служащий будет
  закреплен за несуществующим офисом.
  Можно указать СУБД, чтобы она обеспечивала
  ограничение на значения внешнего ключа.

• Бизнес
  правила. Обновление информации в базе
  данных может быть ограничено деловыми
  правилами, которым подчиняются сделки,
  представляемые подобными обновлениями.
  Например, организация может установить
  деловое правило, запрещающее сотруднику
  офиса работать одновременно более чем
  с пятью объектами. Можно указать СУБД,
  что следует проверять каждую новую
  строку, добавляемую в таблицу
  property_for_rent
  , и убеждаться, что количество объектов,
  закрепленных за одним сотрудником, не
  превышает установленное деловое
  правило.

• Непротиворечивость.
  Многие реальные деловые операции
  вызывают в базе данных несколько
  изменений одновременно. Например,
  операция “увольнение сотрудника и
  передача всех закрепленных за ним
  объектов другим сотрудникам этого же
  отделения” может включать в себя
  удаление строки из таблицы staff
  с предварительным изменением содержимого
  соответствующих столбцов sno
  таблицы property_for_rent.
  Инструкции DELETE и UPDATE должны быть
  выполнены вместе для того, чтобы база
  данных осталась в правильном,
  непротиворечивом состоянии. Можно
  указать СУБД, что следует обеспечивать
  непротиворечивость изменяемых данных.

Обязательное
наличие данных.
Это условие целостности данных требует,
чтобы некоторые столбцы не содержали
значений NULL. Стандарт ANSI/ISO и большинство
коммерческих СУБД поддерживают выполнение
подобного условия, позволяя пользователю
при создании таблицы объявить, что
некоторые столбцы не могут содержать
значений NULL. Условие задается как часть
инструкции CREATE TABLE в виде ограничения
NOT NULL.

Если
на столбец наложено ограничение NOT
NULL,
то для выполнения этого условия СУБД
обеспечивает следующее:

• ни
  в одной инструкции INSERT, добавляющей в
  таблицу строку или строки, нельзя
  указывать значение NULL для этого столбца;
  попытка добавить строку, содержащую
  (явно или неявно) значение NULL для такого
  столбца, вызовет ошибку;

• ни
  в одной инструкции UPDATE, обновляющей
  столбец, нельзя присваивать столбцу
  значение NULL; попытка обновить такой
  столбец, присвоив ему, значение NULL,
  вызовет ошибку.

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

Невозможность
наложения ограничения NOT NULL на уже
существующую таблицу является следствием
того, что в большинстве СУБД значения
NULL реализованы на внутреннем уровне.
Обычно СУБД резервирует в каждой хранимой
строке по одному дополнительному байту
на каждый столбец, в котором значения
NULL допустимы. Дополнительный байт служит
“индикатором” значения NULL, и если в
столбце содержится NULL, то этому байту
присваивается заранее установленное
значение. Если же для столбца определено
ограничение NOT NULL, то байт индикатора
отсутствует, что позволяет экономить
дисковую память. Чтобы динамически
отменить это ограничение, требуется
“на ходу” переконфигурировать хранимые
на диске записи, а в больших базах данных
это весьма накладно.

Условия
на значения.
При создании таблицы за каждым столбцом
закрепляется определенный тип данных,
и СУБД следит за тем, чтобы в столбец
вводились данные только этого типа.

Ограничение
на значения столбца аналогично условию
отбора в предложении WHERE и возвращает
значение TRUE или FALSE. Если для столбца
задано ограничение, то при каждом
добавлении новой строки или обновлении
старой СУБД автоматически проверяет,
выполняется ли ограничение для значения
в этом столбце. Если оно не выполняется,
то инструкция INSERT или UPDATE завершается
ошибкой. Ограничение на значения столбца
задается при определении столбца в
инструкции CREATE TABLE.

Целостность
таблиц (сущностей).
Каждая строка таблицы должна иметь
уникальное значение первичного ключа,
иначе база данных потеряет свою
целостность. В ранних коммерческих СУБД
первичные ключи отсутствовали, но сейчас
они стали повсеместно распространенными.
Первичные, ключи создаются с помощью
инструкции CREATE TABLE. СУБД автоматически
проверяет уникальность первичного
ключа для каждой инструкции INSERT или
UPDATE. Попытка добавить строку с уже
существующим значением первичного
ключа или обновить строку таким образом,
что ее первичный ключ потеряет свою
уникальность, завершится выдачей
сообщения об ошибке.

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

• Добавление
  новой строки-потомка. Когда происходит
  добавление новой строки в таблицу
  staff,
  значение ее внешнего ключа bno должно
  быть равно одному из значений первичного
  ключа bno в таблице-предке branch. Если
  значение внешнего ключа не равно ни
  одному из значений первичного ключа,
  то добавление такой строки разрушит
  целостность базы данных, поскольку
  появится потомок без предка (“сирота”).
  Добавление строки в таблицу branch не
  вызовет проблем; она просто станет
  предком без потомков.

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

• Удаление
  строки-предка. Если из таблицы-предка
  branch
  будет удалена строка, у которой есть
  хотя бы один потомок, то строки-потомки
  станут сиротами. Значения внешних
  ключей в этих строках больше не будут
  равны ни одному из значений первичного
  ключа таблицы-предка. Удаление строки
  из таблицы-потомка не вызовет проблем.
  В этом случае предок этой строки после
  удаления будет иметь на одного потомка
  меньше.

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

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

Вторая
проблема (обновление таблицы – потомка)
решается аналогично: путем проверки
нового значения внешнего ключа. Если
нет ни одного равного ему значения
первичного ключа, инструкция UPDATE
отбрасывается с выдачей сообщения об
ошибке.

Третья
проблема является более сложной.
Предположим, например, что вы закрыли
отделение в Гродно и хотите удалить
соответствующую строку из таблицы
branch. Как в этом случае поступить со
строками-потомками таблицы staff
зависит от ситуации:

• не
  удалять из базы данных отделение до
  тех пор, пока служащие не будут переведены
  в другое отделение;

• автоматически
  удалить всех соответствующих служащих
  из таблицы staff;

• в
  столбце staff.bno
  установить значение NULL для всех
  соответствующих служащих, показывая
  тем самым, что идентификатор их офиса
  неизвестен;

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

Аналогичные
сложности существуют и в четвертой
ситуации. Допустим, по каким-либо причинам
требуется изменить идентификатор
отделения в Витебске с 1 на 2. Подобно
предыдущему примеру, возникает вопрос
о том, как поступить со строками-потомками
в таблице staff,
представляющими служащих Витебского
офиса. И снова проблему можно решить
четырьмя способами:

• не
  изменять идентификатор отделения до
  тех пор, пока служащие не будут переведены
  в другое отделение; в таком случае в
  таблицу branch
  следует
  вначале добавить строку с новым
  идентификатором отделения в Витебске,
  затем обновить таблицу staff
  и,
  наконец, удалить строку со старым
  идентификатором Витебского отделения;

• автоматически
  обновить идентификатор отделения этих
  служащих в таблице staff
  для того, чтобы их строки были по-прежнему
  связаны с Витебской строкой в таблице
  branch
  через ее новый идентификатор;

• в
  столбце staff.bno
  установить для соответствующих служащих
  значение NULL, показывая тем самым, что
  идентификатор офиса неизвестен;

• в
  столбце staff.bno
  установить по умолчанию для соответствующих
  служащих некоторое значение, например
  идентификатор главного офиса в Минске.

Правила
удаления и обновления.
Для каждого отношения предок/потомок
в базе данных, создаваемого внешним
ключом, стандарт SQL позволяет задать
соответствующее правило удаления и
соответствующее правило обновления.
Правило удаления определяет те действия,
которые СУБД выполняет, когда пользователь
пытается удалить строку из таблицы-предка.
Можно задать одно из четырех возможных
правил удаления:

• RESTRICT
  (NO ACTION) – запрещает удаление строки из
  таблицы – предка, если строка имеет
  потомков. Следует также иметь в виду,
  что данное правило зачастую воспринимается
  СУБД по умолчанию и не требует явного
  задания.

• CASCADE
  – определяет, что при удалении
  строки-предка все строки-потомки также
  автоматически удаляются из таблицы-потомка.
  Данное правило опасно в употреблении,
  в случае если существуют многоуровневые
  связи между таблицами.

• SET
  NULL – определяет, что при удалении
  строки-предка внешним ключам во всех
  ее строках-потомках автоматически
  присваивается значение NULL.

• SET
  DEFAULT – определяет, что при удалении
  строки-предка внешним ключам, всем ее
  строкам-потомкам присваивается
  определенное значение, по умолчанию
  установленное для данного столбца.

Аналогично
тому, как правила удаления определяют
действия СУБД при попытке удалить строку
из таблицы – предка, так правила
обновления определяют действия СУБД,
когда пользователь пытается обновить
значение первичного ключа в таблице-предке.
Регламентируются четыре правила
обновления, аналогичные правилам
удаления.

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

В
заключение следует отметить, что
некоторые СУБД, в частности ORACLE
не поддерживают правил SET NULL и SET DEFAULT ,
а правило CASCADE требует явного задания
и поддерживается только для удалений.
Правила каскадного обновления в системе
ORACLE недействительны, поддерживается
только запрет на изменение строк-предков.

Правило
RESTRICT является “одноуровневым” – в
отношении предок/потомок оно затрагивает
только таблицу-предок. Правило CASCADE,
напротив, “многоуровневое”, его следует
применять с осторожностью, поскольку
некорректное его использование может
вызвать широкомасштабное автоматическое
удаление данных. Правила каскадного
обновления могут привести к подобным
многоуровневым обновлениям, если внешний
ключ в таблице-потомке одновременно
является и ее первичным ключом. На
практике такая ситуация встречается
не часто, поэтому каскадное обновление
обычно не имеет таких далеко идущих
последствий, как каскадное удаление.

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

Рисунок
Ссылочный цикл

На
представленном рисунке таблица staff
содержит столбец bno–
внешний ключ для таблицы branch.
Таблица branch,
в свою очередь может содержать столбец
mgr
(с информацией об управляющих отделений)
– внешний ключ для таблицы staff.
Как видно из рисунка, эти два отношения
образуют ссылочный
цикл.
Любая строка таблицы staff
имеет ссылку на строку таблицы branch,
которая имеет ссылку на строку таблицы
staff,
и т.д.

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

Поместить
в базу данных информацию о новом
сотруднике Иване Иванове с назначением
его управляющим в только что открывшемся
отделении в Москве:

insert
into
staff
(sno,
fname,
lname,
salary,
bno)

values
(23,
‘Иван’,
‘Иванов’,
500, 3);

insert
into
branch
(bno,
street,
area,
city,
mgr,
tel_no)

values
(3,
‘…’, ‘…’, ‘Москва’,
23, ‘…’);

Выполнение
первой инструкции будет безрезультатным,
потому что в новой строке есть ссылка
на идентификатор отделения, которого
в базе данных еще нет. Очевидно, что
изменение порядка инструкции INSERT ни к
чему не приведет, так как при выполнении
инструкции для branch
встречается ссылка на идентификатор
служащего (руководитель офиса), который
еще отсутствует в таблице staff.

Для
предотвращения подобной ситуации, по
крайней мере, один из внешних ключей
ссылочного цикла должен допускать
значения NULL. Тогда ввод двух строк можно
выполнить с помощью двух инструкций
INSERT и одной инструкции UPDATE:

insert
into
staff
(sno,
fname,
lname,
salary,
bno)

values
(23, ‘Иван’,
‘Иванов’,
500, null);

insert
into
branch
(bno,
street,
area,
city,
mgr,
tel_no)

values
(3, ‘…’, ‘…’, ‘Москва’,
23, ‘…’);

update
staff

set
bno=3

where
sno=23;

Как
видно из данного примера, в некоторых
ситуациях было бы удобно, чтобы условия
ссылочной целостности вообще не
проверялись до тех пор, пока не будет
выполнен ряд взаимосвязанных обновлений.
К сожалению, в большинстве современных
СУБД отсутствует такой тип комплексной
отложенной проверки. Для Oracle самым
простым способом решения этой проблемы
является программное включение/отключение
всех ограничений на время добавления
данных в таблицы.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Таблицы с ограничениями в стандарте языка

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

• обязательные данные;
• ограничения для доменов полей;
• целостность сущностей;
• ссылочная целостность ;
• требования конкретного предприятия.

Большая часть перечисленных ограничений задается в операторах CREATE TABLE и ALTER TABLE.

Создание таблицы

В стандарте SQL дано несколько вариантов определения оператора создания таблицы, однако его базовый формат имеет следующий вид:

<определение_таблицы> ::=
CREATE TABLE имя_таблицы
{(имя_столбца тип_данных [ NOT NULL ][ UNIQUE]
[DEFAULT <значение>]
[ CHECK (<условие_выбора>)][,...n]}
[CONSTRAINT имя_ограничения]
[PRIMARY KEY (имя_столбца [,...n])
{[UNIQUE (имя_столбца [,...n])}
[FOREIGN KEY (имя_столбца_внешнего_ключа 
    [,...n])
REFERENCES имя_род_таблицы 
    [(имя_столбца_род_таблицы [,...n])],
[MATCH {PARTIAL | FULL}]
[ON UPDATE {CASCADE| SET NULL |SET DEFAULT 
    |NO ACTION}]                        
[ON DELETE {CASCADE| SET NULL |SET DEFAULT
   	|NO ACTION}]
{[CHECK(<условие_выбора>)][,...n]})

Ограничения

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

Обязательные данные

Для некоторых столбцов требуется наличие в каждой строке таблицы конкретного и допустимого значения, отличного от опущенного значения или значения NULL. Для заданий ограничений подобного типа стандарт SQL предусматривает использование спецификации NOT NULL.

Требования конкретного предприятия

Обновления данных в таблицах могут быть ограничены существующими в организации требованиями (бизнес-правилами). Стандарт SQL позволяет реализовать бизнес-правила предприятий с помощью предложения CHECK и ключевого слова UNIQUE.

Ограничения для доменов полей

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

Целостность сущностей

Первичный ключ таблицы должен иметь уникальное непустое значение в каждой строке. Стандарт SQL позволяет задавать подобные требования поддержки целостности данных с помощью фразы PRIMARY KEY. В пределах таблицы она может указываться только один раз. Однако существует возможность гарантировать уникальность значений и для любых альтернативных ключей таблицы, что обеспечивает ключевое слово UNIQUE. Кроме того, при определении альтернативных ключей рекомендуется использовать и спецификаторы NOT NULL.

Ссылочная целостность

Внешние ключи представляют собой столбцы или наборы столбцов, предназначенные для связывания каждой из строк дочерней таблицы, содержащей этот внешний ключ, со строкой родительской таблицы, содержащей соответствующее значение потенциального ключа. Стандарт SQL предусматривает механизм определения внешних ключей с помощью предложения FOREIGN KEY, а фраза REFERENCES определяет имя родительской таблицы, т.е. таблицы, где находится соответствующий потенциальный ключ. При использовании этого предложения система отклонит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значение внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы. Когда действия системы выполняются при поступлении операторов UPDATE и DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблицы, то они зависят от правил поддержки ссылочной целостности, указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY. Если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие возможности:

• CASCADE — выполняется удаление строки из родительской таблицы, сопровождающееся автоматическим удалением всех ссылающихся на нее строк дочерней таблицы;
• SET NULL — выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL ;
• SET DEFAULT — выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;
• NO ACTION — операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена.

Те же самые правила применяются в языке SQL и тогда, когда значение потенциального ключа родительской таблицы обновляется.

Определитель MATCH позволяет уточнить способ обработки значения NULL во внешнем ключе.

При определении таблицы предложение FOREIGN KEY может указываться произвольное количество раз.

В операторе CREATE TABLE используется необязательная фраза DEFAULT, которая предназначена для задания принимаемого по умолчанию значения, когда в операторе INSERT значение в данном столбце будет отсутствовать.

Фраза CONSTRAINT позволяет задать имя ограничению, что позволит впоследствии отменить то или иное ограничение с помощью оператора ALTER TABLE.

Изменение и удаление таблицы

Для внесения изменений в уже созданные таблицы стандартом SQL предусмотрен оператор ALTER TABLE, предназначенный для выполнения следующих действий:

• добавление в таблицу нового столбца;
• удаление столбца из таблицы;
• добавление в определение таблицы нового ограничения;
• удаление из определения таблицы существующего ограничения;
• задание для столбца значения по умолчанию;
• отмена для столбца значения по умолчанию.

Оператор изменения таблицы имеет следующий обобщенный формат:

<изменение_таблицы> ::=
ALTER TABLE имя_таблицы
[ADD [COLUMN]имя_столбца тип_данных 
  	[ NOT NULL ][UNIQUE]
[DEFAULT <значение>][ CHECK (<условие_выбора>)]]
[DROP [COLUMN] имя_столбца [RESTRICT | CASCADE ]]
[ADD [CONSTRAINT [имя_ограничения]]
[{PRIMARY KEY (имя_столбца [,...n])
    |[UNIQUE (имя_столбца [,...n])}
|[FOREIGN KEY (имя_столбца_внешнего_ключа [,...n])
    REFERENCES имя_род_таблицы 
       [(имя_столбца_род_таблицы [,...n])],
[ MATCH {PARTIAL | FULL}
    [ON UPDATE {CASCADE| SET NULL |
        SET DEFAULT | NO ACTION}]
    [ON DELETE {CASCADE| SET NULL |
        SET DEFAULT | NO ACTION}]
    |[CHECK(<условие_выбора>)][,...n]}]
[DROP CONSTRAINT имя_ограничения 
     [RESTRICT | CASCADE]]
[ALTER [COLUMN] SET DEFAULT <значение>]
[ALTER [COLUMN] DROP DEFAULT]

Здесь параметры имеют то же самое назначение, что и в определении оператора CREATE TABLE.

Оператор ALTER TABLE реализован не во всех диалектах языка SQL. В некоторых диалектах он поддерживается, однако не позволяет удалять из таблицы уже существующие столбцы.

Для удаления таблицы используется команда DROP TABLE.

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

Существуют различные типы целостности данных:

• Целостность полей – указывает набор значений данных, которые являются правильными для поля, и определяет, возможно ли использование нулевого значения. Например, поле для хранения пола человека может содержать одно из двух значений – М или Ж. Во-первых, этого достаточно, во-вторых, других значений пола просто не бывает и мы должны запретить ввод других букв в данное поле. Целостность полей часто всего (и лучше) обеспечивается с помощью ограничения CHECK, формата (с помощью шаблона) или региона возможных значений для поля.
• Целостность таблицы – требуют, чтобы все строки в таблице имели уникальный идентификатор, называемый первичным ключом. Может ли первичный ключ изменяться, или может ли строка удаляться, зависит от уровня целостности. Например, в некоторых случаях можно разрешить удаление записей, но чаще всего оно должно быть запрещено. Не желательно терять данные, потому что мы в последствии не сможем узнать историю изменений в таблице.
• Целостность ссылок – подразумевает отношения между первичным ключом (таблицы, на которую ссылаются) и внешним ключом (таблицы, которая ссылается на другую) всегда защищенными. Строка основной таблицы, на которую ссылаются, не может быть удалена и первичный ключ не может быть изменен, если вторичный ключ ссылается на строку, пока не будет уничтожена связь. Иначе связь нарушается и восстановить ее потом становится проблематичным. Вы можете назначить отношения внутри таблицы или между несколькими отдельными таблицами с помощью встроенных в SQL Server средств, не надеясь на возможности языка программирования, который вы используете для доступа к данным. Конечно же, связь между таблицами можно навести и без внешних ключей, но в этом случае сервер не гарантирует целостность. Вся ответственность ложиться на программиста.

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

Как мы можем гарантировать целостность данных? Для этого существует два способа: описанная целостность данных и предшествующая целостность данных. Пока эти понятия не понятны, но после того, как вы увидите, какими средствами достигается тот, или иной способ, все встанет на свои места.

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

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

Предшествующая целостность данных – это программа, которая определяет критерии, которым должны соответствовать данные. Этот метод обеспечивается с помощью процедур и триггеров (о них мы поговорим в главе 3), которые могут выполняться на сервере или с помощью кода программ в клиентском приложении.

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

Ограничение – это основной метод обеспечения целостности данных. В этой секции описывается, как определить, какой тип ограничения использовать, какой тип данных и для какого ограничения использовать, и как определить ограничения.

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

Как мы уже знаем, ограничения могут создаваться во время создания таблицы (CREATE TABLE) или редактирования (ALTER TABLE). Если ограничение назначается отдельному полю, оно называется ограничения уровня поля. Если ограничение ссылается на несколько полей, оно называется ограничением уровня таблицы, даже если оно ссылается не на все колонки таблицы.

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

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

1.5.1. DEFAULT

Ограничение DEFAULT помещает значение в колонку, когда оно не было указано в операторе INSERT. Оно относится только к оператору добавления записи (INSERT) и не срабатывает во время изменения полей (оператор UPDATE). Таким образом, данное ограничение не гарантирует, что поле содержит значение. Пользователь может добавить строку и потом с помощью UPDATE обнулить содержимое поле со значением по умолчанию.

Таким образом, DEFAUL является самым простым и быстрым по скорости выполнения методом обеспечения целостности, но не является гарантом. Необходимы дополнительные средства, например, ограничение на диапазон вводимых значений или триггер. Например, в листинге 1.10, помимо значения DEFAULT мы создаем ограничение, которое не позволяет записывать в поле нулевые значения, что защитит нас от возможности записи в поле NULL даже при обновлении данных:

Листинг 1.10. Создание таблицы с ограничением DEFAULT и CHECK

-- Создание таблицы
CREATE TABLE TestTable 
(
 iID int DEFAULT 1,
 CONSTRAINT check_iID CHECK (iID is NOT NULL)
)

-- Добавление записи с числом 10 в колонке iID
INSERT INTO TestTable 
VALUES (10)

-- Обновление существующих записей, в поле iID 
-- записывается нулевое значение
UPDATE TestTable 
SET iID=NULL

В данном примере мы устанавливаем сразу два ограничения на поле «iID» таблицы TestTable. Первое DEFAULT устанавливает значение по умолчанию, если во время добавления записи для поля «iID» не было указано значения. Вторая проверка CHECK не позволит сделать поле нулевым с помощью операции обновления записей.

После этого в листинге показаны примеры добавления и обновления записи. Во время обновления мы пытаемся записать в поле значение NULL. В ответ на это сервер вернет нам ошибку и сообщит, что сработало ограничение check_iID.

1.5.2. CHECK

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

CREATE TABLE TestTable 
(
 id int DEFAULT 1 NOT NULL,
 vcName varchar(50) NOT NULL,
 dBirthDate datetime, 
 CONSTRAINT CK_birthdate 
  CHECK (dBirthDate > '01-01-1900'
         AND dBirthDate<getdate())
)

В данном примере создается таблица для хранения имен и дат рождений «dBirthDate». Дата рождения не может быть меньше 1900. Людей, которым более 105 лет на земле осталось не так много, и вероятность того, что такие люди попали к нам в базу стремится к нулю.

Как мы уже знаем, ограничение CHECK срабатывает не только при вставке данных, но и при обновлении.

При создании ограничения можно ссылаться и на другие таблицы. Например, в следующей таблицы помимо даты рождения человека в таблице будет храниться дата выдачи паспорта – «dDocDate». Вполне логично, что дата выдачи паспорта должна быть больше даты рождения и меньше текущей. Паспорт не может быть выдан до рождения, поэтому в следующей таблице мы гарантируем целостность поля даты рождения:

CREATE TABLE TestTable 
(
 id int DEFAULT 1 NOT NULL,
 vcName varchar(50) NOT NULL,
 dBirthDate datetime, 
 dDocDate datetime, 
 CONSTRAINT CK_birthdate 
  CHECK (dDocDate>dBirthDate 
        AND dBirthDate<getdate())
)

Мы еще не говорили про запросы выборки данных и подзапросы, но необходимо заметить, что в ограничении CHECK нельзя использовать подзапросы.

1.5.3. Ключи

Ограничение PRIMARY KEY определяет первичный ключ таблицы, который уникально идентифицирует строку. Это гарантирует целостность таблицы. Когда мы изучали оператор PRIMARY KEY, то уже видели примеры и мне добавить нечего. Давайте только сведем все вышесказанное, чтобы увидеть свойства первичного ключа:

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

Ограничение FOREIGN KEY (внешний ключ) гарантирует ссылочную целостность. Ограничение внешнего ключа определяет ссылку на колонку с первичным ключом или уникальную колонку в этой же или другой таблице. С помощью такого ключа обеспечивается целостность связей между таблицами.

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

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

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

Опция REFERENCE команд CREATE TABLE и ALTER TABLE поддерживаю опции ON DELETE и ON UPDATE. Эти опции позволят вам указать опции CASCADE и NO ACTION:

[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ] 

NO ACTION указывает что любые попытки удалить или обновить ключ, на который ссылается вторичный ключ в другой таблице заканчиваются ошибкой, и изменения откатываются. Это значение по умолчанию и я рекомендую остановиться на нем. Напоминаю, что без особой надобности не стоит включать каскадных действий.

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

1.5.4. Уникальность

Ограничение UNIQUE (уникальность) указывает, что две строки в колонке не могут содержать одно и тоже значение. Это ограничение обеспечивает целостность таблицы с уникальным индексом. Ограничение уникальности эффективно, когда вы уже имеете первичный ключ, но хотите гарантировать, что другое поле тоже уникально.

В отличии от первичного ключа, у уникального поля может быть одна строка с нулевым значением. Это необходимо учитывать и если вы хотите, чтобы поле не могло содержать нулевого значения, добавьте ограничение CHECK, например:

CREATE TABLE TestTable 
(
 iID int DEFAULT 1,
 CONSTRAINT check_iID CHECK (iID is NOT NULL),
 CONSTRAINT u_iID
   UNIQUE NONCLUSTERED(iID)
)

В этом примере создается два ограничения на поле «iID»: одно на уникальность и одно на запрет NULL значений.

Ограничений уникальности может быть несколько в таблице, и для каждого такого поля будет создаваться индекс.

1.5.5. Отключение ограничений

Для повышения производительности, иногда разумно отключить ограничения. Для примера, более эффективно позволить выполнить большую операцию обновления или вставки данных, без ограничений.

Когда вы определяете ограничение на таблицу, которая уже содержит данные, MS SQL Server проверяет данные автоматически, гарантируя, что после создания ограничения, существующие данные соответствуют требованиям.

Отключать можно только ограничения CHECK и FOREIGN KEY. Другие ограничения должны быть удалены и потом снова добавлены.

Для отключения проверки, когда вы добавляете ограничения CHECK и FOREIGN KEY на таблицу с существующими данными, включите опцию WITH NOCHECK в оператор ALTER TABLE.

В следующем примере, мы добавляем ограничение FOREING KEY. Ограничение не проверяет существующие данные на момент добавления ограничения:

ALTER TABLE TestTable
WITH NOCHECK
 ADD CONSTRAINT FK_TestTable
 FOREIGN KEY (Field1)
 REFERENCES PrimaryTable(Field2)

Вы можете отключить проверку ограничений на существующие ограничения CHECK и FOREIGN KEY так, что любые данные, которые вы изменяете или добавляете в таблицу, не проверяются с ограничением. Вы можете захотеть отключить проверку ограничений когда:

• вы уже убедились, что данные соответствуют требованиям;
• вы хотите загрузить данные, которые не соответствуют ограничению. Позже, вы можете выполнить запрос для изменения данных и включить ограничение.

Включение ограничения, которое было отключено, требует выполнения другого оператора ALTER TABLE, которое содержит опцию CHECK или CHECK ALL.

ALTER TABLE имя таблицы
 {CHECK | NOCHECK} CONSTRAINT 
 {ALL | ограничение [, …]}

1.5.5. Роли и объекты значений по умолчанию

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

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

Объект значения по умолчанию создается следующим образом:

CREATE DEFAULT имя
 AS выражение константы

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

Следующий пример помещает шаблон номера телефона, если не указано реальное значение:

CREATE DEFAULT default_phone_no
 AS '(000)00-00-00'

Следующая команда связывает созданный объект с полем «Phone» таблицы TestTable:

EXEC sp_bindefault default_ phone_no, 'TestTable.Phone'

Объекты значений по умолчанию имеют свои ограничения:

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

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

Объявление роли может содержать любые выражения, которые действительны для оператора WHERE, который мы будем внимательно рассматривать в главе 2. В общем виде роль выглядит следующим образом:

CREATE RULE имя
 AS выражение условия

После создания роли, вы должны связать его с колонкой или типом данных определенным пользователем с помощью вызова системной процедуры sp_bindrule. Для отключения правила выполните системную процедуру sp_unbinrule. В следующем примере, правило гарантирует, что поле «Pol» может содержать только букву М или Ж:

CREATE RULE rule_pol
 AS @Pol IN ('М', 'Ж')

Следующий пример связывает созданную роль с полем » Pol» таблицы TestTable:

EXEC sp_bindrule rule_pol, 'TestTable.Pol'

Для удаления значения по умолчанию из базы данных используйте оператор DROP:

DROP DEFAULT  имя [,…]
DROP RULE имя [,…]

Например, созданную ранее роль можно удалить командой:

DROP RULE rule_pol

Правила являются достаточно мощным решением, но при этом они обладают достаточно большим количеством ограничений:

• новое правило не может повлиять на уже существующие значения, потому что оно срабатывает только при добавлении или изменении строк данных;
• в правилах можно использовать только не сложные вычисления с константами и функциями MS SQL Server;
• в отличии от ограничений, нельзя сравнивать значения полей;
• в правиле нельзя обращаться к таблицам для выборки данных. Чтобы была возможность для выборки, стоит обратить внимание на триггеры, которые мы будем рассматривать в 3-й главе;
• с одним столбцом можно связать только одно правило. Если бы можно было связывать два правила, то у нас появилась хоть какая-то возможность создавать более сложные запреты, но пока этого нет. Если попытаться связать правило со столбцом, у которого уже есть правило, то старое значение будет заменено новым;
• вполне логичное ограничение – тип данных, используемый в правиле должен совпадать с типом поля;
• связанное правило нельзя удалить. Необходимо сначала удалить связь.

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

Мы рассмотрели правила потому, что они есть и вы должны о них знать, а может быть, вы найдете им применения, даже не смотря на то, что это не рекомендуется. Но прежде чем вы это сделаете, спешу вас предостеречь. То, что не рекомендуется к использованию производителем и оставлено только для совместимости может больше не появиться в будущих версиях. Это значит, что в следующей версии MS SQL Server правила могут быть выведены из Transact-SQL, и ваша база данных может перестать работать или будет работать без ограничений и потребуются изменения структуры.