如何在 SQLite 中创建外键

SQLite 的外键功能虽然默认关闭，但一旦正确启用和使用，就能有效维护数据的完整性和一致性。

发布于

关系型数据库的核心价值在于建立数据之间的关联，而外键正是实现这种关联的桥梁。想象一个图书馆管理系统，如果没有外键约束，就可能出现借阅记录对应到不存在的图书编号，或者删除图书时留下孤立的借阅记录。SQLite 的外键功能虽然默认关闭，但一旦正确启用和使用，就能有效维护数据的完整性和一致性。

为什么需要外键约束

外键不仅仅是两个表之间的连接线，它更是数据完整性的守护者。通过外键约束，我们可以：

  • 维护引用完整性：确保子表记录总是指向有效的父表记录
  • 自动级联操作：设置删除或更新时的自动处理规则
  • 建立清晰关系：直观展现数据实体间的关联
  • 防止孤立数据：避免产生无意义的"孤儿"记录

启用 SQLite 的外键支持

由于历史兼容性原因，SQLite 默认关闭外键约束，需要手动开启。

连接数据库时启用

sqlite3 mydatabase.db "PRAGMA foreign_keys = ON;"

在会话中启用

PRAGMA foreign_keys = ON;

验证外键状态

PRAGMA foreign_keys;

返回 1 表示已启用，0 表示禁用。

基础外键创建方法

创建外键的基本语法是在建表时定义 FOREIGN KEY 约束。

单列外键示例

CREATE TABLE departments (
    dept_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

复合外键示例

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    order_date TEXT,
    PRIMARY KEY (order_id, customer_id)
);

CREATE TABLE order_items (
    item_id INTEGER PRIMARY KEY,
    order_id INTEGER,
    customer_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    FOREIGN KEY (order_id, customer_id)
        REFERENCES orders(order_id, customer_id)
);

外键约束行为配置

通过 ON DELETE 和 ON UPDATE 子句，可以定义引用变更时的处理方式。

级联删除

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER,
    FOREIGN KEY (author_id)
        REFERENCES authors(author_id)
        ON DELETE CASCADE
);

当作者记录删除时，自动删除关联的所有图书。

设置为 NULL

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

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount REAL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET NULL
);

客户记录删除时，相关订单的 customer_id 自动设为 NULL。

其他约束行为

  • RESTRICT：阻止父表记录的删除或更新（默认行为）
  • NO ACTION：与 RESTRICT 类似，但检查时机略有不同
  • SET DEFAULT：将外键值设为列定义的默认值

修改表添加外键约束

对于已存在的表，可以通过 ALTER TABLE 添加外键约束。

添加新列时包含外键

ALTER TABLE employees
ADD COLUMN manager_id INTEGER
REFERENCES employees(emp_id);

为现有列添加外键

-- SQLite 需要重建表来实现这个操作
CREATE TABLE new_orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date TEXT
);

INSERT INTO new_orders
SELECT * FROM orders;

DROP TABLE orders;
ALTER TABLE new_orders RENAME TO orders;

外键约束验证与维护

创建外键后，需要确保现有数据满足约束条件。

验证外键约束

PRAGMA foreign_key_check;

返回所有违反外键约束的记录。

检查特定表的外键

PRAGMA foreign_key_check(orders);

临时禁用约束检查

PRAGMA foreign_keys = OFF;
-- 执行可能违反约束的操作
PRAGMA foreign_keys = ON;

实际应用场景示例

让我们看几个外键在实际系统中的典型应用。

博客系统关系模型

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
    post_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES users(user_id)
        ON DELETE CASCADE
);

CREATE TABLE comments (
    comment_id INTEGER PRIMARY KEY,
    post_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    content TEXT,
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
        ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE SET NULL
);

库存管理系统

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category_id INTEGER,
    FOREIGN KEY (category_id)
        REFERENCES categories(category_id)
        ON UPDATE CASCADE
);

CREATE TABLE inventory (
    item_id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    warehouse_id INTEGER NOT NULL,
    quantity INTEGER CHECK (quantity >= 0),
    FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE RESTRICT,
    FOREIGN KEY (warehouse_id)
        REFERENCES warehouses(warehouse_id)
        ON DELETE CASCADE
);

总结

SQLite 的外键功能虽然需要显式启用，但提供了完整的关系数据库特性：

  • 关系维护：通过简单的语法建立表间关联
  • 灵活行为：支持多种级联操作和约束方式
  • 数据完整：自动阻止无效引用和孤立数据
  • 兼容标准：遵循 SQL 标准的外键行为

在实际应用中，建议：

  1. 始终启用外键支持（PRAGMA foreign_keys = ON）
  2. 根据业务逻辑选择合适的约束行为
  3. 为外键列创建索引以提高查询性能
  4. 定期检查外键约束确保数据一致性

正确使用外键可以大幅减少应用层的验证代码，让数据库真正成为数据完整性的最后防线。