外键

本主题介绍了 Spanner 中的外键以及如何使用外键来 在数据库解决方案中强制执行参照完整性。

概览

外键允许您定义表之间的关系。Spanner 可确保这些关系的数据完整性。

假设您是电子商务企业的首席开发者。您正在设计一个数据库来处理客户订单。数据库需要存储每个订单、客户和产品的相关信息。图 1 展示了应用的基本数据库结构。

图 1：订单处理数据库示意图

您可以定义一个存储客户信息的 Customers 表、一个跟踪所下全部订单的 Orders 表以及一个存储客户可以订购的每件商品的相关信息的 Products 表。

图 1 还展示了映射到以下现实世界关系的表之间的关联：

• 客户下了订单

• 下了某商品的订单

您决定数据库应实施以下规则，以确保系统中的订单有效。

• 您无法为不存在的客户创建订单。

• 客户无法对您没有的商品下订单。

实施这些规则（即“限制条件”），即表示我们正在维护数据的参照完整性。当数据库保持参照完整性时，所有尝试添加无效数据的操作都将失败，这将导致数据之间出现无效链接或引用。参照完整性可防止用户出错。Spanner 通过外键强制执行引用完整性。

使用外键强制执行引用完整性

我们再次看一下订单处理示例，如图 2 所示，在设计中添加了更多细节。

图 2：采用外键的数据库架构示意图

现在设计为每个表中都显示了列名称和类型。Orders 表还定义了两个外键关系。FK_CustomerOrder 可确保 Orders 中所有行都具有有效的 CustomerID。FK_ProductOrder 外键可确保 Orders 表中的所有 ProductID 值都有效。下表将这些限制条件与我们希望强制执行的实际规则一一对应。

外键名称	限制	实际说明
FK_CustomerOrder	确保 Orders 中所有行都具有有效 CustomerID	有效客户下了订单
FK_ProductOrder	确保 Orders 中所有行都具有有效 ProductID	对有效商品下了订单

Spanner 使任何尝试在其中插入或更新行的事务失败 在以下位置找不到 CustomerID 或 ProductID 的 Orders 表 Customers 和 Products 表。此外，它也无法执行在 Customers 和 Products 表中尝试更新或删除行的事实，从而导致 Orders 表中的数据失效。如需详细了解 Spanner 验证限制条件，请参阅事务限制条件 验证。

定义外键

在您的 Spanner 数据库中创建和移除外键 使用 DDL使用 CREATE TABLE 语句将外键添加到新表中。同样，您可以向容器中添加或从中移除外键 使用 ALTER TABLE 语句创建现有表。以下是使用外键创建新表的示例。

GoogleSQL

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
  OrderID BIGINT NOT NULL,
  CustomerID BIGINT NOT NULL,
  Quantity BIGINT NOT NULL,
  ProductID BIGINT NOT NULL,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
  PRIMARY KEY (OrderID)
);

如需查看如何创建和管理外键的更多示例，请参阅创建和管理外键关系。

注意 ：您还可以使用 Google Cloud 控制台添加和移除外键。 从表创建界面中选择以文本形式修改，然后输入 DDL 以管理表的外键。

以下是 Spanner 中外键的特征列表。

• 定义外键的表是引用表，而外键列是引用列。

• 外键引用“被引用”表的的“被引用”列。

• 在上面的示例中，您可以为每个外键限制条件指定名称。如果您 未指定名称，则 Spanner 会自动生成一个名称。通过 可通过 Spanner 的 INFORMATION_SCHEMA 查询生成的名称。 例如，限制条件名称的范围限定为架构以及表和索引的名称，并且在架构内必须具有唯一性。

• 引用列数和被引用列数必须相同。顺序很重要。第一个引用列引用第一个被引用列，第二个引用列引用第二个被引用列，以此类推。

• 引用列及其被引用的对应项目的类型必须相同。这些列还必须可编入索引。

• 无法使用 allow_commit_timestamp=true 选项在列上创建外键。

• 数组列不受支持。

• 不支持 JSON 列。

• 外键可以引用同一表的列（“自引用”外键）。例如，一个员工表具有一个引用该表的 EmployeeId 列的 ManagerId 列。

• 外键还可以在表之间形成循环关系，其中两个表直接或间接地相互引用。由于必须先有被引用表，然后才能创建外键，因此必须使用 ALTER TABLE 语句添加至少其中一个外键。

• 被引用键必须是唯一的。Spanner 使用 PRIMARY KEY 如果外键的引用列与 引用表的主键列。如果 Spanner 无法使用 引用了表的主键，它会在表上创建一个 UNIQUE NULL_FILTERED INDEX 引用的列。

• Spanner 或许还可以使用引用的主键， 但这种情况不太常见否则，Spanner 会创建一个 对引用列执行 NULL_FILTERED INDEX 操作。

