4.4.5 参照完整性

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 3k 阅读时长 ≈ 3 分钟

4.4.5 参照完整性

参照完整性是什么

我们常常希望保证在一个关系中给定属性集上的取值也在另一关系的特定属性集的取值中出现。这种情况称为参照完整性(referential integrity)。
正如我们此前在3.2.2节所见,外码可以用作为SQL中create table语句一部分的foreign key子句来声明。
我们用大学数据库SQL DLL定义的一部分来说明外码声明:

1
2
3
4
5
6
7
8
9
10
create table course(
    course_id varchar(8), 
    title varchar(50), 
    dept_name varchar(20),
    credits numeric(2,0) check (credits > 0),
    primary key (course_id),
    foreign key (dept_name)
        references department(dept_name)
        on delete set null
);

course表的定义中有一个声明”foreign key(dept_name) references department“。这个外码声明表示,在每个课程元组中指定的系名必须在department关系中存在。没有这个约束,就可能会为一门课程指定一个不存在的系名。

什么是外码

更一般地,令关系r1和r2的属性集分别为R1和R2,主码分别为K1和K2。如果要求对r2中任意元组t2,均存在r1中元组t1使得t1.K1=t2.α,我们称R2的子集α为参照关系r1中K1的外码(foreigkey)。

参照完整性约束

这种要求称为参照完整性约束(referential-intergrity constraint)或子集依赖( subset dependency)。

子集依赖这种称法是由于上述参照完整性可以表示为这样一种要求:r2中α上的取值集合必须是r1中K1上的取值集合的子集。
请注意,为使参照完整性约束有意义,α和K1必须是相容的属性集;也就是说,
要么α等于K1,
要么它们必须包含相同数目的属性,并且对应属性的类型必须相容(这里我们假设α和K,是有序的)。

参照完整性约束和外码约束的区别

  • 默认情况下,SQL中**外码参照的是被参照表中的主码属性**。
  • 不同于外码约束,参照完整性约束通常不要求K1是r1的主码;其结果是,r1中可能有不止个元组在属性K1上取值相同。

个人总结

  • 参照完整性约束要求有就行了,这可能会有重复.
  • 外码约束不仅要有,还要唯一

references子句

SQL还支持一个可以显式指定被参照关系的属性列表的references子句。

references子句指定的被参照关系的属性列表必须是候选码

然而,这个指定的属性列表必须声明为被参照关系的候选码,要么使用primary key约束,要么使用unique约束。

参照的属性不是候选码 的情况

在更为普遍的参照完整性约束形式中,被参照的属性不必是候选码,这样的形式还不能在SQL中直接声明。SQL标准提供了另外的结构用于实现这样的约束,4.4.7节将描述这样的结构。

外码定义 示例

我们可以使用如下的简写形式作为属性定义的一部分,并声明该属性为外码:

1
2
depr_name varchar(20)
    references department

违反参照完整性约束时的做法

做法1 拒绝

当违反参照完整性约束时,通常的处理是拒绝执行导致完整性破坏的操作(即进行更新操作的事务被回滚)。

做法2 修改参照关系中的元组来恢复完整性

但是,在foreign key子句中可以指明:如果被参照关系上的删除或更新动作违反了约束,那么系统必须采取一些步骤通过修改参照关系中的元组来恢复完整性约束,而不是拒绝这样的动作。考虑在关系course上的如下完整性约束定义:

1
2
3
4
5
6
7
8
create table course(
    ...
    foreign key (dept_name)
        references department(dept_name)
        on delete cascade
        on update cascade,
    ...
);

级联删除

由于有了与外码声明相关联的on delete cascade子句,
如果删除department中的元组导致了此参照完整性约束被违反,则删除并不被系统拒绝,
而是对course关系作”级联”删除,即删除course关系中参照被删除系名的元组。

级联更新

类似地,如果更新被参照字段时违反了约束,则更新操作并不被系统拒绝,
而是将course中参照的元组的dept_name字段也改为新值。

设置为空或默认值

SQL还允许foreign key子句指明除cascade以外的其他动作,例如:

  • 用set null代替上面的cascade,这样如果约束被违反:可将参照域(dept_name)置为null,
  • 用set default代替上面的cascade,这样如果约束被违反:可将参照域(dept_name)置为域的默认值。

级联更新或删除会传递

如果存在涉及多个关系的外码依赖链,则在链一端所做的删除或更新可能传至整个链。

级联更新或级联删除也无法解决 则撤销所有级联操作

如果个级联更新或删除导致的对约束的违反不能通过进一步的级联操作解决,则系统中止该事务。于是,该事务所做的所有改变及级联动作将被撤销。

外码可以为null

  • 空值使得SQL中参照约束的语义复杂化了。外码中的属性允许为null,只要它们没有被声明为not null。
  • 如果给定元组中外码的所有列上均取非空值,则对该元组采用外码约束的通常定义。
  • 如果某外码列为null,则该元组自动被认为满足约束
  • 这样的规定有时不一定是正确的选择,因此SQL也提供一些结构使你可以改变对空值的处理，我们在此不讨论这样的结构。