Мартин Грубер - Понимание SQL
Имеется несколько атрибутов таких определений о которых нужно поговорить. Причина по которой мы решили сделать поля cnum и snum в таблице Порядков, единым внешним ключом - это гарантия того, что для каждого заказчика содержащегося в порядках, продавец кредитующий этот порядок - тот же что и указаный в таблице Заказчиков. Чтобы создать такой внешний ключ, мы были бы должны поместить ограничение таблицы UNIQUE в два поля таблицы Заказчиков, даже если оно необязательно для самой этой таблицы. Пока поле cnum в этой таблица имеет ограничение PRIMARY KEY, оно будет уникально в любом случае, и следовательно невозможно получить еще одну комбинацию поля cnum с каким-то другим полем.
Создание внешнего ключа таким способом поддерживает целостность базы данных, даже если при этом вам будет запрещено внутреннее прерывание по ошибке и кредитовать любого продавца, иного чем тот который назначен именно этому заказчику. С точки зрения поддержания целостности базы данных, внутренние прерывания (или исключения ) конечно же нежелательны. Если вы их допускаете и в то же время хотите поддерживать целостность вашей базы данных, вы можете обьявить поля snum и cnum в таблице Порядков независимыми внешними ключами этих полей в таблице Продавцов и таблице Заказчиков, соответственно. Фактически, использование поля snum в таблице Порядков, как мы это делали, необязательно, хотя это полезно было сделать для разнообразия. Поле cnum связывая каждый порядок заказчиков в таблице Заказчиков, в таблице Порядков и в таблице Заказчиков, должно всегда быть общим чтобы находить правильное поле snum для данного порядка (не разрешая никаких исключений).
Это означает что мы записываем фрагмент информации - какой заказчик назначен к какому продавцу - дважды, и нужно будет выполнять дополнительную работу чтобы удостовериться, что обе версии согласуются. Если мы не имеем ограничения внешнего ключа как сказано выше, эта ситуация будет особенно проблематична, потому что каждый порядок нужно будет проверять вручную (вместе с запросом ), чтобы удостовериться что именно соответствующий продавец кредитовал каждую соответствующую продажу.
Наличие такого типа информационной избыточности в вашей базе данных, называется деморализация (denormalization ), что не желательно в идеальной реляционной базе данных, хотя практически и может быть разрешена. Деморализация может заставить некоторые запросы выполняться быстрее, поскольку запрос в одной таблице выполняется всегда значительно быстрее чем в обьединении.
ДЕЙСТВИЕ ОГРАНИЧЕНИЙКак такие ограничения воздействуют на возможность и невозможность Вами использовать команды модификации DML? Для полей, определенных как внешние ключи, ответ довольно простой: любые значения которые вы помещаете в эти поля с командой INSERT или UPDATE должны уже быть представлены в их родительских кючах. Вы можете помещать пустые(NULL) значения в эти поля, несмотря на то что значения NULL не позволительны в родительских ключах, если они имеют ограничение NOT NULL. Вы можете удалять (DELETE ) любые строки с внешними ключами не используя родительские ключи вообще. Поскольку затронут вопрос об изменении значений родительского ключа, ответ, по определению ANSI, еще проще, но возможно несколько более ограничен: любое значение родительского ключа ссылаемого с помощью значения внешнего ключа, не может быть удалено или изменено. Это означает, например, что вы не можете удалить заказчика из таблицы Заказчиков пока он еще имеет порядки в таблице Порядков. В зависимости от того, как вы используете эти таблицы, это может быть или желательно или хлопотно. Однако - это конечно лучше чем иметь систему, которая позволит вам удалить заказчика с текущими порядками и оставить таблицу Порядков ссылающейся на несуществующих заказчиков.
Смысл этой системы оганичения в том, что создатель таблицы Порядков, используя таблицу Заказчиков и таблицу Продавцов как родительские ключи может наложить значительные ограничения на действия в этих таблицах. По этой причине, вы не сможете использовать таблицу которой вы не распоряжаетесь (т.е. не вы ее создавали и не вы являетесь ее владельцем), пока владелец(создатель) этой таблицы специально не передаст вам на это право (что объясняется в Главе 22).
Имеются некоторые другие возможные действия изменения родительского ключа, которые не являются частью ANSI, но могут быть найдены в некоторых коммерческих программах. Если вы хотите изменить или удалить текущее ссылочное значение родительского ключа, имеется по существу три возможности:
* Вы можете ограничить, или запретить, изменение (способом ANSI ), обозначив, что изменения в родительском ключе - ограничены.
* Вы можете сделать изменение в родительском ключе и тем самым сделать изменения во внешнем ключе автоматическим, что называется - каскадным изменением.
* Вы можете сделать изменение в родительском ключе, и установить внешний ключ в NULL, автоматически (полагая, что NULLS разрешен во внешнем ключе ), что называется - пустым изменением внешнего ключа.
Даже в пределах этих трех категорий, вы можете не захотеть обрабатывать все команды модификации таким способом. 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 ), объявляется как первичный ключ, а администратор, как внешний ключ, будет ссылаться на нее:
