【MySQL6】约束

一、约束概述

1. 什么是约束

约束是作用于数据表字段上的规则，用于限制存入表中的数据。

2. 使用目的

保障数据库数据的正确性、有效性、完整性，防止脏数据入库。

3. 六大约束分类汇总

表格

二、前五类单表约束实战（非空 / 唯一 / 主键 / 默认 / 检查）

1. 需求说明（user 用户表）

表格

2. 建表 SQL（修正 MySQL 语法问题）

原代码两处语法坑：

1. user是 MySQL 系统关键字，表名必须用反引号`user`
2. SQL 判断逻辑符不能写&&，标准使用AND

sql

CREATE TABLE `user`( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID', name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名', age INT CHECK ( age > 0 AND age <= 120 ) COMMENT '年龄', status CHAR(1) DEFAULT '1' COMMENT '状态', gender CHAR(1) COMMENT '性别' ) COMMENT = '用户信息表';

3. 插入测试数据 + 约束报错解析

sql

-- 1. 合法数据，插入成功 INSERT INTO `user`(name,age,status,gender) VALUES ('Tom1',19,'1','男'),('Tom2',25,'0','男'); INSERT INTO `user`(name,age,status,gender) VALUES ('Tom3',19,'1','男'); -- 2. 违反【NOT NULL非空约束】：name传入null，插入失败 INSERT INTO `user`(name,age,status,gender) VALUES (null,19,'1','男'); -- 3. 违反【UNIQUE唯一约束】：Tom3姓名已存在，重复插入失败 INSERT INTO `user`(name,age,status,gender) VALUES ('Tom3',19,'1','男'); -- 4. 合法数据，年龄80符合0~120范围 INSERT INTO `user`(name,age,status,gender) VALUES ('Tom4',80,'1','男'); -- 5. 违反【CHECK检查约束】：age=-1小于0，插入失败 INSERT INTO `user`(name,age,status,gender) VALUES ('Tom5',-1,'1','男'); -- 6. 违反【CHECK检查约束】：age=121超过上限120，插入失败 INSERT INTO `user`(name,age,status,gender) VALUES ('Tom5',121,'1','男'); -- 7. 合法数据，未传入status，自动填充默认值'1'（DEFAULT默认约束生效） INSERT INTO `user`(name,age,gender) VALUES ('Tom5',120,'男');

单表约束总结

1. NOT NULL：字段禁止为空；
2. UNIQUE：字段值全局唯一；
3. PRIMARY KEY主键：自带非空 + 唯一，搭配AUTO_INCREMENT实现自增 ID；
4. DEFAULT：省略字段传参时自动填充默认值；
5. CHECK：限制字段数值区间、格式，过滤非法数据。

三、外键约束 FOREIGN KEY（多表关联约束）

1. 外键核心概念

1. 作用：建立两张表的数据关联，维护父子表数据一致性、完整性；
2. 父表（主表）：被引用的表（示例：dept部门表）；
3. 子表（从表）：包含外键字段、引用父表主键的表（示例：emp员工表）；
4. 无外键时，可随意插入不存在的部门 ID，会产生无效脏数据；添加外键后会校验关联关系。

2. 父子表建表与基础数据

父表：dept 部门表

sql

CREATE TABLE dept( id INT AUTO_INCREMENT COMMENT '部门ID' PRIMARY KEY, name VARCHAR(50) NOT NULL COMMENT '部门名称' ) COMMENT = '部门父表'; -- 插入部门数据 INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办');

子表：emp 员工表（dept_id 用于关联部门 id）

sql

CREATE TABLE emp( id INT AUTO_INCREMENT COMMENT '员工ID' PRIMARY KEY, name VARCHAR(50) NOT NULL COMMENT '姓名', age INT COMMENT '年龄', job VARCHAR(20) COMMENT '职位', salary INT COMMENT '薪资', entrydate DATE COMMENT '入职时间', managerid INT COMMENT '直属领导ID', dept_id INT COMMENT '关联部门ID（外键字段）' ) COMMENT = '员工子表'; -- 插入员工测试数据 INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5), (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1), (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1), (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1), (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1), (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1);

3. 外键基础语法

（1）建表后追加外键

sql

ALTER TABLE 子表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (子表外键字段) REFERENCES 父表(父表主键字段);

实战添加外键：

sql

-- 给emp表dept_id添加外键，关联dept表id ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id);

（2）删除已存在的外键

sql

ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept_id;

4. 外键更新 / 删除五种行为（ON UPDATE / ON DELETE）

表格

示例 1：级联更新、级联删除 CASCADE

sql

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE;

效果：修改 / 删除 dept 表部门 id，emp 表内对应员工dept_id同步修改 / 删除。

示例 2：置空 SET NULL

sql

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id) ON UPDATE SET NULL ON DELETE SET NULL;

效果：删除部门后，该部门员工的dept_id自动变为 NULL。

四、全文知识点总结

MySQL 共 6 大类常用约束，分为两大模块：

1. 单表字段约束（作用于单张表）
   • NOT NULL 非空约束
   • UNIQUE 唯一约束
   • PRIMARY KEY 主键约束
   • DEFAULT 默认约束
   • CHECK 检查约束
2. 多表关联约束（跨两张表）
   • FOREIGN KEY 外键约束，搭配 5 种删除更新行为控制父子表联动逻辑