创建和管理外键关系

本页面介绍了如何管理数据库中的外键关系。

外键是表之间共享的列，用于在相关数据之间建立关联。当您使用外键时，Spanner 会确保维护这种关系。

下图显示了一个基本数据库架构，其中一个表中的数据与另一个表中的数据存在关系。

显示表之间的外键关系的数据库架构示例。

图 1：订单处理数据库架构图

图 1 中显示了架构中的三个表：

Customers 表记录了每个客户的名称。
Orders 表跟踪所有订单。
Products 表存储每个产品的产品信息。

这些表之间存在两个外键关系：

在 Orders 表和 Customers 表之间定义一个外键关系，以确保除非有相应的客户，否则无法创建订单。
Orders 表和 Products 表之间的外键关系可确保无法为不存在的商品创建订单。

本主题以之前的架构为例，讨论可用于管理数据库中表之间关系的数据定义语言 (DDL) CONSTRAINT 语句。

默认情况下，Spanner 中的所有外键都是强制执行外键，用于强制执行参照完整性。在 Spanner 中，您还可以选择使用信息性外键，这种外键不会验证或强制执行参照完整性。如需了解详情，请参阅外键比较和选择要使用的外键类型。如果未指定，则此页面上示例中的外键是强制执行的外键。

向新表添加外键

假设您已在基本商品订购数据库中创建了 Customers 表。现在，您需要一个 Orders 表来存储客户所下订单的相关信息。为了确保所有订单都有效，您不希望系统将 Orders 表中没有匹配条目的行插入 Customers 表。因此，您需要一个强制执行的外键来建立这两个表之间的关系。一种选择是向新表添加 CustomerID 列，并将其用作外键，以与 Customers 表中的 CustomerID 列建立关系。

创建带有外键的新表时，您可以使用 REFERENCE 建立与其他表的关系。包含 REFERENCE 语句的表称为“引用”表。REFERENCE 语句中指定的表是“被引用”表。REFERENCE 语句中指定的列称为引用列。

以下示例展示了如何使用 CREATE TABLE DDL 语句创建 Orders 表，该表具有引用 Customers 表中 CustomerID 的外键限制条件。

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)
);

前面的语句包含一个 CONSTRAINT 子句，该子句具有以下特征：

使用 CONSTRAINT 语法为限制条件命名，从而更轻松地使用所选名称丢弃表。
限制条件的名称为 FK_CustomerOrder。限制条件名称的范围限定为架构，并且在架构内必须具有唯一性。
您定义约束条件的 Orders 表是引用表。Customers表是“被引用”表。
引用表中的引用列为 CustomerID。它引用 Customers 表中的 CustomerID 字段。如果有人尝试使用 Customers 中不存在的 CustomerID 将行插入 Orders，则该插入将失败。

以下示例显示了一个替代的表创建语句。此处定义了没有名称的外键限制条件。使用此语法时，Spanner 会为您生成名称。如需查看所有外键的名称，请参阅查看外键关系的属性。

GoogleSQL

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  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,
  FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
  PRIMARY KEY (OrderID)
);

向现有表中添加外键

您还希望确保客户只能订购已存在的产品。如果您的表具有现有限制条件，您必须舍弃所有限制条件。在 Spanner 中，表中的所有强制执行的限制条件都必须在单个批处理 DDL 语句中同时实现。

如果您的表没有现有限制条件，则可以使用 ALTER TABLE DDL 语句向现有 Orders 表添加强制执行的外键限制条件，如以下示例所示：

ALTER TABLE Orders
  ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

Orders 中的引用列为 ProductID，它引用了 Products 中的 ProductID 列。如果您不介意 Spanner 为您命名这些约束条件，请使用以下语法：

ALTER TABLE Orders
  ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

向新表添加具有删除操作的外键

不妨回顾一下之前的示例，其中商品订购数据库中有一个 Customers 表，需要一个 Orders 表。您想要添加引用 Customers 表的外键限制条件。不过，您希望确保在将来删除客户记录时，Spanner 也会删除该客户的所有订单。在这种情况下，您需要使用 ON DELETE CASCADE 操作和外键限制条件。

以下是 Orders 表的 CREATE TABLE DDL 语句，其中包含引用 Customers 表的外键限制条件（带有 ON DELETE CASCADE 操作）。

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) ON DELETE CASCADE
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
  OrderID BIGINT NOT NULL,
  CustomerID BIGINT NOT NULL,
  Quantity BIGINT NOT NULL,
  ProductID BIGINT NOT NULL,
  FOREIGN KEY (CustomerID)
    REFERENCES Customers (CustomerID) ON DELETE CASCADE,
  PRIMARY KEY (OrderID)
);

