Как ограничения воздействуют на возможность и невозможность использования команды модификации DML? Для полей, определённых как внешние ключи, ответ довольно простой: любые значения, которые вы помещаете в эти поля командой INSERT или UPDATE, должны уже быть представлены в их родительских ключах. Вы можете помещать пустые (NULL) значения в эти поля, несмотря на то что значения NULL непозволительны в родительских ключах, если они имеют ограничение NOT NULL. Вы можете удалять (DELETE) любые строки с внешними ключами, не используя родительские ключи вообще.
Поскольку затронут вопрос об изменении значений родительского ключа, ответ, по определению ANSI, ещё проще, но, возможно, несколько более ограничен: любое значение родительского ключа, на который ссылаются с помощью значения внешнего ключа, не может быть удалено или изменено. Это означает, например, что вы не можете удалить заказчика из таблицы Заказчиков, пока он ещё имеет заказы в таблице Заказов. В зависимости от того, как вы используете эти таблицы, это может быть или желательно, или хлопотно. Однако это, конечно, лучше, чем иметь систему, которая позволит вам удалить заказчика с текущими заказами и оставить таблицу Заказов ссылающейся на несуществующих заказчиков. Смысл этой системы ограничения в том, что создатель таблицы Заказов, используя таблицу Заказчиков и таблицу Продавцов как родительские ключи, может наложить значительные ограничения на действия в этих таблицах. По этой причине вы не сможете использовать таблицу которой вы не распоряжаетесь (т.е. не вы её создавали и не вы являетесь её владельцем), пока владелец (создатель) этой таблицы специально не передаст вам на это право (что объясняется в Главе 22).
Имеются некоторые другие возможные действия изменения родительского ключа, которые не являются частью ANSI, но могут быть найдены в некоторых коммерческих программах. Если вы хотите изменить или удалить текущее ссылочное значение родительского ключа, имеются три возможности:
* Вы можете ограничить или запретить изменение (способом ANSI), обозначив, что изменения в родительском ключе ограничены.
* Вы можете сделать изменение в родительском ключе и тем самым сделать изменения во внешнем ключе автоматическим, что называется каскадным изменением.
* Вы можете сделать изменение в родительском ключе и установить внешний ключ в NULL автоматически (полагая, что NULL разрешены во внешнем ключе), что называется пустым изменением внешнего ключа.
Даже в пределах этих трёх категорий вы можете не захотеть обрабатывать все команды модификации таким способом. INSERT, конечно, к делу не относится. Она помещает новые значения родительского ключа в таблицу, так что ни одно из этих значений не может быть вызвано в данный момент. Однако вы можете захотеть позволить модификациям быть каскадными, но без удалений, и наоборот. Лучшей может быть ситуация, которая позволит вам определять любую из трёх категорий, независимо от команд UPDATE и DELETE. Мы будем, следовательно, ссылаться на эффекты модификации (update effects) и эффекты удаления (delete effects), которые определяют, что случится, если вы выполните команды UPDATE или DELETE в родительском ключе. Эти эффекты, о которых мы говорили, называются: Ограниченные (RESTRICTED) изменения, Каскадируемые (CASCADES) изменения и Пустые (NULL) изменения.
Фактические возможности вашей системы должны строго соответствовать стандарту ANSI - это эффекты модификации и удаления, оба автоматически ограниченные - для более идеальной ситуации, описанной выше. В качестве иллюстрации мы покажем несколько примеров того, что вы можете делать с полным набором эффектов модификации и удаления. Конечно, эффекты модификации и удаления, являющиеся нестандартными средствами, испытывают недостаток в стандартном синтаксисе. Синтаксис, который мы используем здесь, прост в написании и будет служить в дальнейшем для иллюстрации функций этих эффектов.
Для полноты эксперимента позволим себе предположить, что вы имеете причину изменить поле snum таблицы Продавцов в случае, когда наша таблица Продавцов изменяет разделы. (Обычно изменение первичных ключей это не то, что мы рекомендуем делать практически. Просто это ещё один из доводов, чтобы иметь первичные ключи, которые не умеют делать ничего другого, кроме как действовать как первичные ключи: они не должны изменяться.) Когда вы изменяете номер продавца, вы хотите, чтобы были сохранены все его заказчики. Однако, если этот продавец покидает свою фирму или компанию, вы можете не захотеть удалить его заказчиков при удалении его самого из БД. Взамен вы захотите убедиться, что заказчики назначены кому-нибудь ещё. Чтобы сделать это, вы должны указать UPDATE с Каскадируемым эффектом, и DELETE с Ограниченным эффектом.
CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY, cname char(10) NOT NULL, city char(10), rating integer, snum integer REFERENCES Salespeople, UPDATE OF Salespeople CASCADES, DELETE OF Salespeople RESTRICTED);
Если вы теперь попробуете удалить Peel из таблицы Продавцов, команда будет недопустима, пока вы не измените значение поля snum заказчиков Hoffman и Clemens для другого назначенного продавца. С другой стороны, вы можете изменить значение поля snum для Peel на 1009, и Hoffman и Clemens будут также автоматически изменены.
Третий эффект - Пустые (NULL) изменения. Бывает, что, когда продавцы оставляют компанию, их текущие заказы не передаются другому продавцу. С другой стороны, вы хотите отменить все заказы автоматически для заказчиков, чьи счета вы удалите. Изменив номера продавца или заказчика, можно просто передать их ему. Пример ниже показывает, как вы можете создать таблицу Заказов с использованием этих эффектов.
CREATE TABLE Orders (onum integer NOT NULL PRIMARY KEY, amt decimal, odate date NOT NULL cnum integer NOT NULL REFERENCES Customers snum integer REFERENCES Salespeople, UPDATE OF Customers CASCADES, DELETE OF Customers CASCADES, UPDATE OF Salespeople CASCADES, DELETE OF Salespeople NULLS);
Конечно, в команде DELETE с эффектом Пустого изменения в таблице Продавцов, ограничение NOT NULL должно быть удалено из поля snum.
ВНЕШНИЕ КЛЮЧИ, КОТОРЫЕ ССЫЛАЮТСЯ НА СВОИ ПОДЧИНЁННЫЕ ТАБЛИЦЫ
Как было упомянуто ранее, ограничение FOREIGN KEY может представить имя это в частной таблице, как таблице родительского ключа. Будучи далеко не простой, эта особенность может пригодиться.
Предположим, что мы имеем таблицу Employees с полем manager (администратор). Это поле содержит номера каждого из служащих, некоторые из которых являются ещё и администраторами. Но, так как каждый администратор одновременно является служащим, то он, естественно, будет также представлен и в этой таблице.
Давайте создадим таблицу, где номер служащего (столбец с именем empno), объявляется как первичный ключ, а администратор как внешний ключ будет ссылаться на нее:
CREATE TABLE Employees (empno integer NOT NULL PRIMARY KEY, name char(10) NOT NULL UNIOUE, manager integer REFERENCES Employees);
(Так как внешний ключ это ссылаемый первичный ключ таблицы, список столбцов может быть исключен.)
Как вы видите, каждый из них (но не Atali) ссылается на другого служащего в таблице как на своего администратора. Atali, имеющий наивысший номер в таблице, должен иметь значение, установленное в NULL. Это дает другой принцип справочной целостности. Внешний ключ, который ссылается обратно на частную таблицу, должен позволять значения = NULL. Если это не так, то как бы вы могли вставить первую строку? Даже если эта первая строка ссылается на саму себя, значение родительского ключа должно уже быть установлено, когда вводится значение внешнего ключа. Этот принцип будет верен, даже если внешний ключ ссылается обратно к частной таблице не напрямую, а с помощью ссылки к другой таблице, которая затем ссылается обратно к таблице внешнего ключа.
Например, предположим, что наша таблица Продавцов имеет дополнительное поле, которое ссылается на таблицу Заказчиков так, что каждая таблица ссылается на другую, как показано в следующем операторе CREATE TABLE:
CREATE TABLE Salespeople (snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL, city char(10), comm declmal, cnum integer REFERENCES Customers); CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY, cname char(10) NOT NULL, city char(10), rating integer, snum integer REFERENCES Salespeople);
Это называется перекрестной ссылкой. SQL поддерживает это теоретически, но практически это может составить проблему. Любая таблица из этих двух, созданная первой, является ссылочной таблицей, которая ещё не существует для другой. В интересах обеспечения перекрестной ссылки, SQL фактически позволяет это, но никакая таблица не будет пригодна для использования, пока они обе находятся в процессе создания. С другой стороны, если эти две таблицы создаются различными пользователями, проблема становится ещё более трудной. Перекрестна ссылка может стать полезным инструментом, но она не без неоднозначности и опасностей. Предшествующий пример не совсем пригоден для использования, потому что он ограничивает продавца одиночным заказчиком, и, кроме того, совсем не обязательно использовать перекрёстную ссылку, чтобы достичь этого.
Мы рекомендуем чтобы вы были осторожны в его использовании и анализировали, как ваши программы управляют эффектами модификации и удаления, а также процессами привилегий и диалоговой обработки запросов, перед тем как вы создаёте перекрестную систему справочной целостности. (Привилегии и диалоговая обработка запросов будут обсуждаться, соответственно, в Главах 22 и 23.)
РЕЗЮМЕ
Теперь вы имеете достаточно хорошее управление справочной целостностью. Основная идея в том, что все значения внешнего ключа ссылаются на указанную строку родительского ключа. Это означает, что каждое значение внешнего ключа должно быть представлено один раз, и только один раз, в родительском ключе.
Всякий раз, когда значение помещается во внешний ключ, родительский ключ проверяется, чтобы удостовериться, что его значение представлено; иначе команда будет отклонена.
Родительский ключ должен иметь Первичный Ключ (PRIMARY KEY) или Уникальное (UNIQUE) ограничение, гарантирующее, что значение не будет представлено более чем один раз. Попытка изменить значение родительского ключа, которое в настоящее время представлено во внешнем ключе, будет вообще отклонена. Ваша система может, однако, предложить вам выбор, чтобы получить значение внешнего ключа, установленного в NULL или для получения нового значения родительского ключа и указания, какой из них может быть получен независимо для команд UPDATE и DELETE.
Этим завершается наше обсуждение команды CREATE TABLE. Далее мы представим вам другой тип команды CREATE. В Главе 20 вы обучитесь представлению объектов данных, которые выглядят и действуют подобно таблице, но в действительности являются результатами запросов. Некоторые функции ограничений могут также выполняться представлениями, так что вы сможете лучше оценить вашу потребность в ограничениях, после того как вы прочитаете следующие три главы.
