1.5. Обеспечение целостности данных

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

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

• Целостность полей – указывает набор значений данных, которые являются правильными для поля, и определяет, возможно ли использование нулевого значения. Например, поле для хранения пола человека может содержать одно из двух значений – М или Ж. Во-первых, этого достаточно, во-вторых, других значений пола просто не бывает и мы должны запретить ввод других букв в данное поле. Целостность полей часто всего (и лучше) обеспечивается с помощью ограничения 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, и ваша база данных может перестать работать или будет работать без ограничений и потребуются изменения структуры.