前面的语句包含一个带有 ON DELETE CASCADE 子句的外键约束条件。CustomerID 列是外键，引用 Customers 表中的 CustomerID 字段。这意味着，Orders 表中的每个 CustomerID 值也必须存在于 Customers 表中。如果有人尝试从 Customers 表中删除某行，则 Orders 表中引用已删除 CustomerID 值的所有行也会在同一事务中被删除。

向表添加具有删除操作的外键

您还想确保只能为已存在的产品创建订单。您可以使用 ALTER TABLE 向订单表中添加另一个具有 ON DELETE CASCADE 操作的外键限制条件，如下所示：

ALTER TABLE Orders
  ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID)
    REFERENCES Products (ProductID) ON DELETE CASCADE;

从 Products 表中删除一行会删除 Orders 表中引用已删除的 ProductID 值的所有行。

使用信息性外键（仅限 GoogleSQL）

借助信息性外键，查询优化器可以利用外键关系，而不会产生因强制性外键执行的参照完整性检查而产生的开销。在以下情况下，信息性外键非常有用：强制执行严格的引用完整性要么不切实际，要么会产生显著的性能开销。

继续上一个示例，假设您要对 Customers、Orders 和 Products 表之间的关系进行建模。不过，在表格数据中强制执行严格的参照完整性可能会导致性能瓶颈，尤其是在订单量较高的购物高峰期。此外，客户可能会订购已停产并从 Products 表中移除的商品。

您可以使用信息性外键创建 Orders 表：

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) NOT ENFORCED,
    CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID) NOT ENFORCED
) PRIMARY KEY (OrderID);

通过使用 NOT ENFORCED 创建信息性外键，您可以允许订单引用不存在的客户或商品。如果客户账号可能会被删除或产品可能会停售，最好使用信息性外键，而不是强制执行的外键限制条件。使用信息性外键时，Spanner 不会执行参照完整性验证。这样可以减少写入开销，从而可能在订单处理高峰期提高性能。

您可以允许查询优化器使用这些关系来生成高效的查询计划。这可以提高在外键列上联接表的查询的性能。如需了解详情，请参阅用于优化查询的信息性外键。

跨外键关系查询数据

SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Products ON Orders.ProductsID = Products.ProductID;

使用强制执行外键实现参照完整性

添加强制执行外键关系的主要原因是让 Spanner 能够保持数据的参照完整性。如果您以破坏外键限制条件的方式修改数据，则更新将失败并显示错误。

请考虑图 2 中的数据。一些客户已经订购了商品，如 Orders 表中所示。由于强制外键约束已就位，因此插入到 Orders 表中的数据具有参照完整性。

“客户”表、“产品”表和“订单”表的示例数据。

图 2. 订购数据库中的示例数据。

以下示例展示了当您尝试以破坏参照完整性的方式修改数据时会发生什么情况。

在 Customers 中不存在 CustomerID 值的 Orders 表中添加一行

