很多同学学 SQL 时会把大部分精力花在 SELECT、JOIN、窗口函数这些“查询”语法上觉得约束不过是建表语句里几个可有可无的关键字。但真正到业务系统上线、数据量开始增大时问题就来了用户表里出现几千条重复邮箱订单表里挂着一堆不存在的用户 ID年龄字段被填成 -30备注字段明明是必填却存了一堆 NULL。如果你看过经典的数据库管理系统课程——比如 Neso Academy 的 DBMS 系列——会发现它专门用一整讲来拆解 SQL 中的约束原因很简单约束不是建表的附属品而是数据库抵御脏数据的第一道防线。这篇文章会从数据库管理系统的基础概念出发把 SQL 约束这一块彻底讲透。你不需要先建索引、不需要会存储过程只要跟着文章把建表语句跑通就能理解约束在数据完整性中的作用以及它在真实项目中应该怎么设计、怎么改、怎么排查问题。1. 这篇文章真正要解决的问题先把结论放在前面数据库管理系统中的 SQL 约束是用声明式规则保证数据完整性的机制它解决的是“数据该不该被写入”的问题。没有约束时你的系统通常是这样的应用层写了校验逻辑但总有接口漏写、绕过去或者代码改了逻辑但数据库旧数据还留着。两个人同时插入同一条订单记录不知道谁是对的。删除一个用户后他的历史订单变成了“无主数据”报表统计直接崩。数值字段存了负数、百分比字段存了 120、日期字段存了“2024-13-45”。这些不是 Bug而是数据约束缺失带来的系统性风险。约束的作用就是把这些规则下沉到数据库层无论哪个应用、哪个接口、哪个人用客户端连上来规则都生效。阅读建议分三类数据库初学者本文帮助你建立“约束分类 数据完整性”的完整框架。后端开发重点看第 4、5 章搞清楚建表后如何修改约束、如何设计外键。负责生产环境的工程师重点看第 7、8、9 章关于约束上线、报错、回滚的实操细节。2. SQL 约束的核心概念与分类2.1 什么是约束约束Constraint是关系型数据库在“定义表结构”阶段就附加在列上的规则。当一条 INSERT、UPDATE、DELETE 语句违反规则时数据库会直接拒绝执行并返回错误。通俗地理解表是装数据的容器约束是容器壁上的刻度线。超过刻度线的数据容器根本不让进。2.2 三种数据完整性与约束的对应关系数据库管理系统中的数据完整性通常分三层每一层对应不同的约束完整性类型含义对应约束实体完整性每一行记录必须能被唯一识别PRIMARY KEY、UNIQUE参照完整性表与表之间的关联关系必须成立FOREIGN KEY用户定义完整性列数据必须满足业务口径NOT NULL、CHECK、DEFAULT这套分类不是考试知识点而是工程判断的依据。比如你发现订单表里有“无主订单”问题出在参照完整性发现同一个人有十条重复账号问题出在实体完整性。2.3 六大常见 SQL 约束速览约束作用典型使用场景NOT NULL列不允许为 NULL用户昵称、订单编号UNIQUE列或列组合值不重复邮箱、手机号、身份证PRIMARY KEY唯一标识一行且不允许 NULL主键 IDFOREIGN KEY引用另一张表的合法记录订单表的用户 IDCHECK列值必须满足条件年龄 0-150、分数 0-100DEFAULT未显式赋值时使用默认值创建时间、状态字段这里有个容易混淆的点DEFAULT 严格说是“列的默认值定义”但它和约束一样参与写入规则所以大多数教材把它归入约束体系本文沿用这个分类。2.4 约束与索引的关系UNIQUE 和 PRIMARY KEY 在 MySQL 中会同时创建唯一索引FOREIGN KEY 通常要求被引用列有索引。也就是说一部分约束不仅有规则作用还有性能副作用。这也是为什么约束设计需要提前规划而不是上线后随机添加。3. 环境准备与实验表设计3.1 环境说明本文代码以 MySQL 8.0 为准因为 MySQL 8.0.16 之后才开始真正强制执行 CHECK 约束这个版本对学约束更友好。SQL Server、Oracle 对应的语法差异会在文中标注。数据库MySQL 8.0工具mysql 命令行或 Navicat、DBeaver不需要额外的编程环境第 6 章的 Python 验证脚本可选如果你用的是云数据库建库建表权限可能需要申请建议先在本地环境或测试库中操作。3.2 经典学生-课程-选课三张表为了讲清约束我们使用数据库教材里最经典的三张表学生表、课程表、选课表。CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4; USE school; -- 学生表 CREATE TABLE student ( student_id INT NOT NULL, -- 非空 email VARCHAR(100) NOT NULL, -- 非空 name VARCHAR(50) NOT NULL, -- 非空 gender CHAR(1) NULL, -- 允许为空 age INT -- 年龄后文用 CHECK 限制 ); -- 课程表 CREATE TABLE course ( course_id INT NOT NULL, course_name VARCHAR(100) NOT NULL, credit DECIMAL(3,1) NOT NULL ); -- 选课表 CREATE TABLE enrollment ( id INT AUTO_INCREMENT NOT NULL, student_id INT NOT NULL, course_id INT NOT NULL, enroll_date DATE NOT NULL, score DECIMAL(5,2) NULL );上面的建表语句“能用”但它只是把列定义出来没有加任何约束。这正是很多项目第一版表的真实状态——看着正常实际上没有任何防线。我们从第 4 章开始逐步给这三张表加上完整约束。4. 六大核心约束逐一拆解4.1 NOT NULL非空约束NOT NULL 是最容易理解的约束列值不能为 NULL。在业务里“未知”和“没有”不一样。用户注册时间如果为 NULL下游统计就不知道应该按什么时间点算活跃订单金额如果为 NULL报表 SUM 结果可能直接错。改造后的学生表CREATE TABLE student ( student_id INT NOT NULL, email VARCHAR(100) NOT NULL, name VARCHAR(50) NOT NULL, gender CHAR(1) NULL, age INT );在这里student_id、email、name 都禁止为 NULLgender 允许 NULL。允许 NULL 的业务含义是“用户可以不填性别”禁止 NULL 的业务含义是“用户必须有姓名”。很多新手会犯一个错误把空字符串和 NULL 混为一谈。是字符串类型的有效值NULL 是“没有值”。NOT NULL 防不住空字符串如果需要防空字符串同时还要配合 CHECK 约束或应用层校验。4.2 UNIQUE唯一约束UNIQUE 保证列或列的组合不重复。注意MySQL 的 UNIQUE 索引允许插入多个 NULL因为 NULL 被认为是“未知”不参与重复比较。这一点在面试里经常被问到。添加唯一约束后的建表语句CREATE TABLE student ( student_id INT NOT NULL, email VARCHAR(100) NOT NULL, name VARCHAR(50) NOT NULL, gender CHAR(1) NULL, age INT, UNIQUE KEY uq_student_email (email), UNIQUE KEY uq_student_name_gender (name, gender) );第二行uq_student_name_gender (name, gender)是复合唯一约束含义是“同一个人可以同名同一性别下也可以同名但同名 同性别不能重复”。实际项目中复合唯一经常用来防止并发重复插入比如“同一个用户在同一门课程里只能有一条选课记录”。4.3 PRIMARY KEY主键约束主键是实体完整性的核心它要求列值非空且唯一。一张表只能有一个主键但主键可以由多列组成也就是联合主键。改造学生表CREATE TABLE student ( student_id INT NOT NULL, email VARCHAR(100) NOT NULL, name VARCHAR(50) NOT NULL, gender CHAR(1) NULL, age INT, PRIMARY KEY (student_id), UNIQUE KEY uq_student_email (email) );选课表则适合用联合主键来防止重复选课CREATE TABLE enrollment ( student_id INT NOT NULL, course_id INT NOT NULL, enroll_date DATE NOT NULL, score DECIMAL(5,2) NULL, PRIMARY KEY (student_id, course_id) );这里PRIMARY KEY (student_id, course_id)表示同一个学生和同一门课的组合只能出现一次。这是多对多关联表的经典主键设计。4.4 FOREIGN KEY外键约束外键约束解决的是参照完整性问题。它的含义是外键列的值必须能在被引用表的主键或唯一键中找到。给选课表加上外键CREATE TABLE enrollment ( student_id INT NOT NULL, course_id INT NOT NULL, enroll_date DATE NOT NULL, score DECIMAL(5,2) NULL, PRIMARY KEY (student_id, course_id), CONSTRAINT fk_enrollment_student FOREIGN KEY (student_id) REFERENCES student(student_id), CONSTRAINT fk_enrollment_course FOREIGN KEY (course_id) REFERENCES course(course_id) );外键的效果是双向的插入选课记录时student_id 必须在 student 表中存在course_id 必须在 course 表中存在。删除 student 表中的学生时如果该学生在 enrollment 中有记录删除会被拒绝除非定义 ON DELETE 行为。ON DELETE 的常见写法FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE -- 删除学生时自动删除他的选课记录以及FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE SET NULL -- 删除学生时把选课记录里的 student_id 置为 NULL外键不是万能的。它保证的是引用存在不保证业务正确。比如选课表里的 course_id 引用了一个“已下架”的课程外键层面依然合法但业务上可能有问题。所以外键只解决“引用不存在”这一层问题。4.5 CHECK检查约束CHECK 约束允许你写任意布尔表达式数据库在写入时校验。MySQL 8.0.16 之前InnoDB 虽然支持解析 CHECK 但不会执行这也是很多老项目“明明写了 CHECK 却完全不生效”的根源。MySQL 8.0.16 的完整建表CREATE TABLE student ( student_id INT NOT NULL, email VARCHAR(100) NOT NULL, name VARCHAR(50) NOT NULL, gender CHAR(1) NULL, age INT, PRIMARY KEY (student_id), UNIQUE KEY uq_student_email (email), CONSTRAINT ck_student_age CHECK (age 0 AND age 150), CONSTRAINT ck_student_gender CHECK (gender IN (M, F)) );CHECK 的表达式可以很灵活常见场景包括数值范围score 0 AND score 100枚举值status IN (PENDING, PAID, CANCELLED)组合逻辑end_date IS NULL OR end_date start_dateSQL Server、Oracle 的 CHECK 语法与标准 SQL 一致可以放心使用。如果你的业务库还是 MySQL 5.7请务必意识到 CHECK 是不执行的需要靠应用层或触发器补上。4.6 DEFAULT默认值约束DEFAULT 在未显式赋值时写入默认值。最常见的场景是创建时间和状态字段CREATE TABLE enrollment ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, course_id INT NOT NULL, enroll_date DATE NOT NULL DEFAULT (CURRENT_DATE), score DECIMAL(5,2) NULL, CONSTRAINT fk_enrollment_student FOREIGN KEY (student_id) REFERENCES student(student_id), CONSTRAINT fk_enrollment_course FOREIGN KEY (course_id) REFERENCES course(course_id), CONSTRAINT ck_enrollment_score CHECK (score 0 AND score 100) );MySQL 8.0 支持DEFAULT (CURRENT_DATE)这种表达式写法SQL Server 通常写DEFAULT GETDATE()Oracle 支持DEFAULT SYSDATE。不同数据库在表达式支持上略有差异写之前建议查一下你所用数据库的官方文档。5. 约束的日常管理添加、删除与修改建表时没有规划好约束数据跑了一段时间后想补这在实际项目中很常见。第 5 章内容是 CRUD 工程师和高阶运维都必须掌握的 ALTER TABLE 语法。5.1 添加约束在已有学生表上补充唯一约束ALTER TABLE student ADD CONSTRAINT uq_student_phone UNIQUE (phone);补充 CHECK 约束ALTER TABLE student ADD CONSTRAINT ck_student_age CHECK (age 0 AND age 150);补充外键ALTER TABLE enrollment ADD CONSTRAINT fk_enrollment_course FOREIGN KEY (course_id) REFERENCES course(course_id);补充默认值MySQL 写法ALTER TABLE enrollment ALTER COLUMN enroll_date SET DEFAULT (CURRENT_DATE);添加约束前建议先查询现有数据是否违反新规则。比如给 email 加 UNIQUE 之前先执行SELECT email, COUNT(*) FROM student GROUP BY email HAVING COUNT(*) 1;如果查询结果不为空ALTER TABLE 会直接失败或者在你没查清楚时让历史数据变成“非法数据”。这也是“数据库运维先查数据后改结构”的经典教训。5.2 删除约束删除唯一约束MySQL 中要通过索引名删除ALTER TABLE student DROP INDEX uq_student_phone;删除外键约束ALTER TABLE enrollment DROP FOREIGN KEY fk_enrollment_course;删除 CHECK 约束ALTER TABLE student DROP CHECK ck_student_age;删除默认值MySQL 写法ALTER TABLE enrollment ALTER COLUMN enroll_date DROP DEFAULT;5.3 修改约束标准 SQL 中没有直接的“修改约束”语句通常流程是先删除旧约束再添加新约束。比如把 age 的取值范围从 150 改成 120ALTER TABLE student DROP CHECK ck_student_age; ALTER TABLE student ADD CONSTRAINT ck_student_age CHECK (age 0 AND age 120);这在生产环境属于结构变更建议先在测试环境演练并通过备份或事务方式保障可回滚。6. 用程序验证约束Python pymysqlSQL 层面的验证很直接但很多同学想知道应用代码往里写坏数据时约束到底怎么拦截下面用 Python 连接 MySQL插入重复主键和年龄为负数的数据观察约束报错。先安装依赖pip install pymysql验证脚本check_constraint.pyimport pymysql conn pymysql.connect( hostlocalhost, userroot, password你的密码, databaseschool, charsetutf8mb4, ) cursor conn.cursor() # 第 1 条正常数据 try: cursor.execute( INSERT INTO student (student_id, email, name, gender, age) VALUES (%s, %s, %s, %s, %s), (1, aliceexample.com, Alice, F, 20), ) conn.commit() print(第 1 条写入成功) except pymysql.IntegrityError as e: print(第 1 条被拦截, e) # 第 2 条重复主键 student_id 1 try: cursor.execute( INSERT INTO student (student_id, email, name, gender, age) VALUES (%s, %s, %s, %s, %s), (1, bobexample.com, Bob, M, 21), ) conn.commit() print(第 2 条写入成功) except pymysql.IntegrityError as e: print(第 2 条被拦截, e) # 第 3 条年龄为负数触发 CHECK 约束 try: cursor.execute( INSERT INTO student (student_id, email, name, gender, age) VALUES (%s, %s, %s, %s, %s), (2, carolexample.com, Carol, F, -10), ) conn.commit() print(第 3 条写入成功) except pymysql.IntegrityError as e: print(第 3 条被拦截, e) cursor.close() conn.close()运行python check_constraint.py正常情况下的输出大致是第 1 条写入成功 第 2 条被拦截 (1062, Duplicate entry 1 for key student.PRIMARY) 第 3 条被拦截 (3819, Check constraint ck_student_age is violated.)这里的 1062、3819 是 MySQL 的错误码。如果你用的是 SQL Server 或 Oracle错误码不同但拦截逻辑一致。应用层捕获到这类异常时应该把错误映射成用户可读的提示而不是直接堆栈。7. 约束与数据库安全别混淆“完整性”和“注入防护”在讨论约束时常有人把约束和 SQL 注入混在一起。这里必须把两者分清楚约束处理的是写入的数据是否符合逻辑。SQL 注入处理的是执行的 SQL 是否被恶意改写。约束再完善也替代不了参数化查询、权限最小化和输入校验。反过来参数化查询防得住注入也替代不了唯一约束和检查约束。二者是不同维度的安全防线没有谁可以替代谁。实际项目中比较稳妥的组合是应用层做输入校验给用户友好的提示。数据库层加约束形成最后防线。访问数据库使用最小权限账号避免应用账号拥有 DROP、ALTER 等不需要的权限。SQL 统一使用预编译参数避免拼接字符串。8. 常见问题与排查方法问题现象可能原因排查方式解决方案插入数据提示 Duplicate entry违反了 PRIMARY KEY 或 UNIQUE查看错误信息中给出的索引名查询该列是否已有重复值修正业务数据或确认唯一约束是否合理插入数据提示 Column xxx cannot be null违反 NOT NULL 约束检查插入语句是否漏传字段补全字段值或重新评估该列是否真的应该非空外键插入失败Cannot add or update a child row外键引用的值在父表中不存在先查询父表是否存在对应主键先插入父表记录或修正子表引用值MySQL 的 CHECK 不生效数据库版本低于 8.0.16SELECT VERSION();查看版本升级版本或改用应用层校验、触发器删除父表记录失败cannot delete or update a parent row存在外键引用且未设置 ON DELETE 行为查看子表中是否有引用该父行的记录调整业务删除顺序或显式设计 ON DELETE CASCADE / SET NULL创建外键失败被引用表不是 InnoDB、列类型不一致、被引用列没有索引查看表引擎、列类型和索引统一使用 InnoDB保证类型一致确保被引用列有索引给大表添加唯一约束时卡住表数据量过大在线 DDL 耗时较长观察执行计划查看是否锁表评估业务低峰期操作先清理重复数据再添加约束排查思路有一个通用顺序先看错误码再查数据后改结构。数据库报错信息本身就包含了绝大部分线索不要一上来就删表重建。9. 约束设计的最佳实践与工程建议9.1 约束命名规范约束名在报错信息中会出现命名直接决定排查效率。建议团队形成统一规范主键pk_表名唯一uq_表名_列名外键fk_表名_列名检查ck_表名_列名默认值df_表名_列名例如uq_student_email看名字就知道是学生表邮箱唯一约束而 MySQL 自动生成的随机约束名在报错时几乎无法定位。9.2 约束不是越多越好约束有代价UNIQUE、PRIMARY KEY 伴随索引增加写入开销。外键在每个子表插入、更新、删除时都要检查父表高并发下会成为热点。过多的 CHECK 会让业务规则与代码耦合在数据库中后续变更成本变高。核心判断是低变动、强一致的关键规则必须下放到数据库高变动、偏展示的规则可以留在应用层。比如订单金额非负是低变动规则应该用 CHECK商品促销文案长度是高变动规则不应写死在数据库约束里。9.3 生产环境添加约束要“先查数据、再变更、留回滚”给生产表加约束前至少完成三件事在测试环境模拟同样的数据和变更流程。查询现有数据是否违反新约束先清理脏数据。保留备份或使用可在线执行的 DDL避免长时间锁表。MySQL 8.0 支持一些在线 DDL 操作但不同版本支持情况不同不能默认可并发。SQL Server 在部分约束添加场景也会锁表。线上变更最好走自动化流程并放在业务低峰期。9.4 主键设计建议自增主键和雪花 ID 各有优劣。自增主键写入性能好但迁移、合并数据时容易冲突雪花 ID 适合分布式场景但索引存储空间更大。不管选哪种主键都应该是“稳定、无业务含义、非空、唯一”的值。用身份证号、手机号做主键通常不是好设计因为这些业务属性可能变化也会带来隐私合规问题。9.5 唯一约束与幂等设计在支付、订单、消息场景中幂等设计经常依赖唯一约束。比如“支付回调”需要保证同一笔订单只能处理一次可以在回调记录上建uq_callback_order_id。这时候数据库的 UNIQUE 约束在某种意义上是业务幂等的一部分比应用层加锁更可靠。10. 总结与后续学习方向这篇文章围绕“数据完整性”这条主线梳理了 SQL 约束的完整体系约束分为实体完整性主键、唯一、参照完整性外键、用户定义完整性非空、检查、默认值。约束不仅限建表阶段还能通过 ALTER TABLE 在运行期补充。约束是数据库层的最后防线但不是安全防线的全部防 SQL 注入要用参数化查询和最小权限。实际项目中使用约束要关注版本差异、错误码、历史数据清理和变更回滚。如果你是从 Neso Academy 这类数据库管理系统课程入门的看完文章后建议做一个综合实验把学生、课程、选课三张表完整加上所有约束然后故意用错误 SQL 触发每种报错观察 MySQL 的错误码和错误信息。这个过程比背十遍语法都有效。想要进一步深挖可以继续学习约束与索引的底层存储关系。在线 DDL 在不同数据库中的实现差异。触发器、数据库事件与约束的配合使用。多表关联场景下外键与分库分表之间的冲突与取舍。记住一句工程经验表结构是一份契约约束是契约里写得最硬的条款。设计表结构时多花十分钟考虑约束很可能帮你省下未来无数个排查脏数据的深夜。建议把这篇文章收藏起来下次建表时对照检查一遍。