主键和外键约束

适用于： SQL Server 2016 （13.x） 及以后版本 Azure SQL 数据库Azure SQL 托管实例Microsoft Fabric 中的 SQL 数据库

主键和外键是两种类型的约束，可用于强制 SQL Server 表中的数据完整性。 这些是重要的数据库对象。

主键约束

表通常具有包含唯一标识表中每一行的值的一列或一组列。 这样的一列或多列称为表的主键 (PK)，用于强制表的实体完整性。 由于主键约束可保证数据的唯一性，因此经常对标识列定义这种约束。

如果为表指定了主键约束，数据库引擎将通过为主键列自动创建唯一索引来强制数据的唯一性。 当在查询中使用主键时，此索引还允许对数据进行快速访问。 如果对多列定义了主键约束，则一列中的值可能会重复，但来自主键约束定义中所有列的值的任何组合必须唯一。

如下图所示，ProductID 表中的 VendorID 和 Purchasing.ProductVendor 列构成了针对此表的复合主键约束。 这确保了 ProductVendor 表中的每个行都具有 ProductID 和 VendorID 的一个唯一组合。 这样可以防止插入重复的行。

带有复合 PRIMARY KEY 约束的表中各行的示意图。

  • 一个表只能包含一个主键约束。
  • 主键不能超过 32 列，总键长度为 900 字节。
  • 由主键约束生成的索引不会使表中的非聚集索引超过 999 个，聚集索引超过 1 个。
  • 如果未将主键约束指定为聚集还是非聚集，并且表上没有聚集索引，则将使用聚集。
  • 在主键约束中定义的所有列都必须定义为不为 Null。 如果未指定可空性，则参与主键约束的所有列都将被设置为不可为 null。
  • 如果在 CLR 用户定义类型的列中定义主键，则该类型的实现必须支持二进制排序。

外键约束

外键 (FK) 是用于在两个表中的数据之间建立和加强链接的一列或多列的组合，可控制可在外键表中存储的数据。 在外键引用中，当包含一个表的主键值的一个或多个列被另一个表中的一个或多个列引用时，就在这两个表之间创建了链接。 这个列就成为第二个表的外键。

例如，销售订单和销售人员之间存在一种逻辑关系，因此 Sales.SalesOrderHeader 表有一个指向 Sales.SalesPerson 表的外键链接。 SalesPersonID 表中的 SalesOrderHeader 列与 SalesPerson 表的主键列匹配。 SalesPersonID 表中的 SalesOrderHeader 列是 SalesPerson 表的外键。 通过创建这种外键关系，如果 SalesPersonID 表中没有 SalesOrderHeader 的值，则不能将该值插入 SalesPerson 表。

表最多可以将 253 个其他表和列作为外键引用（传出引用）。 SQL Server 2016 (13.x) 将可在单独的表中引用的其他表和列（传入引用）的数量限制从 253 提高至 10,000。 （要求兼容级别至少为 130。）该增加有以下限制：

  • 只有 DELETE DML 操作才支持超过 253 个外键引用。 不支持 UPDATE 和 MERGE 操作。

  • 具有自引用外键的表仍然最多只能有 253 个外键引用。

  • 列存储索引、内存优化表、Stretch Database 或已分区外键表当前不支持超过 253 个外键引用。

    Important

    SQL Server 2022 (16.x) 和 Azure SQL 数据库中已弃用 Stretch Database。 在数据库引擎的未来版本中将删除此功能。 请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。

外键约束的索引

与主键约束不同，创建外键约束不会自动创建对应的索引。 不过，为外键手动创建索引往往很有用，原因如下：

  • 当在查询中组合相关表中的数据时，经常在联接条件中使用外键列，方法是将一个表的外键约束中的一列或多列与另一个表中的主键列或唯一键列匹配。 索引使数据库引擎可以在外键表中快速查找相关数据。 但是，创建此索引并不是必需的。 即使没有对两个相关表定义主键或外键约束，也可以对来自这两个表中的数据进行组合，但两个表间的外键关系说明已用其键作为条件对其进行了优化，以便组合到查询中。

  • 对主键约束的更改可由相关表中的外键约束检查。

引用完整性

尽管外键约束的主要目的是控制可以存储在外键表中的数据，但它还可以控制对主键表中数据的更改。 例如，如果从 Sales.SalesPerson 表中删除某位销售人员对应的行，而 Sales.SalesOrderHeader 表中的销售订单使用了该销售人员的 ID，那么这两个表之间的参照完整性就会被破坏；这样一来，SalesOrderHeader 表中属于该已删除销售人员的销售订单将成为孤立记录，无法链接到 SalesPerson 表中的数据。

外键约束防止这种情况发生。 该约束通过保证：如果对主键表中数据的更改会导致其与外键表中数据的关联失效，则不允许进行此类更改，从而强制实施引用完整性。 如果试图删除主键表中的行或更改主键值，而该主键值与另一个表的外键约束中的值相对应，则该操作将失败。 若要成功更改或删除外键约束中的行，必须先在外键表中删除或更改外键数据，这会将外键链接到不同的主键数据。

级联参照完整性

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

  • NO ACTION

    数据库引擎会引发错误，并且对父表中的行执行的删除或更新操作将被回滚。

  • CASCADE

    如果在父表中更新或删除了一行，则将在引用表中更新或删除相应的行。 如果外键或被引用键中包含 timestamp 列，则不能指定 CASCADE。 对于具有 INSTEAD OF DELETE 触发器的表，不能指定 ON DELETE CASCADE。 无法为具有 INSTEAD OF UPDATE 触发器的表指定 ON UPDATE CASCADE。

  • SET NULL

    如果更新或删除了父表中的相应行，则会将构成外键的所有值设置为 NULL。 若要执行此约束，外键列必须可为空值。 对于带有 INSTEAD OF UPDATE 触发器的表，无法指定。

  • SET DEFAULT

    如果更新或删除了父表中对应的行，则组成外键的所有值都将设置为默认值。 若要执行此约束，所有外键列都必须有默认定义。 如果某个列可为空值，并且未设置显式的默认值，则将使用 NULL 作为该列的隐式默认值。 无法为带有 INSTEAD OF UPDATE 触发器的表指定。

可将 CASCADE、SET NULL、SET DEFAULT 和 NO ACTION 在相互存在引用关系的表上进行组合。 如果 数据库引擎遇到 NO ACTION，它将停止并回滚相关的 CASCADE、SET NULL 和 SET DEFAULT 操作。 当 DELETE 语句导致 CASCADE、SET NULL、SET DEFAULT 或 NO ACTION 操作的组合时，所有 CASCADE、SET NULL 和 SET DEFAULT 操作都会在数据库引擎检查任何 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 触发器。

相关内容

  • Last updated on