数据库管理系统（DBMS）里最容易被忽视、又最容易踩坑的，其实是约束。尤其主键和外键，这两样东西是表结构设计的“地基”。没有主键，一行数据没有唯一身份；没有外键，表和表之间只是看起来有关联，实际可以随便写入脏数据。Neso Academy 在数据库管理系统课程中专门用一整节讲主键与外键约束，今天这篇就把这笔账重新算一遍。
这篇会覆盖以下几个实操点：
- 主键约束是什么，单列主键和复合主键怎么选；
- 外键约束是什么，四种级联策略怎么用；
- 用 SQL 语句实际建表验证约束；
- 批量导入数据时约束如何保护数据；
- 约束对索引和写入性能的影响；
- 常见报错排查和设计最佳实践。
写完之后，可以直接把文中的建表规范保存成自己的项目模板，遇到约束报错也有排查清单可查。
1. 主键和外键核心知识速览
| 能力项 | 说明 |
|---|---|
| 核心概念 | 主键约束（PRIMARY KEY）、外键约束（FOREIGN KEY） |
| 主键作用 | 唯一标识一行记录，非空且唯一 |
| 外键作用 | 保证引用完整性，子表数据必须能在父表中找到对应记录 |
| 适用数据库 | MySQL、PostgreSQL、SQLite、SQL Server、Oracle 等主流关系型数据库 |
| 常见配套约束 | NOT NULL、UNIQUE、CHECK、DEFAULT |
| 级联策略 | CASCADE、SET NULL、SET DEFAULT、RESTRICT / NO ACTION |
| 批量任务影响 | 约束会在写入时逐条校验，批量导入失败时可整体回滚 |
| 性能影响 | 主键自带唯一索引；外键列建议建索引，写入有额外校验开销 |
| 适合读者 | 刚学 SQL 的学生、做数据建模的开发、维护老系统的运维 |
先记住一个判断标准：主键解决“这一行是谁”的问题，外键解决“这一行能不能引用别人”的问题。
2. 主键约束：一张表的数据身份证
主键的全称是 PRIMARY KEY，它由一列或多列组成，用来唯一标识表中的每一行数据。主键要满足两个硬性条件：
- 非空：主键列不能为 NULL，因为 NULL 无法参与唯一性判断；
- 唯一：表中任意两行的主键值不能相同。
这里最容易混淆的是主键和 UNIQUE 约束的区别。UNIQUE 也能保证列值不重复，但它允许 NULL，而且一张表可以有多个 UNIQUE 约束。主键是“唯一 + 非空”的组合，并且一张表只能有一个主键。从逻辑上说，主键就是这张表的“身份证号”，UNIQUE 更像是“身份证号之外的备用唯一标识”，比如手机号、邮箱。
2.1 单列主键
单列主键是最常见的形式，在 CREATE TABLE 时直接跟在列定义后面：
CREATE TABLE student ( student_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, class_name VARCHAR(50) );这里 student_id 就是主键。向 student 表插入数据时，如果插入两条相同 student_id 的记录，数据库会直接拒绝第二条：
INSERT INTO student (student_id, name, class_name) VALUES (1, '张三', '软件1班'); INSERT INTO student (student_id, name, class_name) VALUES (1, '李四', '软件1班'); -- 第二条报错：Duplicate entry '1' for key 'student.PRIMARY'同样，如果尝试插入 NULL 作为 student_id，也会被拒绝。这两个行为就是主键约束在“物理层面”保护数据的方式。
2.2 复合主键
当单列无法唯一标识一行时，可以用多列组合作为主键，这叫复合主键或联合主键。典型场景是选课表：一个学生可以选多门课，一门课可以被多个学生选，但同一个学生选同一门课只能出现一次。
CREATE TABLE course_selection ( student_id INT, course_id INT, semester VARCHAR(20), score DECIMAL(5,2), PRIMARY KEY (student_id, course_id) );这个主键由 student_id 和 course_id 两列联合组成。允许出现 student_id=1, course_id=101，也允许出现 student_id=2, course_id=101，但不允许再次出现 student_id=1, course_id=101。复合主键的列都不能为 NULL。
注意：复合主键的列顺序会影响索引结构。查询时如果只带 course_id 条件，不一定能高效利用主键索引，需要再单独评估。
2.3 用 ALTER TABLE 添加或删除主键
如果表已经建好，可以用 ALTER TABLE 补主键：
ALTER TABLE student ADD PRIMARY KEY (student_id);删除主键：
ALTER TABLE student DROP PRIMARY KEY;需要注意，ALTER TABLE ... ADD PRIMARY KEY在执行前，数据库会检查现有数据是否满足“非空且唯一”。如果表里已经有重复数据，或者存在 NULL，这条语句会失败。所以给老表加主键前，一定要先做数据清洗。
3. 外键约束：表与表之间的引用完整性
主键管的是“表内”的数据唯一性，外键管的是“表间”的数据一致性。
外键（FOREIGN KEY）定义在子表上，它引用的目标是父表的某个列，这个列通常就是父表的主键或唯一键。外键约束强制要求：子表中写入的外键值，必须在父表对应列中存在。
用一个经典场景来说明。课程表 course 是父表，选课表 course_selection 是子表：
CREATE TABLE course ( course_id INT PRIMARY KEY, course_name VARCHAR(100) NOT NULL ); CREATE TABLE course_selection ( student_id INT, course_id INT, score DECIMAL(5,2), PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES student(student_id), FOREIGN KEY (course_id) REFERENCES course(course_id) );在这个结构里，course_selection 的 course_id 被外键约束限制为：只能写入 course 表中已存在的 course_id。
如果我尝试插入一条 course_id=999 的选课记录，而 course 表里没有 999 这门课：
INSERT INTO course_selection (student_id, course_id) VALUES (1, 999);数据库会报外键约束错误。这条错误就是在阻止“引用不存在的数据”，避免产生孤儿记录。
3.1 外键对父表的反向影响
外键不只限制子表写入，也会限制父表的删除和更新。
- 如果删除父表中仍被子表引用的记录，数据库默认会拒绝；
- 如果修改父表的主键值，数据库默认也会拒绝，除非定义了级联规则。
这个反向限制经常让初学者困惑：“我明明操作的是 course 表，为什么报错提示被 course_selection 引用？”原因就是外键维护的是表间的引用完整性，父表的改动会波及子表。
3.2 外键列和被引用列必须类型匹配
这是一个高频踩坑点。外键列的数据类型必须与被引用列兼容，INT 对 INT，VARCHAR(50) 对 VARCHAR(50)。如果 student 表的 student_id 是 INT，而 course_selection 表的 student_id 写成 BIGINT 或 VARCHAR，建表时可能不会立即报错，但实际使用中会出现类型转换问题，严重时直接导致索引失效。
更稳妥的做法是：同一条关系链上的主键和外键，类型、长度、字符集都保持一致。
4. 外键级联操作：ON DELETE / ON UPDATE
外键约束允许在 REFERENCES 子句后面声明级联行为，用来定义父表数据被删除或更新时，子表应该怎么响应。主流数据库支持的策略有四种。
| 策略 | 行为 | 适用场景 |
|---|---|---|
| CASCADE | 父表删除或更新时，子表对应记录同步删除或更新 | 订单明细随主订单一起清理 |
| SET NULL | 父表删除或更新时，子表外键列置为 NULL | 员工离职后，历史记录保留但不再关联 |
| SET DEFAULT | 父表删除或更新时，子表外键列设为默认值 | 少数数据库支持，如 MySQL InnoDB 部分场景 |
| RESTRICT / NO ACTION | 如果子表还有引用，父表不允许删除或更新 | 默认行为，适合需要避免误删的业务 |
4.1 级联删除示例
把学生-选课关系做成级联删除：删除学生时，该学生的选课记录一起删除。
CREATE TABLE course_selection ( student_id INT, course_id INT, score DECIMAL(5,2), PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE RESTRICT ON UPDATE CASCADE );这里用了两种策略：
- student_id 的外键使用 CASCADE，表示删学生时，选课记录跟着删；
- course_id 的外键使用 RESTRICT，表示课程如果已经被选，就不允许直接删除课程。
4.2 什么时候用 SET NULL
SET NULL 适合“保留历史记录但解除关联”的场景。比如员工表 employee 和操作日志表 operation_log，员工离职后不希望删除日志，只希望日志不再关联到这个员工：
CREATE TABLE operation_log ( log_id INT PRIMARY KEY, operator_id INT, action VARCHAR(100), FOREIGN KEY (operator_id) REFERENCES employee(employee_id) ON DELETE SET NULL );使用 SET NULL 的前提是外键列允许 NULL。如果外键列本身带 NOT NULL 约束，这个策略会直接冲突。
4.3 级联策略并不是越多越好
级联删除很省事，但危险也在这。一条 DELETE 语句可能通过外键链带出一连串删除操作，影响范围远超预期。生产环境中，涉及核心业务表的级联删除，建议先做影响分析，确认子表关联关系后再决定是否使用 CASCADE。
5. 实操环境准备与建表测试
讲完概念，接下来用一条完整链路验证约束效果。这里选用 SQLite 作为演示环境，因为它零配置、单文件、支持标准 SQL，适合快速验证。MySQL 和 PostgreSQL 的语法差异主要体现在细节上，本节会额外标注。
5.1 环境准备清单
| 检查项 | 建议 |
|---|---|
| 操作系统 | Windows / Linux / macOS 均可 |
| 数据库 | SQLite 3.x，或 MySQL 8.x / PostgreSQL 15+ |
| 客户端 | 命令行 sqlite3，或 DBeaver / Navicat |
| Python | 3.8+，用于批量插入测试脚本 |
SQLite 默认情况下外键约束是关闭的，每次连接需要手动开启：
PRAGMA foreign_keys = ON;MySQL 的 InnoDB 引擎默认开启外键约束检查，但要求表引擎统一为 InnoDB。PostgreSQL 默认支持外键约束，无需额外开关。
5.2 建表脚本
-- 学生表：主键约束 CREATE TABLE student ( student_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, class_name VARCHAR(50) ); -- 课程表：主键约束 CREATE TABLE course ( course_id INT PRIMARY KEY, course_name VARCHAR(100) NOT NULL ); -- 选课表：复合主键 + 外键约束 CREATE TABLE course_selection ( student_id INT, course_id INT, score DECIMAL(5,2), PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE, FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE RESTRICT );在 MySQL 中执行相同脚本时，如果要保持外键策略一致，需要确保表引擎是 InnoDB，并且字符集统一，推荐写成：
CREATE TABLE course_selection ( ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;注意 SQLite 在较老版本中对外键的ON DELETE RESTRICT支持与 MySQL 表现略有差异，实际效果要以目标数据库版本为准。
5.3 验证主键约束
插入正常数据：
INSERT INTO student (student_id, name, class_name) VALUES (1, '张三', '软件1班'); INSERT INTO student (student_id, name, class_name) VALUES (2, '李四', '软件2班'); INSERT INTO course (course_id, course_name) VALUES (101, '数据库原理'); INSERT INTO course (course_id, course_name) VALUES (102, '操作系统');尝试插入重复主键：
INSERT INTO student (student_id, name, class_name) VALUES (1, '王五', '软件3班'); -- 预期结果：主键冲突，插入失败尝试插入 NULL 主键：
INSERT INTO student (student_id, name, class_name) VALUES (NULL, '赵六', '软件4班'); -- 预期结果：NOT NULL 约束失败5.4 验证外键约束
合法插入选课记录：
INSERT INTO course_selection (student_id, course_id) VALUES (1, 101); -- 预期结果：成功，因为 student_id=1 存在，course_id=101 存在非法插入：
INSERT INTO course_selection (student_id, course_id) VALUES (99, 101); -- 预期结果：失败，student 表中不存在 id=99删除被引用的父表记录：
DELETE FROM course WHERE course_id = 101; -- 预期结果：失败，course_selection 表仍引用 course_id=101，且该外键使用 RESTRICT删除学生并级联清理：
DELETE FROM student WHERE student_id = 2; -- 预期结果：成功，同时 course_selection 中 student_id=2 的记录被级联删除这些步骤执行完，就能直观看到约束的拦截逻辑。判断标准很简单：该拦的拦住了，该放的通过了。
6. 约束在批量任务中的验证
实际项目中很少逐条 INSERT，更多是批量导入或程序批量写入。约束在这种情况下依然生效，而且它的价值会被放大：批量任务中只要有一条数据违反约束，整批写入可以根据事务策略回滚，避免脏数据混入表中。
6.1 批量导入前的检查项
批量导入前，建议先完成三个检查：
- 源数据中主键是否唯一，是否存在重复；
- 外键对应的父表记录是否齐全；
- 文本字段长度是否超过目标列定义。
这三个检查如果依赖数据库约束去拦截，也不是不行，但效率很低。更合理的做法是在导入脚本里先做一次数据校验，再用数据库约束作为最后一道防线。
6.2 Python 批量插入示例
下面用 Python + SQLite 模拟一个批量插入场景。脚本先开启外键约束，然后用事务批量写入，遇到异常时整体回滚。
import sqlite3 conn = sqlite3.connect("school.db") cursor = conn.cursor() # 开启外键约束 cursor.execute("PRAGMA foreign_keys = ON;") # 建表 cursor.execute(""" CREATE TABLE IF NOT EXISTS student ( student_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, class_name VARCHAR(50) ); """) # 准备批量数据 students = [ (1, "张三", "软件1班"), (2, "李四", "软件1班"), (3, "王五", "软件2班"), (3, "赵六", "软件2班"), # 模拟重复主键 ] try: cursor.executemany( "INSERT INTO student(student_id, name, class_name) VALUES (?, ?, ?)", students, ) conn.commit() print("批量插入成功") except Exception as e: conn.rollback() print("批量插入失败，已回滚，错误信息：", e)这段脚本执行后，会因为第四条数据的 student_id=3 与第三条重复，导致整批插入回滚。这就是事务 + 约束的组合效果：要么全部成功，要么全部失败。
6.3 批量任务失败重试建议
批量导入失败时，不要盲目重跑。建议先按以下步骤处理：
- 从错误信息中提取违规数据；
- 单独查询父表数据，确认外键引用是否缺失；
- 备份原表后清理重复主键或空值；
- 重新执行导入，并观察日志中的失败条数。
如果批量任务数据量很大，可以按批次提交并在每个批次外层加事务，避免单条坏数据导致整个文件无法导入。常见的做法是把导入文件拆分为每个 1000~5000 条的小批次，配合日志输出，哪一批出错就定位哪一批。
7. 约束与性能观察
主键和外键约束不只是一个逻辑概念，它们对数据库性能有实实在在的影响。这一节不讨论具体压测数据，只给出通用的观察维度和判断思路。
7.1 主键自动创建索引
主键约束在多数数据库中会自动创建一个唯一索引。这个索引一方面保证唯一性，另一方面加速基于主键的查询。所以主键并不是“白占空间”，它同时承担着索引职责。
如果一张表经常使用复合主键查询，比如WHERE student_id = 1 AND course_id = 101，复合主键索引效率会很高。但如果查询条件是WHERE course_id = 101，索引不一定能用上，这时需要根据实际查询模式增加单独索引。
7.2 外键列的索引问题
外键约束的行为在不同数据库中不一样：
- MySQL InnoDB 会自动为外键列创建索引，如果该列原本没有索引；
- PostgreSQL 不会自动为外键列创建索引，需要手动
CREATE INDEX； - SQLite 同样不会自动为外键列建索引。
外键列没有索引时，父表删除或更新记录时，数据库需要扫描整个子表来判断是否有引用，数据量大时删除操作会明显变慢。所以建外键时，最好同时确认外键列上的索引是否存在。PostgreSQL 中手动建索引的语句是：
CREATE INDEX idx_selection_student ON course_selection (student_id); CREATE INDEX idx_selection_course ON course_selection (course_id);7.3 写入性能的额外开销
外键约束会让每次 INSERT、UPDATE、DELETE 都多一次或多次引用查询。子表写入时要查父表，父表删除时要查子表。这个开销在数据量小的时候可以忽略，但在高并发写入场景下会放大。
如果业务对写入性能极其敏感，而且应用层已经做了完整的引用校验，有些团队会选择去除外键约束，只保留主键，把引用完整性交给应用层去保证。但这是一种权衡，不是推荐做法。我的建议是：默认保留外键，除非你能明确说出外键带来的性能瓶颈具体在哪个环节。
7.4 如何观察性能
在测试环境可以用以下方式观察：
EXPLAIN SELECT * FROM course_selection WHERE student_id = 1;查看执行计划中是否使用索引。也可以在批量导入时对比开启外键约束和关闭外键约束的耗时差异，但要注意关闭外键约束属于危险操作，做完后必须重新校验数据完整性。
8. 常见问题与排查方法
| 问题现象 | 可能原因 | 排查方式 | 解决方案 |
|---|---|---|---|
| 插入数据报主键重复 | 表内已存在相同主键值 | 查询主键列现有最大值或重复值 | 检查导入数据去重逻辑，或改用自增主键 |
| 插入数据报主键不能为 NULL | 主键列插入 NULL | 检查 INSERT 语句是否漏掉主键列 | 应用层在写入前补全主键值 |
| 删除父表记录被拒绝 | 子表有外键引用，且策略为 RESTRICT | 查询子表是否存在外键值 | 先删除子表记录，或改用级联删除 |
| 外键关联失败，提示找不到记录 | 子表写入的外键值在父表不存在 | 用 SELECT 查询父表数据 | 先导入父表数据，再导入子表数据 |
| 批量导入大量失败 | 源数据主键重复或外键缺失 | 按批次导入并记录日志 | 拆分批次，清洗源数据后重试 |
| 建外键时报类型不匹配 | 外键列和被引用列类型不一致 | 使用 SHOW CREATE TABLE 查看两端列定义 | 统一数据类型和长度 |
| 查询外键列很慢 | 外键列没有建立索引 | EXPLAIN 查看执行计划 | 手动创建索引 |
| MySQL 外键不生效 | 表引擎不是 InnoDB | 查看表引擎 | 改为 InnoDB 并重建外键 |
| SQLite 外键不生效 | 连接时未执行 PRAGMA foreign_keys = ON | 检查连接初始化代码 | 每次连接执行 PRAGMA |
| 复合主键查询慢 | 查询条件未包含复合主键最左列 | 查看执行计划 | 补充对应索引或调整查询条件 |
排查约束问题时，先分清报错来自哪个层面：是主键唯一性冲突，还是外键引用缺失，还是类型转换问题。数据库报错信息通常已经很明确，关键是不要只看错误码，要有意识地去看错误信息里提到的表名和索引名。
9. 最佳实践与使用建议
9.1 主键设计建议
- 优先使用自增整数或 UUID 作为代理主键，避免使用业务字段作主键；
- 业务字段的唯一性可以用 UNIQUE 约束单独保证；
- 复合主键能用但慎用，列顺序要和查询模式匹配；
- 给老表加主键前，先做重复值和 NULL 检查。
一个常见的做法是给学生表增加自增主键：
CREATE TABLE student ( student_id INT AUTO_INCREMENT PRIMARY KEY, student_no VARCHAR(20) UNIQUE NOT NULL, name VARCHAR(50) NOT NULL );这里 student_id 是代理主键，student_no 是业务唯一编号，用 UNIQUE 约束保证不重复。
9.2 外键设计建议
- 外键列类型必须与被引用列完全一致；
- 删除策略和更新策略要按业务语义选择，不要所有表都用 CASCADE；
- 核心业务表的外键建议保留，保证数据完整性；
- 外键列记得建索引，尤其是 PostgreSQL 和 SQLite 环境；
- 多级外键链操作前，先梳理影响范围。
9.3 数据导入和工程化规范
- 批量导入时保持事务，失败可回滚；
- 批量任务加日志，记录成功条数和失败原因；
- 导入前先跑一次数据质量检查脚本；
- 生产环境执行 DDL 前，先备份原表；
- 涉及人脸、声音、版权素材等业务数据时，确保数据来源合法并获得授权，数据库字段设计也要考虑数据合规和隐私要求，比如敏感字段单独加密存储。
9.4 约束并不是越多越好
约束是保证数据质量的手段，但过度设计会拖慢写入性能，也会让业务流程变得僵硬。实际项目中，主键必须有，外键看业务关系强度，CHECK 约束用于能明确枚举的规则。每次加约束时都可以问一句：这个规则是否属于数据本身的固有逻辑？如果是，加约束是对的；如果只是某个业务流程的临时规则，加在应用层更合适。
10. 总结与下一步
主键和外键是数据库管理系统中最基础也最实用的两个约束。
主键的价值在表内部：保证每行数据可被唯一识别。外键的价值在表之间：保证引用关系不会被破坏。两者配合，才能让关系型数据库真正体现出“关系”二字的意义。
值得先做的三件事：
第一，用本文的 SQL 脚本在 SQLite 或 MySQL 里建一套学生-课程-选课表，把所有约束报错都触发一遍，感受数据库在哪个环节拦截；第二，写一个 Python 批量导入脚本，观察事务和约束的组合效果；第三，检查你正在维护的表，确认外键列是否都有索引，主键设计是否合理。
最容易踩的坑有三个：一是外键列和主键列类型不一致，二是删除父表数据时没考虑子表引用，三是 SQLite 忘记开启外键检查导致约束“看起来没生效”。
后续可以从两个方向继续扩展：一是深入学习索引原理，理解主键索引在 B+ 树中的组织方式；二是研究不同数据库对约束的实现差异，比如 MySQL 的 FOREIGN KEY 检查时机、PostgreSQL 的约束命名规范和延迟约束。把这两块吃透，建表设计就不会再靠感觉了。
