《数据库系统概论》- 课本理论整理05之数据库完整性

数据库的完整性（integrity)是指数据的【正确性（correctness）】和【相容性（compat-ability)】

案例

学生的学号必须唯一
性别只能是男或女
本科学生年龄的取值范围为14~50的整数
学生所选的课程必须是学校开设的课程
学生所在的院系必须是学校已经成立的院系。

为了保证DB的完整性，DBMS必须实现如下功能：

定义实体完整性(entity integrity)

• -> 列级约束条件
• -> 表级约束条件

对单属性构成的码

[案例：将Student表的Sno属性定义为码]

CREATE TABLE Student
(
  Sno CHAR(9) PRIMARY KEY, /*列级约束条件*/  
  Sname CHAR(20) NOT NULL,
  Sage SMALLINT
  //, PRIMARY KEY(Sno) /*表级约束条件*/
);

对多属性构成的码

[案例：将SC表中的Sno、Cno属性组定义为码]

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

实体完整性检查和违约处理(entity integrity check and default handling)

实体完整性规则的检查：
(1)主码值是否唯一，若不唯一则拒绝插入或修改.
(2)主码值是否为空，若为空格则决绝插入或修改.
实现完整性规则检查方法两种
第一种是【全表扫描】： 依次判断表中每一条记录的主码值与将插入记录的主码值判断是否相同若相同决绝插入。
第二种是【构建索引】： 由于全表扫描过于耗时所以要避免全表扫描通过在主码上自动建立一个索引。

通过B+树索引查找基本表中是否已经存在新的主码值将大大提高效率

参照完整性（referential integrity)

• FOREIGN KEY定义哪些列为外码
• REFERENCES短语指明这些外码参加哪些表的主码。
  [案例：关系SC中一个元组即一个学生学修某门课程的成绩,(Sno, Cno)是主码Sno,Cno分别参照引用Student表和Course表的主码]

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), /*表级定义参照完整性*/
);

用户定义完整性（User-defined Integrity)

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

属性上的约束条件

在CREATE TABLE中定义属性的同时确定属性的约束条件。

• 列值非空(NOT NULL)
• 列值唯一(UNIQUE)
• 检查列值是否满足一个条件表达式(CHECK)
  【案例：定义一个员工表】

CREATE TABLE emp
(
  emp_no INT,
  emp_nickname UNIQUE NOT NULL, /*UNIQUE + NOT NULL = PRIMARY KEY 即列值唯一不为空*
  gender CHAR(2) CHECK(gender IN ('男', '女')),
  job_level SMALLINT CHECK(credit > 0 AND credit < 10),
  PRIMARY KEY(emp_no) /*表级实体完整性*/
);

当向表中插入元组或修改属性的值时，DBMS将检查属性上的约束是否被满足否则拒绝执行。

元组上的约束条件

在CREATE TABLE中可以用CHECK短语定义元组上的约束条件(constraint condition)即元组级别的限制。
【案例：当学生的性别是男时，其名字不能以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两个属性值之间的约束条件*/
);

当性别为男性的时候则要求名字不能以Ms.大头
若Ssex='男',条件若想为真则 Sname NOT LIKE 'Ms.%' 必须为真

当插入元组时不满足元组级别约束条件则拒绝插入。

完整性约束名子句

给约束定义别名利用CONSTRAINT
格式:

CONSTRAINT <完整性约束条件名><完整性约束条件>

<完整性约束条件>包括UNIQUE、NOT NULL、PRIMARY KEY、FOREIGN KEY、CHECK短语等。
[案例 建立学生登记表Student,要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”]

CREATE TABLE Student
(
  Sno NUMERIC(6)
    CONSTRAINT c_1 CHECK(Sno BETWEEN 90000 AND 99999),
  Sname CHAR(20)
    CONSTRAINT c_2 NOT NULL,
  Sage NUMERIC(3)
    CONSTRAINT c_3 CHECK(Sage < 30),
  Ssex CHAR(3)
    CONSTRAINT c_4 CHECK(Ssex IN('男', '女')),
 CONSTRAINT StudentKey PRIMARY KEY(Sno)
);
/*在Student表上建立5个约束条件，包括主码约束、c_1、c_2、c_3、c_4*/

[建立教师表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(2),
  CONSTRAINT TeacherKey FOREIGN KEY(Deptno) REFERENCE DEPT(Deptno),
  CONSTRAINT c_1 CHECK(Sal + Deduct >= 3000)
);

修改表中的完整性限制

使用ALTER TABLE语句修改表中的完整性限制
[案例：去掉Student表中对性别的限制]

ALTER TABLE Student
  DROP CONSTRAINT c_4;

[案例：修改表Student中的约束条件，要求学号改为900000~999999]之间，年龄由小于30改为小于40
可以先删除原有的约束条件在添加新的约束条件

ALTER TABLE Student
  DROP CONSTRAINT c_1;
ALTER TABLE Student
  CONSTRAINT c_1 CHECK(Sno BETWEEN 900000 AND 999999);
ALTER TABLE Student
  DROP CONSTRAINT c_3;
ALTER TABLE Student
  ADD CONSTRAINT c_3 CHECK(Sage < 40);

域中的完整性限制

域：一组具有相同数据类型的值的集合
[建立一个性别域并声明性别与的访问]
第一种

CREATE DOMAIN GenderDoamin CHAR(2)
  CHECK (VALUE IN('男', '女'));

[建立一个性别域GenderDomain并为其定义别名]

CREATE DOMAIN GenderDomain CHAR(2)
  CONSTRAINT GD CHECK(VALUE IN ('男','女'));

[删除域GenderDomain的限制条件GD]

