OpenTenBase的外键（Foreign Key）和外键级联

外键Foreign Key作用外键是用来在两个表之间建立连接的一种约束。它指向另一个表的主键确保数据之间的引用完整性。其目的是为了杜绝孤儿记录例如一条选课记录指向一个不存在的学生ID语法格式-- 1.列级约束直接写在字段后面CREATETABLE子表名(列名 数据类型REFERENCES父表名(父表列),其他列...);-- 2. 表级约束写在所有字段后面CREATETABLE子表名(列名1数据类型,列名2数据类型,FOREIGNKEY(子表列名)REFERENCES父表名(父表列名));-- 3. 自定义约束名sqlCREATETABLE子表名(列名1数据类型,列名2数据类型,CONSTRAINT约束名FOREIGNKEY(子表列名)REFERENCES父表名(父表列名)举个例子父表为学生表子表为选课表列级约束直接写在字段后面-- 父表学生表CREATETABLEstudent(student_id BIGSERIALPRIMARYKEY,student_noVARCHAR(20)UNIQUE,student_nameVARCHAR(50)NOTNULL);-- 子表选课表列级外键约束CREATETABLEenrollment(enrollment_id BIGSERIALPRIMARYKEY,student_idBIGINTNOTNULLREFERENCESstudent(student_id),-- 列级外键section_idBIGINTNOTNULL,scoreNUMERIC(5,2));表级约束写在所有字段后面-- 子表选课表表级外键约束CREATETABLEenrollment(enrollment_id BIGSERIALPRIMARYKEY,student_idBIGINTNOTNULL,section_idBIGINTNOTNULL,scoreNUMERIC(5,2),FOREIGNKEY(student_id)REFERENCESstudent(student_id)-- 表级外键);自定义约束名表级 CONSTRAINT-子表选课表自定义约束名CREATETABLEenrollment(enrollment_id BIGSERIALPRIMARYKEY,student_idBIGINTNOTNULL,section_idBIGINTNOTNULL,scoreNUMERIC(5,2),CONSTRAINTfk_enrollment_studentFOREIGNKEY(student_id)REFERENCESstudent(student_id)-- 自定义名称);外键REFERENCES student(student_id)其核心作用是确保enrollment表中的每条选课记录都必须对应一个在student表中真实存在的学生。简单来说不能给不存在的学生记录选课成绩。外键级联作用当父表被引用表的数据发生删除或更新时子表包含外键的表中的数据可以自动执行相应的操作级联的两种类型ON DELETE删除时的级联选项行为学生表示例CASCADE删父表自动删子表删除张三 → 自动删除张三的所有选课成绩SET NULL删父表子表外键变NULL删除李四 → 李四的选课记录还在但student_id变成NULLSET DEFAULT删父表子表外键变默认值删除王五 → 王五的选课记录student_id变成0RESTRICT有子表引用就禁止删除默认赵六有选课成绩 → 不让删赵六NO ACTION同RESTRICT同上ON UPDATE更新时的级联选项行为学生表示例CASCADE改父表主键自动改子表外键学生ID从1改成100 → 选课表中的student_id也自动从1变成100RESTRICT有子表引用就禁止更新默认赵六有选课成绩 → 不让改赵六的ID举个例子-- 父表学生表CREATETABLEstudent(student_id BIGSERIALPRIMARYKEY,student_noVARCHAR(20)UNIQUE,student_nameVARCHAR(50)NOTNULL);-- 1. ON DELETE CASCADE级联删除-- 作用删除学生时自动删除该学生的所有选课记录-- 业务场景学生退学成绩也不需要保留了CREATETABLEenrollment_cascade(enrollment_id BIGSERIALPRIMARYKEY,student_idBIGINTNOTNULL,section_idBIGINTNOTNULL,scoreNUMERIC(5,2),FOREIGNKEY(student_id)REFERENCESstudent(student_id)ONDELETECASCADE);-- 2. ON DELETE SET NULL设为空值-- 作用删除学生时保留选课记录但 student_id 变为 NULL-- 业务场景学生毕业后匿名化保留成绩用于统计分析-- 注意student_id 字段不能有 NOT NULL 约束CREATETABLEenrollment_set_null(enrollment_id BIGSERIALPRIMARYKEY,student_idBIGINT,-- 必须允许 NULLsection_idBIGINTNOTNULL,scoreNUMERIC(5,2),FOREIGNKEY(student_id)REFERENCESstudent(student_id)ONDELETESETNULL);-- 3. ON DELETE SET DEFAULT设为默认值-- 作用删除学生时保留选课记录但 student_id 变为默认值0-- 业务场景需要占位符不能为 NULL且能关联到已删除学生记录-- 前提必须存在 student_id0 的记录且字段有 DEFAULT 0CREATETABLEenrollment_set_default(enrollment_id BIGSERIALPRIMARYKEY,student_idBIGINTDEFAULT0,-- 设置默认值section_idBIGINTNOTNULL,scoreNUMERIC(5,2),FOREIGNKEY(student_id)REFERENCESstudent(student_id)ONDELETESETDEFAULT);-- 4. ON DELETE RESTRICT限制删除默认行为-- 作用如果学生有选课记录禁止删除该学生-- 业务场景保护重要数据防止误删有成绩的学生CREATETABLEenrollment_restrict(enrollment_id BIGSERIALPRIMARYKEY,student_idBIGINTNOTNULL,section_idBIGINTNOTNULL,scoreNUMERIC(5,2),FOREIGNKEY(student_id)REFERENCESstudent(student_id)ONDELETERESTRICT);-- 5. ON UPDATE CASCADE级联更新-- 作用更新学生的 student_id 时自动更新选课表中的 student_id-- 业务场景学生ID需要重新编号时自动同步所有关联表CREATETABLEenrollment_update(enrollment_id BIGSERIALPRIMARYKEY,student_idBIGINTNOTNULL,section_idBIGINTNOTNULL,scoreNUMERIC(5,2),FOREIGNKEY(student_id)REFERENCESstudent(student_id)ONUPDATECASCADE);