【课堂笔记】《数据库系统概论（第5版）》-第5章：数据库完整性

数据库完整性

数据库的完整性是指数据的正确性和相容性。

数据库管理系统必须提供以下功能：

1. 提供定义完整性约束条件的机制。
2. 提供完整性检查的方法。
3. 进行违约处理。

5.1 实体完整性

5.1.1 定义实体完整性

关系模型的实体完整性在create table中用primary key定义。

对于单一属性构成的码，可以定义为列级约束条件或者表级约束条件；

对于多个属性构成的码，只能采用表级定义（详见样例）：

create table Student(
    Sno char(9) primary key,
    /*在列级定义主码*/
);

create table Student(
    Sno char(9),
    primary key(Sno)
    /*在表级定义主码*/
);

create table SC(
    Sno char(9) not null,
    Cno char(4) not null,
    primary key(Sno, Cno)
    /*只能在表级定义主码*/
)

5.1.2实体完整性检查和违约处理

在定义了表的主码之后，每当插入新的记录或是修改记录的时候，都会进行如下的检查：

• 检查主码的值是否唯一，如果不唯一则拒绝插入或修改。
• 检查主码的各个属性是否为空，如果是空就拒绝拒绝插入或修改。

5.2参照完整性

5.2.1定义参照完整性

关系模型的参照完整性在create table中使用foreign key定义哪些为外码，用references短语指明这些外码参照哪些表的主码。

样例：

create table SC(
    Sno char(9) not null,
    Cno char(4) not null,
    Grade smallint,

    primary key(Sno, Cno),
    foreign key(Sno) references Student(Sno),
    foreign key(Cno) references Course(Cno),
);

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

对被参照表和参照表进行增删修改有可能会破坏参照完整性，必须进行检查以确保两个表的相容性。

被参照表（如Student）	参照表（如SC）	违约处理
参照完整性被破坏	插入元组	拒绝
参照完整性被破坏	修改外码值	拒绝
删除元组	参照完整性被破坏	拒绝、级联删除、设为空值
修改主码值	参照完整性被破坏	拒绝、级联删除、设为空值

解释：

• 在SC中增加或修改一个元组，该元组的Sno在Student找不到对应的值。
• 在Student中删除或修改一个元组，造成在SC表中有些Sno在Student中找不到对应的值。

上述情况发生时，一般有两种解决方案：

• 拒绝执行相应的命令no action，此为**默认策略*。
• 级联操作cascade，删除或修改参照表中所有导致不一致的元组。
• 设为空值（在定义外码时，应该定义外码是否能为空值，根据实际情况）。

显示说明参照完整性的违约处理示例：

create table SC(
    Sno char(9),
    Cno char(4),
    Grade smallint,

    /*表级实体完整性，Sno和Cno都不能为空*/
    primary key(Sno, Cno),

    /*表级定义参照完整性*/
    foreign key(Sno) references Student(Sno)

        on delete cascade

        on update cascade,
    
    foreign key(Cno) references Course(Cno)

        on delete no action

        on update cascade
);

5.3用户定义的完整性

用户定义的完整性就是指针对某一具体应用的数据必须满足的语义要求。

5.3.1属性上的约束条件

在create table中定义属性的同时，可以同时定义约束条件，包括：

• 列值非空not null
• 列值唯一unique
• 检查列值是否满足一个条件表达式check语句

create table Student(
    Sno char(9) unique not null,
    Sname char(8) not null,
    Ssex char(2) check (Ssex in ('男', '女')),
);

在插入新的元组或者修改元组时，如果不满足属性上的约束条件，操作将被拒绝执行。

5.3.2元组上的约束条件

在create table的同时可以使用check语句定义元组级别的约束条件（即不同的属性之间应该满足的条件）。

/*当性别是男时，姓名不能以Ms.开头（什么鬼样例）*/
create table Student(
    Sname char(8) not null,
    Ssex char(2) not null,
    check(Ssex == '女' or Sname not like 'Ms.%')
);

当插入或是修改元组的时候，元组上的约束条件将被检查，不满足的操作将会被拒绝执行。

5.4完整性约束命名子句

SQL在create table中提供了完整性约束命名子句constraint，可以更加灵活的删除、增加一个约束条件。

1. 完整性约束命名子句

   constraint <完整性约束条件名> <完整性约束条件>;
   

   create table Student(
       Sno numeric(6) 
           constraint c1 check(Sno between 100000 and 999999),
   
       Sname char(20)
           constraint c2 not null,
   
       Sage numeric(3)
           constraint c3 check(Sage < 30),
   
       Ssex char(2) 
           constraint c4 check(Ssex in ('男', '女')),
   
       constraint SK primary key(Sno),
   );
   

2. 修改表中的完整性限制

   可以用alter table修改。

   alter table Student
   drop constraint c3;
   
   alter table Student
   add constraint cc check(Sagr < 40);
   

5.6断言

通过使用create assertion语句来创建断言，可以用来指定更具一般性的约束，可以定义涉及多个表或聚集操作的比较复杂的完整性约束。

在断言创建之后，任何对断言中涉及的操作都会出发关系数据库管理系统对断言的检查，不能使断言为真的操作都会被拒绝执行。

1. 创建断言的语句格式

   create assertion <断言名> <check 子句>;
   

   样例：限制每门课最多60人选课。

   create assertion sample1 
   check (60 >= all(select count(*) from SC group by Cno));
   

2. 删除断言的语句格式

   drop assertion <断言名>;
   

5.7触发器

定义：用户定义在关系表上的一类由事件驱动的特殊过程。

5.7.1定义触发器

触发器又叫做事件-条件-动作规则，当特定的系统事件发生时，对规则的条件进行检查，如果成立则执行动作。

建立触发器的一般格式：

create trigger <触发器名>
{before | after} <触发事件> on <表名>
referencing new|old row as <变量>
for each {row | statement}
[when <触发条件>] <触发动作体>

详细说明：

• 表的拥有者才能在表上创建触发器，数量受关系数据库管理系统限制
• 触发器名可以包含或者不包含模式名
• 统一模式下，触发器名必须是唯一的，并且触发器名和表名要在同一模式下
• 触发器只能建立在基本表上，不能建立在视图上
• 触发事件可以是delete、update、insert等或是这几个事件的组合
• 触发器可以分为行级触发器和语句级触发器
• 如果没有触发条件，动作体在触发器出发后立即执行，如果有触发条件，动作体在条件为真的情况下执行
• 如果触发器的动作体执行失败，激活触发器的事件就会终止执行。

样例：单独记录成绩增加超过10%的学生：

create trigger SC_T
after update of Grade on SC
referencing
oldrow as oldtuple,
newrow as newtuple
for each row
when (newtuple.Grade >= oldtuple.Grade * 1.1)
insert into SC_T (Sno, Cno)
values (oldyuple.Sno, oldtuple.Cno);

5.7.2激活触发器

触发器激活时的执行顺序：

1. 该表上的before触发器
2. 激活触发器的SQL语句
3. 该表上的after触发器

如果存在多个before|after触发器，遵循先创建先执行的原则。

5.7.3删除触发器

drop trigger <触发器名> on <表名>;