第五章数据库完整性

2024-01-19 10:14:34

1. 数据库完整性

数据库的完整性包括

数据的正确性
数据的相容性

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

数据的完整性

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

数据的安全性

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

维护数据库完整性RDBMS 需要提供的操作

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

2. 实体完整性

2.1 实体完整性定义

1 /*列级定义主码*/
2 CREATE TABLE Student(
3     Sno CHAR(9) PRIMARY KEY,
4     Sname CHAR(20) NOT NULL,
5     Ssex CHAR(2),
6     Sage SMALLINT,
7     Sdept CHAR(20)
8 );

1  /*表级定义主码*/
2 CREATE TABLE SC(
3      Sno CHAR(9) NOT NULL,
4      Cno CHAR(4) NOT NULL,
5      Grade SMALLINT,
6      PRIMARY KEY(Sno,Cno)
7  );

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

插入或对主码列进行更新操作时，关系数据库管理系统按照实体完整性规则自动进行检查。

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

检查方法

全表扫描依次判断表中每一条记录的主码值与将插入记录上的主码值（或者修改的新主码值）是否相同（耗时严重
B+树索引

2.参照完整性

2.1 定义参照完整性

 1 /*关系SC中（Sno，Cno）是主码。Sno，Cno分别参照Student表
 2 的主码和Course表的主码 、定义SC中的参照完整性*/
 3 CREATE TABLE SC
 4     (  Sno    CHAR(9)  NOT NULL, 
 5     Cno     CHAR(4)  NOT NULL,  
 6     Grade    SMALLINT,
 7     PRIMARY KEY (Sno, Cno),   /*在表级定义实体完整性*/
 8         FOREIGN KEY (Sno) REFERENCES Student(Sno),  
 9             /*在表级定义参照完整性*/
10         FOREIGN KEY (Cno) REFERENCES Course(Cno)    
11             /*在表级定义参照完整性*/
12 );

2.2 定义参照完整性违约处理

 1  CREATE TABLE SC
 2     (  
 3     Sno   CHAR(9)  NOT NULL，
 4     Cno   CHAR(4)  NOT NULL，
 5     Grade  SMALLINT,
 6     PRIMARY KEY(Sno,Cno)，             
 7     FOREIGN KEY (Sno) REFERENCES Student(Sno) 
 8         ON DELETE CASCADE        /*级联删除SC表中相应的元组*/
 9         ON UPDATE CASCADE,      /*级联更新SC表中相应的元组*/
10     FOREIGN KEY (Cno) REFERENCES Course(Cno)                        
11         ON DELETE NO ACTION     
12     /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
13         ON UPDATE CASCADE   
14     /*当更新course表中的cno时，级联更新SC表中相应的元组*/
15     );

3.用户定义的完整性

3.1 属性上的约束条件

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

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

插入元组或修改属性的值时，关系数据库管理系统检查属性上的约束条件是否被满足
如果不满足则操作被拒绝执行

1 CREATE TABLE  SC
2         (  Sno     CHAR(9) ,
3         Cno    CHAR(4),
4         Grade   SMALLINT CHECK (Grade>=0 AND Grade <=100)，
5         /*Grade取值范围是0到100*/
6         PRIMARY KEY (Sno,Cno),
7         FOREIGN KEY (Sno) REFERENCES Student(Sno),
8         FOREIGN KEY (Cno) REFERENCES Course(Cno)
9         );

3.2 元祖上定义的约束条件

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

插入元组或修改属性的值时，关系数据库管理系统检查元组上的约束条件是否被满足
如果不满足则操作被拒绝执行

 1 /*当学生的性别是男时，其名字不能以Ms.打头。*/
 2 CREATE TABLE Student
 3         (  Sno    CHAR(9), 
 4         Sname  CHAR(8) NOT NULL，
 5         Ssex    CHAR(2),
 6         Sage   SMALLINT,
 7         Sdept  CHAR(20),
 8         PRIMARY KEY (Sno),
 9         CHECK (Ssex=‘女‘ OR Sname NOT LIKE ‘Ms.%‘)
10                 /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/
11         )

4. 完整性约束命名子句

4.1 建立完整性约束

 1 /*建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。*/
 2     CREATE TABLE Student
 3       (   Sno  NUMERIC(6)
 4           CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
 5           Sname  CHAR(20)  
 6           CONSTRAINT C2 NOT NULL,
 7           Sage  NUMERIC(3)
 8           CONSTRAINT C3 CHECK (Sage < 30),
 9           Ssex  CHAR(2)
10           CONSTRAINT C4 CHECK (Ssex IN ( ‘男’,‘女‘)),
11           CONSTRAINT StudentKey PRIMARY KEY(Sno)
12         );
13 /*在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。*

4.2 修改完整性约束

 1 /*修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40
 2 可以先删除原来的约束条件，再增加新的约束条件*/
 3 ALTER TABLE Student
 4 DROP CONSTRAINT C1;
 5 ALTER TABLE Student
 6 ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999),
 7 ALTER TABLE Student
 8 DROP CONSTRAINT C3;
 9 ALTER TABLE Student
10 ADD CONSTRAINT C3 CHECK(Sage < 40);

5.域中的完整性限制

6.断言

6.1 定义断言

可以使用 CREATE ASSERTION语句，通过声明性断言来指定更具一般性的约束。

可以定义涉及多个表的或聚集操作的比较复杂的完整性约束。

