创建和管理外键关系

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

外键允许您定义表之间的关系。Cloud Spanner 可确保保持这些表之间的参照完整性。下图展示了我们将在本指南中使用的简单数据库架构。

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

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

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

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

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

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

  • Orders 表和 Products 表之间的外键关系可确保无法为不存在的商品创建订单。

以此架构为参考，我们来看看可用于在数据库中管理这些限制条件的数据定义语言 (DDL) 语句。

在创建新表时添加外键

假设我们已经在简单的商品订购数据库中创建了 Customers 表。我们需要一个 Orders 表来存储客户所下订单的相关信息。为了确保所有订单都有效，我们不希望系统将 Customers 表中没有匹配条目的行插入“订单”表。

以下是 Orders 表（包含引用 Customers 表的外键限制条件）的 CREATE TABLE DDL 语句。

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

前面的语句包含一个 CONSTRAINT子句，我们可以对其进行如下描述：

  • 通过使用 CONSTRAINT 语法，您可以为限制条件命名，从而更轻松地丢弃所选名称。

  • 限制条件的名称为 FK_CustomerOrder。限制条件名称的范围限定为架构，并且在架构内必须具有唯一性。

  • 我们定义限制条件的 Orders 表称为“引用”表。Customers表是“被引用”表。

  • 引用表中的“引用”列为 CustomerID。它引用 Customers 表中的 CustomerID 字段。如果有人尝试使用 Customers 中不存在的 CustomerID 将行插入 Orders，则该插入将失败。

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

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

向现有表中添加外键

我们还想确保只能为已存在的产品创建订单。我们将使用 ALTER TABLE 向订单表中添加另一个外键限制条件，如下所示：

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

Orders 中的引用列为 ProductID，它引用了“产品”中的 ProductID 列。同样，如果 Cloud Spanner 可以为您命名这些限制条件，请使用以下语法：

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

跨外键关系查询数据

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

参照完整性示例

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

考虑下面图 2 中的数据。一些客户已经订购了商品，如订单表中所示。由于已有外键，我们可以保证插入到 Orders 表中的数据具有参照完整性。

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

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

让我们来看看当我们尝试以破坏参照完整性的方式修改数据时会发生什么情况。

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

根据上图中的示例数据，如果我们尝试了以下修改，会出现什么情况？

INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID)
  VALUES (19, 337876, 4, 447);

在此例中，我们尝试使用 Customers 表中不存在的 CustomerID (447) 向 Orders 插入行。如果我们允许此操作，系统将出现一个无效订单。但是，我们定义的从 Orders 表到 Customers 表的外键限制条件可以提供保护。INSERT 操作将失败，并显示以下消息（假设限制条件称为 FK_CustomerOrder）。

Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`. Cannot find referenced values in Customers(CustomerID).

尝试在外键限制条件中引用客户时从 Customers 表中删除一行。

假设客户退订了我们的网店。我们希望从后端删除该客户，因此我们尝试执行以下操作。

DELETE FROM Customers WHERE CustomerID = 721;

在本示例中，Cloud Spanner 通过外键限制条件检测到 Orders 表中仍然存在引用我们尝试删除的客户行的记录。此时会显示以下错误。

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

要解决此问题，我们需要先删除 Orders 中的所有引用条目。 另一个解决方案是，如果我们将 CustomerID 定义为 NULLABLE，则将 CustomerID 字段设置为 NULL 以移除引用。外键不支持 CASCADE DELETE。

查看外键关系的属性

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

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

外键支持性索引由 Cloud 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 is true;

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

移除外键关系

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

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

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

支持更复杂的外键关系

多列

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

在以下示例中，外键定义让 Cloud Spanner 了解到 TopH 表中的每个 SongName 值都必须与 Songs 表中的值相符，并且每个 SingerFirstName 和 SingerLastName 值对在 Singers 表中必须具有匹配的 FirstName 和 LastName 值对。

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

循环引用

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

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

自引用表

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

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

后续步骤