第五章——数据库完整性

数据库完整性

先上图！

概述

数据库的完整性

• 数据的正确性和相容性

数据库的完整性和安全性的区别

数据的完整性

• 防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据
• 防范对象：不合语义的、不正确的数据

数据的安全性

• 保护数据库防止恶意的破坏和非法的存取
• 防范对象：非法用户和非法操作

完整性控制机制的3个组成部分

• 提供定义完整性约束条件的机制
• 提供完整性检查的方法
• 违约处理

实体完整性

单属性构成的码有两种说明方法

• 定义为列级约束条件
• 定义为表级约束条件

多个属性构成的码只有一种说明方法

• 定义为表级约束条件

[例5.2] 将SC表中的Sno，Cno属性组定义为码。

CREATE TABLE SC
     (Sno   CHAR(9)  NOT NULL， 
      Cno  CHAR(4)  NOT NULL，  
      Grade    SMALLINT，
      PRIMARY KEY (Sno，Cno)     /*只能在表级定义主码*/
    );

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

对主码列进行插入或更新操作时，DBMS按照实体完整性规则自动进行检查。

1. 检查主码值是否唯一，如果不唯一则拒绝插入或修改
2. 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

法一：

检查记录中主码值是否唯一的一种方法是进行全表扫描

• 依次判断表中每一条记录的主码值与将插入或修改记录的主码值是否相同

法二：

• 为了避免全表扫描一般会在主码上建立索引

参照完整性

例如 关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是外码。Sno，Cno分别参照引用Student表的主码和Course表的主码

[例5.3] 定义SC中的参照完整性。

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)
  );

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

对被参照表和参照表进行增删改操作时有可能破坏参照完整性，必须进行检查

选修关系SC：

可能破坏参照完整性情况一：

SC表中增加一个元祖，该元祖的Sno属性值在表Student中找不到一个元祖，其Sno属性值与之相等。

可能破坏参照完整性情况二：

修改SC表中的一个元祖，修改后该元祖的Sno属性值在表Student中找不到一个元祖，其Sno属性值与之相等。

可能破坏参照完整性情况三：

从Student表中删除一个元祖，造成SC表中的某些元祖的Sno属性值在表Student 中找不到一个元祖，其Sno属性值与之相等。

可能破坏参照完整性情况四：
修改Student表中一个元祖的Sno属性，造成SC表中的某些元祖的Sno属性值在表Student 中找不到一个元祖，其Sno属性值与之相等。

违约处理：

• 拒绝执行
• 级联操作（删除/修改被参照表的同时删除/修改参照表）
• 设置为空值（置参照表中所有不一致元组置为空值）

[例5.4] 显式说明参照完整性的违约处理示例。

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) 
           ON DELETE CASCADE             /*级联删除SC表中相应的元组*/
           ON UPDATE CASCADE，        /*级联更新SC表中相应的元组*/
    FOREIGN KEY (Cno)  REFERENCES  Course(Cno) 	                    
           ON DELETE NO ACTION 	       /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
           ON UPDATE CASCADE            /*当更新course表中的cno时，级联更新SC表中相应的元组*/
        )；

用户定义的完整性

包括：

• 属性上的约束条件的定义
• 属性上的约束条件检查和违约处理
• 元组上的约束条件的定义
• 元组上的约束条件检查和违约处理

CREATE TABLE时定义

• 列值非空（NOT NULL）
• 列值唯一（UNIQUE）
• 检查列值是否满足一个条件表达式（CHECK）

用CHECK短语指定列值应该满足的条件

[例5.7] Student 表的 Ssex只允许取“男”或“女”

create table student(
sno char(9) primary key,
sname char(8) not null,
ssex char(2) check(ssex in ('男','女')),
--性别只允许取男或女
ssage smallint,
sdept char(20)
);

属性上的约束条件检查和违约处理 :

• 插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足
• 如果不满足则操作被拒绝执行

元组上约束条件的定义

• 在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制
• 同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件

[例5.9] 当学生的性别是男时，其名字不能以Ms.打头

CREATE TABLE Student
         (Sno    CHAR(9)， 
          Sname  CHAR(8) NOT NULL，
          Ssex    CHAR(2)，
          Sage   SMALLINT，
          Sdept  CHAR(20)，
          PRIMARY KEY (Sno)，
          CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')  /*定义元组中Sname和 Ssex属性值间的约束条件*/
        )；
--性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立；
--当性别是男性时，要通过检查则名字不能以 Ms.打头

元组上的约束条件检查和违约处理

• 插入元组或修改属性的值时，RDBMS检查元组上的约束条件是否被满足
• 如果不满足则操作被拒绝执行

完整性约束命名字句

CONSTRAINT 约束，用来对完整性约束条件命名

CONSTRAINT <完整性约束条件名>
［ PRIMARY KEY短语 | FOREIGN KEY短语 | CHECK短语 ］

[例5.10] 建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”

CREATE TABLE Student
         (Sno  NUMERIC(6)
          constraint C1 check(Sno BETWEEN 90000 AND 99999)，
          Sname  CHAR(20)  
          CONSTRAINT C2 NOT NULL，
          Sage  NUMERIC(3)
          CONSTRAINT C3 CHECK (Sage < 30)，
          Ssex  CHAR(2)
          CONSTRAINT C4 CHECK (Ssex IN ( '男'，'女'))，
          CONSTRAINT StudentKey PRIMARY KEY(Sno)
        )；
--在Student表上建立了5个约束条件，包括主码约束以及C1、C2、C3、C4四个列级约束。

[例5.11] 建立教师表Teacher，要求每个教师的应发工资 不低于3000元。（应发工资等于实发工资Sal和扣除项Deduct之和）

      CREATE TABLE TEACHER
         (Eno  NUMERIC(4) PRIMARY KEY,
          Ename  CHAR(10) ,          
          Job  char(8),
          Sal NUMERIC(7,2),
          Deduct NUMERIC(7,2),
          Deptno  NUMERIC(7,2),
          CONSTRAINT EMPFKEY FOREGIN KEY (Deptno) REFERENCES DEPT(Deptno)，
          CONSTRAINT C1 CHECK(Sal + Deduct >= 3000)
        )；

修改表中的完整性限制

**使用ALTER TABLE语句修改表中的完整性限制
**
[例5.13] 修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40

• 先删除原来的约束条件，再增加新的约束条件

alter table student
drop constraint C1;

alter table student
add constraint check(sno between 900000 and 9999999);

        ALTER TABLE Student
        DROP CONSTRAINT C3;

        ALTER TABLE Student
        ADD CONSTRAINT C3 CHECK (Sage < 40)；