SQL 表关联关系、外键、级联操作 完整技术笔记

一、表之间三种关联关系数据库设计中实体与实体分为一对一、多对一、多对多。1. 一对一场景用户表、用户详情表一张表拆分成两张一一对应。关系规则A表一条记录只能对应B表一条记录。实现方式任意一张表添加外键同时给外键添加 unique 唯一约束保证一条数据只能绑定另表一条记录。-- user 用户主表createtableuser(idintprimarykeyauto_increment,usernamevarchar(20));-- user_info 用户详情表外键user_id加unique实现一对一createtableuser_info(idintprimarykeyauto_increment,addrvarchar(50),user_idintunique,-- unique 关键保证一对一foreignkey(user_id)referencesuser(id));2. 多对一一对多⭐最常用场景学生和班级多个学生属于同一个班级一个班级包含多名学生。关系规则多方学生增加外键引用一方班级的主键。实现在多的那一侧建立外键。-- 一方班级表createtableclasses(class_idintprimarykeyauto_increment,class_namevarchar(30));-- 多方学生表多方放外键 cidcreatetablestudents(stu_numintprimarykey,stu_namevarchar(20),cidint,foreignkey(cid)referencesclasses(class_id));3. 多对多场景学生 - 课程一个学生选多门课一门课被多个学生选。关系规则两张主表不能直接加外键新建一张中间表关联表中间表分别持有两张主表的外键。中间表主键可以使用两个外键做联合主键。-- 学生表createtablestudent(sidintprimarykeyauto_increment,snamevarchar(20));-- 课程表createtablecourse(cidintprimarykeyauto_increment,cnamevarchar(20));-- 中间表 student_course 实现多对多createtablestudent_course(sidint,cidint,-- 设置联合主键避免重复选课primarykey(sid,cid),foreignkey(sid)referencesstudent(sid),foreignkey(cid)referencescourse(cid));二、外键约束基础外键用于维护两张表之间的参照关系子表的外键字段引用父表的主键保证数据引用完整性。父表被引用的表示例classes班级表子表持有外键的表示例students学生表在默认外键约束下如果子表还有记录引用父表主键不允许直接修改、删除父表对应的记录会抛出错误。-- 尝试修改父表主键子表存在引用执行报错updateclassessetclass_id5whereclass_nameJava2104;-- 尝试删除父表记录子表存在引用执行报错deletefromclasseswhereclass_id1;报错信息ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails三、不使用级联手动分步处理没有配置级联规则时若必须修改父表主键需要手动解除关联再操作分为三步将子表关联记录的外键设置为null切断参照关系修改父表的主键字段将子表外键重新更新为父表新主键值-- 步骤1子表对应外键置 null解除和父表的关联updatestudentssetcidnullwherecid1;-- 步骤2修改父表班级主键idupdateclassessetclass_id5whereclass_nameJava2104;-- 步骤3子表数据重新绑定父表新idupdatestudentssetcid5wherestu_numin(20210101,20210102,20210103);缺点流程繁琐需要人工维护两张表的数据容易漏改造成脏数据。四、级联操作级联操作给外键定义联动规则父表主键发生更新、删除时数据库自动处理子表关联数据。级联选项行为说明on update cascade父表主键更新子表外键同步自动更新on delete cascade父表记录删除子表关联记录同步删除on update set null父表主键更新子表外键设置为 null子表字段必须允许为nullon delete set null父表记录删除子表外键设置为 nullrestrict默认子表存在引用禁止修改/删除父表抛出1451错误4.1 给已有表添加带级联的外键已有旧外键需要先删除旧约束再重新创建带级联的外键。-- 删除原来的外键约束altertablestudentsdropforeignkeyFK_STUDENTS_CLASSES;-- 重建外键配置级联更新、级联删除altertablestudentsaddconstraintFK_STUDENTS_CLASSESforeignkey(cid)referencesclasses(class_id)onupdatecascadeondeletecascade;4.2 级联更新演示配置完成修改父表主键子表外键自动跟随变化不需要手动操作子表。-- 修改父表班级idupdateclassessetclass_id5whereclass_id1;-- 查询学生表原来cid1的数据会自动变成cid5select*fromstudents;4.3 级联删除演示执行父表删除语句子表所有关联记录会被一起删除。-- 删除父表班级该班级下全部学生记录同步被删除deletefromclasseswhereclass_id5;五、⚠️ 开发注意事项on delete cascade风险很高删除父表会连带清除子表业务数据极易发生误删事故生产环境谨慎使用。子表外键与父表被引用主键数据类型、长度必须完全一致否则外键创建失败。如果使用set null模式子表的外键字段必须设置允许null语法才可以生效。很多业务项目不在数据库层面建立物理外键改为业务代码逻辑维护表关联关系规避级联带来的数据安全问题。