cascading mysql 主键_主键和外键约束 - SQL Server | Microsoft Docs

最新推荐文章于 2021-11-19 18:53:34 发布

青亭网

最新推荐文章于 2021-11-19 18:53:34 发布

阅读量245

点赞数

文章标签： cascading mysql 主键

本文链接：https://blog.csdn.net/weixin_32019361/article/details/113378421

版权

主键和外键是SQL Server中强制数据完整性的关键约束。主键确保表中每一行的唯一性，外键则在两个表之间建立链接，确保引用完整性。外键约束可以通过级联操作（CASCADE、SET NULL、SET DEFAULT 或 NO ACTION）来控制对主键数据的更改。这些约束限制了无效数据的插入，并帮助维护数据库的稳定性和一致性。

摘要由CSDN通过智能技术生成

主键和外键约束Primary and Foreign Key Constraints

07/25/2017

本文内容

适用于：Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance适用于：Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance

主键和外键是两种类型的约束，可用于强制 SQL ServerSQL Server 表中的数据完整性。Primary keys and foreign keys are two types of constraints that can be used to enforce data integrity in SQL ServerSQL Server tables. 这些是重要的数据库对象。These are important database objects.

本主题包含以下各节：This topic contains the following sections.

主键约束Primary Key Constraints

表通常具有包含唯一标识表中每一行的值的一列或一组列。A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. 这样的一列或多列称为表的主键 (PK)，用于强制表的实体完整性。This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. 由于主键约束可保证数据的唯一性，因此经常对标识列定义这种约束。Because primary key constraints guarantee unique data, they are frequently defined on an identity column.

如果为表指定了主键约束，数据库引擎Database Engine 将通过为主键列自动创建唯一索引来强制数据的唯一性。When you specify a primary key constraint for a table, the 数据库引擎Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. 当在查询中使用主键时，此索引还允许对数据进行快速访问。This index also permits fast access to data when the primary key is used in queries. 如果对多列定义了主键约束，则一列中的值可能会重复，但来自主键约束定义中所有列的值的任何组合必须唯一。If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique.

如下图所示， Purchasing.ProductVendor 表中的 ProductID 和 VendorID 列构成了针对此表的复合主键约束。As shown in the following illustration, the ProductID and VendorID columns in the Purchasing.ProductVendor table form a composite primary key constraint for this table. 这确保了 ProductVendor 表中的每个行都具有 ProductID 和 VendorID 的一个唯一组合。This makes sure that every row in the ProductVendor table has a unique combination of ProductID and VendorID. 这样可以防止插入重复的行。This prevents the insertion of duplicate rows.

一个表只能包含一个主键约束。A table can contain only one primary key constraint.

主键不能超过 16 列且总密钥长度不能超过 900 个字节。A primary key cannot exceed 16 columns and a total key length of 900 bytes.

由主键约束生成的索引不会使表中的索引数超过 999 个非聚集索引和 1 个聚集索引。The index generated by a primary key constraint cannot cause the number of indexes on the table to exceed 999 nonclustered indexes and 1 clustered index.

如果没有为主键约束指定聚集或非聚集索引，并且表中没有聚集索引，则使用聚集索引。If clustered or nonclustered is not specified for a primary key constraint, clustered is used if there no clustered index on the table.

在主键约束中定义的所有列都必须定义为不为 Null。All columns defined within a primary key constraint must be defined as not null. 如果没有指定为 Null 性，则参与主键约束的所有列的为 Null 性都将设置为不为 Null。If nullability is not specified, all columns participating in a primary key constraint have their nullability set to not null.

如果在 CLR 用户定义类型的列中定义主键，则该类型的实现必须支持二进制排序。If a primary key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering.

Foreign Key ConstraintsForeign Key Constraints

外键 (FK) 是用于在两个表中的数据之间建立和加强链接的一列或多列的组合，可控制可在外键表中存储的数据。A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. 在外键引用中，当包含一个表的主键值的一个或多个列被另一个表中的一个或多个列引用时，就在这两个表之间创建了链接。In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. 这个列就成为第二个表的外键。This column becomes a foreign key in the second table.

