在关系型数据库管理系统中SQL 约束是表结构定义里最容易忽视、却最能决定数据质量的部分。无论是学生阶段的课程作业还是生产环境里的订单表和用户表只要约束设计得合理非法数据会在进入表之前就被数据库拦截约束设计得随意应用层再努力防御也会出现重复账号、NULL 关键字段、不存在的外键、超出范围的金额等脏数据。这篇文章围绕 Neso Academy 的数据库管理系统课程中 SQL 约束这一主题把六类常见约束、验证约束生效的示例查询以及约束冲突时的排查链路完整梳理一遍。读完这篇文章你可以做到说清楚每条约束到底保护什么在 MySQL 中建表并定义完整约束用示例 DML 语句触发约束并读懂错误通过系统表查看约束元数据在生产表上安全地增删约束。演示环境以 MySQL 8.0 为主同时会指出 SQL Server、PostgreSQL 等数据库在语法和默认行为上的差异。1. 先理解 SQL 中的约束到底约束谁1.1 用一句话说明约束的作用域在 SQL 中约束Constraint是数据库定义在表上的准入规则。每当向表插入数据、更新已有数据或删除被引用的数据时数据库引擎会先检查这些规则。规则通过语句继续执行规则不通过数据库直接返回错误并且不会把不符合规则的数据落盘。这个检查发生在数据库引擎内部与应用层代码无关。也就是说即使有人绕过了业务系统直接通过命令行、报表工具或临时脚本向数据库写入数据约束依然会强制校验。很多人会问既然应用层已经做了非空、重复、范围校验为什么还要在数据库里再写一遍约束原因有三点应用层校验可能遗漏不同版本、不同团队开发的接口对同一字段的校验逻辑并不总是一致。应用层可以被绕过历史系统、数据修复脚本、批量导入都可能直接访问数据库。数据库要承担数据完整性的最后防线这也是关系型数据库区别于文件存储和 NoSQL 的一个重要体现。这里要注意约束负责的是数据是否符合规则而不负责程序是否报错得好看。程序得到的是一条错误代码和一段错误文本错误如何映射成用户友好的提示仍然由应用层处理。1.2 数据完整性类型与约束的对应关系约束是数据完整性的落地手段。数据完整性通常分为四类实体完整性表中每一行都有一个可区分的标识通常由主键或唯一键承担。域完整性某一列的值必须在允许的范围内包括是否允许为空、是否是默认值、是否满足取值范围。引用完整性一张表中的外键值必须能在父表中找到对应记录或者被明确允许为空。用户定义完整性业务层面的自定义规则例如订单金额必须大于零、订单状态只能在给定枚举中。完整性类型常见约束要解决的问题实体完整性PRIMARY KEY、UNIQUE行与行不能混淆域完整性NOT NULL、DEFAULT、CHECK列的值格式和范围合法引用完整性FOREIGN KEY表与表之间的引用关系有效用户定义完整性CHECK、复合约束业务规则在数据库层落地理解这个对应关系有助于阅读建表脚本。看到一张表的主键、唯一约束、外键和 CHECK 约束时可以迅速判断每一行数据要满足哪些规则以及修改数据时最可能从哪一层报错。1.3 六类常用约束速览在标准 SQL 和主流数据库中最常见的约束有六种。约束类型作用违反时的表现NOT NULL该列不允许出现 NULL插入或更新时列值为 NULL语句报错UNIQUE该列或组合列的值不能重复插入重复值时语句报错PRIMARY KEY每行唯一标识等价于 NOT NULL UNIQUE重复值或 NULL 都会报错FOREIGN KEY子表列值必须在父表被引用列中存在父表删除被引用行或子表写入非法值时按规则拒绝或级联CHECK列值必须满足给定的布尔表达式表达式结果为 FALSE 时语句报错DEFAULT未指定值时填入默认值不报错而是自动补值其中 DEFAULT 严格来说属于列默认值属性而不是一种“拒绝数据”的约束但在设计表结构时它经常和 NOT NULL 一起出现。一个典型的组合是状态列status TINYINT NOT NULL DEFAULT 1既保证它有值又保证省略时自动落到“正常”状态。容易误解的一点是 UNIQUE 与 NULL 的关系。多数数据库允许 UNIQUE 列中出现多个 NULL因为 NULL 表示“未知”多个“未知”并不被认为彼此重复。这个行为在需要“可空但不可重复”的业务场景下需要特别小心通常要配合部分索引或扩展字段而不是只依赖 UNIQUE 约束。1.4 约束在语句执行链路中的位置一条写入语句的执行链路大致如下应用发送 INSERT / UPDATE / DELETE 语句。数据库解析 SQL确定操作对象表和列。优化器生成执行计划期间会读取表结构的元数据包括约束信息。存储引擎逐行执行写入并在写入前检查涉及列的约束。一旦约束不满足当前语句失败并返回错误码和错误文本。在 InnoDB 存储引擎的事务环境中约束错误通常导致当前语句失败但不会自动回滚整个事务中此前已经执行的语句。具体如何处理取决于业务代码是否把整个事务标记为回滚。这也是一个面试中容易被追问的细节。注意不要只在创建表成功后就认为约束全部生效。约束是否真的被数据库执行取决于数据库版本、存储引擎和约束类型。尤其是 CHECK 约束在 MySQL 8.0.16 之前存在“被解析但不强制执行”的情况后面会专门演示。2. 创建约束前先准备演示环境2.1 选型MySQL 8.0 为主其它数据库差异在哪里为了演示 SQL 约束的完整行为下面使用 MySQL 8.0。原因有三个MySQL 安装方便适合在一台虚拟机或容器里快速搭建学习环境。MySQL 8.0.16 之后CHECK 约束会被真正强制执行便于观察约束冲突。MySQL 的外键级联行为、错误码都比较典型和 SQL Server、PostgreSQL 对照时可以看清差异。如果本地没有 MySQL 8.0推荐使用 Docker 快速启动一个学习实例docker run --name sql-demo -e MYSQL_ROOT_PASSWORDroot123 -e MYSQL_DATABASElearn_sql -p 3306:3306 -d mysql:8.0启动后进入容器docker exec -it sql-demo mysql -uroot -p输入密码后执行USE learn_sql;这个口令只适合学习环境生产环境请使用强密码并配置远程访问白名单。版本差异是学习约束时最容易踩的坑数据库CHECK 约束行为备注MySQL 5.7 及更早会解析 CHECK 语法但默认不执行可用触发器模拟或升级到 8.0MySQL 8.0.16 及以后强制执行 CHECK 约束本文演示环境SQL Server强制执行 CHECK可在导入历史数据时临时 NOCHECKPostgreSQL强制执行 CHECK还支持延迟约束Oracle强制执行 CHECK支持 ENABLE / DISABLE 状态凡是在生产环境使用数据库第一件事就是确认版本再决定哪些约束能依赖数据库执行哪些约束只能退回到应用层或触发器。2.2 创建用户表和订单表为了把六类约束放到一张完整的表结构中这里设计一个最简单的用户-订单场景用户表保存账号信息订单表保存每个用户的订单。两个表通过外键关联。CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(32) NOT NULL, email VARCHAR(128) NOT NULL, age INT, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_users_email (email), CONSTRAINT chk_users_age CHECK (age IS NULL OR (age 0 AND age 120)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;逐条说明id INT NOT NULL AUTO_INCREMENT主键列不能为空由数据库自增。username VARCHAR(32) NOT NULL用户名不能为空。email VARCHAR(128) NOT NULL邮箱不能为空同时UNIQUE KEY uk_users_email保证邮箱不能重复。age INT允许为空但如果有值必须在 0 到 120 之间由chk_users_age控制。status TINYINT NOT NULL DEFAULT 1状态列不能为空未显式指定时默认写入 1。created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP创建时间自动填充当前时间。然后创建订单表CREATE TABLE orders ( order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10, 2) NOT NULL, status VARCHAR(16) NOT NULL DEFAULT PENDING, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (order_id), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id), CONSTRAINT chk_orders_amount CHECK (amount 0), CONSTRAINT chk_orders_status CHECK (status IN (PENDING, PAID, SHIPPED, CANCELLED)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;订单表的约束设计逻辑order_id BIGINT订单量通常比用户量大使用 BIGINT 更稳妥。user_id INT NOT NULL订单必须归属某个用户类型与 users.id 保持一致。fk_orders_user外键约束外键列引用 users.id。amount DECIMAL(10,2) NOT NULL金额用精确小数不能用 FLOAT 或 DOUBLE。chk_orders_amount金额必须大于 0防止插入 0 或负数订单。chk_orders_status订单状态只能是列表中的四种之一。注意在 MySQL 中外键引用的父表列必须是主键或唯一键列。这里 users.id 是主键所以可以引用如果引用一个既不是主键也不是唯一键的列建表会直接报错。2.3 插入测试数据验证约束是否真的在工作先插入一个正常用户INSERT INTO users (username, email, age, status) VALUES (alice, aliceexample.com, 18, 1);这条语句应该成功返回 1 行受影响。再尝试插入重复邮箱INSERT INTO users (username, email, age) VALUES (bob, aliceexample.com, 20);预期报错ERROR 1062 (23000): Duplicate entry aliceexample.com for key users.uk_users_email再尝试插入年龄为负数的用户INSERT INTO users (username, email, age) VALUES (bob, bobexample.com, -5);预期报错ERROR 3819 (HY000): Check constraint chk_users_age is violated.再尝试插入一个 user_id 不存在的订单INSERT INTO orders (user_id, amount, status) VALUES (999, 100.00, PENDING);预期报错ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (learn_sql.orders, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id))这三条报错分别对应 UNIQUE、CHECK、FOREIGN KEY。看到错误文本时先从约束名或键名定位是哪张表的哪一个规则再检查输入数据不要只记“报错了”。最后插入一个合法用户和一条合法订单INSERT INTO users (username, email, age) VALUES (bob, bobexample.com, NULL);这里 age 传 NULL 是允许的因为 CHECK 条件里写了age IS NULL OR ...status 省略后会自动填 1。INSERT INTO orders (user_id, amount, status) VALUES (1, 99.90, PENDING);订单插入成功因为 user_id1 在 users 表中存在。2.4 使用 DESCRIBE 和 SHOW CREATE TABLE 检查约束创建表后通过两个命令确认约束结构DESCRIBE users;这个命令输出列名、类型、是否为空、默认值等基础信息能够快速看到 NOT NULL 和 DEFAULT但看不到 CHECK 和外键细节。要查看完整约束使用SHOW CREATE TABLE users\G在 MySQL 客户端中\G会把结果竖向展示。输出结果中可以看到PRIMARY KEY、UNIQUE KEY uk_users_email、CONSTRAINT chk_users_age CHECK等完整定义。同理检查 ordersSHOW CREATE TABLE orders\G这一步是所有约束排查的基础。收到约束相关报错后先打开SHOW CREATE TABLE确认目标表当前实际存在的约束再看报错文本指向哪个约束。3. 用示例查询验证六大约束3.1 NOT NULL 与 DEFAULT先约定空值和默认值NOT NULL 表面简单但语义容易被忽略它约束的是 NULL 而不是空值。NULL表示“未赋值、未知”空字符串是一个具体的值。因此VARCHAR NOT NULL允许空字符串存在除非额外使用 CHECK 或应用层校验禁止。演示 NOT NULL-- 不提供 email且 email 没有默认值 INSERT INTO users (username, age) VALUES (carol, 22);预期报错ERROR 1364 (HY000): Field email doesnt have a default value再试显式 NULLINSERT INTO users (username, email, age) VALUES (carol, NULL, 22);预期报错ERROR 1048 (23000): Column email cannot be null现在演示 DEFAULTINSERT INTO users (username, email, age) VALUES (carol, carolexample.com, 22);status 列没有出现在语句中但因为status TINYINT NOT NULL DEFAULT 1数据库自动填入 1。查询验证SELECT username, email, status, created_at FROM users WHERE username carol;结果中 status 是 1created_at 是当前时间。这说明 NOT NULL 和 DEFAULT 是配合使用的有 DEFAULT 的列在写入时可以省略没有 NOT NULL 也没有 DEFAULT 的列可以写 NULL既没有 NOT NULL 也没有 DEFAULT 的列写入时必须给出值。这里有一个常见设计状态、创建时间这类列全部写成NOT NULL DEFAULT ...可以避免后续查询出现大量 NULL 判断。3.2 UNIQUE 与 PRIMARY KEY重复数据怎么拦截PRIMARY KEY 和 UNIQUE 都用于保证行唯一性。区别在于一个表只能有一个 PRIMARY KEY但可以有多个 UNIQUE 约束PRIMARY KEY 列不允许 NULL而 UNIQUE 列在多数数据库中允许出现多个 NULL。建表时重复邮箱已经被唯一约束拦截。现在演示复合唯一约束。在“学生选课”场景里同一学生在同一学期不能重复选同一门课需要用到多列唯一CREATE TABLE course_selection ( student_id INT NOT NULL, course_id INT NOT NULL, semester VARCHAR(16) NOT NULL, already_paid TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (student_id, course_id, semester) ) ENGINEInnoDB;先插入一行正常数据INSERT INTO course_selection (student_id, course_id, semester) VALUES (1, 101, 2025-S1);再插入同一学生、同一课程、同一学期的记录INSERT INTO course_selection (student_id, course_id, semester) VALUES (1, 101, 2025-S1);预期报错ERROR 1062 (23000): Duplicate entry 1-101-2025-S1 for key course_selection.PRIMARY组合主键的含义是“三列组合整体不能重复”而不是要求每一列都全局唯一。这是一个经常被误解的点student_id可以出现多次course_id也可以出现多次但三列的组合不能重复。再看 UNIQUE 和 PRIMARY KEY 的对比维度PRIMARY KEYUNIQUE表中数量最多一个可以有多个是否允许 NULL不允许多数数据库允许多个 NULL是否自动建索引是是用途行唯一标识业务字段唯一性需要提醒一点不要把唯一性的验证全部依赖应用层。并发场景下两个请求可能同时通过应用层检查然后先后写入数据库。如果没有唯一约束和唯一索引最终就会产生两条重复数据。数据库层的 UNIQUE 约束是并发场景下的兜底措施。3.3 CHECK 约束数据库替你执行业务规则CHECK 约束是“用户定义完整性”的典型实现。它允许你在建表时写一个布尔表达式数据库在插入或更新时逐行判断表达式是否成立。在本文的 orders 表里chk_orders_amount和chk_orders_status就是两个 CHECK 约束。演示INSERT INTO orders (user_id, amount, status) VALUES (1, 0, PENDING);预期报错ERROR 3819 (HY000): Check constraint chk_orders_amount is violated.再演示状态枚举INSERT INTO orders (user_id, amount, status) VALUES (1, 88.00, REFUNDED);预期报错ERROR 3819 (HY000): Check constraint chk_orders_status is violated.CHECK 表达式处理 NULL 时要特别小心。标准 SQL 的逻辑是三值逻辑FALSE 表示违反约束TRUE 和 UNKNOWN 都表示符合约束。也就是说如果 CHECK 表达式的值是 UNKNOWN数据库会认为满足约束。这解释了为什么 users 表的 age CHECK 写成CONSTRAINT chk_users_age CHECK (age IS NULL OR (age 0 AND age 120))如果不写age IS NULL只写age 0 AND age 120那么当 age 为 NULL 时整个表达式结果是 UNKNOWN按标准也会被判定为不违反约束。但为了可读性和避免不同数据库实现差异建议在 CHECK 表达式里显式处理 NULL 分支。CHECK 约束的能力边界也值得知道表达式必须是确定性的不能依赖随机函数、用户变量或当前会话状态。在主流数据库中CHECK 表达式通常不允许包含子查询。CHECK 约束适合表达单行内的简单规则不适合跨表规则跨表规则应交给外键或触发器。如果数据库版本太老导致 CHECK 不生效比如 MySQL 5.7通常有两种替代方式使用触发器或者在应用层中央校验。触发器维护成本高应用层校验可能被绕过因此升级数据库版本往往是最省事的做法。3.4 FOREIGN KEY表与表之间的引用规则外键约束解决引用完整性问题。orders.user_id 引用 users.id这条规则保证只要 orders 里有一条记录的 user_id 值为 Nusers 表里就必须存在 id 为 N 的行。验证合法外键INSERT INTO orders (user_id, amount, status) VALUES (1, 88.00, PENDING);因为 users 表中有 id1这条语句成功。验证非法外键INSERT INTO orders (user_id, amount, status) VALUES (1000, 88.00, PENDING);预期报错ERROR 1452 (23000): Cannot add or update a child row再验证父表删除被引用行。默认情况下试图删除被订单引用的用户会被拒绝DELETE FROM users WHERE id 1;预期报错ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (learn_sql.orders, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id))这是外键的默认保护行为。如果业务上希望在删除用户时同时删除其个人资料可以定义ON DELETE CASCADE。演示CREATE TABLE user_profiles ( id INT NOT NULL, bio VARCHAR(255), PRIMARY KEY (id), CONSTRAINT fk_profiles_user FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE ) ENGINEInnoDB;先插入一个新用户和个人资料然后删除用户观察个人资料是否被级联删除INSERT INTO users (username, email, age) VALUES (dave, daveexample.com, 30); SET dave_id LAST_INSERT_ID(); INSERT INTO user_profiles (id, bio) VALUES (dave_id, this is dave); DELETE FROM users WHERE id dave_id;删除后查询 user_profiles对应行已经不存在。ON DELETE 的常见选项需要理解选项行为RESTRICT有子行引用时父行不允许删除NO ACTION标准 SQL 行为MySQL 中与 RESTRICT 表现一致CASCADE删除父行时同步删除引用它的子行SET NULL删除父行时将子表外键列置为 NULL前提是该列允许 NULLSET DEFAULT删除父行时将子表外键列置为默认值但 InnoDB 不支持生产环境对 CASCADE 要非常谨慎一条 DELETE 可能连带删除大量子表数据。建议在删除前先使用 SELECT 统计受影响子行数量。3.5 通过系统表查询约束元数据约束信息并不是只有通过SHOW CREATE TABLE才能看到。MySQL 的信息模式information_schema中集中保存了与约束相关的元数据适合自动化脚本和排查工具使用。查询某张表的所有约束SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA learn_sql AND TABLE_NAME users;结果会看到 PRIMARY KEY、UNIQUE、CHECK 等类型的约束。查询外键列和引用关系SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA learn_sql AND TABLE_NAME orders;查询外键的级联规则SELECT CONSTRAINT_NAME, DELETE_RULE, UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA learn_sql AND TABLE_NAME orders;查询 CHECK 约束表达式SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CHECK_CLAUSE FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA learn_sql;不同数据库的系统表名称不一样但思路相同数据库关键元数据视图MySQLinformation_schema.TABLE_CONSTRAINTS、KEY_COLUMN_USAGE、REFERENTIAL_CONSTRAINTS、CHECK_CONSTRAINTSSQL Serversys.key_constraints、sys.foreign_keys、sys.check_constraintsPostgreSQLpg_constraint、pg_attribute 联合查询OracleALL_CONSTRAINTS、ALL_CONS_COLUMNS编写数据库巡检脚本时优先使用这些系统表而不是去解析SHOW CREATE TABLE的文本。系统表返回的是结构化字段更适合做差异比对和告警。3.6 约束验证速查表可以用下面这张表作为日常验证约束的速查参考。约束类型正常语句示例违反语句示例预期错误关键字NOT NULLINSERT 时给列赋普通值INSERT 时列写 NULLcannot be nullUNIQUE插入一个新值插入已存在的值Duplicate entryPRIMARY KEY插入不在主键列表中的组合插入重复主键Duplicate entryFOREIGN KEY子表外键值在父表中存在子表外键值在父表中不存在foreign key constraint failsCHECK满足表达式不满足表达式Check constraint ... is violatedDEFAULTINSERT 中省略该列无对应错误观察默认值即可无错误默认值被写入4. 约束报错怎么排查4.1 常见错误码与错误文本速查数据库返回约束错误时通常同时包含数字错误码、SQLSTATE 和错误文本。MySQL 中常见错误码如下错误码SQLSTATE错误文本特征涉及的约束104823000Column xxx cannot be nullNOT NULL1364HY000Field xxx doesnt have a default valueNOT NULL 且无 DEFAULT106223000Duplicate entry ... for key ...UNIQUE 或 PRIMARY KEY3819HY000Check constraint ... is violatedCHECK145223000Cannot add or update a child rowFOREIGN KEY145123000Cannot delete or update a parent rowFOREIGN KEY 的删除限制排查这类错误时不要只盯着错误文本的前半段要重点看三处信息错误码决定去文档搜索方向。约束名或键名例如uk_users_email、fk_orders_user直接告诉你哪个规则被违反。表名和库名确认是否在当前连接的目标库上。4.2 从错误文本倒推问题链路以ERROR 1452 (23000): Cannot add or update a child row为例完整排查顺序是确认错误发生在哪个 SQL 语句是 INSERT 还是 UPDATE。提取约束名fk_orders_user它位于learn_sql.orders表。执行SHOW CREATE TABLE orders\G查看fk_orders_user的定义确认它引用users(id)。检查语句里写入orders.user_id的值。在父表执行SELECT id FROM users WHERE id 值如果查询不到说明外键值不存在。这类问题的修复方式简单直接-- 先查父表是否存在该 id SELECT id FROM users WHERE id 1000; -- 如果不存在可以改成存在的 id或先在父表插入对应用户再以ERROR 3819 (HY000): Check constraint chk_orders_amount is violated.为例从错误文本找到约束名chk_orders_amount。查询约束定义SELECT CONSTRAINT_NAME, CHECK_CLAUSE FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME chk_orders_amount;得到条件amount 0。检查插入命令中的 amount 值是否小于或等于 0。这里容易忽略的是错误文本只会告诉你“违反约束”不会告诉你当前值是多少。把约束条件和当前值放在一起对比是最高效的定位方式。4.3 约束排查五问遇到约束相关问题时可以按五个问题检查是什么约束错误文本里是否有约束名或索引名属于哪张表约束定义在父表还是子表错误指向哪张表条件是什么通过 SHOW CREATE TABLE 或系统表查看约束表达式。当前值是什么把导致失败的 SQL 中的值提取出来。上下文是什么是否在事务中、是否处于批量导入模式、数据库版本是否支持该约束注意排查时先看约束名再看约束所在表不要一上来就去改数据。很多情况下改数据不能解决问题因为约束本身仍然会拒绝下一次非法写入。4.4 约束排查清单排查项检查方式确认标准约束是否被数据库强制执行SELECT VERSION(); 查看数据库版本MySQL 的 CHECK 需要 8.0.16约束定义是否正确SHOW CREATE TABLE 表名与需求中的规则一致存量数据是否满足新约束SELECT COUNT(*) ... WHERE 违反约束的条件新增约束前计数为 0外键两端列类型是否一致SHOW CREATE TABLE 父表、子表类型和字符集兼容外键列是否允许 NULLDESC 表名如果业务允许无主子记录可以设为 NULL当前连接是否在目标库SELECT DATABASE();不匹配会导致找不到表或约束5. 约束的动态管理与工程取舍5.1 用 ALTER TABLE 添加和删除约束业务变化后约束也需要演进。ALTER TABLE 是修改约束的主要手段。给已有表添加 CHECKALTER TABLE users ADD CONSTRAINT chk_users_email_not_empty CHECK (email );给已有表添加 UNIQUEALTER TABLE users ADD CONSTRAINT uk_users_username UNIQUE (username);当前 users 表中 username 都不同因此这条命令可以成功。它表达的业务含义是用户名也不能重复。给已有表添加外键ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id);删除约束的语法按类型区分。MySQL 中 CHECK 约束使用 DROP CHECK外键使用 DROP FOREIGN KEYALTER TABLE orders DROP CHECK chk_orders_status; ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;SQL Server 和 PostgreSQL 都支持统一的 DROP CONSTRAINT 写法-- SQL Server / PostgreSQL 风格 ALTER TABLE orders DROP CONSTRAINT fk_orders_user;在 MySQL 中删除约束前一定要先通过SHOW CREATE TABLE确认约束名。生产环境中约束名可能是由数据库自动生成的删错对象会造成额外麻烦。添加约束前存量数据校验是必须步骤。例如给 users.email 增加非空 CHECK 时先执行SELECT COUNT(*) AS bad_rows FROM users WHERE email IS NULL OR email ;只要 bad_rows 大于 0直接添加约束大概率失败或者即使添加成功也会把不满足条件的历史数据暴露出来。正确的顺序是先清理存量数据再添加约束最后用查询验证。5.2 建表时定义约束与后期增加约束的选择约束在设计期越早定义越能减少脏数据进入表。但仍有一些场景适合后期增加约束场景建议原因新表、新功能建表时定义全部核心约束成本最低无需处理存量数据老表需收紧业务规则后期增加 CHECK / UNIQUE原本数据可能已经违反新规则需先清理大表增加外键低峰期执行评估锁和耗时外键检查可能触发大量索引扫描临时导入历史数据先禁约束或使用导入工具选项导入后再校验避免逐条失败提高导入效率对于临时导入需要特别说明导入完成后必须重新开启约束并执行校验不能把“临时关闭”留在生产环境。5.3 新增约束时的操作规范给生产环境的表增加约束建议按下面顺序操作在测试环境用相同结构和数据量演练一遍。确认数据库版本对目标约束的支持情况。备份表定义和数据至少保留一份可用快照。先检查存量数据是否满足新约束不满足则清洗。在低峰期执行 ALTER TABLE并关注锁等待和 IO。变更后通过信息模式查询约束是否已存在。用典型非法语句测试约束是否真正生效。通知业务方和监控方避免因短暂锁表触发大量超时告警。这一步里最容易出现问题的是大表变更。MySQL 8.0 中很多 DDL 操作使用 INPLACE 算法但仍然会产生短暂的锁表和额外空间开销。生产环境不要在自己开发机上模拟结论要以测试环境和压测结果为准。5.4 学习环境与生产环境的约束行为差异同一个约束语句在学习环境里能跑通不代表生产环境也可以直接执行。常见差异如下维度学习环境生产环境数据量几百行百万行以上ALTER TABLE 耗时毫秒级可能需要分钟级且会锁表约束测试可以随意触发错误需要小流量、回滚方案和监控权限root 直接改最小权限DDL 走审批版本随意升级需要兼容老版本和依赖约束策略全部约束都加考虑外键、CHECK 对性能和维护的影响这不是说生产环境不应该使用约束而是说约束的变更要当成一次发布来对待而不是一条即兴命令。6. 约束相关常见坑与最佳实践6.1 六个高频踩坑点坑 1MySQL 老版本不执行 CHECK 约束。现象建表时写了 CHECK插入非法数据却没有报错。原因MySQL 8.0.16 之前会解析 CHECK 语法但不强制执行。解决升级到 8.0.16 以上或在老版本中使用触发器或依赖应用层校验。上线前一定要实际插入一条非法数据验证约束生效。坑 2UNIQUE 约束允许多个 NULL。现象给某个可空列加了 UNIQUE 约束却发现数据库允许两行完全相同的 NULL。原因多数数据库认为 NULL 不等于 NULL多个未知值不算重复。解决如果业务要求“可空但只能有一个 NULL”需要结合条件索引或触发器实现。坑 3CHECK 条件没有处理 NULL导致规则判断与预期不一致。现象想限制 age 在 0 到 120 之间但 age NULL 的数据仍然插入成功。原因SQL 三值逻辑下NULL 参与表达式会得到 UNKNOWNUNKNOWN 不一定被判定为违反 CHECK。解决在 CHECK 表达式中明确写col IS NULL OR (条件)或者给列加 NOT NULL。坑 4外键列没有按业务查询方式规划索引删除父行时锁等待高。现象删除父表一行生产高并发时出现大量锁等待。原因子表外键列虽然有自动索引但删除路径和高频查询并不完全一致外键检查需要扫描更多数据。解决根据业务查询模式在子表外键列或组合列上显式建索引删除前使用 EXPLAIN 确认执行计划。坑 5增加约束时忽略存量数据。现象ALTER TABLE ADD CHECK 或 ADD UNIQUE 直接失败。原因表中已有数据不满足新约束。解决先执行SELECT COUNT(*)检查存量数据清洗后再加约束。坑 6删除外键约束时写错表。现象在父表 users 上执行删除 fk_orders_user报约束不存在。原因外键约束虽然引用父表但约束定义在子表 orders 上属于子表对象。解决先执行SHOW CREATE TABLE orders\G确认约束所在表再在子表上删除。6.2 建表前约束设计检查清单每次建表都可以按下面清单过一遍避免遗漏关键规则每个表是否有主键是用业务字段还是代理主键哪些列在业务语义上不能为空是否都加了 NOT NULL需要自动填充的列是否设置了 DEFAULT哪些业务字段要求全局唯一是否用 UNIQUE 约束表达数值类型是否有范围要求是否用 DECIMAL 而不是浮点数枚举状态字段是否用 CHECK 限制取值范围需要跨表引用的列是否定义了 FOREIGN KEY列类型和父表是否一致删除父表行时业务上是希望拒绝、级联还是置空约束命名是否可读是否遵循pk_、uk_、fk_、chk_前缀约定数据库版本是否支持计划使用的约束老版本是否有替代方案清单里的每一项都能落成一条实际的 DDL 语句而不是抽象建议。6.3 扩展方向掌握约束的基础使用之后可以从以下几个方面继续深入约束与索引的关系。主键、唯一约束、外键背后都对应索引。理解索引可以减少冗余索引也能解释为什么有些约束会影响查询性能。触发器和延迟约束。数据库版本不支持 CHECK 时可以用触发器模拟PostgreSQL 支持 DEFERRABLE 约束允许在事务提交时再检查约束。这些机制处理复杂业务规则时很有用。数据库迁移工具。使用 Flyway、Liquibase 管理表结构和约束变更把约束改动纳入版本控制避免不同环境执行顺序不一致。约束与数据同步。数据仓库、日志表等写入频率极高的场景可能需要放弃部分约束以换取写入吞吐这就要在应用层和数据质量任务中兜底。这个取舍属于架构设计没有绝对标准。数据库安全。使用参数化查询和最小权限账户在应用层做输入校验不要把 SQL 拼接进查询字符串。约束可以防止脏数据但不能替代安全编码规范。最后回到开头的问题约束不是“给数据库添麻烦”而是把对数据的基本判断放到最合适的位置。数据库每拒绝一次错误数据都是在给上层应用减少一次修复成本。推荐的练习方式是建两张业务表把所有约束写全然后故意用各种错误语句把它“打挂”看错误码是否能快速定位到具体约束遇到约束不生效时先确认数据库版本和表结构再怀疑语法。把这一套流程走熟之后再去思考外键、CHECK 在生产环境中的取舍你会更有判断力。
SQL约束详解：六大约束与数据完整性实战指南
在关系型数据库管理系统中SQL 约束是表结构定义里最容易忽视、却最能决定数据质量的部分。无论是学生阶段的课程作业还是生产环境里的订单表和用户表只要约束设计得合理非法数据会在进入表之前就被数据库拦截约束设计得随意应用层再努力防御也会出现重复账号、NULL 关键字段、不存在的外键、超出范围的金额等脏数据。这篇文章围绕 Neso Academy 的数据库管理系统课程中 SQL 约束这一主题把六类常见约束、验证约束生效的示例查询以及约束冲突时的排查链路完整梳理一遍。读完这篇文章你可以做到说清楚每条约束到底保护什么在 MySQL 中建表并定义完整约束用示例 DML 语句触发约束并读懂错误通过系统表查看约束元数据在生产表上安全地增删约束。演示环境以 MySQL 8.0 为主同时会指出 SQL Server、PostgreSQL 等数据库在语法和默认行为上的差异。1. 先理解 SQL 中的约束到底约束谁1.1 用一句话说明约束的作用域在 SQL 中约束Constraint是数据库定义在表上的准入规则。每当向表插入数据、更新已有数据或删除被引用的数据时数据库引擎会先检查这些规则。规则通过语句继续执行规则不通过数据库直接返回错误并且不会把不符合规则的数据落盘。这个检查发生在数据库引擎内部与应用层代码无关。也就是说即使有人绕过了业务系统直接通过命令行、报表工具或临时脚本向数据库写入数据约束依然会强制校验。很多人会问既然应用层已经做了非空、重复、范围校验为什么还要在数据库里再写一遍约束原因有三点应用层校验可能遗漏不同版本、不同团队开发的接口对同一字段的校验逻辑并不总是一致。应用层可以被绕过历史系统、数据修复脚本、批量导入都可能直接访问数据库。数据库要承担数据完整性的最后防线这也是关系型数据库区别于文件存储和 NoSQL 的一个重要体现。这里要注意约束负责的是数据是否符合规则而不负责程序是否报错得好看。程序得到的是一条错误代码和一段错误文本错误如何映射成用户友好的提示仍然由应用层处理。1.2 数据完整性类型与约束的对应关系约束是数据完整性的落地手段。数据完整性通常分为四类实体完整性表中每一行都有一个可区分的标识通常由主键或唯一键承担。域完整性某一列的值必须在允许的范围内包括是否允许为空、是否是默认值、是否满足取值范围。引用完整性一张表中的外键值必须能在父表中找到对应记录或者被明确允许为空。用户定义完整性业务层面的自定义规则例如订单金额必须大于零、订单状态只能在给定枚举中。完整性类型常见约束要解决的问题实体完整性PRIMARY KEY、UNIQUE行与行不能混淆域完整性NOT NULL、DEFAULT、CHECK列的值格式和范围合法引用完整性FOREIGN KEY表与表之间的引用关系有效用户定义完整性CHECK、复合约束业务规则在数据库层落地理解这个对应关系有助于阅读建表脚本。看到一张表的主键、唯一约束、外键和 CHECK 约束时可以迅速判断每一行数据要满足哪些规则以及修改数据时最可能从哪一层报错。1.3 六类常用约束速览在标准 SQL 和主流数据库中最常见的约束有六种。约束类型作用违反时的表现NOT NULL该列不允许出现 NULL插入或更新时列值为 NULL语句报错UNIQUE该列或组合列的值不能重复插入重复值时语句报错PRIMARY KEY每行唯一标识等价于 NOT NULL UNIQUE重复值或 NULL 都会报错FOREIGN KEY子表列值必须在父表被引用列中存在父表删除被引用行或子表写入非法值时按规则拒绝或级联CHECK列值必须满足给定的布尔表达式表达式结果为 FALSE 时语句报错DEFAULT未指定值时填入默认值不报错而是自动补值其中 DEFAULT 严格来说属于列默认值属性而不是一种“拒绝数据”的约束但在设计表结构时它经常和 NOT NULL 一起出现。一个典型的组合是状态列status TINYINT NOT NULL DEFAULT 1既保证它有值又保证省略时自动落到“正常”状态。容易误解的一点是 UNIQUE 与 NULL 的关系。多数数据库允许 UNIQUE 列中出现多个 NULL因为 NULL 表示“未知”多个“未知”并不被认为彼此重复。这个行为在需要“可空但不可重复”的业务场景下需要特别小心通常要配合部分索引或扩展字段而不是只依赖 UNIQUE 约束。1.4 约束在语句执行链路中的位置一条写入语句的执行链路大致如下应用发送 INSERT / UPDATE / DELETE 语句。数据库解析 SQL确定操作对象表和列。优化器生成执行计划期间会读取表结构的元数据包括约束信息。存储引擎逐行执行写入并在写入前检查涉及列的约束。一旦约束不满足当前语句失败并返回错误码和错误文本。在 InnoDB 存储引擎的事务环境中约束错误通常导致当前语句失败但不会自动回滚整个事务中此前已经执行的语句。具体如何处理取决于业务代码是否把整个事务标记为回滚。这也是一个面试中容易被追问的细节。注意不要只在创建表成功后就认为约束全部生效。约束是否真的被数据库执行取决于数据库版本、存储引擎和约束类型。尤其是 CHECK 约束在 MySQL 8.0.16 之前存在“被解析但不强制执行”的情况后面会专门演示。2. 创建约束前先准备演示环境2.1 选型MySQL 8.0 为主其它数据库差异在哪里为了演示 SQL 约束的完整行为下面使用 MySQL 8.0。原因有三个MySQL 安装方便适合在一台虚拟机或容器里快速搭建学习环境。MySQL 8.0.16 之后CHECK 约束会被真正强制执行便于观察约束冲突。MySQL 的外键级联行为、错误码都比较典型和 SQL Server、PostgreSQL 对照时可以看清差异。如果本地没有 MySQL 8.0推荐使用 Docker 快速启动一个学习实例docker run --name sql-demo -e MYSQL_ROOT_PASSWORDroot123 -e MYSQL_DATABASElearn_sql -p 3306:3306 -d mysql:8.0启动后进入容器docker exec -it sql-demo mysql -uroot -p输入密码后执行USE learn_sql;这个口令只适合学习环境生产环境请使用强密码并配置远程访问白名单。版本差异是学习约束时最容易踩的坑数据库CHECK 约束行为备注MySQL 5.7 及更早会解析 CHECK 语法但默认不执行可用触发器模拟或升级到 8.0MySQL 8.0.16 及以后强制执行 CHECK 约束本文演示环境SQL Server强制执行 CHECK可在导入历史数据时临时 NOCHECKPostgreSQL强制执行 CHECK还支持延迟约束Oracle强制执行 CHECK支持 ENABLE / DISABLE 状态凡是在生产环境使用数据库第一件事就是确认版本再决定哪些约束能依赖数据库执行哪些约束只能退回到应用层或触发器。2.2 创建用户表和订单表为了把六类约束放到一张完整的表结构中这里设计一个最简单的用户-订单场景用户表保存账号信息订单表保存每个用户的订单。两个表通过外键关联。CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(32) NOT NULL, email VARCHAR(128) NOT NULL, age INT, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_users_email (email), CONSTRAINT chk_users_age CHECK (age IS NULL OR (age 0 AND age 120)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;逐条说明id INT NOT NULL AUTO_INCREMENT主键列不能为空由数据库自增。username VARCHAR(32) NOT NULL用户名不能为空。email VARCHAR(128) NOT NULL邮箱不能为空同时UNIQUE KEY uk_users_email保证邮箱不能重复。age INT允许为空但如果有值必须在 0 到 120 之间由chk_users_age控制。status TINYINT NOT NULL DEFAULT 1状态列不能为空未显式指定时默认写入 1。created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP创建时间自动填充当前时间。然后创建订单表CREATE TABLE orders ( order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10, 2) NOT NULL, status VARCHAR(16) NOT NULL DEFAULT PENDING, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (order_id), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id), CONSTRAINT chk_orders_amount CHECK (amount 0), CONSTRAINT chk_orders_status CHECK (status IN (PENDING, PAID, SHIPPED, CANCELLED)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;订单表的约束设计逻辑order_id BIGINT订单量通常比用户量大使用 BIGINT 更稳妥。user_id INT NOT NULL订单必须归属某个用户类型与 users.id 保持一致。fk_orders_user外键约束外键列引用 users.id。amount DECIMAL(10,2) NOT NULL金额用精确小数不能用 FLOAT 或 DOUBLE。chk_orders_amount金额必须大于 0防止插入 0 或负数订单。chk_orders_status订单状态只能是列表中的四种之一。注意在 MySQL 中外键引用的父表列必须是主键或唯一键列。这里 users.id 是主键所以可以引用如果引用一个既不是主键也不是唯一键的列建表会直接报错。2.3 插入测试数据验证约束是否真的在工作先插入一个正常用户INSERT INTO users (username, email, age, status) VALUES (alice, aliceexample.com, 18, 1);这条语句应该成功返回 1 行受影响。再尝试插入重复邮箱INSERT INTO users (username, email, age) VALUES (bob, aliceexample.com, 20);预期报错ERROR 1062 (23000): Duplicate entry aliceexample.com for key users.uk_users_email再尝试插入年龄为负数的用户INSERT INTO users (username, email, age) VALUES (bob, bobexample.com, -5);预期报错ERROR 3819 (HY000): Check constraint chk_users_age is violated.再尝试插入一个 user_id 不存在的订单INSERT INTO orders (user_id, amount, status) VALUES (999, 100.00, PENDING);预期报错ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (learn_sql.orders, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id))这三条报错分别对应 UNIQUE、CHECK、FOREIGN KEY。看到错误文本时先从约束名或键名定位是哪张表的哪一个规则再检查输入数据不要只记“报错了”。最后插入一个合法用户和一条合法订单INSERT INTO users (username, email, age) VALUES (bob, bobexample.com, NULL);这里 age 传 NULL 是允许的因为 CHECK 条件里写了age IS NULL OR ...status 省略后会自动填 1。INSERT INTO orders (user_id, amount, status) VALUES (1, 99.90, PENDING);订单插入成功因为 user_id1 在 users 表中存在。2.4 使用 DESCRIBE 和 SHOW CREATE TABLE 检查约束创建表后通过两个命令确认约束结构DESCRIBE users;这个命令输出列名、类型、是否为空、默认值等基础信息能够快速看到 NOT NULL 和 DEFAULT但看不到 CHECK 和外键细节。要查看完整约束使用SHOW CREATE TABLE users\G在 MySQL 客户端中\G会把结果竖向展示。输出结果中可以看到PRIMARY KEY、UNIQUE KEY uk_users_email、CONSTRAINT chk_users_age CHECK等完整定义。同理检查 ordersSHOW CREATE TABLE orders\G这一步是所有约束排查的基础。收到约束相关报错后先打开SHOW CREATE TABLE确认目标表当前实际存在的约束再看报错文本指向哪个约束。3. 用示例查询验证六大约束3.1 NOT NULL 与 DEFAULT先约定空值和默认值NOT NULL 表面简单但语义容易被忽略它约束的是 NULL 而不是空值。NULL表示“未赋值、未知”空字符串是一个具体的值。因此VARCHAR NOT NULL允许空字符串存在除非额外使用 CHECK 或应用层校验禁止。演示 NOT NULL-- 不提供 email且 email 没有默认值 INSERT INTO users (username, age) VALUES (carol, 22);预期报错ERROR 1364 (HY000): Field email doesnt have a default value再试显式 NULLINSERT INTO users (username, email, age) VALUES (carol, NULL, 22);预期报错ERROR 1048 (23000): Column email cannot be null现在演示 DEFAULTINSERT INTO users (username, email, age) VALUES (carol, carolexample.com, 22);status 列没有出现在语句中但因为status TINYINT NOT NULL DEFAULT 1数据库自动填入 1。查询验证SELECT username, email, status, created_at FROM users WHERE username carol;结果中 status 是 1created_at 是当前时间。这说明 NOT NULL 和 DEFAULT 是配合使用的有 DEFAULT 的列在写入时可以省略没有 NOT NULL 也没有 DEFAULT 的列可以写 NULL既没有 NOT NULL 也没有 DEFAULT 的列写入时必须给出值。这里有一个常见设计状态、创建时间这类列全部写成NOT NULL DEFAULT ...可以避免后续查询出现大量 NULL 判断。3.2 UNIQUE 与 PRIMARY KEY重复数据怎么拦截PRIMARY KEY 和 UNIQUE 都用于保证行唯一性。区别在于一个表只能有一个 PRIMARY KEY但可以有多个 UNIQUE 约束PRIMARY KEY 列不允许 NULL而 UNIQUE 列在多数数据库中允许出现多个 NULL。建表时重复邮箱已经被唯一约束拦截。现在演示复合唯一约束。在“学生选课”场景里同一学生在同一学期不能重复选同一门课需要用到多列唯一CREATE TABLE course_selection ( student_id INT NOT NULL, course_id INT NOT NULL, semester VARCHAR(16) NOT NULL, already_paid TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (student_id, course_id, semester) ) ENGINEInnoDB;先插入一行正常数据INSERT INTO course_selection (student_id, course_id, semester) VALUES (1, 101, 2025-S1);再插入同一学生、同一课程、同一学期的记录INSERT INTO course_selection (student_id, course_id, semester) VALUES (1, 101, 2025-S1);预期报错ERROR 1062 (23000): Duplicate entry 1-101-2025-S1 for key course_selection.PRIMARY组合主键的含义是“三列组合整体不能重复”而不是要求每一列都全局唯一。这是一个经常被误解的点student_id可以出现多次course_id也可以出现多次但三列的组合不能重复。再看 UNIQUE 和 PRIMARY KEY 的对比维度PRIMARY KEYUNIQUE表中数量最多一个可以有多个是否允许 NULL不允许多数数据库允许多个 NULL是否自动建索引是是用途行唯一标识业务字段唯一性需要提醒一点不要把唯一性的验证全部依赖应用层。并发场景下两个请求可能同时通过应用层检查然后先后写入数据库。如果没有唯一约束和唯一索引最终就会产生两条重复数据。数据库层的 UNIQUE 约束是并发场景下的兜底措施。3.3 CHECK 约束数据库替你执行业务规则CHECK 约束是“用户定义完整性”的典型实现。它允许你在建表时写一个布尔表达式数据库在插入或更新时逐行判断表达式是否成立。在本文的 orders 表里chk_orders_amount和chk_orders_status就是两个 CHECK 约束。演示INSERT INTO orders (user_id, amount, status) VALUES (1, 0, PENDING);预期报错ERROR 3819 (HY000): Check constraint chk_orders_amount is violated.再演示状态枚举INSERT INTO orders (user_id, amount, status) VALUES (1, 88.00, REFUNDED);预期报错ERROR 3819 (HY000): Check constraint chk_orders_status is violated.CHECK 表达式处理 NULL 时要特别小心。标准 SQL 的逻辑是三值逻辑FALSE 表示违反约束TRUE 和 UNKNOWN 都表示符合约束。也就是说如果 CHECK 表达式的值是 UNKNOWN数据库会认为满足约束。这解释了为什么 users 表的 age CHECK 写成CONSTRAINT chk_users_age CHECK (age IS NULL OR (age 0 AND age 120))如果不写age IS NULL只写age 0 AND age 120那么当 age 为 NULL 时整个表达式结果是 UNKNOWN按标准也会被判定为不违反约束。但为了可读性和避免不同数据库实现差异建议在 CHECK 表达式里显式处理 NULL 分支。CHECK 约束的能力边界也值得知道表达式必须是确定性的不能依赖随机函数、用户变量或当前会话状态。在主流数据库中CHECK 表达式通常不允许包含子查询。CHECK 约束适合表达单行内的简单规则不适合跨表规则跨表规则应交给外键或触发器。如果数据库版本太老导致 CHECK 不生效比如 MySQL 5.7通常有两种替代方式使用触发器或者在应用层中央校验。触发器维护成本高应用层校验可能被绕过因此升级数据库版本往往是最省事的做法。3.4 FOREIGN KEY表与表之间的引用规则外键约束解决引用完整性问题。orders.user_id 引用 users.id这条规则保证只要 orders 里有一条记录的 user_id 值为 Nusers 表里就必须存在 id 为 N 的行。验证合法外键INSERT INTO orders (user_id, amount, status) VALUES (1, 88.00, PENDING);因为 users 表中有 id1这条语句成功。验证非法外键INSERT INTO orders (user_id, amount, status) VALUES (1000, 88.00, PENDING);预期报错ERROR 1452 (23000): Cannot add or update a child row再验证父表删除被引用行。默认情况下试图删除被订单引用的用户会被拒绝DELETE FROM users WHERE id 1;预期报错ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (learn_sql.orders, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id))这是外键的默认保护行为。如果业务上希望在删除用户时同时删除其个人资料可以定义ON DELETE CASCADE。演示CREATE TABLE user_profiles ( id INT NOT NULL, bio VARCHAR(255), PRIMARY KEY (id), CONSTRAINT fk_profiles_user FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE ) ENGINEInnoDB;先插入一个新用户和个人资料然后删除用户观察个人资料是否被级联删除INSERT INTO users (username, email, age) VALUES (dave, daveexample.com, 30); SET dave_id LAST_INSERT_ID(); INSERT INTO user_profiles (id, bio) VALUES (dave_id, this is dave); DELETE FROM users WHERE id dave_id;删除后查询 user_profiles对应行已经不存在。ON DELETE 的常见选项需要理解选项行为RESTRICT有子行引用时父行不允许删除NO ACTION标准 SQL 行为MySQL 中与 RESTRICT 表现一致CASCADE删除父行时同步删除引用它的子行SET NULL删除父行时将子表外键列置为 NULL前提是该列允许 NULLSET DEFAULT删除父行时将子表外键列置为默认值但 InnoDB 不支持生产环境对 CASCADE 要非常谨慎一条 DELETE 可能连带删除大量子表数据。建议在删除前先使用 SELECT 统计受影响子行数量。3.5 通过系统表查询约束元数据约束信息并不是只有通过SHOW CREATE TABLE才能看到。MySQL 的信息模式information_schema中集中保存了与约束相关的元数据适合自动化脚本和排查工具使用。查询某张表的所有约束SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA learn_sql AND TABLE_NAME users;结果会看到 PRIMARY KEY、UNIQUE、CHECK 等类型的约束。查询外键列和引用关系SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA learn_sql AND TABLE_NAME orders;查询外键的级联规则SELECT CONSTRAINT_NAME, DELETE_RULE, UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA learn_sql AND TABLE_NAME orders;查询 CHECK 约束表达式SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CHECK_CLAUSE FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA learn_sql;不同数据库的系统表名称不一样但思路相同数据库关键元数据视图MySQLinformation_schema.TABLE_CONSTRAINTS、KEY_COLUMN_USAGE、REFERENTIAL_CONSTRAINTS、CHECK_CONSTRAINTSSQL Serversys.key_constraints、sys.foreign_keys、sys.check_constraintsPostgreSQLpg_constraint、pg_attribute 联合查询OracleALL_CONSTRAINTS、ALL_CONS_COLUMNS编写数据库巡检脚本时优先使用这些系统表而不是去解析SHOW CREATE TABLE的文本。系统表返回的是结构化字段更适合做差异比对和告警。3.6 约束验证速查表可以用下面这张表作为日常验证约束的速查参考。约束类型正常语句示例违反语句示例预期错误关键字NOT NULLINSERT 时给列赋普通值INSERT 时列写 NULLcannot be nullUNIQUE插入一个新值插入已存在的值Duplicate entryPRIMARY KEY插入不在主键列表中的组合插入重复主键Duplicate entryFOREIGN KEY子表外键值在父表中存在子表外键值在父表中不存在foreign key constraint failsCHECK满足表达式不满足表达式Check constraint ... is violatedDEFAULTINSERT 中省略该列无对应错误观察默认值即可无错误默认值被写入4. 约束报错怎么排查4.1 常见错误码与错误文本速查数据库返回约束错误时通常同时包含数字错误码、SQLSTATE 和错误文本。MySQL 中常见错误码如下错误码SQLSTATE错误文本特征涉及的约束104823000Column xxx cannot be nullNOT NULL1364HY000Field xxx doesnt have a default valueNOT NULL 且无 DEFAULT106223000Duplicate entry ... for key ...UNIQUE 或 PRIMARY KEY3819HY000Check constraint ... is violatedCHECK145223000Cannot add or update a child rowFOREIGN KEY145123000Cannot delete or update a parent rowFOREIGN KEY 的删除限制排查这类错误时不要只盯着错误文本的前半段要重点看三处信息错误码决定去文档搜索方向。约束名或键名例如uk_users_email、fk_orders_user直接告诉你哪个规则被违反。表名和库名确认是否在当前连接的目标库上。4.2 从错误文本倒推问题链路以ERROR 1452 (23000): Cannot add or update a child row为例完整排查顺序是确认错误发生在哪个 SQL 语句是 INSERT 还是 UPDATE。提取约束名fk_orders_user它位于learn_sql.orders表。执行SHOW CREATE TABLE orders\G查看fk_orders_user的定义确认它引用users(id)。检查语句里写入orders.user_id的值。在父表执行SELECT id FROM users WHERE id 值如果查询不到说明外键值不存在。这类问题的修复方式简单直接-- 先查父表是否存在该 id SELECT id FROM users WHERE id 1000; -- 如果不存在可以改成存在的 id或先在父表插入对应用户再以ERROR 3819 (HY000): Check constraint chk_orders_amount is violated.为例从错误文本找到约束名chk_orders_amount。查询约束定义SELECT CONSTRAINT_NAME, CHECK_CLAUSE FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME chk_orders_amount;得到条件amount 0。检查插入命令中的 amount 值是否小于或等于 0。这里容易忽略的是错误文本只会告诉你“违反约束”不会告诉你当前值是多少。把约束条件和当前值放在一起对比是最高效的定位方式。4.3 约束排查五问遇到约束相关问题时可以按五个问题检查是什么约束错误文本里是否有约束名或索引名属于哪张表约束定义在父表还是子表错误指向哪张表条件是什么通过 SHOW CREATE TABLE 或系统表查看约束表达式。当前值是什么把导致失败的 SQL 中的值提取出来。上下文是什么是否在事务中、是否处于批量导入模式、数据库版本是否支持该约束注意排查时先看约束名再看约束所在表不要一上来就去改数据。很多情况下改数据不能解决问题因为约束本身仍然会拒绝下一次非法写入。4.4 约束排查清单排查项检查方式确认标准约束是否被数据库强制执行SELECT VERSION(); 查看数据库版本MySQL 的 CHECK 需要 8.0.16约束定义是否正确SHOW CREATE TABLE 表名与需求中的规则一致存量数据是否满足新约束SELECT COUNT(*) ... WHERE 违反约束的条件新增约束前计数为 0外键两端列类型是否一致SHOW CREATE TABLE 父表、子表类型和字符集兼容外键列是否允许 NULLDESC 表名如果业务允许无主子记录可以设为 NULL当前连接是否在目标库SELECT DATABASE();不匹配会导致找不到表或约束5. 约束的动态管理与工程取舍5.1 用 ALTER TABLE 添加和删除约束业务变化后约束也需要演进。ALTER TABLE 是修改约束的主要手段。给已有表添加 CHECKALTER TABLE users ADD CONSTRAINT chk_users_email_not_empty CHECK (email );给已有表添加 UNIQUEALTER TABLE users ADD CONSTRAINT uk_users_username UNIQUE (username);当前 users 表中 username 都不同因此这条命令可以成功。它表达的业务含义是用户名也不能重复。给已有表添加外键ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id);删除约束的语法按类型区分。MySQL 中 CHECK 约束使用 DROP CHECK外键使用 DROP FOREIGN KEYALTER TABLE orders DROP CHECK chk_orders_status; ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;SQL Server 和 PostgreSQL 都支持统一的 DROP CONSTRAINT 写法-- SQL Server / PostgreSQL 风格 ALTER TABLE orders DROP CONSTRAINT fk_orders_user;在 MySQL 中删除约束前一定要先通过SHOW CREATE TABLE确认约束名。生产环境中约束名可能是由数据库自动生成的删错对象会造成额外麻烦。添加约束前存量数据校验是必须步骤。例如给 users.email 增加非空 CHECK 时先执行SELECT COUNT(*) AS bad_rows FROM users WHERE email IS NULL OR email ;只要 bad_rows 大于 0直接添加约束大概率失败或者即使添加成功也会把不满足条件的历史数据暴露出来。正确的顺序是先清理存量数据再添加约束最后用查询验证。5.2 建表时定义约束与后期增加约束的选择约束在设计期越早定义越能减少脏数据进入表。但仍有一些场景适合后期增加约束场景建议原因新表、新功能建表时定义全部核心约束成本最低无需处理存量数据老表需收紧业务规则后期增加 CHECK / UNIQUE原本数据可能已经违反新规则需先清理大表增加外键低峰期执行评估锁和耗时外键检查可能触发大量索引扫描临时导入历史数据先禁约束或使用导入工具选项导入后再校验避免逐条失败提高导入效率对于临时导入需要特别说明导入完成后必须重新开启约束并执行校验不能把“临时关闭”留在生产环境。5.3 新增约束时的操作规范给生产环境的表增加约束建议按下面顺序操作在测试环境用相同结构和数据量演练一遍。确认数据库版本对目标约束的支持情况。备份表定义和数据至少保留一份可用快照。先检查存量数据是否满足新约束不满足则清洗。在低峰期执行 ALTER TABLE并关注锁等待和 IO。变更后通过信息模式查询约束是否已存在。用典型非法语句测试约束是否真正生效。通知业务方和监控方避免因短暂锁表触发大量超时告警。这一步里最容易出现问题的是大表变更。MySQL 8.0 中很多 DDL 操作使用 INPLACE 算法但仍然会产生短暂的锁表和额外空间开销。生产环境不要在自己开发机上模拟结论要以测试环境和压测结果为准。5.4 学习环境与生产环境的约束行为差异同一个约束语句在学习环境里能跑通不代表生产环境也可以直接执行。常见差异如下维度学习环境生产环境数据量几百行百万行以上ALTER TABLE 耗时毫秒级可能需要分钟级且会锁表约束测试可以随意触发错误需要小流量、回滚方案和监控权限root 直接改最小权限DDL 走审批版本随意升级需要兼容老版本和依赖约束策略全部约束都加考虑外键、CHECK 对性能和维护的影响这不是说生产环境不应该使用约束而是说约束的变更要当成一次发布来对待而不是一条即兴命令。6. 约束相关常见坑与最佳实践6.1 六个高频踩坑点坑 1MySQL 老版本不执行 CHECK 约束。现象建表时写了 CHECK插入非法数据却没有报错。原因MySQL 8.0.16 之前会解析 CHECK 语法但不强制执行。解决升级到 8.0.16 以上或在老版本中使用触发器或依赖应用层校验。上线前一定要实际插入一条非法数据验证约束生效。坑 2UNIQUE 约束允许多个 NULL。现象给某个可空列加了 UNIQUE 约束却发现数据库允许两行完全相同的 NULL。原因多数数据库认为 NULL 不等于 NULL多个未知值不算重复。解决如果业务要求“可空但只能有一个 NULL”需要结合条件索引或触发器实现。坑 3CHECK 条件没有处理 NULL导致规则判断与预期不一致。现象想限制 age 在 0 到 120 之间但 age NULL 的数据仍然插入成功。原因SQL 三值逻辑下NULL 参与表达式会得到 UNKNOWNUNKNOWN 不一定被判定为违反 CHECK。解决在 CHECK 表达式中明确写col IS NULL OR (条件)或者给列加 NOT NULL。坑 4外键列没有按业务查询方式规划索引删除父行时锁等待高。现象删除父表一行生产高并发时出现大量锁等待。原因子表外键列虽然有自动索引但删除路径和高频查询并不完全一致外键检查需要扫描更多数据。解决根据业务查询模式在子表外键列或组合列上显式建索引删除前使用 EXPLAIN 确认执行计划。坑 5增加约束时忽略存量数据。现象ALTER TABLE ADD CHECK 或 ADD UNIQUE 直接失败。原因表中已有数据不满足新约束。解决先执行SELECT COUNT(*)检查存量数据清洗后再加约束。坑 6删除外键约束时写错表。现象在父表 users 上执行删除 fk_orders_user报约束不存在。原因外键约束虽然引用父表但约束定义在子表 orders 上属于子表对象。解决先执行SHOW CREATE TABLE orders\G确认约束所在表再在子表上删除。6.2 建表前约束设计检查清单每次建表都可以按下面清单过一遍避免遗漏关键规则每个表是否有主键是用业务字段还是代理主键哪些列在业务语义上不能为空是否都加了 NOT NULL需要自动填充的列是否设置了 DEFAULT哪些业务字段要求全局唯一是否用 UNIQUE 约束表达数值类型是否有范围要求是否用 DECIMAL 而不是浮点数枚举状态字段是否用 CHECK 限制取值范围需要跨表引用的列是否定义了 FOREIGN KEY列类型和父表是否一致删除父表行时业务上是希望拒绝、级联还是置空约束命名是否可读是否遵循pk_、uk_、fk_、chk_前缀约定数据库版本是否支持计划使用的约束老版本是否有替代方案清单里的每一项都能落成一条实际的 DDL 语句而不是抽象建议。6.3 扩展方向掌握约束的基础使用之后可以从以下几个方面继续深入约束与索引的关系。主键、唯一约束、外键背后都对应索引。理解索引可以减少冗余索引也能解释为什么有些约束会影响查询性能。触发器和延迟约束。数据库版本不支持 CHECK 时可以用触发器模拟PostgreSQL 支持 DEFERRABLE 约束允许在事务提交时再检查约束。这些机制处理复杂业务规则时很有用。数据库迁移工具。使用 Flyway、Liquibase 管理表结构和约束变更把约束改动纳入版本控制避免不同环境执行顺序不一致。约束与数据同步。数据仓库、日志表等写入频率极高的场景可能需要放弃部分约束以换取写入吞吐这就要在应用层和数据质量任务中兜底。这个取舍属于架构设计没有绝对标准。数据库安全。使用参数化查询和最小权限账户在应用层做输入校验不要把 SQL 拼接进查询字符串。约束可以防止脏数据但不能替代安全编码规范。最后回到开头的问题约束不是“给数据库添麻烦”而是把对数据的基本判断放到最合适的位置。数据库每拒绝一次错误数据都是在给上层应用减少一次修复成本。推荐的练习方式是建两张业务表把所有约束写全然后故意用各种错误语句把它“打挂”看错误码是否能快速定位到具体约束遇到约束不生效时先确认数据库版本和表结构再怀疑语法。把这一套流程走熟之后再去思考外键、CHECK 在生产环境中的取舍你会更有判断力。
