• 项目

级联引用完整性约束

通过使用级联引用完整性约束，您可以定义当用户试图删除或更新现有外键指向的键时，SQL Server 执行的操作。

CREATE TABLE 语句和 ALTER TABLE 语句的 REFERENCES 子句支持 ON DELETE 子句和 ON UPDATE 子句。还可以使用“外键关系”对话框定义级联操作：

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

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

如果没有指定 ON DELETE 或 ON UPDATE，则默认为 NO ACTION。

  • ON DELETE NO ACTION
    指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚 DELETE 语句。

  • ON UPDATE NO ACTION
    指定如果试图更新某一行中的键值，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚 UPDATE 语句。

CASCADE、SET NULL 和 SET DEFAULT 允许通过删除或更新键值来影响指定具有外键关系的表，这些外键关系可追溯到在其中进行修改的表。如果为目标表也定义了级联引用操作，那么指定的级联操作也将应用于删除或更新的那些行。不能为具有 timestamp 列的外键或主键指定 CASCADE。

  • ON DELETE CASCADE
    指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则也将删除所有包含那些外键的行。

  • ON UPDATE CASCADE
    指定如果试图更新某一行中的键值，而该行的键值被其他表的现有行中的外键所引用，则组成外键的所有值也将更新到为该键指定的新值。

    注意注意

    如果 timestamp 列是外键或被引用键的一部分，则不能指定 CASCADE。

  • ON DELETE SET NULL
    指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为 NULL。为了执行此约束，目标表的所有外键列必须可为空值。

  • ON UPDATE SET NULL
    指定如果试图更新某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为 NULL。为了执行此约束，目标表的所有外键列必须可为空值。

  • ON DELETE SET DEFAULT
    指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为它们的默认值。为了执行此约束，目标表的所有外键列必须具有默认定义。如果某个列可为空值，并且未设置显式的默认值，则将使用 NULL 作为该列的隐式默认值。因 ON DELETE SET DEFAULT 而设置的任何非空值在主表中必须有对应的值，才能维护外键约束的有效性。

  • ON UPDATE SET DEFAULT
    指定如果试图更新某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为它们的默认值。为了执行此约束，目标表的所有外键列必须具有默认定义。如果某个列可为空值，并且未设置显式的默认值，则将使用 NULL 作为该列的隐式默认值。因 ON UPDATE SET DEFAULT 而设置的任何非空值在主表中必须有对应的值，才能维护外键约束的有效性。

以 AdventureWorks2008R2 中 Purchasing.ProductVendor 表上的 FK_ProductVendor_Vendor_VendorID 约束为例。此约束在 ProductVendor 表中的 VendorID 列与 Purchasing.Vendor 表中的 VendorID 主键列之间建立了一种外键关系。如果为此约束指定了 ON DELETE CASCADE，则从 Vendor 中删除 VendorID 等于 100 的行也将删除 ProductVendor 中 VendorID 等于 100 的三行。如果为此约束指定了 ON UPDATE CASCADE，那么，在将 Vendor 表中 VendorID 值从 100 更新为 155 时，也将更新 ProductVendor 中 VendorID 值当前等于 100 的三行中的 VendorID 值。

不能为带有 INSTEAD OF DELETE 触发器的表指定 ON DELETE CASCADE。对于带有 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。针对 TableA 中的主键，用 ON DELETE CASCADE 定义 TableB 中的外键。针对 TableB 中的主键，用 ON DELETE CASCADE 定义 TableC 中的外键。如果 DELETE 语句删除 TableA 中的行，则该操作也将删除 TableB 中具有与 TableA 中所删除的主键匹配的任何外键的所有行，然后删除 TableC 中具有与 TableB 中所删除的主键匹配的任何外键的所有行。

由单个 DELETE 或 UPDATE 触发的一系列级联引用操作必须形成不包含循环引用的树。在 DELETE 或 UPDATE 所产生的所有级联引用操作的列表中，每个表只能出现一次。此外，级联引用操作树与任何指定的表之间只能有一条路径。树的任何分支在遇到指定了 NO ACTION 或默认为 NO ACTION 的表时都将结束。

触发器和级联引用操作

级联引用操作按下列方式激发 AFTER UPDATE 或 AFTER DELETE 触发器：

首先执行由原始 DELETE 或 UPDATE 直接导致的所有级联引用操作。

如果为受影响的表定义了任何 AFTER 触发器，则在执行完所有级联操作后激发这些触发器。这些触发器将按与级联操作相反的顺序激发。如果单个表中存在多个触发器，它们将按随机顺序激发，除非专门为表指定了第一个或最后一个触发器。此顺序是使用 sp_settriggerorder 指定的。

如果多个级联链源自作为 UPDATE 或 DELETE 操作的直接目标的表，则这些链激发各自的触发器的顺序是不定的。但是，只有当一条链激发其所有的触发器之后，另一条链才开始激发。

不管是否影响任何行，作为 UPDATE 或 DELETE 操作的直接目标的表上的 AFTER 触发器都会激发。在这种情况下，级联操作不会影响其他表。

如果上面的任一触发器对其他表执行 UPDATE 或 DELETE 操作，这些操作将启动辅助级联链。在激发所有主链上的所有触发器后，会分别为每个 UPDATE 或 DELETE 操作处理这些辅助链。可能会为后续的 UPDATE 或 DELETE 操作递归重复此过程。

在触发器内执行 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

当指定了 CASCADE、SET NULL 或 SET DEFAULT 时，sp_fkeys 和 sp_foreignkeys 返回的 UPDATE_RULE 和 DELETE_RULE 列将返回 0；当指定了 NO ACTION 或默认为 NO ACTION 时，将返回 1。

将外键指定为 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（不适用）。