Поделиться через

  • Статья

Каскадные ограничения ссылочной целостности

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

Предложения REFERENCES инструкций CREATE TABLE и ALTER TABLE поддерживают предложения ON DELETE и ON UPDATE. Каскадные действия могут также быть определены с помощью диалогового окна Связи внешнего ключа:

  • [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

  • [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

По умолчанию подразумевается действие NO ACTION, если предложения ON DELETE и ON UPDATE не указаны.

  • ON DELETE NO ACTION
    Указывает, что при попытке удалить строку с ключом, на которую ссылаются внешние ключи в строках других таблиц, нужно сообщить об ошибке, а для инструкции DELETE выполнить откат.

  • ON UPDATE NO ACTION
    Указывает, что при попытке обновить ключевое значение, на которое ссылаются внешние ключи в строках других таблиц, нужно сообщить об ошибке, а для инструкции UPDATE выполнить откат.

Действия CASCADE, SET NULL и SET DEFAULT позволяют удалять и обновлять значения ключей, влияющие на таблицы, в которых определены связи внешних ключей, приводящие к таблице, в которую вносятся изменения. Если каскадные ссылочные действия были также определены для целевых таблиц, то и там указанные каскадные действия будут применены при обновлении или удалении соответствующих строк. Значение CASCADE не может быть указано для внешних и первичных ключей в столбцах типа timestamp.

  • ON DELETE CASCADE
    Указывает, что при попытке удалить строку с ключом, на которую ссылаются внешние ключи в строках других таблиц, все строки, содержащие эти внешние ключи, также должны быть удалены.

  • ON UPDATE CASCADE
    Указывает, что при попытке обновить ключевое значение, на которое ссылаются внешние ключи в строках других таблиц, все значения, составляющие этот внешний ключ, также должны быть обновлены до нового значения ключа.

    ПримечаниеПримечание

    Значение CASCADE не может быть указано, если столбец типа timestamp является частью внешнего или ссылочного ключа.

  • ON DELETE SET NULL
    Указывает, что при попытке удалить строку с ключом, на которую ссылаются внешние ключи в строках других таблиц, все значения, составляющие эти внешние ключи, должны быть изменены на NULL. Чтобы выполнялось это ограничение, все столбцы внешних ключей целевой таблицы должны допускать значение NULL.

  • ON UPDATE SET NULL
    Указывает, что при попытке обновить ключевое значение, на которое ссылаются внешние ключи в строках других таблиц, все значения, составляющие эти внешние ключи, должны быть изменены на NULL. Чтобы выполнялось это ограничение, все столбцы внешних ключей целевой таблицы должны допускать значение NULL.

  • ON DELETE SET DEFAULT
    Указывает, что при попытке удалить строку с ключом, на которую ссылаются внешние ключи в строках других таблиц, все значения, составляющие эти внешние ключи, должны быть изменены на значения по умолчанию. Чтобы выполнялось это ограничение, для всех столбцов внешних ключей целевой таблицы должно быть определено значение по умолчанию. Если столбец допускает значение NULL и множество значений по умолчанию не задано явно, NULL становится неявным значением по умолчанию для данного столбца. Все задаваемые предложением ON DELETE SET DEFAULT значения, отличные от NULL, должны иметь соответствующие значения в основной таблице, чтобы сохранить целостность ограничения внешнего ключа.

  • ON UPDATE SET DEFAULT
    Указывает, что при попытке обновить ключевое значение, на которое ссылаются внешние ключи в строках других таблиц, все значения, составляющие эти внешние ключи, должны быть изменены на значение по умолчанию. Чтобы выполнялось это ограничение, для всех столбцов внешних ключей целевой таблицы должно быть определено значение по умолчанию. Если столбец допускает значение NULL и множество значений по умолчанию не задано явно, NULL становится неявным значением по умолчанию для данного столбца. Все задаваемые предложением ON UPDATE SET DEFAULT значения, отличные от NULL, должны иметь соответствующие значения в основной таблице, чтобы сохранить целостность ограничения внешнего ключа.

Рассмотрим ограничение FK_ProductVendor_Vendor_VendorID таблицы Purchasing.ProductVendor базы данных AdventureWorks. Это ограничение устанавливает связь внешнего ключа из столбца VendorID таблицы ProductVendor в первичный ключ VendorID таблицы Purchasing.Vendor. Если для этого ограничения указано действие ON DELETE CASCADE, удаление строки в таблице Vendor, где столбец VendorID имеет значение 100, приведет к удалению трех строк в таблице ProductVendor, где столбец VendorID также имеет значение 100. Если для этого ограничения указано действие ON UPDATE CASCADE, обновление значения столбца VendorID в таблице Vendor со 100 до 155 приведет к обновлению значений столбца VendorID в трех строках таблицы ProductVendor, где столбец VendorID также имеет значение 100.

Действие ON DELETE CASCADE не может быть указано в таблице, для которой определен триггер INSTEAD OF DELETE. В таблицах, для которых определены триггеры INSTEAD OF UPDATE, не могут быть указаны следующие действия: ON DELETE SET NULL, ON DELETE SET DEFAULT, ON UPDATE CASCADE, ON UPDATE SET NULL и ON UDATE SET DEFAULT.

Множественные каскадные действия

Отдельные инструкции DELETE или UPDATE могут начать серию каскадных ссылочных действий. Например, база данных содержит три таблицы: TableA, TableB и TableC. Внешний ключ таблицы TableB определен с действием ON DELETE CASCADE и связан с первичным ключом таблицы TableA. Внешний ключ таблицы TableC определен с действием ON DELETE CASCADE и связан с первичным ключом таблицы TableB. Если инструкция DELETE удаляет строки в таблице TableA, эта же операция приведет к удалению всех строк в таблице TableB, в которых внешние ключи совпадают с удаленными первичными ключами таблицы TableA, а затем — к удалению всех строк в таблице TableC, в которых внешние ключи совпадают с удаленными первичными ключами таблицы TableB.

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

Триггеры и каскадные ссылочные действия

Каскадные ссылочные действия запускают триггеры AFTER UPDATE или AFTER DELETE следующим образом:

Все каскадные ссылочные действия, прямо вызванные исходными инструкциями DELETE или UPDATE, выполняются первыми.

Если есть какие-либо триггеры AFTER, определенные для измененных таблиц, эти триггеры запускаются после выполнения всех каскадных действий. Эти триггеры запускаются в порядке, обратном каскадным действиям. Если для одной таблицы определены несколько триггеров, они запускаются в случайном порядке, если только не указаны выделенные первый и последний триггеры таблицы. Этот порядок определяется процедурой sp_settriggerorder.

Если последовательности каскадных действий происходят из таблицы, которая была непосредственной целью действий DELETE или UPDATE, порядок запуска триггеров этими последовательностями действий не определен. Однако одна последовательность действий всегда запускает все свои триггеры до того, как это начнет делать следующая.

Триггер AFTER таблицы, являвшейся непосредственной целью действий DELETE или UPDATE, запускается вне зависимости от того, были ли изменены хоть какие-нибудь строки. В этом случае ни на какие другие таблицы каскадирование не влияет.

Если один из предыдущих триггеров выполняет операции DELETE или UPDATE над другими таблицами, эти операции могут вызывать собственные последовательности каскадных действий. Эти вторичные последовательности действий обрабатываются для каждой операции DELETE или UPDATE после выполнения всех триггеров первичных последовательностей действий. Этот процесс может рекурсивно повторяться для последующих операций DELETE или UPDATE.

Выполнение операций CREATE, ALTER, DELETE или других операций языка DDL внутри триггеров может привести к запуску триггеров DDL. Это может привести к дальнейшим операциям DELETE или UPDATE, которые начнут дополнительные последовательности каскадных действий и запустят свои триггеры.

Если в любой конкретной последовательности каскадных ссылочных действий произойдет ошибка, в этой последовательности не будут запущены никакие триггеры AFTER, а для операций DELETE или UPDATE, создаваемых этой последовательностью, будет выполнен откат.

У таблицы, для которой определен триггер INSTEAD OF, может также быть предложение REFERENCES, указывающее конкретное каскадное действие. Однако триггер AFTER целевой таблицы каскадного действия может выполнить инструкцию INSERT, UPDATE или DELETE для другой таблицы или представления, которое запустит триггер INSTEAD OF для этого объекта.

Сведения из каталога каскадных ссылочных ограничений

Запрос к представлению каталога sys.foreign_keys вернет следующие значения, описывающие каскадные ссылочные ограничения, указанные для внешних ключей.

Значение

Описание

0

NO ACTION

1

CASCADE

2

SET NULL

3

SET DEFAULT

Столбцы UPDATE_RULE и DELETE_RULE, возвращаемые процедурами sp_fkeys и sp_foreignkeys, содержат 0, если указаны действия CASCADE, SET NULL или SET DEFAULT, и 1, если указано действие NO ACTION или используется значение по умолчанию.

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

Имя столбца

Тип данных

Описание

delete_action

nvarchar(9)

Указывает, какое действие используется для удаления: CASCADE, SET NULL, SET DEFAULT, NO ACTION или N/A (не применимо).

update_action

nvarchar(9)

Указывает, какое действие используется для обновления: CASCADE, SET NULL, SET DEFAULT, NO ACTION или N/A (не применимо).