关系型数据库设计基础：约束、三大范式、表关系与表设计流程

文章目录一: 数据库约束1. 什么是数据库约束2. 常见约束类型(1) NOT NULL非空约束(2) UNIQUE唯一约束(3) DEFAULT默认约束(4) PRIMARY KEY主键约束(5) FOREIGN KEY外键约束(6) CHECK检查约束二: 表关系1. 一对一2. 一对多1 : N3. 多对多M : N4. 关系总结三: 关系型数据库三大范式第一范式1NF列不可再分第二范式2NF消除部分依赖第三范式3NF消除传递依赖四: 实际表设计流程文章作者:当战神遇到编程文章专栏:MySQL欢迎大家点赞评论收藏⭐文章一: 数据库约束1. 什么是数据库约束数据库约束是对表中数据进行限制的规则用于保证数据的正确性、完整性、一致性。简单理解防止插入错误数据。2. 常见约束类型约束名作用NOT NULL字段不能为空UNIQUE字段值唯一不能重复PRIMARY KEY主键唯一且不能为空FOREIGN KEY外键保证关联关系DEFAULT默认值CHECK检查数据是否满足条件(1) NOT NULL非空约束某列必须有值。nameVARCHAR(10)NOTNULL例如用户名不能为空。(2) UNIQUE唯一约束该列数据不能重复但可以有一个或多个 NULL不同数据库略有区别。phoneVARCHAR(20)UNIQUE例如手机号不能重复。(3) DEFAULT默认约束插入数据时若未赋值则使用默认值。genderVARCHAR(2)DEFAULT男(4) PRIMARY KEY主键约束这一行/这一条记录的身份标识idINTPRIMARYKEY特点主键的值在表中是唯一的不能为空:主键字段的值必须有内容,不能是空白或NULL只能定义一个主键主键可以是单列也可以是多列组合复合主键。-- 复合主键例子-- 规则 只有当 name 和 class 同时相同时才会被判定为重复。CREATETABLEstudent(nameVARCHAR(10),classVARCHAR(20),PRIMARYKEY(name,class));当主键为整数类型时通常建议开启 AUTO_INCREMENT自增属性让数据库自动维护主键值避免手动维护的麻烦。CREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(10),classVARCHAR(20));主键值交给数据库自动分配插入时可以直接写 NULL无需手动指定INSERTINTOstudentVALUES(NULL,张三,1);核心原理与特点1.数据库内部维护了一个计数器默认情况下新值 上一次分配的值 1。2.自增主键在开发中,特别常用,但对于分布式场景无法应对(5) FOREIGN KEY外键约束用于建立父表主表与子表从表之间的引用关系保证数据的参照完整性避免出现无效的 “孤儿数据”。补充说明父表中被关联的列必须带有索引主键自带索引因此外键通常引用父表的主键。-- 1. 切换数据库USEjava118;-- 2. 创建父表班级表CREATETABLEclass(class_idINTPRIMARYKEYAUTO_INCREMENT,class_nameVARCHAR(10));-- 插入班级数据INSERTINTOclassVALUES(NULL,java110),(NULL,java111),(NULL,java112);-- 3. 创建子表学生表class_id 列设置外键引用 class 表的 class_idCREATETABLEstudent(student_idINTPRIMARYKEYAUTO_INCREMENT,student_nameVARCHAR(10),class_idINT,FOREIGNKEY(class_id)REFERENCESclass(class_id));-- 插入学生数据外键值必须在父表中存在INSERTINTOstudentVALUES(NULL,张三,1),(NULL,李四,2),(NULL,王五,3);外键的核心约束规则子表约束插入 / 修改限制如果父表中不存在对应的外键值子表无法插入或修改该外键列。例如不能给学生插入一个不存在的班级 ID如 class_id99。父表约束删除 / 修改限制:如果父表中的数据正被子表引用则该条数据无法直接删除或修改。例如不能直接删除还有学生关联的班级否则会导致学生的 class_id 变成无效数据。补充说明查看外键约束查看子表结构时Key 列若显示为MUL就代表该列设置了外键约束外键实战电商项目中的「逻辑删除」方案在电商等实际项目中外键的 “父表删除限制” 会带来一个典型矛盾我们通常用逻辑删除来解决。1.业务矛盾外键约束 vs 商品下架业务场景商家想把 “衬衫” 下架相当于删除商品但订单表中已经有大量该商品的历史订单。矛盾点外键约束会禁止直接删除商品否则订单表中的商品 ID 会变成无效数据导致历史订单混乱。2. 解决方案逻辑删除核心原理不真正物理删除数据而是通过一个标记位Flag来表示该数据的 “有效 / 失效” 状态让数据 “假死” 而非消失。实现步骤(1) 给父表增加标记列在商品表中新增一个状态字段例如 isOk○ 1 表示商品有效上架状态○ 0 表示商品失效下架状态(2) 下架操作改为更新标记位不再执行 DELETE 删除语句而是用 UPDATE 把标记位改成 0-- 把商品id1的商品标记为下架UPDATE商品表SETisOk0WHERE商品id1;(3) 用户查询过滤失效数据查询商品列表时只返回标记为有效的数据用户感知不到 “已下架商品” 的存在-- 只给用户展示未下架的商品SELECT*FROM商品表WHEREisOk1;3.逻辑删除的核心优势保留历史数据已下架商品的历史订单记录依然完整可查不会出现数据断裂。数据安全可恢复误操作下架后只需把 isOk 改回 1 即可恢复商品无需复杂的数据恢复流程。兼容外键约束无需删除数据自然避开了外键对父表删除的限制同时依然保证了数据的参照完整性。(6) CHECK检查约束限制字段取值范围。例如年龄不能小于6。CREATETABLEstudent(ageINTCHECK(age6),nameVARCHAR(20));二: 表关系表与表之间常见有 四种关系类型一对一1 : 1一对多1 : N多对多M : N没关系1. 一对一含义一张表中的一条记录只对应另一张表中的一条记录。示例用户表 与 用户详情表user用户表idusername1张三user_info详情表user_id身份证号地址1xxx北京说明一个用户只有一份详情一份详情只属于一个用户一对一 (1:1) 实现代码核心要点 在从表的外键上添加 UNIQUE 约束确保一个主表记录只能被引用一次。-- 1. 创建用户主表CREATETABLEuser(idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(20));-- 2. 创建用户详情表CREATETABLEuser_info(-- user_id 既是外键也必须是唯一的从而实现 1:1user_idINTUNIQUE,id_cardVARCHAR(18),addressVARCHAR(100),-- 建立外键关联FOREIGNKEY(user_id)REFERENCESuser(id));2. 一对多1 : N含义一张表中的一条记录对应另一张表中的多条记录。示例学院表 与 学生表college学院表id学院名1计算机学院student学生表id姓名college_id1001张三11002李四1说明一个学院有多个学生一个学生只属于一个学院一对多 (1:N) 实现代码核心要点 外键直接建立在“多”的一方学生表即可不需要加 UNIQUE。-- 1. 创建学院表 (一)CREATETABLEcollege(idINTPRIMARYKEYAUTO_INCREMENT,college_nameVARCHAR(20));-- 2. 创建学生表 (多)CREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENT,student_nameVARCHAR(10),college_idINT,-- 外键列-- 多个学生可以关联同一个学院IDFOREIGNKEY(college_id)REFERENCEScollege(id));3. 多对多M : N含义两张表中的多条记录彼此对应多条记录。示例学生表 与 课程表一个学生可选多门课一门课可被多个学生选studentid姓名courseid课程名student_course中间表student_idcourse_id1001C011001C021002C01多对多 (M:N) 实现代码核心要点 必须创建一个独立的中间表用来存放两个表的主键关联。-- 1. 创建学生表CREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(10));-- 2. 创建课程表CREATETABLEcourse(idVARCHAR(10)PRIMARYKEY,course_nameVARCHAR(20));-- 3. 创建中间表 (选课表)CREATETABLEstudent_course(student_idINT,course_idVARCHAR(10),-- 建议将两个外键设为联合主键防止同一个学生重复选同一门课PRIMARYKEY(student_id,course_id),-- 分别建立外键关联FOREIGNKEY(student_id)REFERENCESstudent(id),FOREIGNKEY(course_id)REFERENCEScourse(id));4. 关系总结关系类型举例实现方式一对一用户-详情外键 唯一约束一对多学院-学生多方加外键多对多学生-课程建中间表三: 关系型数据库三大范式第一范式1NF列不可再分核心要求字段必须具有原子性不可再分每一列只能存一个值不能存多个值。不满足第一范式的例子学号姓名班级名学校信息101张三Java一班清华大学, 北京市海淀区, 010-8888满足第一范式的例子合格的表学号姓名班级名学校名称学校地址学校电话101张三Java一班清华大学北京市海淀区010-8888注意:数据库的“入场券” 第一范式是关系型数据库的最基本要求。如果不满足 1NF则不能被称为真正的关系型数据库。原子性的标准 原子性不是绝对的而是根据业务需求决定的。○ 例如 如果业务只需要显示全名那么 姓名 就是原子的但如果业务需要区分“姓”和“名”那么 姓名 就得拆分为 姓 和 名 两列。第二范式2NF消除部分依赖核心定义在满足第一范式1NF的基础上消除非主键字段对候选键的“部分函数依赖”。1.必须要懂的底层概念 (术语拆解)候选键 (Candidate Key) vs 主键 (Primary Key)○候选键 表中能唯一标识一行数据的列或列组合。○主键 从候选键中选出来的“正职”班长。○联合主键 由多个列共同组成的唯一主键。此时单独的每一列只能叫“候选键”合在一起才叫“主键”。函数依赖○完全函数依赖 必须通过整个主键才能确定某个数据。(2NF要求)○部分函数依赖 只通过主键中的一部分列就能确定某个数据。 ( 2NF禁止)2.案例剖析这张表中使用学号课程名定义联合主键来唯一标识一个学生某门课程的成绩这张表中学生姓名只依赖于学号,不依赖于课程名,学分只依赖于课程名,不依赖于学号,这两个非关键字段都不满足第二范式的核心定义改正:拆成三个表学生表Id学号学生姓名年龄性别110001张三18男210002李四19女310003王五18男课程表Id课程名学分1MySQL502Java603C60成绩表学生Id课程Id成绩11982110031891210022993398只有单列主键的表通常天然满足第二范式。如果表没有满足第二范式可能出现的问题有1.数据冗余学生的姓名年龄学分都重复的出现造成大量的数据冗余2.更新异常假设需要调整MySQL的学分就需要修改所有记录中关于MySQL的记录.如果部分记录更新失败就会出现同一课程学分不一致的情况表现为数据不一致3.插入异常⽬前这样的设计成绩与每⼀⻔课和学⽣都有对应关系也就是说只有学⽣参加选修课程考试取得了成绩才能⽣成⼀条记录。当有⼀⻔新课还没有学⽣参加考试取得成绩之前那么这⻔新课在数据库中是不存在的因为成绩为空时记录没有意义。4.删除异常把毕业学⽣的考试数据全都删除此时课程和学分的信息也会被删除掉有可能导致⼀段时间内数据库⾥没有某⻔课程和学分的信息。第三范式3NF消除传递依赖定义在满⾜第⼆范式的基础上消除非主键字段对非主键字段的依赖消除传递依赖。一、先理解什么叫传递依赖如果存在主键-A-B说明A 依赖主键B 又依赖 A那么 B 就是间接依赖主键这叫传递依赖。举个例子:学号姓名年龄所属学院学院地址学院电话存在传递依赖学号- 所在学院- 学院电话,存在传递依赖的表不满足第三范式。改正只需要拆分成两张表。1.学院表学院编号学院名学院电话学院地址2.学生表学号姓名年龄学院编号这样设计后两张表中的非主键字段都直接依赖各自主键学生表通过外键学院编号与学院表建立关联关系。四: 实际表设计流程1. 需求分析明确存什么理清业务搞清楚系统有哪些实体如用户、商品、订单以及它们之间的关系。确定字段列出每个实体需要记录的信息如用户有姓名、手机号、注册时间。2. 概念设计画 E-R 图识别关系确定实体间是一对一1:1、一对多1:N还是多对多N:N。绘图使用 E-R 图实体-联系图可视化表达防止漏掉关键关联。3.逻辑设计定表结构表转换将实体转为表字段转为列。数据类型为每个字段选择合适的类型如数字用 INT短文本用 VARCHAR时间用 DATETIME。主键设计每张表必须有一个唯一标识通常是自增 ID 或 UUID。4. 范式约束减少冗余遵循三范式3NF○1NF字段不可再分原子性。○2NF非主键字段必须完全依赖主键消除部分依赖。○3NF非主键字段不能相互依赖消除传递依赖如表里存了部门ID就不应再存部门名称。注有时为了性能会适当“反范式”冗余少量字段减少连表。5. 物理优化提升性能索引设计给高频查询的字段如 username、order_sn建立索引。约束设置设置 NOT NULL非空、DEFAULT默认值、UNIQUE唯一。存储引擎选择合适的引擎如 MySQL 默认的 InnoDB。6. 评审与落地产出 SQL设计评审与开发团队核对是否满足业务需求。编写 DDL编写 CREATE TABLE 语句并执行。维护文档更新数据库字典方便后期维护。