Ссылочные циклы

Каскадные удаления

Правило RESTRICT является “одноуровневым” – в отношении предок/потомок оно затрагивает только таблицу – предок. Правило CASCADE, напротив, “многоуровневое”; как видно на следующем рисунке.

Рисунок 15 Каскадное удаление

Как видно из рисунка, удаление записи об отделении вызывает каскадное удаление соответствующих записей о служащих, что, в свою очередь, вызывает каскадное удаление строк заказов. Как показывает данный пример, правило CASCADE следует применять с осторожностью, поскольку некорректное его использование может вызвать широкомасштабное автоматическое удаление данных. Правила каскадного обновления могут привести к подобным многоуровневым обновлениям, если внешний ключ в таблице-потомке одновременно является и ее первичным ключом. На практике такая ситуация встречается не часто, поэтому каскадное обновление обычно не имеет таких далеко идущих последствий, как каскадное удаление.

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

Рисунок 16 Ссылочный цикл

На представленном рисунке таблица SALESREPS содержит столбец Bno – внешний ключ для таблицы BRANCHES. Таблица BRANCHES, в свою очередь может содержать столбец MGR (с информацией об управляющих отделений) – внешний ключ для таблицы SALESREPS. Как видно из рисунка, эти два отношения образуют ссылочный цикл. Любая строка таблицы STAFF имеет ссылку на строку таблицы BRANCHES, которая имеет ссылку на строку таблицы SALESREPS, и т.д.

Независимо от количества таблиц, принимающих в них участие, ссылочные циклы представляют особую проблему ссылочной целостности. Предположим, например, что в двух таблицах, изображенных на рисунке, для первичных и внешних ключей не допускаются значения NULL. Рассмотрим например следующий запрос на добавление и соответствующие ему инструкции INSERT:

Поместить в базу данных информацию о новом сотруднике Иване Иванове с назначением его управляющим в только что открывшемся отделении в Москве

insert into salesreps (sno, fname, lname, salary, bno)

values (23, ‘Иван’, ‘Иванов’, 500, 3);

insert into branches (bno, street, area, city, mgr, tel_no)

values (3, ‘…’, ‘…’, ‘Москва’, 23, ‘…’);

Но выполнение первой инструкции (для SALESREPS) будет безрезультатным, потому что в новой строке есть ссылка на идентификатор отделения (номер 3), которого в базе данных еще нет. Очевидно, что изменение порядка инструкции INSERT ни к чему не приведет, так как при выполнении первой инструкции (для BRANCHES) есть ссылка на идентификатор служащего (руководитель офиса), а он пока еще отсутствует в базе данных.

Для предотвращения подобной “блокировки ввода” по крайней мере один из внешних ключей ссылочного цикла должен допускать значения NULL. Тогда ввод двух строк можно выполнить с помощью двух инструкций INSERT и одной инструкции UPDATE:

insert into salesreps (sno, fname, lname, salary, bno)

values (23, ‘Иван’, ‘Иванов’, 500, null);

insert into branches (bno, street, area, city, mgr, tel_no)

values (3, ‘…’, ‘…’, ‘Москва’, 23, ‘…’);

update salesreps

set bno = 3

where sno = 23;

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