除非另外命名了方言,否则 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.
主键与外键如何协同工作
主键和外键是关系数据库的骨干。一个表中的主键成为锚点;相关表中的外键指向该锚点。此链接强制执行声明的关系。您还可以 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 转换——在设计错误到达生产环境之前将其捕获。



