Выполнение первой инструкции insert (на этот раз для офиса в Детройте) также закончится безрезультатно, поскольку в новой строке есть ссылка на идентификатор служащего 115 (руководитель офиса), а Бен Адаме пока еще отсутствует в базе данных Для предотвращения подобной блокировки ввода по крайней мере один из внешних ключей ссылочного цикла должен допускать значения null. Учебная база данных спроектирована таким образом, что в столбце mgr значения null не допускаются, а в столбце rep OFFICE - допускаются. Тогда ввод двух строк можно выполнить с помощью двух инструкций insert и одной инструкции update:

INSERT INTO SALESREPS (EMPL NDM, NAME, REP OFFICE, HIRE DATE, GALES) VALUES (115, Ben Adams, NULL, Ol-APR-90, 0.00)

INSERT INTO OFFICES (OFFICE, CITY, REGION, MGR, TARGET, SALES) VALUES (14, Detroit, Eastern, 115, 0.00, 0.00)

UPDATE SALESREPS

SET REP OFFICE =14 WHERE EMPL NUM = 115

Как видно из данного примера, в некоторых ситуациях бььто бы удобно, чтобы условия ссылочной целостности не проверялись до тех пор, пока не будет выполнен ряд взаимосвязанных обновлений. К сожалению, в большинстве современных СУБД отсутствует такой тип комплексной отложенной проверки , хотя в SQL2 он предусмотрен, как будет показано ниже.

Наличие ссылочного цикла накладывает ограничения на выбор правил удаления и обновления для отношений, образующих цикл. Рассмотрим ссылочный цикл из трех таблиц, изображенный на рис. 11.6. Таблица pets содержит имена трех домашних животных и трех мальчиков, которых любят эти животные. Таблица girls содержит имена трех девочек и трех домашних животных, которых любят эти девочки. Таблица boys содержит имена четырех мальчиков и имена девочек, которых любят мальчики. Для всех трех отношений данного цик.та задано правило удаления restrict. А теперь обратите вни.мание на то, что строка для .мальчика Джорджа - это единственная строка в трех таблицах, которую можно удалить. Любая другая строка является предком в каком-нибудь отношении и потому защищена правилом restrict от удаления. Из-за возможности подобной аномалии не следует задавать данное правило для всех отношений ссылочного цикла.

Как показано на рис. 11.7, правило удаления cascade создает не меньшую проблему. На данном рисунке изображены те же таблицы, что и на рис. 11.6, но правило удаления restrict заменено правилом cascade. Предположим, что вы хотите удалить строку для Боба из таблицы boys. В соответствии с правилами удаления, СУБД удалит сфоку Ровера (животного, которое любит Боба) из таблицы pets, затем удалиг сфоку Бетти (которая любит Ровера) из таблицы girls, потом удалит строку Сэма (который любит Бетти) и так далее до тех пор, пока не будут удалены все строки из трех таблиц! Дтя таких маленьких таблиц подобная операция может оказаться Полезной, но для промышленных баз данных с тысячами строк невозможно проследить все каскадные удаления и сохранить целостность базы данных По этой причине в DB2 существует правило, которое запрещает ссылочные циклы из двух или более таблиц, в которых все правила удаления имеют тип cascade В таком цикле хотя бы Одно отношение должно иметь правило удаления restrict или set n jll, чтобы прервать цепочку каскадных удалений.

Таблица PETS

Первичный ключ

restrict Таблица GIRLS i Внешний КЛЮЧ

Первичный ключ

Таблица BOYS

restrict

Внешний ключ

Первичный ключ

restrict

Рис 11 6 Цикл с правилом удаления RESTRICT для всех отношений

Внешний ключ

Таблица PETS

Первичный ключ

Первичный ключ

Таблица SOYS Внешний ключ

Первичный ключ

cascade

Рис. 11.7. Недопустимый цикл, в котором все правила удаления имеют тип CASCADE

Внешние ключи и значения NULL *

в отличие от первичных ключей, внешние ключи в реляционной базе данных могут содержать значения null. В учебной базе данных внешний ключ REP OFFicE в таблице salesreps может принимать значения null. И действительно, данный столбец содержит значение null в строке Тома Снайдера, поскольку Том еше не назначен в какой-либо офис. Но значение null во внешнем ключе вызывает интересный вопрос об условии ссылочной целостности отношения первичный ключ - внешний ключ . Равно значение null одному из значений первичного ключа или нет? Ответ на этот вопрос следующий: Может быть. Все зависит от настоящего значения отсутствующих или неизвестных данных .

Как в СУБД DB2, так и в стандарте SQL1 считается, что внешний ключ, имеющий значение null, удовлетворяет условию ссылочной целостности. Другими словами, сомнение трактуется в пользу строки, и ей разрешается быть частью таблицы-потомка, хотя значение ее внешнего ключа не равно ни одному значению первичного ключа в таблице-предке. Интересно отметить, что условие ссылочной целостности считается выполненным, если любая часть внешнего ключа имеет значение null. Для составных внешних ключей это может привести к непредвиденным последствиям, как, например, в случае составного внешнего ключа, связывающего таблицу orders С таблицей products.

Предположим на минутку, что: 1) в столбце product таблицы orders разрешены значения null и 2) для отношения таблиц products/orders установлено правило удаления set null. (На самом деле учебная база данных спроектирована иначе по причинам, которые станут понятным из данного примера.) Благодаря первому условию в таблицу orders можно успешно добавить заказ на товар с идентификатором производителя (mfr), имеющим значение ABC , и идентификатором товара (product), имеющим значение null, поскольку столбец product может содержать значения null. В СУБД DB2 и стандарте ANSI/ISO считается, что данная строка соответствует условию целостности для таблиц orders и products, хотя в таблице products нет ни одного товара с идентификатором изготовителя ABC .

Правило удаления set null вызывает аналогичный эффект. Удаление строки из таблицы products должно привести к тому, что внешним ключам во всех строках-потомках таблицы orders будет присвоено значение null. Но на самом деле значение null будет записано только в те столбцы внешних ключей, которые могут принимать такие значения. Если бы в таблице products имелась одна строка для предприятия-изготовителя DEF , то удаление этой строки привело бы к тому, что в столбце product таблицы orders для всех ее строк-потомков были установлены значения null, но в столбце mfr сохранились бы значения DEF . В результате данные строки имели бы в столбце mfr значение, которое не соответствовало бы ни одной строке таблицы products.

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

В стандарте SQL2 администратору базы данных предоставляются более широкие возможности относительно значений null во внешних ключах. С помощью инструкции