• 外键不使用您创建的二级索引，而是创建自己的支持性索引。这些支持性索引可用于查询评估，包括明确的 force_index 指令。后备索引的名称 可通过 Spanner 的 INFORMATION_SCHEMA 查询。如需了解详情，请参阅支持性索引。

外键操作

通过外键操作，您可以控制对受限列执行的操作 其引用的列被删除或更新时。Spanner 支持使用 ON DELETE CASCADE 操作。外键处于开启状态时 DELETE CASCADE 操作（在删除包含引用的外部外部变量的行时） 键，则引用该键的所有行也会在同一事务中删除。

您可以使用 在您使用 DDL 创建数据库时执行的操作。使用 CREATE TABLE 语句将带有操作的外键添加到新表。同样，您可以 使用 ALTER TABLE 语句将外键操作添加到现有 或移除某个外键操作。以下示例说明了如何 创建一个具有外键操作的新表。

GoogleSQL

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE ShoppingCarts (
  CartId INT64 NOT NULL,
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
  CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
    REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);

PostgreSQL

CREATE TABLE Customers (
  CustomerId bigint NOT NULL,
  CustomerName character varying(1024) NOT NULL,
  PRIMARY KEY(CustomerId)
);

CREATE TABLE ShoppingCarts (
  CartId bigint NOT NULL,
  CustomerId bigint NOT NULL,
  CustomerName character varying(1024) NOT NULL,
  PRIMARY KEY(CartId),
  CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
    REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);

以下列出了 Spanner。

• 外键操作为 ON DELETE CASCADE 或 ON DELETE NO ACTION。

• 您可以查询 INFORMATION_SCHEMA 以查找 有一项操作

• 在现有外键限制条件上添加外键操作不 支持。您需要添加带有操作的新外键约束。

长时间运行架构更改

将外键添加到现有表或使用外键创建新表可能导致产生长时间运行的操作。对于新表，只有在长时间运行的操作完成之后，才能写入该表。

对于具有外键的新表，Spanner 需要回填 根据需要为每个外键引用的索引。

对于具有外键的现有表，Spanner 需要 根据需要回填引用的索引。此外， Spanner 验证表中的现有数据，以确保其符合 外键的参照完整性约束条件。架构更改失败 如果数据无效，则会发生此错误。

不支持对现有限制条件添加外键操作。周三 建议您执行以下操作：

1. 添加带有操作的新约束条件。
2. 丢弃旧约束条件，不执行任何操作。

这样可以避免 Long-running Alter Constraint Operation 问题。创建 带有 ON DELETE CASCADE 操作的新外键， 是 DELETE CASCADE。丢弃约束条件可能会导致丢弃 外键支持索引（如果索引未用于其他外键） 限制条件。之后，如果用户使用 操作，可能需要长时间运行的操作，包括回填索引、 验证唯一索引约束条件，以及验证外键参照 限制条件。

注意 ：如果不删除旧的外键约束，将会产生开销， 如何验证额外的外键约束条件。

如果由于违反 UNIQUE 限制条件而无法创建被引用索引，则上述架构更改都可能会失败。

您可以查询 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE 以检查外键创建状态。

事务的限制条件验证

Spanner 在事务正在进行时验证外键约束 或者当写入的影响对后续操作可见时 所有操作

插入到引用列中的值将与被引用表和被引用列的值进行匹配。包含 NULL 引用值的行 未选中，也就是说，您可以将它们添加到参考表中。

Spanner 验证所有适用的外键参照限制条件 尝试通过 DML 语句或 API 更新数据时。如果有任何限制条件无效，则所有待更改项都将回滚。

每个 DML 语句完成之后立即进行验证。例如，您必须在插入引用行之前先插入被引用的行。使用 Mutation API 时，Mutation 会进行缓冲，直到提交事务为止。 外键验证将推迟到提交事务之后进行。在这种情况下，可以先插入引用行。

将评估每个事务的修改是否会影响外键限制条件。此类评估可能需要向服务器发送额外请求。 支持性索引还需要额外的处理时间来评估事务修改并维护索引。此外，每个索引都需要额外的存储空间。

支持性索引

外键不使用用户创建的索引。它们会创建自己的支持性索引。

Spanner 可以为每个外部索引创建最多两个二级后备索引 键，一个用于引用列，另一个用于引用列。 但是，外键通常会引用被引用表的主键，因此通常不需要被引用表上的第二个索引。

引用的表的支持性索引是 UNIQUE NULL_FILTERED 索引；如果任何现有数据违反了索引的唯一性限制条件，则无法创建外键。引用表的支持性索引为 NULL_FILTERED。

如果两个或多个外键需要相同的支持性索引，则 Spanner 将为所有这些项创建一个索引丢弃使用该索引的外键，将同时丢弃支持性索引。用户无法更改或丢弃支持性索引。

Spanner 使用每个数据库的信息架构来存储 关于后备索引的元数据。INFORMATION_SCHEMA.INDEXES 中的行 SPANNER_IS_MANAGED 值为 true 时，描述了后备索引。

