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

Целостность сущностей предполагает, что для каждой строки таблицы имеется уникальный идентификатор, который в случае необходимости позволит найти эту строку. Концепция целостности сущностей является основополагающей для проектирования и создания баз данных. Первичный ключ таблицы – это один или несколько столбцов, которые однозначно определяют каждую ее строку. Значения первичного ключа уникальны - не может быть двух совпадающих значений. Более подробно о том, что собой представляют первичные ключи и почему им придают такое большое значение, написано в статье «Как выбрать первичный ключ» в апрельском номере журнала.

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

Неопределенное значение не является ни пустой форматированной величиной для данных типа символов или дат, ни нулевым значением для чисел. Неопределенное значение соответствует одному из трех состояний данных: 1) значение непригодно; 2) значение пригодно, но не доступно; 3) пригодность значения не определена. Подробнее об этом написано в октябрьском номере журнала.

Стандартным способом обеспечения целостности сущностей в среде SQL Server является определение первичных ключей для каждой таблицы. Для этого в операторы CREATE TABLE или ALTER TABLE вводится дополнительная спецификация, как показано в листинге 1. При исполнении такой команды SQL Server строит уникальный индекс по первичному ключу. Такой подход гарантирует претворение в жизнь правила, которое запрещает вводить дубликат значения в столбец, по которому построен уникальный индекс.

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

Внешние ключи, то есть столбцы, которые устанавливают связи ссылающейся таблицы, называемой «мастер», со справочной таблицей, называемой «деталь», реализуют отношение один–ко-многим (1:М) между двумя таблицами. Внешний ключ (столбец в ссылающейся таблице) всегда должен иметь соответствующий ему первичный ключ (столбец того же типа и длины данных в справочной таблице). Домен внешнего ключа не может выходить за границы домена соответствующего ему первичного ключа. Эти домены должны совпадать, в противном случае внешний ключ может иметь неопределенное значение.

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

В случае зависимого (идентифицирующего) отношения каждая строка в ссылающейся таблице должна иметь связанную с ней строку в справочной таблице. Зависимое отношение 1:М требует применения специальных действий. Реализация этого правила может проводиться либо декларированием ссылки внешнего ключа во время создания базы данных (в операторах CREATE TABLE или ALTER TABLE), либо с помощью триггеров. Объявление ссылок во время создания таблицы известно как декларирование ссылочной целостности (ДСЦ). Хотя коды ДСЦ исполняются быстрее, чем коды триггеров, обе схемы контролируют и поддерживают ссылочную целостность данных. Вам не удастся ввести в ссылающуюся таблицу значение внешнего ключа, которое отсутствует в справочной таблице. Листинг 2 содержит пример ДСЦ: столбец pub_id ссылается на столбец первичного ключа таблицы Publishers.

Триггер представляет собой особый вид хранимых процедур, который автоматически вызывается при выполнении определенного действия с таблицей, например, вставки, удаления или обновления строки. Перед тем, как будет произведено действие со строкой (вставка или удаление), код, встроенный в триггер, проверяет заданное условие. В зависимости от результатов проверки исполняется та или иная часть кода триггера. Поскольку триггер является кодом, то в нем можно задавать очень сложные алгоритмы и правила проверки. Например, можно проводить проверки ссылочной целостности в масштабах всей базы данных и выполнять каскадное удаление всех строк из ссылочной таблицы, связанных с определенной записью в ссылающейся таблице. Триггер срабатывает только после того, как будут выполнены все проверки, входящие в ДСЦ. Хотя ДСЦ работает быстрее, чем триггер, но предоставляемые при этом возможности несколько меньше. Использовать и ДСЦ и триггеры для проверки одного и того же условия нецелесообразно, так как ДСЦ выполняется до начала запуска триггеров, и в случае, если проверка условия ДСЦ даст отрицательный результат, триггер так и не будет запущен. Листинг 3 иллюстрирует использование триггеров для проверки ссылочной целостности. Код проводит проверку наличия соответствующей строки в справочной таблице при вводе новой строки в ссылающуюся таблицу.

Целостность домена гарантирует, что все значения некоторого столбца принадлежат множеству допустимых значений. Каждый столбец имеет определенное множество значений, например, для столбца Pubs..zip такое множество составляют все пятизначные числа, а для столбца Pubs..au_name – символьные строки, а для столбца Sales..ord_date - все даты. Если вы задаете ограничение на значения элементов некоторого столбца, то тем самым вы обеспечиваете целостность домена. Реализация целостности доменов может быть очень простой – достаточно правильно выбрать для столбца тип и длину данных. В таблице заголовков базы данных Pubs для столбца Pubdate указан тип данных datetime и введен запрет на неопределенные значения. Подобный выбор создателя таблицы обеспечивает наличие в этом столбце значений, которые являются правильными датами, например, 11/22/99. Таким способом обеспечивается целостность домена.

В стандартах ANSI SQL-89 и SQL-92 введен оператор создания домена CREATE DOMAIN, который в Transact SQL (T-SQL) обрабатывается как задаваемый пользователем тип данных (UDT) с проверками и ограничениями. Домен в ANSI SQL-89/92 получается из существующих базовых типов данных, как в следующем примере псевдокода:

В T-SQL можно создать элементарные домены, строя UDT на базе типов данных SQL Server. Кроме того, можно добавить в UDT опции неопределенных значений, как это показано в примере из электронного руководства по T-SQL:

При использовании этого UDT в операторах CREATE TABLE или ALTER TABLE необходимо дополнить процесс обеспечения целостности доменов проверкой ограничений. Выполняемая во время создания таблицы проверка ограничений обеспечивает целостность домена путем ограничения набора значений, которые могут быть помещены в столбец. В листинге 4 приведен пример подобного ограничения.

ДСЦ также является одной из форм целостности домена. При реализации отношения 1:М домен внешнего ключа и домен соответствующего первичного ключа должны совпадать. В примере листинга 4 никогда не удастся ввести в столбец pub_id значение, которое уже не содержится в таблице Publishers. Таким образом, домен столбца title6.pub_id оказывается ограничен ссылкой внешнего ключа.

Следуя этой логике, можно создавать таблицы, предназначенные специально для ограничения значений столбца в другой таблице, то есть для обеспечения доменной целостности. При этом специальная таблица называется справочной или ссылочной. Она связана с модифицируемой таблицей отношением 1:М, которое всегда выполняется. Например, в листинге 5 столбец Pubs.type ссылается на столбец в таблице TypeTable. Листинг 5 содержит описание структуры справочной таблицы TypeTable и примеры значений. Если вы попытаетесь ввести строку в таблицу Pubs.title7 и при этом используете значение, отсутствующее в таблице TypeTable, то такая операция закончится неудачей. Правила ДСЦ, записанные в Pubs.title7, применяемые совместно с перечнем возможных значений в таблице TypeTable обеспечивают целостность домена.

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

Бизнес-целостность, называемая также пользовательской целостностью, гарантирует, что при работе с базой данных выполняются все заданные пользователем правила бизнеса, инструкции, директивы и процедуры. Обычно бизнес-целостность реализуется с помощью хранимых процедур и триггеров. Хранимая процедура представляет собой запрос, который хранится на сервере баз данных и используется для обработки строк и возврата результатов. Триггеры поддерживают целостность данных «за сценой», поскольку они срабатывают так, что пользователь даже не осознает, что они были запущены. Листинг 7 содержит пример кода триггера, обеспечивающего бизнес-целостность.

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