返回所有指南
SQL··阅读约需 5 分钟

主键与外键:数据库关系的基础

理解SQL中的主键与外键。了解它们如何强制执行数据完整性、建立关系以及与级联操作协同工作。

由订单外键引用的客户主键 42,带有具体的 SQL 约束
在本指南中
  1. 理解主键
  2. 理解外键
  3. 主键与外键如何协同工作
  4. 复合键
  5. 级联操作
  6. 最佳实践
  7. 参考资料与延伸阅读

除非另外命名了方言,否则 SQL 示例使用 PostgreSQL。简短的代码片段假定其引用的表已存在。在应用 DDL 之前,请在目标数据库中验证 DDL。

理解主键

A 主键 是唯一标识数据库表中每一行的列(或一组列)。没有两行可以共享相同的主键值,并且该值永远不能是 NULL。每个表最多可以声明一个主键,选择主键是应用表的一项最佳实践。

自然键与代理键

A 自然键 使用真实世界领域中已有的数据。例如,一个国家的 ISO 代码(US, FR) 或书本的 ISBN。自然键是有意义的,但会随时间改变,并可能带来隐私问题 (例如社会安全号码)。

A 代理主键 是具有业务含义的人工标识符——通常是自动递增的整数或 UUID。代理键稳定、紧凑且易于索引,这就是为什么大多数现代架构都偏爱它们的原因。

-- Natural key example
CREATE TABLE countries (
    iso_code  CHAR(2)      PRIMARY KEY,
    name      VARCHAR(100) NOT NULL
);

-- Surrogate key example
CREATE TABLE users (
    id         SERIAL       PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ  DEFAULT now()
);

理解外键

A 外键 是表中的一个列(或一组列),它引用主键或另一个符合条件的唯一键,包括同一表中的键。它强制执行 参照完整性:非空外键值必须具有匹配的引用的行。可空和复合外键需要显式的可空性和匹配规则。

CREATE TABLE posts (
    id        SERIAL       PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    author_id INT          NOT NULL,
    CONSTRAINT fk_posts_author
        FOREIGN KEY (author_id) REFERENCES users (id)
);

有了这个约束,数据库将拒绝任何 INSERT 进入 posts 其中 author_id 不匹配现有的 users.id.

外键约束在 ERD 中显示为关系连线
主键标识行;外键将这些标识符转换为图中可见的关系。

主键与外键如何协同工作

主键和外键是关系数据库的骨干。一个表中的主键成为锚点;相关表中的外键指向该锚点。此链接强制执行声明的关系。您还可以 JOIN 表,并模拟现实世界的关系,例如“一个用户 拥有多个 posts" 或 "an order 属于 一个客户。"

-- Retrieve every post alongside the author's email
SELECT p.title, u.email
FROM   posts p
JOIN   users u ON u.id = p.author_id;

要在编写 SQL 之前绘制实体草图,请打开 ERD 设计器。要检查上面的表和外键定义,请将它们粘贴到 SQL转ERD编辑器.

复合键

有时单个列不足以保证唯一性。一个 复合键 (也称为 复合键) 将两个或多个列组合成一个主键。这种模式常见于 连接表 (也称为连接表或关联表),用于多对多关系。

-- Junction table for a many-to-many relationship
CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrolled_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students (id),
    FOREIGN KEY (course_id)  REFERENCES courses (id)
);

复合主键 (student_id, course_id) 确保同一个学生不能两次选修同一门课程,同时允许每个学生选修多门课程,每门课程有多个学生。

级联操作

定义外键时,您还可以指定在引用的行更新或删除时会发生什么。这些 引用动作 保持您的数据一致,无需手动清理。

  • CASCADE — 当父行更改时自动删除或更新子行。
  • SET NULL — 将外键列设置为 NULL (该列必须可空)。
  • SET DEFAULT — 将列重置为其默认值,该值仍必须满足外键。支持因引擎而异;MySQL InnoDB 拒绝此操作。
  • RESTRICT / NO ACTION — 拒绝会留下无效引用的更改。在 PostgreSQL 中,当约束可延迟时,NO ACTION 可以延迟;RESTRICT 是即时的。
CREATE TABLE comments (
    id        SERIAL       PRIMARY KEY,
    body      TEXT         NOT NULL,
    post_id   INT          NOT NULL,
    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id)
        REFERENCES posts (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Deleting a post now automatically removes all its comments.

使用 CASCADE 小心 — 删除父行将静默清除所有相关的子行。在审计繁重的系统中,请考虑 RESTRICT 结合软删除标志使用。

最佳实践

  • 偏好 代理主键 (SERIAL, BIGSERIAL, 或 UUID) 作为应用程序表的主键。当领域标识符足够稳定以满足您的要求时,请使用自然键,并规划更改将如何影响引用。
  • 始终 命名你的约束 (例如, fk_posts_author) 以使错误消息具有意义。
  • 对外键列建立索引。许多数据库不会自动在外键上创建索引,而缺少索引会严重降低连接(JOIN)和级联删除的速度。
  • 考虑 级联操作 对于每个外键。默认为 NO ACTION 是最安全的;切换到 CASCADE 仅当子行在没有父行的情况下确实失去意义时。
  • 从模型的标识规则中选择复合键。代理键可以简化引用,但当必须防止重复时,请在原始业务键上保留 UNIQUE 约束。
  • 首先可视地建模您的架构。此 ERD 设计器 允许您绘制概念模型并审查其 SQL 转换——在设计错误到达生产环境之前将其捕获。

参考资料与延伸阅读

更多文章