【MySQL6】约束

一、约束概述1. 什么是约束约束是作用于数据表字段上的规则用于限制存入表中的数据。2. 使用目的保障数据库数据的正确性、有效性、完整性防止脏数据入库。3. 六大约束分类汇总表格约束关键字约束名称作用说明NOT NULL非空约束字段值不能为 NULL必须填写UNIQUE唯一约束字段所有值不能重复允许一个 NULLPRIMARY KEY主键约束等价NOT NULL UNIQUE一张表仅一个主键常搭配自增AUTO_INCREMENTDEFAULT默认约束不主动给字段传值时自动填充预设默认值CHECK检查约束限定字段值满足指定条件MySQL8.0 完整支持FOREIGN KEY外键约束关联两张表保证父子表数据一致性二、前五类单表约束实战非空 / 唯一 / 主键 / 默认 / 检查1. 需求说明user 用户表表格字段类型约束规则idint主键、自增、唯一不为空namevarchar(10)非空 全局唯一ageint年龄范围0 age ≤ 120statuschar(1)不传值默认填充1genderchar(1)无约束可空可重复2. 建表 SQL修正 MySQL 语法问题原代码两处语法坑user是 MySQL 系统关键字表名必须用反引号userSQL 判断逻辑符不能写标准使用ANDsqlCREATE 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检查约束】age121超过上限120插入失败 INSERT INTO user(name,age,status,gender) VALUES (Tom5,121,1,男); -- 7. 合法数据未传入status自动填充默认值1DEFAULT默认约束生效 INSERT INTO user(name,age,gender) VALUES (Tom5,120,男);单表约束总结NOT NULL字段禁止为空UNIQUE字段值全局唯一PRIMARY KEY主键自带非空 唯一搭配AUTO_INCREMENT实现自增 IDDEFAULT省略字段传参时自动填充默认值CHECK限制字段数值区间、格式过滤非法数据。三、外键约束 FOREIGN KEY多表关联约束1. 外键核心概念作用建立两张表的数据关联维护父子表数据一致性、完整性父表主表被引用的表示例dept部门表子表从表包含外键字段、引用父表主键的表示例emp员工表无外键时可随意插入不存在的部门 ID会产生无效脏数据添加外键后会校验关联关系。2. 父子表建表与基础数据父表dept 部门表sqlCREATE 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 用于关联部门 idsqlCREATE 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建表后追加外键sqlALTER 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删除已存在的外键sqlALTER TABLE emp DROP FOREIGN KEY fk_emp_dept_id;4. 外键更新 / 删除五种行为ON UPDATE / ON DELETE表格行为参数规则说明NO ACTION默认行为父表数据被删 / 改子表存在关联数据时禁止操作RESTRICT和 NO ACTION 效果一致MySQL 标准推荐写法CASCADE级联操作父表修改 / 删除同步更新 / 删除子表关联记录SET NULL父表修改 / 删除子表对应外键字段自动置为 NULLSET DEFAULT父表变更时子表外键填充预设默认值极少使用示例 1级联更新、级联删除 CASCADEsqlALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE;效果修改 / 删除 dept 表部门 idemp 表内对应员工dept_id同步修改 / 删除。示例 2置空 SET NULLsqlALTER 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 大类常用约束分为两大模块单表字段约束作用于单张表NOT NULL 非空约束UNIQUE 唯一约束PRIMARY KEY 主键约束DEFAULT 默认约束CHECK 检查约束多表关联约束跨两张表FOREIGN KEY 外键约束搭配 5 种删除更新行为控制父子表联动逻辑