数据库系统概论——参照完整性 MySQL实现

数据库系统概论——参照完整性 & MySQL实现

参照完整性定义

若属性F是基本关系R的外码且与基本关系S的主码K对应，则对于R中的每个元组上F的值必须或取空值或者取等于S中某个元组主码值的值

关系模型的参照完整性定义

  • 定义列为外码
foreign key
  • 指明这些外码参照哪些表的主码
references

例子

  • 列级定义

定义course表的外码为dept_name，参照department表的dept_name

create table course(
course_id varchar(7) primary key,
title varchar(50),
credits numeric(2, 0),
dept_name varchar(50) foreign key references department(dept_name));
  • 表级定义

定义course表的外码为dept_name，参照department表的dept_name

create table course(
course_id varchar(7) primary key,
title varchar(50),
credits numeric(2, 0),
dept_name varchar(50),
foreign key(dept_name) references department(dept_name));
  • 多属性表级定义

定义teaches表的外码为course_id, sec_id, semester, year和ID，分别参照section表和instructor表

create table teaches(
ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4, 0),
primary key(ID, course_id, sec_id, semester, year),
foreign key(course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year),
foreign key(ID) references instructor(ID));

参照完整性检查和违约处理

检查时机

一个参照完整性将两个表中的对应元组联系起来，对被参照表和参照表进行增删改时需要检查参照完整性

破坏参照完整性的情况

  • 插入到参照表中的含有某个外码属性值元组在被参照表中找不到对应元组与之对应

  • 修改参照表中的含有某个外码属性值元组导致在被参照表中找不到对应元组与之对应

  • 在被参照表中删除一个元组，导致参照表某个外码属性值的某些元组在被参照表中找不到对应元组与之对应

  • 在被参照表中修改一个元组，导致参照表某个外码属性值的某些元组在被参照表中找不到对应元组与之对应

违约处理策略

  • 拒绝执行

拒绝（NO ACTION）执行将不允许该操作的执行，策略一般设置为默认策略

  • 级联操作

级联（CASCADE）操作将在删除或修改被参照表导致某些元组与参照表不一致时，删除或修改参照表中所有不一致的元组

  • 设置为空值

设置为空值SET-NULL）将将在删除或修改被参照表导致某些元组与参照表不一致时，将参照表中所有造成不一致的元组设置为空值

设置为空值的策略可能会导致违反实体完整性而被拒绝执行，如设置参照表的主码为空会违背实体完整性

违约处理情况

被参照表参照表违约处理
插入元组拒绝
修改外码值拒绝
删除元组拒绝/级联删除/设置为空值
修改元组拒绝/级联修改/设置为空值

示例

显式定义参照完整性的违规处理策略

create table teaches(
ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4, 0),
primary key(ID, course_id, sec_id, semester, year),
foreign key(course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year)
on delete cascade,
foreign key(ID) references instructor(ID)
on delete no action
on update no action
);

鸣谢

数据库系统概论（第5版）
数据库系统概念（原书第6版）

最后

  • 由于博主水平有限，不免有疏漏之处，欢迎读者随时批评指正，以免造成不必要的误解

数据库系统概论——参照完整性 MySQL实现

数据库系统概论——参照完整性 & MySQL实现

参照完整性定义

若属性F是基本关系R的外码且与基本关系S的主码K对应，则对于R中的每个元组上F的值必须或取空值或者取等于S中某个元组主码值的值

关系模型的参照完整性定义

  • 定义列为外码
foreign key
  • 指明这些外码参照哪些表的主码
references

例子

  • 列级定义

定义course表的外码为dept_name，参照department表的dept_name

create table course(
course_id varchar(7) primary key,
title varchar(50),
credits numeric(2, 0),
dept_name varchar(50) foreign key references department(dept_name));
  • 表级定义

定义course表的外码为dept_name，参照department表的dept_name

create table course(
course_id varchar(7) primary key,
title varchar(50),
credits numeric(2, 0),
dept_name varchar(50),
foreign key(dept_name) references department(dept_name));
  • 多属性表级定义

定义teaches表的外码为course_id, sec_id, semester, year和ID，分别参照section表和instructor表

create table teaches(
ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4, 0),
primary key(ID, course_id, sec_id, semester, year),
foreign key(course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year),
foreign key(ID) references instructor(ID));

参照完整性检查和违约处理

检查时机

一个参照完整性将两个表中的对应元组联系起来，对被参照表和参照表进行增删改时需要检查参照完整性

破坏参照完整性的情况

  • 插入到参照表中的含有某个外码属性值元组在被参照表中找不到对应元组与之对应

  • 修改参照表中的含有某个外码属性值元组导致在被参照表中找不到对应元组与之对应

  • 在被参照表中删除一个元组，导致参照表某个外码属性值的某些元组在被参照表中找不到对应元组与之对应

  • 在被参照表中修改一个元组，导致参照表某个外码属性值的某些元组在被参照表中找不到对应元组与之对应

违约处理策略

  • 拒绝执行

拒绝（NO ACTION）执行将不允许该操作的执行，策略一般设置为默认策略

  • 级联操作

级联（CASCADE）操作将在删除或修改被参照表导致某些元组与参照表不一致时，删除或修改参照表中所有不一致的元组

  • 设置为空值

设置为空值SET-NULL）将将在删除或修改被参照表导致某些元组与参照表不一致时，将参照表中所有造成不一致的元组设置为空值

设置为空值的策略可能会导致违反实体完整性而被拒绝执行，如设置参照表的主码为空会违背实体完整性

违约处理情况

被参照表参照表违约处理
插入元组拒绝
修改外码值拒绝
删除元组拒绝/级联删除/设置为空值
修改元组拒绝/级联修改/设置为空值

示例

显式定义参照完整性的违规处理策略

create table teaches(
ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4, 0),
primary key(ID, course_id, sec_id, semester, year),
foreign key(course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year)
on delete cascade,
foreign key(ID) references instructor(ID)
on delete no action
on update no action
);

鸣谢

数据库系统概论（第5版）
数据库系统概念（原书第6版）

最后

  • 由于博主水平有限，不免有疏漏之处，欢迎读者随时批评指正，以免造成不必要的误解