【 MySQL 】第三节 - 约束实战全攻略

【深度剖析】MySQL 约束实战全攻略从建表到外键行为管理附避坑指南前言在数据库设计中约束Constraint是保障数据一致性、完整性和业务逻辑性的“安全锁”。日前我系统学习了 MySQL 的几种常见约束不仅掌握了基础语法还通过一次次“报错→排查→修复”的过程总结出了一些极其重要但被忽略的细节。一、六大核心约束详解附功能与实际案例1.NOT NULL非空约束 不能为空作用说明NOT NULL该字段不允许插入 NULL 值 案例演示-- 员工姓名不能为空CREATETABLEemp(emp_idINTPRIMARYKEYAUTO_INCREMENT,emp_nameVARCHAR(50)NOTNULL,-- 严禁为空genderCHAR(2),...);应用场景主键、姓名、电话等关键字段必须有值。2.UNIQUE唯一约束 值不能重复作用说明UNIQUE表中某字段的值必须唯一允许一个 NULL 案例演示-- 部门名称必须唯一CREATETABLEdept(dept_idINTPRIMARYKEYAUTO_INCREMENT,dept_nameVARCHAR(50)NOTNULLUNIQUE,-- 不能重复locationVARCHAR(100));注意一个表可以有多个 UNIQUE 约束MySQL 中允许有一个 NULL即允许多个 NULL 值存在其他数据库如 Oracle 严格限制3.PRIMARY KEY主键约束 唯一标识一条记录作用说明PRIMARY KEY唯一 非空 自动创建索引AUTO_INCREMENT自增主键每次插入自动递增 案例演示CREATETABLEdept(dept_idINTPRIMARYKEYAUTO_INCREMENT,-- 自增长主键dept_nameVARCHAR(50)NOTNULLUNIQUE,locationVARCHAR(100));重要原则每张表只能有一个主键推荐使用INTAUTO_INCREMENT作为主键性能最佳4.FOREIGN KEY外键约束 实现表间关联作用说明FOREIGN KEY用于关联另一张表的主键确保引用完整性 案例演示创建员工表时添加外键CREATETABLEemp(emp_idINTPRIMARYKEYAUTO_INCREMENT,emp_nameVARCHAR(50)NOTNULL,genderCHAR(2)CHECK(genderIN(男,女)),jobVARCHAR(50),salaryDECIMAL(10,2),hire_dateDATE,dept_idINT,-- 外键字段-- 外键约束指向 department 表FOREIGNKEY(dept_id)REFERENCESdept(dept_id));关键前提外键字段类型必须与主表主键字段类型一致如均为 INT主表必须存在该主键若不存在创建失败5.DEFAULT默认值约束 插入时自动填充作用说明DEFAULT value如果未指定值则使用默认值 案例演示CREATETABLEemp(emp_idINTPRIMARYKEYAUTO_INCREMENT,emp_nameVARCHAR(50)NOTNULL,statusVARCHAR(20)DEFAULT在职,hire_dateDATEDEFAULTCURDATE());适用场景状态、创建时间、有效标志等常量字段。6.CHECK检查约束 限制数据范围作用说明CHECK (condition)限制字段值必须满足条件 案例演示CREATETABLEemp(genderCHAR(2)CHECK(genderIN(男,女)),ageINTCHECK(age18ANDage65));亮点精准控制业务逻辑如性别只能是“男/女”注意MySQL 在 8.0.16 才真正支持CHECK语法低版本会忽略此约束重点提醒-- 错误示范你会被坑CREATETABLEemp(...genderCHAR(2)CHECK(gender男ORgender女)...);-- MySQL 8.0 以下版本会静默忽略 CHECK二、外键约束详解删除与更新行为策略 外键约束详解从创建到行为管理的全过程实战附真实案例 绝对踩坑点提醒本节内容基于你提供的学习实践代码深入剖析外键的核心机制与千万级开发者易错细节✅ 适合数据库入门 → 中级进阶 → 面试准备重点强调你可能忽略却致命的外键命名冲突和MySQL 不支持 SET DEFAULT问题( 一 )、外键约束的核心概念什么是外键约束外键Foreign Key是一种用于建立两张表之间“引用关系”的约束机制。它确保“子表”中某字段的值必须在“主表”中存在对应的主键值。它的核心目的是维护数据的一致性与完整性防止出现“孤儿记录”。形象比喻就像“员工表”中的dept_id是员工所属部门的“身份证号”必须对得上“部门表”中dept_id的真实编号否则系统不允许插入或删除。 二 、外键的两种创建方式方式1创建表时直接添加外键CREATETABLEemp(emp_idINTPRIMARYKEYAUTO_INCREMENT,emp_nameVARCHAR(50)NOTNULL,dept_idINT,-- 外键约束关键FOREIGNKEY(dept_id)REFERENCESdept(dept_id));✅ 优点建表即规范结构清晰⚠️ 要求主表dept必须已存在且dept_id为主键方式2表创建完成后动态添加外键-- 语法格式ALTERTABLE子表名ADDCONSTRAINT外键名称FOREIGNKEY(外键字段)REFERENCES主表名(主键字段);实战案例为 emp 表添加外键-- 假设 emp 表已存在但尚未添加外键ALTERTABLEempADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdept(dept_id);✅ 成功后外键生效系统将自动阻止非法引用( 三 )、外键删除与修改关键流程必须掌握错误示范你很可能会犯-- 第一次添加成功ALTERTABLEempADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdept(dept_id)ONDELETECASCADE;-- 第二次尝试报错ALTERTABLEempADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdept(dept_id)ONDELETERESTRICT;报错信息ERROR1022(23000): Cant write; duplicate key in table emp根本原因MySQL 禁止同名外键正确做法先删除旧外键再添加新外键-- 步骤1删除已有外键需先知道外键名ALTERTABLEempDROPFOREIGNKEYemp_dept_fk;-- 步骤2重新添加并指定新行为如 RESTRICTALTERTABLEempADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdept(dept_id)ONDELETERESTRICT;小贴士外键名称必须唯一如emp_dept_fk不能重复使用。建议命名规范表名_主表名_fk如emp_dept_fk清晰易懂。( 四 )、外键删除/更新行为策略核心重点外键不仅能绑定关系还能定义“主表被删时子表怎么办”—— 这就是删除/更新行为策略。策略含义示例NO ACTION默认行为阻止删除MySQL 中等同于RESTRICT主表有员工 → 不能删部门RESTRICT与 NO ACTION 作用相同强烈推荐使用删除部门时若有人在那工作则不允许删除CASCADE级联删除 → 主表删子表也删删掉“研发部” → 所有该部门员工被自动删除SET NULL将子表外键置为 NULL要求字段允许 NULL删“研发部” → 员工dept_id变为NULLSET DEFAULT恢复默认值 → ❌MySQL 不支持写了就报错见下文 实战演示五种策略真实效果对比1.NO ACTION/RESTRICT——最推荐安全第一-- 添加删除部门时若员工还存在 → 直接拒绝删除ALTERTABLEempADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdept(dept_id)ONDELETERESTRICT;测试DELETEFROMdeptWHEREdept_id1;-- 报错Cannot delete or update a parent row: a foreign key constraint fails-- 防止了误删数据安全2.CASCADE——一键清空慎用-- 删除主表 → 子表数据自动删除ALTERTABLEempADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdept(dept_id)ONDELETECASCADE; 测试DELETEFROMdeptWHEREdept_id1;-- 执行成功所有 dept_id1 的员工也被删除SELECT*FROMemp;-- 已无研发部员工适用场景父子表为“强关联”关系且删除主表即需清空数据如日志、临时表3.SET NULL——置空外键保留记录前提外键字段必须允许NULL-- 先修改 emp 表让 dept_id 可空ALTERTABLEempMODIFYCOLUMNdept_idINTNULL;-- 添加 SET NULL 策略ALTERTABLEempADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdept(dept_id)ONDELETESETNULL; 测试DELETEFROMdeptWHEREdept_id3;-- 成功员工王五的 dept_id 变为 NULLSELECTemp_id,emp_name,dept_idFROMemp;-- 输出王五 | 3 | NULL优点保留员工记录但离职未归属部门用于“软删除”场景比如人事调动中暂未分配新部门4.SET DEFAULT——最坑MySQL 不支持错误写法ALTERTABLEempADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdept(dept_id)ONDELETESETDEFAULT;报错信息ERROR1064(42000): You have an errorinyourSQLsyntax...重要结论MySQL 完全不支持ON DELETE SET DEFAULT策略这个语法在 Oracle、PostgreSQL 中支持但在 MySQL 中始终无效或报错。你不能再用“恢复默认值”这种思路去设计外键了正确替代方案用SET NULL推荐用RESTRICT最安全不要幻想SET DEFAULT源码-- 创建表-- 部门表CREATETABLEdept(dept_idINTPRIMARYKEYAUTO_INCREMENT,-- 部门ID主键自增dept_nameVARCHAR(50)NOTNULLUNIQUE,-- 部门名称唯一不能为空locationVARCHAR(100)-- 部门位置);-- 创建员工表CREATETABLEemp(emp_idINTPRIMARYKEYAUTO_INCREMENT,-- 员工ID主键自增emp_nameVARCHAR(50)NOTNULL,-- 员工姓名genderCHAR(2)CHECK(genderIN(男,女)),-- 性别ageINT,-- 年龄jobVARCHAR(50),-- 职位salaryDECIMAL(10,2),-- 工资hire_dateDATE,-- 入职日期dept_idINT,-- 所属部门ID-- 外键关联员工表的 dept_id 关联 部门表的 dept_idFOREIGNKEY(dept_id)REFERENCESdept(dept_id));-- 插入数据-- 插入部门INSERTINTOdept(dept_name,location)VALUES(研发部,北京),(销售部,上海),(人事部,广州);-- 插入员工INSERTINTOemp(emp_name,gender,age,job,salary,hire_date,dept_id)VALUES(张三,男,28,Java开发,12000.00,2023-01-10,1),(李四,女,25,销售,8000.00,2023-03-15,2),(王五,男,32,人事经理,15000.00,2022-05-20,3);-- 外键约束-- 概念: 两张表的数据之间建立连接,保证数据的一致性和完整性.-- 外键约束: 创建表时,为某个字段添加外键约束.-- 添加外键约束-- 1、外键可以在创建表时添加 上边旨在创建表的时候添加-- 2、也可以在表创建完成后添加-- alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表名(主表列名);-- 删除外键约束-- 语法alter table 表名 drop foreign key 外键名称;-- 删除外键约束-- 删除/更新行为-- NO ACTION (代码应用):-- 定义外键时默认行为或显式指定 ON DELETE NO ACTION / ON UPDATE NO ACTION。-- 具体应用在创建 emp 表时dept_id 字段默认采用此策略。-- 举例若尝试执行 DELETE FROM dept WHERE dept_id 1;但 emp 表中仍有员工属于该部门-- 数据库将直接抛出外键约束错误阻止删除操作从而保证数据不丢失引用。DELETEfromdeptwheredept_id1;-- CASCADE: 删除/更新行为时如果外键表中有数据则主键表中的数据会级联删除/更新-- 用法是在创建外键约束时或修改外键时通过 ON DELETE CASCADE / ON UPDATE CASCADE 指定的。-- 示例在创建表时添加 FOREIGN KEY (dept_id) REFERENCES dept(dept_id) ON DELETE CASCADE-- 实例演示ALtertableempaddconstraintemp_dept_fkforeignkey(dept_id)referencesdept(dept_id)ondeletecascade;deletefromdeptwheredept_id1;select*fromemp;select*fromdept;-- RESTRICT: 删除/更新行为时如果外键表中有数据则主键表中的数据不能删除/更新-- 用法是在创建外键约束时或修改外键时通过 ON DELETE RESTRICT / ON UPDATE RESTRICT 指定的。-- 示例在创建表时添加 FOREIGN KEY (dept_id) REFERENCES dept(dept_id) ON DELETE RESTRICT-- 实例演示# 注意 MySQL 规则一个表不能有两个同名外键所以第二次直接报错Duplicate foreign key constraint name emp_dept_fk# 为什么上面可以下面不可以# 上面第一次外键不存在 → 可以加 ✅# 下面第二次外键已经存在 → 不能重复加 ❌# 正确做法要改外键规则必须先删旧的# 你想把 ON DELETE CASCADE 改成 ON DELETE RESTRICT必须 先删除旧外键 → 再添加新外键-- 1. 先删除已经存在的外键ALTERTABLEempDROPFOREIGNKEYemp_dept_fk;-- 2. 再添加你想要的 RESTRICT 外键ALTERTABLEempADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdept(dept_id)ONDELETERESTRICT;deletefromdeptwheredept_id2;#删除不了因为外键约束了-- SET NULL: 删除/更新行为时如果外键表中有数据则主键表中的数据会置空-- 用法是在创建外键约束时或修改外键时通过 ON DELETE SET NULL / ON UPDATE SET NULL 指定的。-- 示例在创建表时添加 FOREIGN KEY (dept_id) REFERENCES dept(dept_id) ON DELETE SET NULL-- 示例ALTERTABLEempDROPFOREIGNKEYemp_dept_fk;ALTERTABLEempADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdept(dept_id)ONDELETESETNULL;deletefromdeptwheredept_id3;-- SET DEFAULT: 删除/更新行为时如果外键表中有数据则主键表中的数据会恢复为默认值 ❌ MySQL - 不支持 SET DEFAULT-- 用法是在创建外键约束时或修改外键时通过 ON DELETE SET DEFAULT / ON UPDATE SET DEFAULT 指定的。延伸学习MySQL 官方文档 - Foreign Key Constraints推荐使用 DBeaver / Navicat 看图理解表关系可搭配EXPLAIN查看 SQL 执行计划优化外键性能