|
|
|
от Mike Aubert |
|
|
Добро пожаловать в статью 12 моей серии «Изучи администрирование SQL Server за 15 минут в неделю!». В предыдущей статье мы рассмотрели различные типы данных используемых в SQL Server и то, как добавлять таблицы в базу данных. На этой неделе мы продолжим наше обсуждение таблиц и рассмотрим, как реализовать ссылочную целостность (referential integrity). В статью вошли следующие темы:
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? Короче говоря, мы получим лишние дочерние строки (в таблице с внешним ключом), не имеющие соответствующих им родительских строк (в таблице с первичным ключом). В идеальном случае все приложения должны изменять таблицы должным образом. Но наш мир несовершенен и случаются ситуации (и будут случаться всегда), подобные описанным выше. Для небольшого проекта, в котором программист является также администратором БД, возможно уменьшить количество нарушений, старательно разрабатывая структуру приложений… но что же может случиться, если к базе данных одновременно подключается дюжина приложений, написанных разными программистами?! Простая ошибка может нарушить целостность базы данных и вы никогда не узнаете, откуда она появилась. <soapbox>Повторяйте за мной: «Это обязанность СУРБД (системы управления реляционной базой данных), а не конечного приложения, проверять ссылочную целостность!»</soapbox> Для того чтобы активизировать ссылочную целостность между таблицами, необходимо установить ограничение FOREIGN KEY или «связь» в SQL Server. Для того чтобы продемонстрировать это я создал две таблицы – Parent и Child. Столбец ParentID в таблице Child является внешним ключом, используемым для ссылок на родительскую (для дочерней) таблицу. Диаграмма базы данных показывает эти две таблицы (мы будем изучать, как создавать подобные диаграммы в следующей статье).
До этого момента еще возможно существование таблицы Child без Parent. Для того чтобы установить связь и создать ссылочную целостность: 1. Откройте в дизайнере
таблиц (table
designer)
одну из таблиц, которая станет
частью отношения (в нашем примете
это таблица Parent)
и щелкните в панели инструментов
кнопку
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 позднее) могут приводить к тому, что связи можно будет создать или, наоборот, нельзя. Например: если я разрешу столбцу ParentID в таблице Child принимать пустые значения, то появится возможность добавлять множество строк (допустим, что на столбце ParentID таблицы Child нет ограничения UNIQUE), содержащих <NULL>, или «ничего» в ячейках столбца ParentID. Этот случай показан на следующем рисунке:
Если в столбце ParentID будет что-либо отличное от <NULL>, то связь между таблицами с первичным и внешним ключами может быть установлена. То есть, если в строке, добавленной в приведенную выше таблицу, для ParentID установлено значение 5 (вместо <NULL>), то соответствующая строка с ID равным 5 должна существовать в таблице Parent. Используйте следующие вопросы для того, чтобы добавить к столбцу ограничение FOREIGN KEY: 1. Должна ли каждая строка таблицы с внешним ключом иметь соответствующую строку в таблице с первичным ключом?
2. Какой тип связи существует между таблицами?
И последнее: поскольку Enterprise Manager позволяет нам делать практически все… он также позволяет устанавливать связи при помощи SQL операторов. Посмотрите определение операторов CREATE TABLE и ALTER TABLE в справочной системе SQL Server. Итак, это все на этой неделе. На следующей мы продолжим наше обсуждение ограничений, если говорить конкретнее, мы рассмотрим создание ограничения CHECK. И как всегда…если у вас возникнут какие-либо технические вопросы, пожалуйста, присылайте их на доску объявлений сайта 2000Trainers.com SQL message board. Нетехнические вопросы, комментарии и обратная связь – по адресу моей электронной почты. Я надеюсь, что вы найдете эти статьи полезными и был бы рад узнать ваше мнение о них. Mike Aubert, MCSE, MCDBA, MCSD. |
“Manage Relationships…” (управление
связями). Появится следующее окно:
