本页面介绍了如何管理数据库中的外键关系。
外键是在表之间共享的列,用于在相关数据之间建立关联。当您使用外键时,Spanner 会确保保持这种关系。
下图显示了一个简单的数据库架构,在该架构中,一个表中的数据与另一个表中的数据有关系。
图 1:订单处理数据库架构图
图 1 所示的架构中有三个表:
Customers
表记录了每个客户的名称。Orders
表跟踪所有订单。Products
表存储每个产品的产品信息。
这些表之间存在两个外键关系:
在
Orders
表和Customers
表之间定义一个外键关系,以确保除非有相应的客户,否则无法创建订单。Orders
表和Products
表之间的外键关系可确保无法为不存在的商品创建订单。
以前面的架构为例,本主题讨论了可用于管理数据库中表之间的关系的数据定义语言 (DDL) CONSTRAINT
语句。
在创建新表时添加外键
假设您已在简单的商品订购数据库中创建了一个 Customers
表。您现在需要一个 Orders
表来存储客户所下订单的相关信息。为确保所有订单均有效,除非 Customers
表中也有匹配的条目,否则您不希望系统将行插入 Orders
表中。因此,您需要一个外键在两个表之间建立关系。一种选择是向新表添加一个 CustomerID
列,并将其用作外键,以创建与 Customers
表中的 CustomerID
列的关系。
使用外键创建新表时,可以使用 REFERENCE
与另一个表建立关系。包含 REFERENCE
语句的表称为“引用”表。REFERENCE
语句中指定的表是引用的表。REFERENCE
语句中指定的列称为引用列。
以下示例展示了如何使用 CREATE TABLE
DDL 语句,通过引用 Customers
表中的 CustomerID
的外键约束来创建 Orders
表。
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
中插入包含Customers
中不存在的CustomerID
的行,则插入失败。
以下示例展示了另一种表创建语句。此处定义了没有名称的外键限制条件。使用此语法时,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
列。同样,如果您愿意为 Spanner 命名这些限制条件,请使用以下语法:
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
表中删除一行,则会删除 Orders
表中引用已删除 ProductID
值的所有行。
跨外键关系查询数据
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
表添加一行,并包含Customers
中不存在的CustomerID
值根据上图中的示例数据,如果您尝试进行以下修改,会发生什么情况?
INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID) VALUES (19, 337876, 4, 447);
在这种情况下,系统会尝试向
Orders
插入一个具有Customers
表中不存在的CustomerID
(447) 的行。如果系统执行此操作,您的系统中会出现无效订单。但是,通过向Orders
表添加外键限制条件,表会受保护。如果限制条件称为FK_CustomerOrder
,则INSERT
会失败并显示以下消息。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 表中必须具有匹配值;并且 Singers 表中的每个 SingerFirstName
和 SingerLastName
值对必须具有匹配的 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,但没有外键
- 使用 TableA 上的外键限制条件创建 TableB。
- 在 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 的 INFORMATION SCHEMA。