Добро пожаловать в статью 12 моей серии «Изучи администрирование SQL Server за 15 минут в неделю!». В предыдущей статье мы рассмотрели различные типы данных используемых в SQL Server и то, как добавлять таблицы в базу данных. На этой неделе мы продолжим наше обсуждение таблиц и рассмотрим, как реализовать ссылочную целостность (referential integrity). В статью вошли следующие темы:

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

ACID

Давайте вернемся к статье, посвященной созданию баз данных, в которой я приводил пример транзакции, переводящей деньги с одного банковского счета на другой. Этот пример иллюстрировал, что транзакция препятствует изменению счета А до тех пор, пока не будет завершено изменение счета В. Иначе говоря, в ходе выполнения транзакции, либо все изменения будут внесены в базу данных (commit) или произойдет откат (roll back) всех изменений. Давайте возьмем наше определение транзакции и расширим его.

Короткое определение транзакции звучит так: «последовательность операций, выполняемых как логическая единица работы». Будем считать, что «транзакция» - это целостная логическая единица работы, обладающая четырьмя основными свойствами. Эти свойства (атомарность, согласованность, изоляция и устойчивость) называются ACID-свойствами (от англ. Atomicity, Consistency, Isolation, and Durability):

Атомарность – транзакция должна быть элементарной единицей работы, в ходе которой либо все изменения вносятся в базу данных, либо ни одно из них не вносится.

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

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

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

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

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

Есть много типов ограничений, которые мы можем использовать для того, чтобы контролировать достоверность данных, включая те, что мы уже использовали (такие как возможность ввода в поле пустых значений и типы данных). Другие виды ограничений мы рассмотрим в будущих статьях. В этой же статье давайте сфокусируемся на ограничении Foreign Key (Внешний ключ).

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

Когда таблицы организованы в базу данных, большинство из них, если не все, имеют логическую связь друг с другом. Например, каждой строке таблицы Order (заказ) может соответствовать несколько строк таблицы Order Detail (детали заказа), каждой строке таблицы Salesperson (продавец) – несколько строк из Order и т.д. (если вы не очень понимаете, о чем идет речь, предлагаю вам остановиться и почитать статью о логической структуре баз данных)… что же нам необходимо сделать? Подтверждать достоверность! Приведу пример:

Представим себе, что у меня есть две таблицы, называемые Order и OrderDetail. Каждому заказу может соответствовать множество деталей заказа. Каждая деталь заказа соответствует одному и только одному заказу. Что же случится, если программа, добавив несколько строк в OrderDetail, «зависнет» перед тем, как добавить соответствующие строки в таблицу Order? Или что произойдет, если приложение удалит строку из таблицы Order без удаления соответствующих строк из таблицы OrderDetail? Короче говоря, мы получим лишние дочерние строки (в таблице с внешним ключом), не имеющие соответствующих им родительских строк (в таблице с первичным ключом).

В идеальном случае все приложения должны изменять таблицы должным образом. Но наш мир несовершенен и случаются ситуации (и будут случаться всегда), подобные описанным выше. Для небольшого проекта, в котором программист является также администратором БД, возможно уменьшить количество нарушений, старательно разрабатывая структуру приложений… но что же может случиться, если к базе данных одновременно подключается дюжина приложений, написанных разными программистами?! Простая ошибка может нарушить целостность базы данных и вы никогда не узнаете, откуда она появилась. Повторяйте за мной: «Это обязанность СУРБД (системы управления реляционной базой данных), а не конечного приложения, проверять ссылочную целостность!»

Для того чтобы активизировать ссылочную целостность между таблицами, необходимо установить ограничение FOREIGN KEY или «связь» в SQL Server. Для того чтобы продемонстрировать это я создал две таблицы – Parent и Child. Столбец ParentID в таблице Child является внешним ключом, используемым для ссылок на родительскую (для дочерней) таблицу.

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

