Урок 1: Ограничения целостности данных
В рамках этого урока будут рассмотрены следующие вопросы
} Для чего используются ограничения целостности данных?
} Какие механизмы помогают поддерживать целостность данных?
} Какие типы ограничений целостности данных существуют в Microsoft SQL Server?
Назначение
Ограничения позволяют задать метод, с помощью которого компонент Database Engine автоматически обеспечивает целостность базы данных. Microsoft SQL Server предоставляет два механизма для реализации бизнес-правил и обеспечения целостности данных: ограничения и триггеры.
Типы ограничений целостности данных
Планирование и создание таблиц требует указания допустимых значений для столбцов и определения способов принудительного обеспечения целостности данных в них. SQL Server предоставляет следующие механизмы для принудительного обеспечения целостности данных в столбце.
· Определения DEFAULT значений
· Разрешение значений NULL
· Ограничения PRIMARY KEY
· Ограничения FOREIGN KEY
· Ограничения UNIQUE
· Ограничения CHECK
Ограничения задают правила допустимости определенных значений в столбцах и представляют собой стандартный механизм обеспечения целостности. Оптимизатор запросов также использует определения ограничений для построения высокопроизводительных планов выполнения запросов.
Определение DEFAULT значений
Задает значение по умолчанию, которым Database Engine будет заполнять столбец, если при вставке строки для этого столбца значение не указано. Значение по умолчанию могут быть любым выражением, результат которого является константой, например собственно константой соответствующего типа или встроенной функцией.
Значения по умолчанию создаются с помощью ключевого слова DEFAULT инструкции CREATE TABLE. При этом константное выражение будет присвоено столбцу как значение по умолчанию.
В следующем примере создается таблица, в которой используются различные типы выражений по умолчанию. Здесь объект по умолчанию, назначенный столбцу, создается и связывается со столбцом. Затем производится тестовая вставка данных без указания значений для столбцов со значениями по умолчанию, а также выборка этой тестовой строки для проверки того, что были использованы все значения по умолчанию.
CREATE TABLE test_defaults
(date_ins datetime DEFAULT getdate(), --Preferred default definition
mathcol smallint DEFAULT 10 * 2, --Preferred default definition
char2 char(3) DEFAULT 'xyz') --Preferred default definition;
Ограничение NOT NULL
Указывает, что в столбце недопустимы значения NULL.
Допустимость значения NULL в столбце определяет, могут ли строки таблицы содержать значения NULL для этого столбца. Значение NULL отличается от нуля (0), пробела или символьной строки нулевой длины, например "". Значение NULL обозначает, что поле не было заполнено. Присутствие NULL, как правило, подразумевает, что значение неизвестно или неопределенно.
Когда в таблицу вставляется строка без указания значения для столбца, допускающего значения NULL, компонент Database Engine присваивает ему значение NULL, если отсутствует определение DEFAULT или объект по умолчанию. Столбец, определенный с помощью ключевого слова NULL, также поддерживает явно заданное пользователем значение NULL, независимо от типа данных и наличия соответствующего значения по умолчанию. Значение NULL не следует заключать в кавычки, так как в этом случае оно будет рассматриваться как символьная строка "NULL".
Запрет значений NULL в столбце обеспечивает целостность данных, гарантируя, что столбец в строке будет всегда содержать данные. Если значения NULL запрещены, то пользователь, заполняя таблицу данными, должен ввести значения в столбцы, иначе строка таблицы не будет записана в базу данных.
Ограничения CHECK
Обеспечивают целостность домена путем ограничения значений, которые могут быть помещены в столбец.
Ограничение CHECK задает логическое условие (принимающие значение TRUE, FALSE или unknown), которое применяется ко всем значениям, вставляемым в столбец. Все значения, для которых получается значение FALSE, отбрасываются. Для каждого столбца можно указать несколько ограничений CHECK. Кроме того, можно применять одно проверочное ограничение к нескольким столбцам. Для этого ограничение нужно создать на уровне таблицы.
При задании условия ограничения CHECK допустимо использовать константы, столбцы этой же таблицы и встроенные функции.
В следующем примере показано создание ограничения chk_id. Это ограничение, среди прочего, следит за соблюдением домена первичного ключа, обеспечивая, чтобы в качестве значений ключа вводились только числа указанного диапазона.
CREATE TABLE cust_sample
(
cust_id int PRIMARY KEY,
cust_name char(50),
cust_address char(50),
cust_credit_limit money,
CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000)
)
Ограничения UNIQUE
Обеспечивают уникальность значений в наборе столбцов.
Ограничение UNIQUE не допускает использования одного и того же значения в двух строках столбца. Допускается использование значения NULL в качестве одного из уникальных значений (как и всякое другое значение столбца с ограничением UNIQUE, NULL может встречаться только один раз).
При создании ограничения UNIQUE создается уникальный некластеризованный индекс для принудительного применения ограничение UNIQUE по умолчанию. Если кластеризованный индекс в таблице еще не создан, то можно указать чтобы создавался уникальный кластеризованный индекс.
Для таблицы можно задать несколько ограничений UNIQUE.
Ограничения PRIMARY KEY
Используются для указания столбца или набора столбцов, которые имеют значения, уникально идентифицирующие строку в таблице.
Обычно в таблице есть столбец или комбинация столбцов, содержащих значения, уникально определяющие каждую строку таблицы. Такой столбец или сочетание столбцов, называются потенциальными ключами. На основе этих столбцов можно создать первичный ключ, задав ограничение PRIMARY KEY при создании или изменении таблицы.
В таблице может быть только одно ограничение PRIMARY KEY, и столбец с данным ограничением не может содержать значения NULL. Из-за того, что ограничения PRIMARY KEY гарантируют уникальность данных, они часто определяются в столбце идентификаторов.
При задании для таблицы ограничения PRIMARY KEY компонент Database Engine гарантирует уникальность данных созданием уникального индекса для столбцов первичных ключей. Этот индекс также обеспечивает быстрый доступ к данным при использовании первичного ключа в запросах. Таким образом, выбранные первичные ключи должны соответствовать правилам создания уникальных индексов.
Если ограничение PRIMARY KEY задано более чем для одного столбца, то значения могут дублироваться в пределах одного столбца, но каждая комбинация значений всех столбцов в определении ограничения PRIMARY KEY должна быть уникальной.
Как показано на следующем рисунке, столбцы vendorID и vendorPartID таблицы complexPartTable формируют составное ограничение PRIMARY KEY для данной таблицы. Это гарантирует уникальность комбинации значений столбцов vendorID и vendorPartID.
В столбце первичного ключа значения NULL не допускаются.
При создании ограничения PRIMARY KEY автоматически создается уникальный индекс по столбцам первичного ключа. По умолчанию этот индекс кластеризован. но если в таблице уже есть кластеризованный индекс уникальный индекс первичного ключа будет создан как некластеризованный. Также указать, что индекс будет некластеризованным, можно в команде создания первичного ключа, добавив ключевое слово NONCLUSTERED.
Согласно принципам построения кластеризованных индексов рассмотренных в модуле 4, рекомендуется использовать небольшой столбец с целочисленными значениями в качестве первичного ключа. Это позволит минимизировать затраты дискового пространства для хранения кластеризованного и всех некластеризованных индексов таблицы, поскольку значения столбцов кластеризованного индекса дублируются в узлах некластеризованных индексов.
Хотя уникальность значений ограничения UNIQUE и PRIMARY KEY гарантируют в равной степени, в случае, когда необходимо обеспечить уникальность в столбце или комбинации столбцов, которые не являются первичными ключевыми, вместо ограничения PRIMARY KEY следует использовать ограничение UNIQUE.
В следующем примере создается таблица partTable с указанием поля partId в качестве первичного ключа. Индекс, создаваемый по столбцу partId первичного ключа, будет некластеризованным.
CREATE TABLE partTable
(partId int PRIMARY KEY NONCLUSTERED,
name char(30),
weight decimal(6,2),
color char(15));
Ограничения FOREIGN KEY
Задают и обеспечивают связи между таблицами. Если один или несколько столбцов, в которых находится первичный ключ для одной таблицы, упоминается в одном или нескольких столбцах другой таблицы, то в ссылке внешнего ключа создается связь между двумя таблицами. Этот столбец становится внешним ключом во второй таблице.
Внешний ключ применяется для принудительного установления связи между данными в двух таблицах. В столбцах, включенных во внешний ключ, могут использоваться только значения из столбцов первичного ключа.
Ограничение FOREIGN KEY может ссылаться на столбцы в таблицах этой же базы данных или столбцы этой же самой таблицы.
Ограничение FOREIGN KEY не обязательно должно быть связано с ограничением PRIMARY KEY в другой таблице. Кроме того, с помощью этого ограничения могут указываться столбцы ограничения UNIQUE в другой таблице.
Ограничение FOREIGN KEY может содержать значения NULL. Впрочем, если любой из столбцов сложного ограничения FOREIGN KEY содержит значения NULL, то при проверке будут пропущены все значения, составляющие ограничение FOREIGN KEY. Чтобы проверялись все значения сложного ограничения FOREIGN KEY, укажите для всех участвующих столбцов параметр NOT NULL.
Ссылочная целостность
Хотя главная задача ограничения FOREIGN KEY - управление данными, которые могут быть сохранены в таблице внешнего ключа, это ограничение также контролирует изменение данных в таблице первичного ключа.
Ограничение обеспечивает целостность ссылок следующим образом: оно запрещает изменение данных в таблице первичного ключа, если такие изменения сделают недопустимой ссылку в таблице внешнего ключа. Если при попытке удалить строку в таблице первичного ключа или изменить значение этого ключа окажется, что удаленному или измененному значению первичного ключа соответствует значение в ограничении FOREIGN KEY в другой таблице, то действие выполнено не будет. Для успешного изменения или удаления строки с ограничением FOREIGN KEY необходимо сначала удалить данные внешнего ключа в соответствующей таблице либо изменить данные в таблице внешнего ключа, которые связывают внешний ключ с данными другого первичного ключа. Microsoft SQL Server позволяет автоматизировать эти действия, путем указания двух дополнительных предложений ON DELETE и ON UPDATE.
Предложение ON DELETE определяет действия, предпринимаемые при попытке удаления строки, на которую указывает существующий внешний ключ. В предложении ON DELETE предусмотрены следующие параметры:
· NO ACTION: удаления не произойдет и будет выведено сообщение об ошибке.
· CASCADE: все строки с внешними ключами, указывающими на удаленную строку, также будут удалены.
· SET NULL: всем строкам с внешними ключами, указывающими на удаленную строку, присваивается значение NULL.
· SET DEFAULT: всем строкам с внешними ключами, указывающим на удаленную строку, присваивается установленное для них значение по умолчанию.
Предложение ON UPDATE определяет действия, предпринимаемые при попытке обновления значения потенциального ключа, на которое указывает существующий внешний ключ. Это предложение также поддерживает параметры NO ACTION, CASCADE, SET NULL и SET DEFAULT.
Индексирование ограничений FOREIGN KEY
Необходимость создать индекс внешних ключей часто возникает по следующим причинам:
С помощью ограничений FOREIGN KEY в связанных таблицах проверяются изменения ограничений PRIMARY KEY.
Столбцы внешних ключей часто используются для объединения критериев при соединении данных из связанных таблиц в запросах. Это делается путем сопоставления столбца или столбцов в ограничении FOREIGN KEY в одной таблице с одним или несколькими столбцами первичного или уникального ключей в другой таблице. Индекс позволяет компоненту Database Engine быстро находить связанные данные в таблице внешних ключей. Впрочем, создание индекса не является обязательным. Данные из двух связанных таблиц можно объединять, даже если в таблицах нет соответствующих ограничений PRIMARY KEY или FOREIGN KEY, однако связь внешних ключей между двумя таблицами означает, что эти две таблицы оптимизированы для соединения в запросе, где ключи используются в качестве критериев.