SQL Foreign Key

SQL FOREIGN KEY（外键）约束：确保关系完整性

理解 FOREIGN KEY（外键）约束

一个 FOREIGN KEY（外键）是一个表中的列（或一组列），它引用另一个表中的 PRIMARY KEY（主键）或 UNIQUE（唯一）键。这在两个表之间建立了一个链接，强制执行引用完整性。

我们通过一个例子来说明。考虑两个表：“Persons” 和 “Orders”。

“Persons” 表 (父表)：

PersonID (PK) LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

“Orders” 表 (子表)：

OrderID (PK) OrderNumber PersonID (FK) OrderDate

101 77895 3 2023-01-15

102 44678 3 2023-01-18

103 22456 2 2023-02-01

104 24562 1 2023-02-05

在这个例子中，“Orders” 表中的 PersonID 列是一个 FOREIGN KEY，它引用了 “Persons” 表中的 PersonID（主键）。

FOREIGN KEY 的优点：

• **数据完整性：**防止创建孤立记录的操作（例如，为不存在的人创建订单）。
• **引用一致性：**确保外键列中的值与引用的主键列中的现有值匹配。
• **数据关系：**清晰定义表之间的关系，这对于数据库设计和复杂查询至关重要。

在 CREATE TABLE 中定义 SQL FOREIGN KEY 约束

以下 SQL 展示了在创建 “Orders” 表时如何在 PersonID 列上创建 FOREIGN KEY：

通用语法（广泛支持）：

CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderNumber INT NOT NULL, PersonID INT, OrderDate DATE, CONSTRAINT FK_Order_Person FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) — Optional: ON DELETE and ON UPDATE actions — ON DELETE SET NULL — ON DELETE CASCADE — ON UPDATE CASCADE );

注意：为约束命名（例如，FK_Order_Person）是一个好习惯，可以使后续管理更容易。有些数据库允许为单列使用内联外键定义语法：

CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderNumber INT NOT NULL, PersonID INT REFERENCES Persons(PersonID), — Inline syntax OrderDate DATE );

引用操作（ON DELETE, ON UPDATE）：

• RESTRICT 或 NO ACTION (默认)：如果存在子行，则阻止删除/更新父行。
• CASCADE：删除/更新父行的同时，也删除/更新相应的子行。
• SET NULL：删除/更新父行时，将子行中的外键列设置为 NULL（要求外键列可为空）。
• SET DEFAULT：将外键设置为其默认值（不太常见）。

在 ALTER TABLE 中添加 SQL FOREIGN KEY 约束

要向现有的 “Orders” 表添加 FOREIGN KEY 约束：

通用语法：

ALTER TABLE Orders ADD CONSTRAINT FK_Order_Person FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

删除 FOREIGN KEY 约束

要移除现有的 FOREIGN KEY 约束：

MySQL：

ALTER TABLE Orders DROP FOREIGN KEY FK_Order_Person;

SQL Server, Oracle, PostgreSQL 等 (标准 SQL)：

ALTER TABLE Orders DROP CONSTRAINT FK_Order_Person;

实际应用：FOREIGN KEY 是关系型数据库设计的基础。它们模拟现实世界中的关系（例如，一个客户有多个订单，一个雇员属于一个部门），并确保整个数据库的数据一致性。