SQL 主键和外键这两样东西是数据库表设计的“地基”约束也是最容易在面试和实际开发中翻车的知识点。很多人建表时不加主键或者外键怎么都插不进去数据根本原因不是语法不会写而是没理解约束到底在管什么。这次我们就把主键和外键约束拆开讲透包括创建语法、复合主键、外键的级联策略、建表实战示例以及最常见的约束冲突报错排查思路。本文会按照“先看概念和规格再看创建语法然后跑一个完整的学生-课程-选课建表示例最后讲更新删除约束、性能影响和错误排查”的顺序展开。如果你是后端开发、数据分析师或者正在准备数据库面试这篇文章可以直接收藏。1. 核心能力速览先给一张速览表把主键约束、外键约束和常见约束类型一次看清楚。约束类型作用特点使用场景主键约束PRIMARY KEY唯一标识表中每一行数据非空且唯一一张表只有一个主键可以作用于单列也可以作用于多列组合每张业务表必须有如用户表的 user_id外键约束FOREIGN KEY建立表与表之间的关系维护引用完整性引用其他表的某列通常是主键外键列的值必须存在于被引用表中或者为 NULL订单表引用用户表、选课表引用课程表唯一约束UNIQUE保证一列或一组列的值不重复允许 NULL但 NULL 可以重复不同数据库实现有差异邮箱、手机号等业务唯一字段非空约束NOT NULL保证列的值不能为空用于必填字段用户名、订单金额检查约束CHECK对列的值做条件校验不同数据库支持程度差异较大年龄大于 0、状态值只能为 1 或 2默认约束DEFAULT为列设置默认值不填时自动使用默认值创建时间默认当前时间从这张表能看出来主键和外键并不是“写不写都行”的装饰品。主键管的是“这张表的每一行是否可以被唯一确定”外键管的是“表之间的关联引用是否合法”。两者配合才能让数据库在写入阶段拦住脏数据而不是靠应用程序去判断。真正理解约束需要先明确一个事实约束的本质是数据库层面的数据完整性校验规则。你可以在应用层写 if 判断但应用层判断只能拦自己程序的输入挡不住其他客户端、手工 SQL、脚本批量写入的异常数据。只有把约束建在表结构上数据库引擎才会在每次 INSERT、UPDATE 时自动做校检。下面进入具体语法和用法。2. 主键约束详解2.1 主键的定义与特性主键约束用于唯一标识表中每一行。一个规范的主键必须满足两个基本要求非空、唯一。非空意味着这一列不能写入 NULL唯一意味着表中不能出现两行相同的主键值。这里有一个重要细节主键和唯一索引不完全是一回事。唯一索引允许一个 NULL 值MySQL 中允许多个但语义上不推荐依赖主键完全不允许 NULL。而且主键通常会自动创建一个聚簇索引数据的物理存储顺序会跟随主键值组织这对查询性能影响很大。主键的经典使用场景包括用户表的 user_id。商品表的 product_id。订单表的 order_id。日志表的自增 id。在很多实际系统中主键都被设计成整数自增 ID。但是要注意自增整数主键不一定适合所有场景比如分布式系统里自增主键会带来 ID 生成瓶颈这时候就需要雪花 ID、UUID 或者分布式 ID 方案。2.2 创建主键的三种方式第一种是建表时在列级定义主键语法最简洁CREATE TABLE student ( student_id INT PRIMARY KEY, student_name VARCHAR(50), gender CHAR(1), class_id INT );第二种是建表时在表级定义主键适合需要指定约束名称的场景CREATE TABLE student ( student_id INT, student_name VARCHAR(50), gender CHAR(1), class_id INT, CONSTRAINT pk_student PRIMARY KEY (student_id) );第三种是建表后通过 ALTER TABLE 添加主键ALTER TABLE student ADD PRIMARY KEY (student_id);这三种方式在 MySQL、PostgreSQL、SQL Server 等主流数据库中都可以使用。区别在于约束名称第一种和第三种如果没有显式指定约束名数据库会自动生成一个后续要删除主键时可能需要先查出约束名。2.3 复合主键复合主键是指用两列或多列组合成主键只要组合值不重复即可。最常见的例子是选课表。选课表中一个学生可以选多门课一门课可以被多个学生选单独一列 student_id 无法唯一标识一行单独一列 course_id 也无法唯一标识一行但 student_id course_id 的组合可以唯一确定“某个学生选某门课”这条记录。CREATE TABLE student_course ( student_id INT, course_id INT, score DECIMAL(5,2), PRIMARY KEY (student_id, course_id) );这里要注意复合主键要求的是“组合值唯一”而不是“每一列单独唯一”。同一行里 student_id 重复是允许的cource_id 重复也是允许的只要 (student_id, course_id) 这个组合不重复就行。复合主键实际使用中需要权衡组合列越多唯一性判断越复杂索引占用空间也就越大。如果业务允许更多时候会添加一个无业务含义的自增 id 作为主键再对业务列组合加唯一约束。这两种方案没有绝对优劣取决于查询模式和数据规模。2.4 删除主键约束删除主键的 SQL 在不同数据库里有细微区别。MySQL 的写法是ALTER TABLE student DROP PRIMARY KEY;PostgreSQL 的写法需要显式指定约束名ALTER TABLE student DROP CONSTRAINT pk_student;SQL Server 同样用 DROP CONSTRAINTALTER TABLE student DROP CONSTRAINT pk_student;所以建议在创建主键时显式指定约束名否则后期删约束要先去系统表查名字比较麻烦。3. 外键约束详解3.1 什么是外键外键约束用于建立两张表之间的引用关系。简单说外键列中的值必须存在于被引用表的主键列或唯一键列中或者为 NULL。以一个经典例子说明课程表 course 和老师表 teacher 之间是关联关系每门课程有一个 teacher_id这个 teacher_id 引用 teacher 表的老师主键。如果某门课程的 teacher_id 写着 99但 teacher 表中根本没有 id 为 99 的老师这条课程数据就是无效的。外键约束就是用来拦截这种无效引用的。CREATE TABLE teacher ( teacher_id INT PRIMARY KEY, teacher_name VARCHAR(50) ); CREATE TABLE course ( course_id INT PRIMARY KEY, course_name VARCHAR(100), teacher_id INT, CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) );很多初学者搞不明白“外键列”和“被引用列”的关系。为了说清楚这里拆成三个关键点第一外键约束是定义在“子表”上的字段写在子表中REFERENCES 指向“父表”的列。课程表是子表老师表是父表课程表里的 teacher_id 字段引用老师表里的 teacher_id 字段。第二外键列的值在插入时必须能在父表被引用列中找到。比如课程表插入 teacher_id 10 的课程teacher 表中就必须存在 teacher_id 10 的老师。如果不存在插入会失败并报外键约束错误。第三外键列允许为 NULLNULL 表示“尚未分配”或“无引用”不会被外键检查拦截。这一点在业务设计中常常被利用比如未分配老师的课程可以先把 teacher_id 留空等后续再分配。3.2 外键约束的引用策略外键约束不只是插入时的校验工具它还决定了父表数据被删除或更新时子表的数据应该怎么处理。这是很多 SQL 教程容易忽略的重点。常见的外键引用策略有四种策略行为使用场景RESTRICT如果子表存在引用数据父表的删除或更新会被拒绝防止误删有关联的数据NO ACTION与 RESTRICT 基本一致但检查时机略有差异部分数据库实现相同同样用于拒绝操作CASCADE父表删除时自动删除子表对应数据父表更新主键时自动更新子表外键父子生命周期一致如删除订单后同步删除订单明细SET NULL父表删除或更新时子表外键自动置为 NULL保留历史记录的操作日志、不要求强关联的场景下面是一个 CASCADE 级联删除的建表示例CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE ); CREATE TABLE order_items ( order_item_id INT PRIMARY KEY, order_id INT, product_name VARCHAR(100), quantity INT, CONSTRAINT fk_order_items_orders FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE );这段 SQL 的含义是删除订单时关联的订单明细记录也会自动删除避免残留孤儿数据。这在订单系统里非常实用。如果希望删除老师后课程表中的 teacher_id 自动变为 NULL可以使用 ON DELETE SET NULLCREATE TABLE course ( course_id INT PRIMARY KEY, course_name VARCHAR(100), teacher_id INT, CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE SET NULL );选择哪种策略需要谨慎。CASCADE 确实方便但也有风险父表一次 DELETE 可能级联删除成百上千条子表数据如果业务没有预期这个行为可能造成不可恢复的数据丢失。稳妥的做法是对核心业务表先使用 RESTRICT 防止误删确认级联范围合理后再调整策略。3.3 外键约束的创建方式外键同样可以在建表时和建表后添加。上面已经展示了建表时添加外键的方式。建表后添加外键的语法如下ALTER TABLE course ADD CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id);要删除外键约束MySQL 的写法是ALTER TABLE course DROP FOREIGN KEY fk_course_teacher;PostgreSQL 和 SQL Server 的写法是ALTER TABLE course DROP CONSTRAINT fk_course_teacher;4. 建表实战学生-课程-选课完整示例从概念到实际应用之间还差一个完整建模过程。下面用一个经典的教学案例把主键、复合主键、外键串起来。业务需求如下学生表 student记录学生信息。课程表 course记录课程和授课老师。老师表 teacher记录老师信息。选课表 student_course记录学生选了哪门课程、考了多少分。4.1 创建老师表CREATE TABLE teacher ( teacher_id INT PRIMARY KEY, teacher_name VARCHAR(50) NOT NULL, title VARCHAR(20) );这里 teacher_id 作为主键。主键列不需要再额外加 NOT NULL因为主键本身已经隐式包含非空约束。4.2 创建课程表CREATE TABLE course ( course_id INT PRIMARY KEY, course_name VARCHAR(100) NOT NULL, teacher_id INT, CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE SET NULL );课程表通过 teacher_id 引用老师表删除老师后课程保留但 teacher_id 置空。这是考虑到课程数据有历史价值不应该因为老师离职就一起删掉。4.3 创建学生表CREATE TABLE student ( student_id INT PRIMARY KEY, student_name VARCHAR(50) NOT NULL, gender CHAR(1) DEFAULT 1, enroll_date DATE );4.4 创建选课表CREATE TABLE student_course ( student_id INT, course_id INT, score DECIMAL(5,2), PRIMARY KEY (student_id, course_id), CONSTRAINT fk_sc_student FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE, CONSTRAINT fk_sc_course FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE CASCADE );选课表是典型的中间关系表。主键用 student_id 和 course_id 组成复合主键保证同一个学生不能重复选同一门课。两个外键分别指向学生表和课程表学生退学或课程下线时选课记录应该同步清理所以使用 ON DELETE CASCADE。4.5 插入数据验证约束建表后先插入老师和学生数据INSERT INTO teacher (teacher_id, teacher_name) VALUES (1, 张老师); INSERT INTO teacher (teacher_id, teacher_name) VALUES (2, 李老师); INSERT INTO student (student_id, student_name) VALUES (101, 小明); INSERT INTO student (student_id, student_name) VALUES (102, 小红);正常插入课程数据INSERT INTO course (course_id, course_name, teacher_id) VALUES (1001, 数据库原理, 1);这里 teacher_id1 在 teacher 表中存在所以插入成功。接下来试一条外键非法插入INSERT INTO course (course_id, course_name, teacher_id) VALUES (1002, 编译原理, 99);teacher 表中没有 teacher_id99 的记录数据库会报外键约束错误这条插入不会生效。这是约束起作用的最直接演示。再插入选课记录INSERT INTO student_course (student_id, course_id, score) VALUES (101, 1001, 92.5); INSERT INTO student_course (student_id, course_id, score) VALUES (101, 1001, 88.0);第二条插入会失败因为 (101, 1001) 这个主键组合已经存在。复合主键在这里拦住了重复选课。4.6 级联删除效果验证删除学生 101DELETE FROM student WHERE student_id 101;由于选课表的外键使用了 ON DELETE CASCADEstudent_course 表中所有 student_id101 的记录都会自动被删除不会残留“学生在校但选课记录丢失人”的脏数据。如果当初在设计时把外键策略设为 RESTRICT这条删除会被直接拒绝提示存在关联记录。两种策略没有谁更好关键看业务预期。5. 修改和删除约束的 SQL 操作在实际项目里表结构不会是永远不变的。业务调整时可能需要删除旧的外键、添加新约束。这里把常用的 ALTER TABLE 约束操作整理出来。添加主键ALTER TABLE student ADD PRIMARY KEY (student_id);删除主键ALTER TABLE student DROP PRIMARY KEY;添加外键ALTER TABLE course ADD CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id);删除外键ALTER TABLE course DROP FOREIGN KEY fk_course_teacher;添加唯一约束ALTER TABLE student ADD CONSTRAINT uk_student_email UNIQUE (email);添加检查约束MySQL 8.0.16 之后的版本支持ALTER TABLE student ADD CONSTRAINT chk_student_gender CHECK (gender IN (0, 1));修改约束前最需要确认的一件事是目标表里是否已经存在违反约束的数据。比如给一列加上唯一约束而表中已经有重复值ALTER TABLE 命令会直接报错。正确的操作顺序是先查出重复数据并清洗再添加约束。SELECT student_id, email, COUNT(*) FROM student GROUP BY email HAVING COUNT(*) 1;这条查询能快速定位重复的邮箱清洗后再执行唯一约束的添加。6. 约束对索引和查询性能的影响主键和外键约束不只是数据完整性的保障它们对查询性能也有直接影响。先说主键。大多数数据库中主键会自动创建索引MySQL InnoDB 引擎中主键索引就是聚簇索引数据行按照主键值的顺序物理存储。这意味着按主键查询时数据库可以直接定位到数据页查询速度非常快。反过来如果表没有主键InnoDB 会选择一个唯一索引代替如果没有唯一索引则生成隐藏主键。隐藏主键对开发不可见还会额外占用存储空间所以业务表都应该显式设计主键。再说外键。外键字段上的索引容易被忽略。当外键约束创建后MySQL 会自动为外键列创建索引但其他数据库不一定都会自动创建。如果外键列经常作为查询条件或 JOIN 的连接字段没有索引会导致全表扫描数据量大时性能明显下降。一个常见的性能优化建议是在被频繁引用和 JOIN 的列上主动创建索引。比如选课表的 student_id 和 course_id 虽然是复合主键的前缀列已经可以走索引但在更复杂的场景中要检查 EXPLAIN 的执行计划。EXPLAIN SELECT * FROM student_course sc JOIN student s ON sc.student_id s.student_id WHERE sc.course_id 1001;如果执行计划中出现全表扫描就需要考虑为 course_id 单独建索引因为复合主键 (student_id, course_id) 中 course_id 不是最左前缀无法直接利用该索引加速单独按 course_id 的查询。7. 常见问题与排查方法约束相关的报错是数据库使用中最常见的一类问题。下面给出一张排查表覆盖主键外键的典型报错场景。问题现象可能原因排查方式解决方案主键重复插入失败表中已存在相同主键值查询表中已有数据改用自增主键或更换主键值主键为 NULL插入失败插入语句没有给主键列赋值检查 INSERT 语句给主键列赋值或使用自增主键外键插入时报引用错误外键值在被引用表中不存在查询父表是否有对应主键值先插入父表数据再插入子表数据删除父表数据被拒绝子表存在引用数据外键策略为 RESTRICT查询子表关联记录先删除子表数据或改用 CASCADE级联删除后数据意外消失外键策略设置成了 CASCADE查看建表语句中的 ON DELETE 策略修复策略或临时关闭外键检查后恢复数据ALTER 添加主键失败目标列存在重复值或 NULL用 GROUP BY 查重复值清洗数据后重新添加约束添加唯一约束失败目标列存在重复值按列分组统计去重后再添加约束外键列没有索引导致 JOIN 慢外键列缺少索引使用 EXPLAIN 查看执行计划为外键列创建索引删除约束名找不到建表时未指定约束名称查看表中约束信息查询系统表获取约束名后删除有一个常见操作可以留作备用在数据导入阶段如果表中已有数据不完整需要先导数据后建约束可以使用数据库提供的延时约束或临时关闭外键检查功能。MySQL 中可以执行SET FOREIGN_KEY_CHECKS 0;批量导入完成后再开启SET FOREIGN_KEY_CHECKS 1;要注意这只是一个临时措施。生产环境的数据导入应该走完整的校验流程关闭外键检查虽然提供了便利但也意味着数据完整性的校检被延后到导入结束之后需要自己补充验证步骤否则脏数据会悄悄进入表里。8. 最佳实践与使用建议结合项目中的实际经验给出下面几条针对主键和外键约束的使用建议。第一每张表都要有主键。即使是日志表、临时表、中间表也应该有一个能唯一标识行的列或组合列。没有主键的表在数据复制、更新、删除、去重场景中非常痛苦而且会让数据库的存储和查询烦琐很多。第二主键设计优先考虑稳定且无业务含义的列。自增整数和 UUID 都是常见方案。自增整数占用空间小、索引效率高适合单库单应用场景UUID 适合分布式环境但索引性能略差。不要在业务主键上叠加太多语义比如不要用身份证号、手机号做物理主键这类信息一旦业务规则变化改主键的成本极高。第三外键约束要按业务选择策略。强关联数据用 CASCADE 可以省掉大量手动处理弱关联或历史数据保留场景用 SET NULL默认情况建议用 RESTRICT 或 NO ACTION 保守处理防止误删。第四不要过度使用复合主键。复合主键能解决唯一性问题但会让外键引用变得复杂。如果业务表列数很多可以考虑添加一个单列自增主键再对业务列组合添加唯一约束。这样既保证了唯一性也简化了其他表对这张表的引用。第五约束设计要在建表阶段完成不要等数据量大了再补。数据量小的时候补一个主键或外键可能只要几秒钟。数据量到了千万级别添加索引或约束可能锁表很久直接影响业务。第六涉及测试环境或人工操作时要养成先备份再改表结构的习惯。删除主键、修改外键策略、批量清理数据这些操作都有一定的风险。测试库可以随意玩生产环境必须确认影响面。9. 总结与下一步主键约束和外键约束是数据库表设计的核心内容也是检验一个人 SQL 基本功是否扎实的常见考点。读完这篇内容建议先做两件事第一打开你常用的数据库客户端建一张学生表和课程表按文中示例把主键、复合主键、外键、CASCADE 策略都跑一遍第二检查你当前负责的业务表看看哪些表缺主键哪些外键列没有索引哪些外键策略可能与业务预期不一致。最容易踩的坑是三个一是觉得主键可有可无先建表后续再说结果数据量上来后补主键变成大工程二是外键策略不假思索全部用 CASCADE误删数据时才发现级联范围远超预期三是只建外键不加索引主表 JOIN 子表时性能断崖式下降。接下来可以继续学习的方向包括SQL 中的唯一约束和检查约束、数据库事务与并发控制、以及基于外键关系设计复杂业务模型。主键和外键只是数据完整性的第一层保障学会结合索引和事务去思考数据才能把表结构设计得更加稳定。建议收藏备用建表时回来对照一遍。