创建和管理外键关系

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

外键是在表之间共享的列,用于建立 之间的关联。使用外键时,Spanner 可确保 这种关系的维护。

下图展示了一个简单的数据库架构,其中表中的数据具有 另一个表中的数据之间的关系。

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

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

图 1 所示的架构中有三个表:

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

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

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

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

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

在创建新表时添加外键

假设您在简单产品中创建了一个 Customers 表 订单数据库。现在,您需要一个 Orders 表来存储有关 客户下达的订单为确保所有订单均有效,您不应 让系统向 Orders 表中插入行,除非还有一个 Customers 表中的匹配条目。因此,您需要一个外键 在两个表之间建立关系。一种选择是添加 CustomerID 列添加到新表,并将其用作创建 与 Customers 表中的 CustomerID 列的关系。

使用外键创建新表时,您可以使用 REFERENCE 与另一个表建立关系。表格 包含 REFERENCE 语句的表格称为“引用”表。通过 REFERENCE 语句中指定的表是被引用的表。REFERENCE 语句中指定的那个称为引用列。

以下示例展示了如何使用 CREATE TABLE DDL 语句 使用引用的外键约束条件创建 OrdersCustomers 表中的 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 字段。如果有人尝试 向 Orders 中插入一个其 CustomerID 中不存在的行 Customers,插入失败。

以下示例展示了另一个表创建语句。此处定义了没有名称的外键限制条件。当您使用此语法时,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)
);

向现有表中添加外键

您还需要确保客户只能订购 存在。首先,您必须删除现有限制条件。然后,您可以使用 ALTER TABLE,用于向 Orders 表添加另一个外键约束条件,如 如下所示:

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

Orders 中的引用列为 ProductID,它引用了“产品”中的 ProductID 列。同样,如果您愿意 请使用以下语法:

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

在创建新表时添加带有删除操作的外键

回想一下前面的示例,您的产品中包含 Customers 表 需要 Orders 表的订购数据库。您想要添加外键 引用 Customers 表的限制条件。不过,您需要确保 将来删除客户记录时,Spanner 同时删除该客户的所有订单。在本示例中,您希望使用 带有外键约束条件的 ON DELETE CASCADE 操作。

以下用于 Orders 表的 CREATE TABLE DDL 语句包含 使用 ON DELETE CASCADE 操作引用 Customers 表的外键限制条件。

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 表中删除一行会删除 引用已删除的 ProductID 值的 Orders 表。

跨外键关系查询数据

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

Spanner 如何保持引用完整性的示例

添加外键关系的主要原因是,为了让 Spanner 能够维护 参照完整性 数据。如果您以打破外键约束的方式修改数据, 更新失败并报错。

请参考图 2 中的数据。一些客户已经订购了商品,如订单表中所示。由于存在现有外键,因此您可以 可以保证插入到 Orders 表中的数据 参照完整性。

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

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

以下示例显示了您尝试修改 可能会破坏引用完整性。

  • Orders 表中添加一个 CustomerID 值不是 存在于Customers

    根据 上面的图表?

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

    在此情况下,系统会尝试向 Orders 中插入一行 Customers 表中不存在的 CustomerID (447)。如果系统 那么您的系统中会存在无效订单。不过,使用 Orders 表中添加的外键约束条件,则表为 受保护。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;
    

    在此示例中,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 处理引用条目的删除。

查看外键关系的属性

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;

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

移除外键关系

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

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

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

支持更复杂的外键关系

多列

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

在以下示例中,外键定义告知 Spanner TopHits 表中的每个 SongName 值都必须在 Songs 表;以及每个SingerFirstNameSingerLastName对值 必须在 Singers 中有匹配的 FirstNameLastName 对值 表格。

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 语句。示例如下

  1. 创建 TableA,但没有外键
  2. 使用 TableA 上的外键限制条件创建 TableB。
  3. 在 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)
);

后续步骤