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变成NULL
  SET DEFAULT	删父表，子表外键变默认值	删除王五 → 王五的选课记录student_id变成0
  RESTRICT	有子表引用就禁止删除（默认）	赵六有选课成绩 → 不让删赵六
  NO ACTION	同RESTRICT	同上

• ON UPDATE（更新时的级联）

  选项	行为	学生表示例
  CASCADE	改父表主键，自动改子表外键	学生ID从1改成100 → 选课表中的student_id也自动从1变成100
  RESTRICT	有子表引用就禁止更新（默认）	赵六有选课成绩 → 不让改赵六的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_id=0 的记录，且字段有 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);