如何为 Sqlite 数据库的表添加外键

在数据库设计中，外键（Foreign Key）是建立表与表之间关系的重要机制。通过外键约束，可以确保数据的完整性和一致性，避免出现"孤儿记录"（即子表中引用主表中不存在的记录）。Sqlite 作为一款轻量级数据库，从 3.6.19 版本开始完整支持外键约束，但需要手动启用。本文将详细介绍如何在 Sqlite 中使用外键，包括基本语法、实际示例以及注意事项。

启用 Sqlite 的外键支持

虽然 Sqlite 支持外键约束，但默认情况下该功能是关闭的。这是为了保持向后兼容性。要使用外键，需要在每次数据库连接时执行以下命令：

PRAGMA foreign_keys = ON;

这条命令需要在每个数据库会话中执行，它不会持久化到数据库文件中。建议在打开数据库连接后立即启用此设置。

创建表时定义外键

在创建新表时，可以通过 FOREIGN KEY 子句定义外键关系。基本语法如下：

CREATE TABLE 子表名称 (
    列1 数据类型,
    列2 数据类型,
    ...
    FOREIGN KEY (子表列名) REFERENCES 父表名称(父表列名)
);

例如，我们创建一个 orders 表，它引用 customers 表中的 customer_id：

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    order_date TEXT NOT NULL,
    customer_id INTEGER,
    amount REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

在这个例子中，orders 表中的 customer_id 必须存在于 customers 表的 customer_id 列中，否则插入操作会失败。

多列外键和组合外键

Sqlite 也支持多列外键，即外键可以引用父表中的多个列。这在需要复合主键的情况下特别有用：

CREATE TABLE departments (
    company_id INTEGER,
    dept_id INTEGER,
    dept_name TEXT,
    PRIMARY KEY (company_id, dept_id)
);

CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    company_id INTEGER,
    dept_id INTEGER,
    emp_name TEXT,
    FOREIGN KEY (company_id, dept_id) REFERENCES departments(company_id, dept_id)
);

添加外键到现有表

如果已经存在的表需要添加外键约束，可以使用 ALTER TABLE 命令。不过 Sqlite 的 ALTER TABLE 功能有限，只能通过以下步骤实现：

创建新表（包含所需的外键约束）
将旧表数据导入新表
删除旧表
重命名新表为旧表名称

示例：

-- 假设原始的 orders 表没有外键约束
-- 创建临时表
CREATE TABLE orders_new (
    order_id INTEGER PRIMARY KEY,
    order_date TEXT NOT NULL,
    customer_id INTEGER,
    amount REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 复制数据
INSERT INTO orders_new SELECT * FROM orders;

-- 删除旧表
DROP TABLE orders;

-- 重命名新表
ALTER TABLE orders_new RENAME TO orders;

外键约束行为

Sqlite 允许指定当父表记录被更新或删除时的行为，通过 ON DELETE 和 ON UPDATE 子句实现：

CASCADE：级联操作，父表变更时子表相应变更
RESTRICT：阻止父表变更（如果有引用存在）
SET NULL：将引用列设置为 NULL
SET DEFAULT：将引用列设置为默认值
NO ACTION：不采取特殊动作（默认行为）

示例：

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE RESTRICT
);

在这个例子中，当 customers 表中的某个客户被删除时，该客户的所有订单也会被自动删除（ON DELETE CASCADE）。但如果尝试更新 customers 表中的 customer_id，而该 ID 被 orders 表引用，则操作会被阻止（ON UPDATE RESTRICT）。

禁用外键约束检查

在某些情况下（如批量导入数据时），可能需要临时禁用外键检查以提高性能或允许违反约束的数据：

PRAGMA foreign_keys = OFF;
-- 执行不需要外键检查的操作
PRAGMA foreign_keys = ON;

注意：这种操作应谨慎使用，因为它可能导致数据库不一致。

查看外键关系

要查看数据库中的外键关系，可以使用以下命令：

PRAGMA foreign_key_list(表名);

例如：

PRAGMA foreign_key_list(orders);

这将返回 orders 表中定义的所有外键约束信息。

总结

外键是维护数据库完整性的强大工具，Sqlite 提供了完整的外键支持，尽管需要手动启用。通过合理使用外键约束，可以确保数据之间的关系始终保持一致。在实际应用中，应该：

始终记得启用 PRAGMA foreign_keys = ON
在表设计阶段就规划好外键关系
根据业务需求选择合适的 ON DELETE 和 ON UPDATE 行为
谨慎使用临时禁用外键的功能

正确使用外键可以显著减少应用程序中数据一致性检查的代码量，让数据库自身来维护这些基本规则。