根据上图中的示例数据，如果您尝试了以下修改，会出现什么情况？
```
INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID)
  VALUES (19, 337876, 4, 447);
```
在此例中，系统会尝试使用 Customers 表中不存在的 CustomerID (447) 向 Orders 插入行。如果系统执行此操作，您的系统中将出现一个无效订单。不过，由于您已向 Orders 表添加了强制执行的外键限制条件，因此您的表会受到保护。INSERT 操作将失败，并显示以下消息（假设限制条件称为 FK_CustomerOrder）。
```
Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`.
Cannot find referenced values in Customers(CustomerID).
```
与强制执行的外键不同，信息性外键不会强制执行参照完整性。如果 FK_CustomerOrder 是信息性外键，则插入语句会成功，因为 Spanner 不会验证 Customers 表中是否存在相应的 CustomerID。因此，数据可能不符合 FK_CustomerOrder 定义的参照完整性。
尝试在外键限制条件中引用客户时从 Customers 表中删除一行。

假设客户退订了您的网店。您希望从后端删除该客户，因此尝试执行以下操作。
```
DELETE FROM Customers WHERE CustomerID = 721;
```
在本示例中，Spanner 通过外键限制条件检测到 Orders 表中仍然存在引用您尝试删除的客户行的记录。此时会显示以下错误。

Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.

如需解决此问题，您需要先删除 Orders 中的所有引用条目。您还可以使用 ON DELETE CASCADE 操作定义外键，以让 Spanner 处理对引用条目的删除。

同样，如果 FK_CustomerOrder 是信息性外键，则删除操作会成功，因为 Spanner 不保证信息性外键的参照完整性。

查看外键关系的属性

Spanner 的 INFORMATION_SCHEMA 包含有关外键及其支持性索引的信息。以下示例介绍了您可以通过查询“信息架构”回答的一些问题。

如需详细了解支持性索引，请参阅外键支持性索引。

我的数据库中定义了哪些限制条件？

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';

我的数据库中定义了哪些外键？

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

哪些索引是外键的二级索引（亦称“反向索引”？

外键支持性索引由 Spanner 管理，因此在 INDEXES 视图上查询 SPANNER_IS_MANAGED 会返回所有支持性索引。

SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
  i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED = 'YES';

外键限制条件定义了哪些参照操作？

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE,
  rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

外键是强制执行还是不强制执行？

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE, tc.ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';

如需了解详情，请参阅信息架构。

移除外键关系

以下 DDL 会从Orders表中丢弃外键限制条件。

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

删除限制条件本身时，系统会自动丢弃外键支持性索引。

支持更复杂的外键关系

以下主题将介绍如何使用外键来强制执行表之间更复杂的关系。

多列

外键可以引用多个列。列的列表构成一个键，它对应于表的主键或支持性索引。引用表包含被引用表键的外键。

在以下示例中，强制执行的外键定义表明：

TopHits 表中的每个 SongName 值都必须在 Songs 表中有一个匹配的值。
每对 SingerFirstName 和 SingerLastName 值都必须在 Singers 表中有一对匹配的 FirstName 和 LastName 值。

GoogleSQL

CREATE TABLE TopHits (
  Rank INT64 NOT NULL,
  SongName STRING(MAX),
  SingerFirstName STRING(MAX),
  SingerLastName STRING(MAX),

  -- Song names must either be NULL or have matching values in Songs.
  FOREIGN KEY (SongName) REFERENCES Songs (SongName),

  -- Singer names must either be NULL or have matching values in Singers.
  FOREIGN KEY (SingerFirstName, SingerLastName)
  REFERENCES Singers (FirstName, LastName)

) PRIMARY KEY (Rank);

PostgreSQL

CREATE TABLE TopHits (
  Rank BIGINT NOT NULL,
  SongName VARCHAR,
  SingerFirstName VARCHAR,
  SingerLastName VARCHAR,

  -- Song names must either be NULL or have matching values in Songs.
  FOREIGN KEY (SongName) REFERENCES Songs (SongName),

  -- Singer names must either be NULL or have matching values in Singers.
  FOREIGN KEY (SingerFirstName, SingerLastName)
  REFERENCES Singers (FirstName, LastName),

  PRIMARY KEY (Rank)
);

循环引用

可能由于旧版原因或反规范化，有时表具有循环依赖项。Spanner 外键允许循环引用。由于必须先有被引用表，然后外键才能引用该表，因此其中一个外键必须添加 ALTER TABLE 语句。示例如下

创建 TableA，但没有外键。
创建 TableB，并对 TableA 应用外键限制条件。
在 TableA 上使用 ALTER TABLE 以创建外键对 TableB 的引用。

自引用表

循环引用的一种特殊类型是定义引用同一个表的外键的表。例如，以下代码段显示了用于强制要求员工的 ManagerId 也是员工的外键。

GoogleSQL

CREATE TABLE Employees (
  EmployeeId INT64 NOT NULL,
  EmployeeName STRING(MAX) NOT NULL,
  ManagerId INT64,
  FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);

PostgreSQL

CREATE TABLE Employees (
  EmployeeId BIGINT NOT NULL,
  EmployeeName VARCHAR NOT NULL,
  ManagerId BIGINT,
  FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId),
  PRIMARY KEY (EmployeeId)
);

后续步骤

详细了解 Spanner 中的外键支持。
详细了解 Spanner 的信息架构。

创建和管理外键关系 使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

向新表添加外键

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

向现有表中添加外键

向新表添加具有删除操作的外键

GoogleSQL

PostgreSQL

向表添加具有删除操作的外键

使用信息性外键（仅限 GoogleSQL）

跨外键关系查询数据

使用强制执行外键实现参照完整性

查看外键关系的属性

移除外键关系

支持更复杂的外键关系

多列

GoogleSQL

PostgreSQL

循环引用

自引用表

GoogleSQL

PostgreSQL

后续步骤

创建和管理外键关系