断言创建以后，任何使断言不为真值的操作都会被拒绝执行

1 /*限制数据库课程最多60名学生选修*/
2 CREATE ASSERTION ASSE_SC_DB_NUM
3 CHECK (
4     60 >= (select count(*)
5     /*此断言的谓词涉及聚集操作count的SQL语句*/
6     From Course,SC
7     Where SC.Cno=Course.Cno and Course.Cname =‘数据库‘)
8 );

 1 /*限制每一门课程最多60名学生选修*/
 2 CREATE ASSERTION ASSE_SC_CNUM1
 3     CHECK(60 >= ALL (
 4         SELECT count(*)                                      
 5         FROM     SC 
 6         GROUP by cno
 7         )
 8 );
 9 /*此断言的谓词，涉及聚集操作count 和分组函数group by的SQL语句*/
10

 1 /*限制每个学期每一门课程最多60名学生选修
 2 首先需要修改SC表的模式，增加一个“学期（TERM）”属性*/
 3 ALTER TABLE SC ADD TERM DATE;
 4 /*然后，定义断言：*/
 5 CREATE ASSERTION ASSE_SC_CNUM2
 6      CHECK(60 >= ALL (
 7          SELECT count(*)                                   
 8          FROM SC
 9          GROUP by cno,TERM)
10 );

　　6.2 删除断言

DROP ASSERTION 断言名;

如果断言很复杂，则系统在检测和维护断言的开销较高，这是在使用断言时应该注意的

7.触发器

　7.1 定义触发器

触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程

触发器保存在数据库服务器中
任何用户对表的增、删、改操作均由服务器自动激活相应的触发器
触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力

1）表的拥有者才可以在表上创建触发器

2）触发器名

触发器名可以包含模式名，也可以不包含模式名
同一模式下，触发器名必须是唯一的
触发器名和表名必须在同一模式下

3）表名

触发器只能定义在基本表上，不能定义在视图上

当基本表的数据发生变化时，将激活定义在该表上相应触发事件的触发器

4）触发事件

触发事件可以是INSERT、DELETE或UPDATE 也可以是这几个事件的组合

还可以UPDATE OF<触发列，...>，即进一步指明修改哪些列时激活触发器

AFTER/BEFORE是触发的时机 AFTER表示在触发事件的操作执行之后激活触发器 BEFORE表示在触发事件的操作执行之前激活触发器

（5）触发器类型行级触发器（FOR EACH ROW）语句级触发器（FOR EACH STATEMENT）

例如,在例5.11的TEACHER表上创建一个AFTER UPDATE触发器，触发事件是UPDATE语句：

UPDATE TEACHER SET Deptno=5;

假设表TEACHER有1000行如果是语句级触发器，那么执行完该语句后，触发动作只发生一次

如果是行级触发器，触发动作将执行1000次

（6）触发条件

触发器被激活时，只有当触发条件为真时触发动作体才执行;否则触发动作体不执行。

如果省略WHEN触发条件，则触发动作体在触发器激活后立即执行

（7）触发动作体

触发动作体可以是一个匿名PL/SQL过程块也可以是对已创建存储过程的调用
如果是行级触发器，用户可以在过程体中使用NEW和OLD引用事件之后的新值和事件之前的旧值
如果是语句级触发器，则不能在触发动作体中使用NEW或OLD进行引用
如果触发动作体执行失败，激活触发器的事件就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化

 1 /*当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：
 2 SC_U（Sno,Cno,Oldgrade,Newgrade）
 3 其中Oldgrade是修改前的分数，Newgrade是修改后的分数。*/
 4 CREATE TRIGGER  SC_T        
 5 AFTER UPDATE OF Grade ON SC
 6 REFERENCING
 7     OLD row  AS  OldTuple,
 8     NEW row AS  NewTuple
 9     FOR EACH ROW     
10     WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade)
11         INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)  
12 VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

 1 /*将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。*/
 2 CREATE TRIGGER Student_Count
 3 AFTER INSERT ON Student  
 4 /*指明触发器激活的时间是在执行INSERT后*/     
 5     REFERENCING
 6          NEW TABLE AS DELTA
 7     FOR EACH STATEMENT  
 8            /*语句级触发器, 即执行完INSERT语句后下面的触发动作体才执行一次*/
 9         INSERT INTO StudentInsertLog (Numbers)
10            SELECT COUNT(*) FROM DELTA

 1 /*定义一个BEFORE行级触发器，
 2 为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。*/
 3 CREATE TRIGGER Insert_Or_Update_Sal 
 4 BEFORE INSERT OR UPDATE ON Teacher  
 5 /*触发事件是插入或更新操作*/
 6     REFERENCING NEWROW AS new
 7     FOR EACH ROW        /*行级触发器*/
 8         BEGIN                             /*定义触发动作体，是PL/SQL过程块*/
 9     IF (new.Job=‘教授‘) AND (new.Sal < 4000) 
10     THEN  new.Sal :=4000;                
11 END IF;

　7.2 激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行

一个数据表上可能定义了多个触发器，遵循如下的执行顺序:

（1）执行该表上的BEFORE触发器;

（2）激活触发器的SQL语句;

（3）执行该表上的AFTER触发器。

7.3 删除触发器

删除触发器的SQL语法： DROP TRIGGER <触发器名> ON <表名>;

触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。

第五章数据库完整性

码农公寓

相关文章