例如，因为销售订单和销售人员之间存在一种逻辑关系，所以 Sales.SalesOrderHeader 表含有一个指向 Sales.SalesPerson 表的外键链接。For example, the Sales.SalesOrderHeader table has a foreign key link to the Sales.SalesPerson table because there is a logical relationship between sales orders and salespeople. SalesOrderHeader 表中的 SalesPersonID 列与 SalesPerson 表中的主键列相对应。The SalesPersonID column in the SalesOrderHeader table matches the primary key column of the SalesPerson table. SalesOrderHeader 表中的 SalesPersonID 列是指向 SalesPerson 表的外键。The SalesPersonID column in the SalesOrderHeader table is the foreign key to the SalesPerson table. 通过创建此外键关系，如果 SalesPerson 表的主键中不存在 SalesPersonID 的值，则 SalesPersonID 的值将无法插入到 SalesOrderHeader 表。By creating this foreign key relationship, a value for SalesPersonID cannot be inserted into the SalesOrderHeader table if it does not already exist in the SalesPerson table.

表最多可以将 253 个其他表和列作为外键引用(传出引用)。A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 将可在单独的表中引用的其他表和列(传入引用)的数量限制从 253 提高至 10,000。increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (兼容性级别至少必须为 130。)数量限制的提高带来了下列约束：(Requires at least 130 compatibility level.) The increase has the following restrictions:

只有 DELETE DML 操作才支持超过 253 个外键引用。Greater than 253 foreign key references are only supported for DELETE DML operations. 不支持 UPDATE 和 MERGE 操作。UPDATE and MERGE operations are not supported.

对自身进行外键引用的表仍只能进行 253 个外键引用。A table with a foreign key reference to itself is still limited to 253 foreign key references.

列存储索引、内存优化表、Stretch Database 或已分区外键表暂不支持进行超过 253 个外键引用。Greater than 253 foreign key references are not currently available for columnstore indexes, memory-optimized tables, Stretch Database, or partitioned foreign key tables.

外键约束的索引Indexes on Foreign Key Constraints

与主键约束不同，创建外键约束不会自动创建对应的索引。Unlike primary key constraints, creating a foreign key constraint does not automatically create a corresponding index. 但是由于以下原因，对外键手动创建索引通常是有用的：However, manually creating an index on a foreign key is often useful for the following reasons:

当在查询中组合相关表中的数据时，经常在联接条件中使用外键列，方法是将一个表的外键约束中的一列或多列与另一个表中的主键列或唯一键列匹配。Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the foreign key constraint of one table with the primary or unique key column or columns in the other table. 索引使数据库引擎Database Engine 可以在外键表中快速查找相关数据。An index enables the 数据库引擎Database Engine to quickly find related data in the foreign key table. 但是，创建此索引并不是必需的。However, creating this index is not required. 即使没有对两个相关表定义主键或外键约束，也可以对来自这两个表中的数据进行组合，但两个表间的外键关系说明已用其键作为条件对其进行了优化，以便组合到查询中。Data from two related tables can be combined even if no primary key or foreign key constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria.

对主键约束的更改可由相关表中的外键约束检查。Changes to primary key constraints are checked with foreign key constraints in related tables.

引用完整性Referential Integrity

尽管外键约束的主要目的是控制可以存储在外键表中的数据，但它还可以控制对主键表中数据的更改。Although the main purpose of a foreign key constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. 例如，如果在 Sales.SalesPerson 表中删除一个销售人员行，而这个销售人员的 ID 由 Sales.SalesOrderHeader 表中的销售订单使用，则这两个表之间关联的完整性将被破坏； SalesOrderHeader 表中删除的销售人员的销售订单因为与 SalesPerson 表中的数据没有链接而变得孤立了。For example, if the row for a salesperson is deleted from the Sales.SalesPerson table, and the salesperson's ID is used for sales orders in the Sales.SalesOrderHeader table, the relational integrity between the two tables is broken; the deleted salesperson's sales orders are orphaned in the SalesOrderHeader table without a link to the data in the SalesPerson table.