在直接调用信息架构的 SQL 查询之外， Google Cloud 控制台不会以其他方式显示任何信息 数据库的后备索引

长时间运行的删除级联操作

当您从引用的表中删除行时，Spanner 需要删除 在引用表中引用已删除行的所有行。这可能会导致 造成一种级联效应，一次删除操作可能导致数以千计 删除操作添加带删除级联的外键约束 对表进行操作或者使用 delete 创建具有外键约束的表 级联操作会减慢删除操作的速度。

超出外键删除级联的变更限制

使用外键删除级联删除大量记录 对性能的影响这是因为删除的每条记录都会触发 与使用外部 API 的 键。如果事务中的变更数量超过 80,000， 交易失败

需要使用外键删除来删除大量记录 您应该先明确删除子表中的行， 从父表中删除行。这样可以防止 避免因变更限制而失败

外键和表交错的比较

Spanner 的表交错是许多父子项的理想选择 并且子表的主键包含父表的主键 主键列。子行及其父行共用位置可以显著提高性能。

外键是一种较通用的父子解决方案，并解决了其他用例。外键不限于主键列，而表可以具有多个外键关系，二者在某些关系中可以作为父键，而在其他关系中可以作为子键。但是，外键关系不会隐含表在存储层中共处同一位置的关系。

我们来看一个示例，使用之前在本主题中讨论过的订单处理架构。回想一下，我们的 Orders 表的定义如下：

图 3：采用外键的数据库架构示意图

图 3 中的设计存在一些限制。例如，每个订单目前只能包含一个订购商品。

假设我们的客户希望每笔订单能够订购多件商品。我们可以通过引入 OrderItems 表来增强设计，该表包含客户订购的每件商品。我们可以引入另一个外键来表示 Orders 和 OrderItems 之间新的一对多关系。但是，我们也知道，对各个订单及其各自的订购商品运行查询需要很长时间。集中存放这些数据可以提高性能，因此我们要创建 使用 Spanner 的表交错功能实现父子关系。

下面是我们对 OrderItems 表的定义（与 Orders 交错）。

GoogleSQL

CREATE TABLE OrderItems (
  OrderID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
) PRIMARY KEY (OrderID, ProductID),
  INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

PostgreSQL

CREATE TABLE OrderItems (
  OrderID BIGINT NOT NULL,
  ProductID BIGINT NOT NULL,
  Quantity BIGINT NOT NULL,
  FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
  PRIMARY KEY (OrderID, ProductID)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

图 4 直观展示了更新后的数据库架构，因为引入了这个新表 OrderItems 与 Orders 交错。在这里，您还可以看到这两个表之间的一对多关系。

图 4：添加交错的 OrderItems 表

在此配置中，我们可以在每个订单中拥有多个 OrderItems 条目，每个订单的 OrderItems 条目是交错的，因此与这些订单位于同一位置。通过这种方式进行物理交错 Orders 和 OrderItems 可以提高性能，有效地预连接表并允许您一起访问相关的行，同时最小化磁盘访问。对于 例如，Spanner 可以在本地通过主键执行联接，从而最大限度地减少 磁盘访问权限和网络流量等

如果事务中的变更数量超过 80,000， 交易失败这种大型级联删除非常适合具有 “在父级中交错”但不适用于具有外键的表 关系。如果您有外键关系并且需要删除一个 大量行，则应明确从子节点中删除这些行 表格。

如果您的一个用户表与另一个表存在外键关系，并且 从引用的表中删除一行会触发删除数百万行 您应该为架构设计一个删除级联操作， “interleaved in parent”。

注意：Google 建议您选择将父子关系表示为交错表或外键，但不能同时表示为这两者。同时使用两者 可能会消耗额外的存储空间和计算资源， 以验证约束条件并维护后备索引。

对照表

下表汇总了外键与表交错的比较方式。您可以根据这些信息来确定什么方式适合您的设计。

父子关系类型	表交错	外键
可以使用主键	是	是
可以使用非主键列	否	是
支持的父数据数量	0 .. 1	0 .. N
同时存储父数据和子数据	是	否
支持级联删除	是	是
Null 匹配模式	如果所有引用值与被引用值没有区别，则传递。 Null 值与 Null 值没有区别，但 Null 值与非 Null 值有区别。	如果有任何引用值为 Null，则传递。 如果所有引用值均为非 Null 值，且被引用表中有值等于引用值的行，则传递。 如果找不到匹配的行，则失败。
强制执行时间	使用 Mutation API 时的操作。 使用 DML 时的每个语句。	使用变更 API 时的每项事务。 使用 DML 时的每个语句。
可以轻松移除	不可以。除非删除整个子表，否则一旦创建表交错就无法移除。	是

后续步骤

• 了解如何创建和管理外键关系。

• 详细了解信息架构。