sql-foreign-key

SQL 外键

什么是 SQL 外键？

外键（Foreign Key）是一种约束，它在两张表之间创建链接。它是一个表中的列（或一组列），引用另一个表的主键。此链接用于强制执行参照完整性（referential integrity），这意味着它能防止那些会导致“孤立”记录（带有无效链接的记录）的操作。

包含外键的表称为子表（child table）或引用表（referencing table）。包含它所引用主键的表称为父表（parent table）或被引用表（referenced table）。

概念示例

考虑两个表：Customers 和 Orders。每个订单都必须属于一个客户。Orders 表将有一个 customer_id 列，它是一个外键，引用 Customers 表中的 customer_id 主键。这确保了两点：

• 你不能为在 Customers 表中不存在的 customer_id 创建订单。
• 你不能（默认情况下）删除在 Orders 表中仍有订单的客户。

创建外键

让我们首先创建父表 Customers。

CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

在表创建期间（推荐）

最佳实践是在创建子表时定义外键。为方便管理，命名你的约束至关重要。

CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY(customer_id)
        REFERENCES Customers(customer_id)
);

在现有表上

你还可以使用 ALTER TABLE 将外键约束添加到现有表。只有当外键列中的所有现有值在父表的主键中都存在匹配值时，此操作才会成功。

ALTER TABLE Orders
ADD CONSTRAINT fk_orders_customers
    FOREIGN KEY(customer_id)
    REFERENCES Customers(customer_id);

参照操作：ON DELETE 和 ON UPDATE

外键可以配置为自动处理当被引用的父行被删除或更新时，子行会发生什么。这些功能很强大，但必须谨慎使用。

• NO ACTION / RESTRICT（默认）：如果存在任何子行，则阻止父行的删除或更新。操作将以错误失败。
• CASCADE（级联）：如果父行被删除，所有相应的子行也会自动删除。如果父键被更新，子键也会更新以匹配。
• SET NULL：如果父行被删除或键被更新，子行中的外键列将被设置为 NULL。这要求外键列允许为 NULL。
• SET DEFAULT：类似于 SET NULL，但将外键列设置为其默认值。

示例：ON DELETE CASCADE

在这里，如果一个客户被删除，他们所有的订单也会自动删除。

CREATE TABLE Orders (
    -- ... other columns
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY(customer_id) REFERENCES Customers(customer_id)
        ON DELETE CASCADE
);

删除外键

要删除外键约束，你需要使用 ALTER TABLE 并指定约束的名称。这突出了为什么命名你的约束是一项关键的最佳实践。

ALTER TABLE Orders
DROP CONSTRAINT fk_orders_customers;

主键 vs. 外键

方面	主键	外键
目的	在其自己的表中唯一标识一条记录。	将一条记录链接到另一个表中的主键。
唯一性	每行都必须是唯一的。	可以有重复值（例如，一个客户可以有多个订单）。
NULL 值	不能为 NULL。	可以为 NULL，除非同时应用了 NOT NULL 约束。
每表的数量	每表只有一个。	一个表可以有多个外键，链接到不同的父表。

常见错误和最佳实践

• 错误：“foreign key constraint fails”。当你尝试向外键列插入一个在父表主键中不存在的值时，会发生此错误。
• 错误：“Cannot delete or update a parent row”。当你尝试删除一个仍被子行引用的父行时（默认情况下），会发生此错误。
• 最佳实践：始终显式命名你的约束。
• 最佳实践：确保外键和它所引用的主键的数据类型一致。
• 最佳实践：仔细选择你的 ON DELETE / ON UPDATE 操作。CASCADE 功能强大，但如果处理不当，可能导致意外的大量删除。