ALTER DOMAIN GenderDomain
  DROP CONSTRAINT GD;

[在域GenderDomain上增加限制条件GDD]

ALTER DOMAIN GenderDomain
  ADD CONSTRAINT GDD CHECK(VALUE IN('1','0'));

触发器(Trigger)

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

一旦定义触发器将保存在DBServer中，用户在对DB中的数据进行DML操作的时候将自动激活相应的触发器.
触发器类似于约束但比约束更为灵活可实施更为复杂的检查和操作，具有更精细和强大的数据控制能力。

定义触发器

触发器又称为【事件-条件-动作（event-condition-action)规则】
格式：

CREATE TRIGGER <触发器名称> /*每当触发事件发生的时候，该触发器将被激活*
{BEFORE|AFTER} <触发事件> ON <表名> /*指明触发器激活的时间是在执行触发事件前或后*/
 
REFERENCING NEW|OLD ROW AS<变量> /*REFERENCING指出引用的变量*/
FOR EACH{ROW|STATEMENT} /*定义触发器的类型，致命动作体执行的频率*/
[WHERE<触发条件>]<触发动作体> /*仅当触发条件为真的时候才会执行触发动作体*/

注意项

1. 只有表的拥有者才能在所拥有的表上设置触发器
2. 同一个模式下，触发器名必须唯一
3. 触发器只能定义在基本表上，不能定义在视图上

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

4. 触发事件：【INERT、UPDATE、DELETE】或几个动作的组合
5. AFTER/BEFORE是触发的时机

AFTER表明在触发事件的操作执行之后激活触发器；BEFORE表明在触发事件的操作执行之前激活触发器。
6.触发器的类型： 行级触发器(FOR EACH ROW)和语句级触发器(FOR EACH STATEMENT)
行级触发器对每一次的数据变动都要触发相应的事件，语句级触发器要到整个数据变动完毕之后才触发相应的事件
eg: UPDATE TEACHER SET Deptno=5;
若TEACHER由1000行，对语句级触发器要在UPDATE语句后触发动作体执行一次，当对于行级触发器，触发动作体将执行1000次。
7.触发条件： 当触发器被激活时，只有当触发条件为真时触发动作体才会被执行，否则触发动作体不执行。如果省略WHEN触发条件，则触发动作体在触发器激活后立即执行。

【案例： 对表SC的Grade属性修改，若分数增加10%，则将此次操作记录到另一个表SC_U(Sno, Cno, Oldgrade, Newgrade)中，其中Oldgrade时修改前的分数，Newgrade是修改后的分数】

CREATE TRIGGER SC_T /*SC_T是触发器的名字*/
AFTER UPDATE OF Grade ON SC /*UPDATE OF Grade ON SC是触发事件即对SC表中Grade字段更新*/
/*AFTER是触发的时机，表示对SC的Grade属性修改完后再触发下面的规则*/
REFERENCING
  OLDROW AS OldTuple,
  NEWROW AS NewTuple
FOR EACH ROW /*行级触发器， 即每执行一次Grade的更新，下面的规则就执行一次*/
WHEN(NewTuple.Grade >= 1.1 * OldTuple.Grade) /*触发条件，只有该条件为真时才执行*/
  INSERT INTO SC_U(Sno, Cno, OldGrade, NewGrade) 
  VALUES(OldTuple.Sno, OldTuple.Grade, NewTuple.Grade)

REFERENCING指出引用的变量，如果触发事件是UPDATE操作并由FOR EACH ROW子句则引用的变量有【OLDROW】和【NEWROW】，分别表示修改之前的元组和修改之后的元组
若没有FOR EACH ROW子句，则可以引用的变量有OLDTABLE和NEWTABLE，OLDTABLE表示表中原来的内容，NEWTABLE表示表中变化后的部分。

【案例：将对表Student的插入操作所增加的学生个数记录到表student-InsertLog】中

CREATE TRIGGER Student_Count
AFTER INSERT IN Student /*触发器激活的事件是在对Student表执行更新的INSERT之后*/
REFERENCING
  NEW TABLE AS DELTA
FOR EACH STATEMENT /*语句级触发器，即执行完INSERT语句后下面的触发动作体才执行一次*/
INSERT INTO StudentInsertLog(Numbers)
SELECT COUNT(*) FROM DELTA

FOR EACH STATEMENT 语句级触发器则在INSERT语句执行完毕之后才执行一次触发器中的动作
默认的触发器是语句级触发器，DELTA是一个关系名，其模式与Student相同，包含元组是INSERT语句增加的元组。

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

CREATE TRIGGER Insert_Or_Update_Sal /*对教师表插入或更行时激活触发器*/
BEFORE INSERT OR UPDATE ON Teacher /*BEFORE触发事件*/
REFERENCING NEW row AS newTuple
FOR EACH ROW /*行级触发器*/
BEGIN
  IF(newtuple.Job='教授') AND (newtuple.Sal<4000)
        THEN newtuple.Sal:=4000; /*使用插入或更新操作后的新值*/
  END IF;
END; /*触发动作体结束*/

定义的是BEFORE触发器，在插入和更新教师记录前就可以按照触发器的规则调整教授的工资，不必等插入后再检查再调整

激活触发器

触发器的执行是由触发事件激活，并由DBServer自动执行的。一个数据表上可能定义多个触发器eg: 多个BEFORE触发器、多个AFTER触发器
=>同一个表上触发器执行顺序如下：
1.执行该表的BEFORE触发器
2.激活触发器的SQL语句
3.执行该表上的AFTER触发器
对于同一个表上的多个BEFORE(AFTER)触发器，遵循“谁先创建谁先执行”的原则，即按照触发器创建的事件先后顺序执行。