До этого момента еще возможно существование таблицы Child без Parent.

Для того чтобы установить связь и создать ссылочную целостность:

1. Откройте в дизайнере таблиц (table designer) одну из таблиц, которая станет частью отношения (в нашем примете это таблица Parent) и щелкните в панели инструментов кнопку “Manage Relationships…” (управление связями). Появится следующее окно:

2. Щелкните New.

3. Установите таблицу Parent в качестве “Primary key table” (таблицы с первичным ключом) и Child в качестве “Foreign key table” (таблицы с внешним ключом).

4. Под полем Parent выберите ID в первом поле. Под Child выберите ParentID. Ваш экран должен выглядеть так:

Перед тем, как вы щелкните Close и сохраните таблицу, давайте рассмотрим опции в нижней части этого окна:

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

Enforce relationship for replication (Форсировать связь через репликацию) – применяет ограничение при копировании таблицы с внешним ключом на другую базу данных (мы вернемся к репликации позднее).

Enforce relationship for INSERTs and UPDATEs (Форсировать связь для операторов INSERT и UPDATE) – применяет ограничение для данных, вводимых, удаляемых или обновляемых в таблице с внешним ключом. Также предотвращает удаление строки в таблице с первичным ключом, если соответствующая строка существует в таблице с внешним ключом.

Cascade Update Related Fields (Каскадное обновление связанных полей) – приказывает СУБД автоматически обновлять значения внешнего ключа этой связи, если обновляется значение первичного ключа. Например: Если эта опция выбрана и значение CustomerID меняется с 1 на 2, то любая таблица с внешним ключом (такая как Order), ссылающаяся на CustomerID, будет обновлена так, чтобы ссылаться на новые значения CustomerID.

Cascade Delete Related Fields (Каскадное удаление связанных полей) – приказывает СУБД автоматически удалять строки таблицы с внешним ключом, если соответствующие строки в таблице с первичным ключом были удалены. Например: если эта опция выбрана и заказ был удален из таблицы Order, все строки с внешними ключами (например, в таблице OrderDetails), ссылающиеся на данный заказ будут также удалены.

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

Теперь диаграмма базы данных показывает связь «один ко многим», обеспечивающую ссылочной целостность:

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

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

Также помните, что такие факторы, как допуск пустых значений или наличие ограничения UNIQUE в столбце с внешним ключом (и еще раз: мы рассмотрим, как добавлять ограничение UNIQUE позднее) могут приводить к тому, что связи можно будет создать или, наоборот, нельзя. Например: если я разрешу столбцу ParentID в таблице Child принимать пустые значения, то появится возможность добавлять множество строк (допустим, что на столбце ParentID таблицы Child нет ограничения UNIQUE), содержащих , или «ничего» в ячейках столбца ParentID. Этот случай показан на следующем рисунке:

Если в столбце ParentID будет что-либо отличное от , то связь между таблицами с первичным и внешним ключами может быть установлена. То есть, если в строке, добавленной в приведенную выше таблицу, для ParentID установлено значение 5 (вместо ), то соответствующая строка с ID равным 5 должна существовать в таблице Parent.

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

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

• Да – Удалите флажок “Allow Null” для столбца с внешним ключом;
• Нет – Установите флажок “Allow Null” для столбца с внешним ключом.

2. Какой тип связи существует между таблицами?

• Один к одному – Добавьте ограничение UNIQUE в столбец с внешним ключом;
• Один ко многим – Не добавляйте ограничение UNIQUE в столбец с внешним ключом;
• Многие ко многим – Создайте дополнительную таблицу и не добавляйте ограничение UNIQUE ни в один из столбцов с внешним ключом.

И последнее: поскольку Enterprise Manager позволяет нам делать практически все… он также позволяет устанавливать связи при помощи SQL операторов. Посмотрите определение операторов CREATE TABLE и ALTER TABLE в справочной системе SQL Server.

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