数据库完整性

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

1、实体完整性

定义实体完整性（主属性不能为空）

在CREAT TABLE中用PRIMARY KEY定义

 1 //将student表中的Sno定义为主属性
 2 CREATE TABLE Student
 3        (Sno CHAR(9) PRIMARY KEY, /*在列级定义主码*/
 4         Sname char(20) NOT NULL,
 5         Sage SMALLINT,
 6         Ssex CHAR(2) ,
 7         Sdept CHAR(20)
 8         );
 9 //将SC表中的Sno，Cno属性定义为主码
10 CREATE TABLE SC
11         (Sno CHAR(9) NOT NULL,
12          Cno CHAR(4) NOT NULL,
13          Grade SMALLINT,
14          PRIMARY KEY(Sno,Cno)     /*定义表级主码*/
15          );

实体完整性的检查和约束

a、检查主码值是否唯一，如果不唯一则拒绝插入和修改（全表扫描，在主码上建立一个索引）

b、检查主码各个属性是否为空，只要有一个为空则拒绝插入修改

2、参照完整性

1 CREATE TABLE SC
2          (Sno CHAR(9) NOT NULL,
3           Cno CHAR(4) NOT NULL,
4           Grade SMALLINT,
5           PRIMARY KEY(Sno,Cno)     /*定义表级主码*/
6           FOREIGN KEY(Sno) REFERENCES Student(Sno),
7           FOREIGN KEY(Cno) REFERENCES Course(Cno)/*在表级定义参照完整性*/
8           );

定义参照完整性

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

a、拒绝执行（NO ACTION）

不允许该操作执行，一般为默认策略

b、级联操作（CASCADE）

当删除或修改被参照表中的一个元组导致与参照表不一致时，删除或修改参照表中所有导致不一致的元组

c、设置为空置

当删除或修改被参照表中的一个元组导致与参照表不一致时，则将参照表中所有造成不一致的元组设置为空值

 1 CREATE TABLE SC
 2          (Sno CHAR(9) NOT NULL,
 3           Cno CHAR(4) NOT NULL,
 4           Grade SMALLINT,
 5           PRIMARY KEY(Sno,Cno)     /*定义表级主码*/
 6           FOREIGN KEY(Sno) REFERENCES Student(Sno),
 7                 ON DELETE CASCADE  /*级联删除*/
 8 
 9           FOREIGN KEY(Cno) REFERENCES Course(Cno)/*在表级定义参照完整性*/
10                 ON DELETE NOT ACTION /*拒绝删除*/
11           );

3、用户定义完整性

属性上约束条件的定义

属性值限制：

a、列值非空（NOT NULL）

b、列值唯一（UNIQUE）

c、检查列值是否满足一个条件表达式（CHECK语句）

1   CREATE TABLE Student
2         (Sno  CHAR(9) PRIMARY KEY，
3          Sname CHAR(8) NOT NULL，                    
4          Ssex  CHAR(2)  CHECK (Ssex IN (‘男’，‘女’) ) ，               
5              /*性别属性Ssex只允许取'男'或'女' */
6          Sage  INT，
7          Sdept  CHAR(20)
8         );

元组上约束条件的定义

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

示例：当学生的性别是男时，其名字不能以Ms.打头。

 1  CREATE TABLE Student
 2          (Sno    CHAR(9)，
 3           Sname  CHAR(8) NOT NULL，
 4           Ssex    CHAR(2)，
 5           Sage   SMALLINT，
 6           Sdept  CHAR(20)，
 7           PRIMARY KEY (Sno)，
 8           CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
 9           /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/
10 
11         )；

完整性约束命名的子句

语法格式：CONSTRAINT<完整性约束条件名>[PRIMARY KEY 短语|FOREIGN KEY短语|CHECK 短语]

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

 1  CREATE TABLE Student
 2       (Sno  NUMERIC(6) 
 3             CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999)，
 4         Sname  CHAR(20) 
 5             CONSTRAINT C2 NOT NULL，
 6         Sage  NUMERIC(3) 
 7             CONSTRAINT C3 CHECK (Sage < 30)，
 8         Ssex  CHAR(2) 
 9             CONSTRAINT C4 CHECK (Ssex IN ( '男'，'女'))，
10             CONSTRAINT StudentKey PRIMARY KEY(Sno)
11       )；

修改表中的完整性限制

可以使用ALTER TABLE语句修改表中的完整性限制。

示例：修改表Student中的约束条件，要求学号改为在900000~999999之间。

1 ALTER TABLE Student
2    DROP CONSTRAINT C1;
3 
4 /*可以先删除原来的约束条件，再增加新的约束条件*/
5 ALTER TABLE Student
6    ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999);

触发器

定义触发器

1 CREATE TRIGGER <触发器名>

2 　　　　{AFTER | BEFORE}<触发器事件>ON <表名>
3 　　　　FOR EACH {ROW|STATEMENT}
4 　　　　[WHEN <触发条件>]
5 　　　　<触发动作体>

详细说明

1) 表的拥有者即创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器

2) 当这个表的数据发生变化时，将激活定义在该表上相应<触发事件>的触发器

3) 触发事件可以是insert、delete或update，也可以是这几个事件的组合，如insert or update等。Update后面还可以有of<触发列，…>，即进一步指明修改哪些列时触发器激活

4) 触发器按照所触发动作的间隔尺寸可以分为行级触发器（for each row）和语句级触发器（for each statement）。

行级触发器和语句级触发器区别：如果使用的是语句级触发器，那么触发器只会在相应的语句（eg：update）执行或者是执行之后执行一次，一般情况下不会这样使用；而对于行级触发器，相应的语句（eg:update）有影响到几行记录，就执行几次触发器。

5) 触发器被激活时，只有当触发条件为真时触发动作体才执行，否则触发动作体不执行。如果省略when触发条件，则触发动作体在触发器激活后立即执行

6) 触发动作体既可以是一个匿名的PL/SQL过程块，也可以是对已创建存储过程的调用。再一个行级触发器能够通过new和old获得update或者insert发生之前的新值和发生值之后的旧值。

示例：定义一个before行级触发器，为教师表定义完整性规则，“教授”的工资不得低于4000元，如果低于4000自动改为4000元

1 CREATE TRIGGER tri_teacher /*在Teacher 表上定义触发器，触发事件是插入或更新*/
2 BEFORE INSERT OR UPDATE ON Teacher
3 REFERENCING NEW row AS newTuple
4 FOR EACH ROW  /*这是行级触发器*/
5 BEGIN  /*定义触发器动作体，这是一个PL/SQL过程块*/
6      IF (newtuple.Job = ‘教授’) AND ( newtuple.sal<4000) /*在过程块中使用插入或更新后的值*/
7             THEN new.sal:=4000;
8 END IF;
9 END；