外键约束防止这种情况发生。A foreign key constraint prevents this situation. 如果主键表中数据的更改使之与外键表中数据的链接失效，则这种更改将无法实现，从而确保了引用完整性。The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. 如果试图删除主键表中的行或更改主键值，而该主键值与另一个表的外键约束中的值相对应，则该操作将失败。If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the foreign key constraint of another table. 若要成功更改或删除外键约束中的行，必须先在外键表中删除或更改外键数据，这会将外键链接到不同的主键数据。To successfully change or delete a row in a foreign key constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.

级联引用完整性Cascading Referential Integrity

通过使用级联引用完整性约束，您可以定义当用户试图删除或更新现有外键指向的键时，数据库引擎Database Engine 执行的操作。By using cascading referential integrity constraints, you can define the actions that the 数据库引擎Database Engine takes when a user tries to delete or update a key to which existing foreign keys point. 可以定义以下级联操作。The following cascading actions can be defined.

NO ACTIONNO ACTION

数据库引擎Database Engine 将引发错误，此时将回滚对父表中行的删除或更新操作。The 数据库引擎Database Engine raises an error and the delete or update action on the row in the parent table is rolled back.

CASCADECASCADE

如果在父表中更新或删除了一行，则将在引用表中更新或删除相应的行。Corresponding rows are updated or deleted in the referencing table when that row is updated or deleted in the parent table. 如果 timestamp 列是外键或被引用键的一部分，则不能指定 CASCADE。CASCADE cannot be specified if a timestamp column is part of either the foreign key or the referenced key. 不能为带有 INSTEAD OF DELETE 触发器的表指定 ON DELETE CASCADE。ON DELETE CASCADE cannot be specified for a table that has an INSTEAD OF DELETE trigger. 对于带有 INSTEAD OF UPDATE 触发器的表，不能指定 ON UPDATE CASCADE。ON UPDATE CASCADE cannot be specified for tables that have INSTEAD OF UPDATE triggers.

SET NULLSET NULL

如果更新或删除了父表中的相应行，则会将构成外键的所有值设置为 NULL。All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is updated or deleted. 若要执行此约束，外键列必须可为空值。For this constraint to execute, the foreign key columns must be nullable. 无法为带有 INSTEAD OF UPDATE 触发器的表指定。Cannot be specified for tables that have INSTEAD OF UPDATE triggers.

SET DEFAULTSET DEFAULT

如果更新或删除了父表中对应的行，则组成外键的所有值都将设置为默认值。All the values that make up the foreign key are set to their default values if the corresponding row in the parent table is updated or deleted. 若要执行此约束，所有外键列都必须有默认定义。For this constraint to execute, all foreign key columns must have default definitions. 如果某个列可为空值，并且未设置显式的默认值，则将使用 NULL 作为该列的隐式默认值。If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column. 无法为带有 INSTEAD OF UPDATE 触发器的表指定。Cannot be specified for tables that have INSTEAD OF UPDATE triggers.

可将 CASCADE、SET NULL、SET DEFAULT 和 NO ACTION 在相互存在引用关系的表上进行组合。CASCADE, SET NULL, SET DEFAULT and NO ACTION can be combined on tables that have referential relationships with each other. 如果数据库引擎Database Engine 遇到 NO ACTION，它将停止并回滚相关的 CASCADE、SET NULL 和 SET DEFAULT 操作。If the 数据库引擎Database Engine encounters NO ACTION, it stops and rolls back related CASCADE, SET NULL and SET DEFAULT actions. 如果 DELETE 语句导致 CASCADE、SET NULL、SET DEFAULT 和 NO ACTION 操作的组合，则在数据库引擎Database Engine 检查所有 NO ACTION 前，将应用所有 CASCADE、SET NULL 和 SET DEFAULT 操作。When a DELETE statement causes a combination of CASCADE, SET NULL, SET DEFAULT and NO ACTION actions, all the CASCADE, SET NULL and SET DEFAULT actions are applied before the 数据库引擎Database Engine checks for any NO ACTION.

触发器和级联引用操作Triggers and Cascading Referential Actions

级联引用操作按下列方式激发 AFTER UPDATE 或 AFTER DELETE 触发器：Cascading referential actions fire the AFTER UPDATE or AFTER DELETE triggers in the following manner:

首先执行由原始 DELETE 或 UPDATE 直接导致的所有级联引用操作。All the cascading referential actions directly caused by the original DELETE or UPDATE are performed first.

如果为受影响的表定义了任何 AFTER 触发器，则在执行完所有级联操作后激发这些触发器。If there are any AFTER triggers defined on the affected tables, these triggers fire after all cascading actions are performed. 这些触发器将按与级联操作相反的顺序激发。These triggers fire in opposite order of the cascading action. 如果单个表中存在多个触发器，它们将按随机顺序激发，除非专门为表指定了第一个或最后一个触发器。If there are multiple triggers on a single table, they fire in random order, unless there is a dedicated first or last trigger for the table. This order is as specified by using sp_settriggerorder.

如果多个级联链源自作为 UPDATE 或 DELETE 操作的直接目标的表，则这些链激发各自的触发器的顺序是不定的。If multiple cascading chains originate from the table that was the direct target of an UPDATE or DELETE action, the order in which these chains fire their respective triggers is unspecified. 但是，只有当一条链激发其所有的触发器之后，另一条链才开始激发。However, one chain always fires all its triggers before another chain starts firing.

不管是否影响任何行，作为 UPDATE 或 DELETE 操作的直接目标的表上的 AFTER 触发器都会激发。An AFTER trigger on the table that is the direct target of an UPDATE or DELETE action fires regardless of whether any rows are affected. 在这种情况下，级联操作不会影响其他表。There are no other tables affected by cascading in this case.

如果上面的任一触发器对其他表执行 UPDATE 或 DELETE 操作，这些操作将启动辅助级联链。If any one of the previous triggers perform UPDATE or DELETE operations on other tables, these actions can start secondary cascading chains. 在激发所有主链上的所有触发器后，会分别为每个 UPDATE 或 DELETE 操作处理这些辅助链。These secondary chains are processed for each UPDATE or DELETE operation at a time after all triggers on all primary chains fire. 可能会为后续的 UPDATE 或 DELETE 操作递归重复此过程。This process may be recursively repeated for subsequent UPDATE or DELETE operations.

在触发器内执行 CREATE、ALTER、DELETE 或其他数据定义语言 (DDL) 操作可能会导致 DDL 触发器激发。Performing CREATE, ALTER, DELETE, or other data definition language (DDL) operations inside the triggers may cause DDL triggers to fire. 之后，就可能会执行启动其他级联链和触发器的 DELETE 或 UPDATE 操作。This may subsequently perform DELETE or UPDATE operations that start additional cascading chains and triggers.

如果任何特定的级联引用操作链中产生错误，都将引发错误并且不会在该链中激发任何 AFTER 触发器，而创建该链的 DELETE 或 UPDATE 操作将回滚。If an error is generated inside any particular cascading referential action chain, an error is raised, no AFTER triggers are fired in that chain, and the DELETE or UPDATE operation that created the chain is rolled back.

具有 INSTEAD OF 触发器的表不能同时具有指定级联操作的 REFERENCES 子句。A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. 但是，级联操作目标表的 AFTER 触发器可对另一个表或视图执行 INSERT、UPDATE 或 DELETE 语句，这将激发为该对象定义的 INSTEAD OF 触发器。However, an AFTER trigger on a table targeted by a cascading action can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object.