6-4-3

6.4.1 完整性子系统
1.完整性的定义
完整性（Integrity）： 数据库中完整性（Integrity）一词是指数据的正确性（Correctness）、有效性（Validity）和相容性（Consistency），防止错误的数据进入数据库。  
正确性： 所谓正确性是指数据的合法性，譬如数值型数据中只能含数字而不能含字母；所谓有效性是指数据是否属于所定义的有效范围；所谓相容性是指表示同一事实的两个数据应相同，不一致就是不相容。  
2.完整性规则
(1)DBMS必须提供一种功能来保证数据库中数据是正确的，避免非法的不符合语义的错误数据的输入和输出，即所谓“垃圾进垃圾出”（Garbage In Garbage Out）所造成的无效操作和错误操作。
(2)检查数据库中数据是否满足规定的条件称为“完整性检查”。
(3)数据库中数据应该满足的条件称为“完整性约束条件”，有时也称为完整性规则。
3.完整性子系统的功能
(1)监督事务的执行，并测试是否违反完整性规则。
(2)若有违反现象，则采取恰当的操作，譬如拒绝操作、报告违反情况、改正错误等方法来处理。
4.完整性规则集
(1)完整性子系统是根据“完整性规则集”工作的。
(2)完整性规则集是由DBA或应用程序员事先向完整性子系统提供的有关数据约束的一组规则。 6.4.2 SQL中的完整性约束
1.域约束
(1)定义
　　 ○SQL可以用“CREATE DOMAIN”语句定义新的域，并且还可出现CHECK子句。
(2)实例
　　 ①定义一个新的域COLOR，可用下列语句实现：
　　 CREATE DOMAIN COLOR CHAR(6) DEFAULT '???' CONSTRAINT VALID_COLORS CHECK(VALUE IN ('Red', 'Yellow', 'Blue', 'Green', '???')); /* CONSTRAINT VALID_COLORS表示为这个域约束起个名字VALID_COLORS */
　　 ②将域用在创建基本表PART上：
　　 CREATE TABLE PART ( …, COLOR COLOR, … );
　　 ③域约束起作用
　　 若用户插入一个零件记录时未提交颜色COLOR值，那么颜色值将被缺省地置为“???”。若用户输入了非法的颜色值，则操作失败，系统将产生一个约束名为VALID_COLORS的诊断信息。
2.基本表约束
(1)候选键的定义
　　 ①候选键的定义形式
　　 UNIQUE(<列名序列>)或 PRIMARY KEY(<列名序列>)
　　 ②说明
　　 a.实际上UNIQUE方式定义了表的候选键，但只表示了值是惟一的，值非空还需在列定义时带有选项NOT NULL。
　　 b.PRIMARY方式定义了表的主键。一个基本表只能指定一个主键。当是主键时，指定的列会自动被认为是非空的。
(2)外键的定义
　　 ①外键的定义形式
　　 FOREIGN KEY(<列名序列>) REFERENCES <参照表> [(<列名序列>)] [ ON DELETE <参照动作> ] [ ON UPDATE <参照动作> ]
　　 ②说明
　　 a.第一个列名序列是外键，第二个列名序列是参照表中的主键或候选键。
　　 b.参照动作可以有五种方式：NO ACTION（默认）、CASCADE、RESTRICT、SET NULL或SET DEFAULT。
　　 c.在实际应用中，作为主键的关系称为参照表，作为外键的关系称为依赖表。
　　 d.对参照表的删除操作和修改主键值的操作，会对依赖关系产生的影响由参照动作决定。
　　 ③参照动作
　　 a.删除参照表中元组时的考虑
　　 ·NO ACTION方式：对依赖表没有影响。
　　 ·CASCADE方式：将依赖表中所有外键值与参照表中要删除的主键值相对应的元组一起删除。
　　 ·RESTRICT方式：只有当依赖表中没有一个外键值与要删除的参照表中主键值相对应时，系统才能执行删除操作，否则拒绝此删除操作。
　　 ·SET NULL方式：删除参照表中元组时，将依赖表中所有与参照表中被删主键值相对应的外键值均置为空值。
　　 ·SET DEFAULT方式：与上述SET NULL方式类似，只是把外键值均置为预先定义好的缺省值。
　　 b.修改参照表中主键值时的考虑
　　 ·NO ACTION：对依赖表没有影响。
　　 ·CASCADE方式：将依赖表中与参照表中要修改的主键值相对应的所有外键值一起修改。
　　 ·RESTRICT方式：只有当依赖表中没有外键值与参照表中要修改的主键值相对应时，系统才能修改参照表中主键值，否则拒绝此修改操作。
　　 ·SET NULL方式：修改参照表中主键值时，将依赖表中所有与这个主键值相对应的外键值均置为空值。
　　 ·SET DEFAULT方式：与上述SET NULL方式类似，只是把外键值均置为预先定义好的缺省值。
(3)“检查约束”的定义
　　 ①方法
　　 在关系定义中的任何所需地方加上关键字CHECK和约束的条件：CHECK (<条件表达式>)
　　 ②说明
　　 a.这种约束是对单个关系的元组值加以约束。
　　 b.这种约束在插入元组或修改元组时，系统要测试新的元组值是否满足条件。
　　 c.如果新的元组值不满足检查约束中的条件，那么系统将拒绝这个插入操作或修改操作。
　　 ③实例1
　　 在教学数据库中，如果要求学生关系S中存储的学生信息满足下列条件：男同学的年龄应在15~35岁之间，女同学的年龄应在15~30岁之间，那么可在关系S的定义中加入一个检查子句：
　　 CHECK (AGE >= 15 AND ((SEX ='M' AND AGE <= 35) OR (SEX ='F' AND AGE <= 30)));
　　 ④实例2
　　 在关系SC的定义中，参照完整性也可以不用外键子句定义，而用检查子句定义：
　　 CREATE TABLE SC (
　　 S# CHAR(4),
　　 C# CHAR(4),
　　 GRADE SMALLINT,
　　 PRIMARY KEY (SNO,CNO),
　　 CHECK (S# IN(SELECT S# FROM S)),
　　 CHECK (C# IN(SELECT C# FROM C)));
3.断言
(1)引言
　　 如果完整性约束牵涉面较广，与多个关系有关，或者与聚合操作有关，那么SQL2提供“断言”（Assertions）机制让用户书写完整性约束。断言可以像关系一样，用CREATE语句定义。
(2)定义断言的句法
　　 CREATE ASSERTION <断言名> CHECK (<条件>)
(3)撤销断言的句法
　　 DROP ASSERTION <断言名>
　　 但是撤销断言的句法中不提供RESTRICT和CASCADE选项。
(4)实例
　　 ①每位教师开设的课程不能超过10门。
　　 CREATE ASSERTION ASSE1 CHECK (10 >= ALL (SELECT COUNT(C#) FROM C GROUP BY T#));
　　 ②不允许男同学选修WU老师的课程。
　　 CREATE ASSERTION ASSE2 CHECK (NOT EXISTS(SELECT *
　　 FROM SC WHERE C# IN(SELECT C# FROM C,T WHERE C.C# = T.T# AND TNAME ='WU') AND S# IN(SELECT S# FROM S WHERE SEX ='M')));
　　 ③每门课程最多50名男学生选修。
　　 CREATE ASSERTION ASSE3 CHECK (50 >= ALL(SELECT COUNT(SC.SNO) FROM S,SC WHERE S.S# = SC.S# AND SEX='M' GROUP BY C#));
(5)说明
　　 有时，断言也可以在关系定义中用检查子句形式定义，但是检查子句不一定能保证完整性约束彻底实现，而断言能保证不出差错。 6.4.3 SQL3的触发器
1.触发器结构
(1)触发器的定义
触发器（Trigger）： 是一个能由系统自动执行对数据库修改的语句。触发器有时也称为主动规则（Active Rule）或事件——条件——动作规则（Event—Condition—Action Rule，ECA规则）。   (2)发器的组成
　　 ①事件。事件是指对数据库的插入、删除、修改等操作。触发器在这些事件发生时，将开始工作。
　　 ②条件。触发器将测试条件是否成立。如果条件成立，就执行相应的动作，否则什么也不做。
　　 ③动作。如果触发器测试满足预定的条件，那么就由DBMS执行这些动作（即对数据库的操作）。这些动作能使触发事件不发生，即撤销事件，例如删除一插入的元组等。这些动作也可以是一系列对数据库的操作，甚至可以是与触发事件本身无关的其他操作。
2.元组级触发器实例
(1)目的
　　 这个触发器规定，在修改关系SC的成绩值时，要求修改后的成绩一定不能比原来的低，否则就拒绝修改。
(2)触发器程序
　　 CREATE TRIGGER TRIG1 …①
　　 AFTER UPDATE OF GRADE ON SC …②
　　 REFERENCING …③
　　 OLD AS OLDTUPLE …④
　　 NEW AS NEWTUPLE …⑤
　　 FOR EACH ROW …⑥
　　 WHEN (OLDTUPLE.GRADE > NEWTUPLE.GRADE) …⑦
　　 UPDATE SC …⑧
　　 SET GRADE = OLDTUPLE.GRADE …⑨
　　 WHERE C# = NEWTUPLE.C# …⑩ 　　 说明：
　　 第①行说明触发器的名字为TRIG1。
　　 第②行给出触发事件，即对关系SC的成绩值修改后激活触发器。此处“AFTER”称为触发器动作时间，“UPDATE”称为触发事件，“ON SC”子句命名了触发器的目标表。
　　 第③~⑤行为触发器的条件和动作部分设置必要的元组变量，OLDTUPLE和NEWTUPLE分别为修改前、后的元组变量。
　　 第⑥~⑩行为触发器的动作部分。触发动作分为三个部分：动作间隔尺寸，动作时间条件，动作体。
　　 第⑥行为动作间隔尺寸。表示触发器对每一个修改的元组都要检查一次。如果没有这一行，则表示“FOR EACH STATEMENT”，即表示触发器对SQL语句的执行结果只检查一次。
　　 第⑦行为动作时间条件，也就是触发器的条件部分。这里，如果修改后的值比修改前的值小，那么必须恢复修改前的值。
　　 第⑧~⑩行为动作体，是触发器的动作部分。这里是SQL的修改语句。这个语句的作用是恢复修改前的旧值。
(3)触发器的撤销语句
　　 ①语句：DROP TRIGGER
　　 ②例如：DROP TRIGGER TRIG1
3.触发器结构的组成

(1)动作时间
　　 ①BEFORE：在触发事件进行以前，测试WHEN条件是否满足。若满足则先执行动作部分的操作，然后再执行触发事件的操作（此时可不管WHEN条件是否满足）。
　　 ②AFTER：在触发事件完成以后，测试WHEN条件是否满足，若满足则执行动作部分的操作。
　　 ③INSTEAD OF：在触发事件发生时。只要满足WHEN条件，就执行动作部分的操作，而触发事件的操作不再执行。（Oracle中的规定）
(2)触发事件
　　 ①触发事件定义了激活触发器的SQL数据更新语句的类别。
　　 ②触发事件有三类：UPDATE、DELETE和INSERT。
　　 ③只有在UPDATE时，允许后面跟“OF <属性表>”短语。在其他两种情况时，是对整个元组的操作，不允许后面跟“OF <属性表>”短语。
(3)目标表（ON子句）
　　 ○当目标表的数据被更新（插入、删除、修改）时，将激活触发器。
(4)旧值和新值的别名表（REFERENCES子句）
　　 ①如果触发事件是UPDATE，那么应该用“OLD AS”和“NEW AS”子句定义修改前后的元组变量。
　　 ②如果是DELETE，那么只要用“OLD AS”子句定义元组变量。
　　 ③如果是INSERT，那么只要用“NEW AS”子句定义元组变量。
(5)触发动作
　　 ①动作间隔尺寸
　　 a.FOR EACH ROW，元组级触发器，对每一个修改的元组都要检查一次。
　　 b.FOR EACH STATEMENT，语句级触发器，对SQL语句的执行结果去检查。
　　 ②动作时间条件
　　 a.动作时间条件用WHEN子句定义，它可以是任意的条件表达式。
　　 b.当触发器被激活时，如果条件是Ture，则执行动作体的SQL语句，否则不执行。
　　 ③动作体
　　 a.动作体定义了当触发器被激活时想要DBMS执行的SQL语句。
　　 b.动作体若是一个SQL语句，直接写上即可；若是一系列的SQL语句，则用分号定界，再使用BEGIN ATOMIC …… END限定。
4.语句级触发器实例
(1)目的
　　 在关系SC中修改课程号C#，也就是学生的选课登记需作变化。
　　 在关系SC中有个约束，要求保持每门课程选修人数不超过50。如果更改课程号后，违反这个约束，那么这个更改应该不做。
(2)触发器程序
　　 CREATE TRIGGER TRIG2
　　 INSTEAD OF UPDATE OF C# ON SC
　　 REFERENCING
　　 OLD_TABLE AS OLDSTUFF
　　 NEW_TABLE AS NEWSTUFF
　　 WHEN ( 50>=ALL ( SELECT COUNT ( S# )
　　 FROM (( SC EXCEPT OLDSTUFF ) UNION NEWSTUFF )
　　 GROUP BY C# )
　　 )
　　 BEGIN ATOMIC
　　 DELETE FROM SC WHERE ( S#,C#,GRADE ) IN OLDSTUFF;
　　 INSERT INTO SC SELECT * FROM NEWSTUFF
　　 END
(3)说明
　　 在语句级触发器，不能直接引用修改前后的元组，但可以引用修改前后的元组集。
　　 旧的元组集由被删除的元组或被修改元组的旧值组成，新的元组集由插入的元组或被修改元组的新值组成。
　　 用OLD_TABLE AS OLDSTUFF和NEW_TABLE AS NEWSTUFF说明两个关系变量OLDSTUFF和NEWSTUFF。
　　 由于这个触发器的动作时间是INSTEAD OF（见第2行），因此任何企图修改关系SC中C#值的操作都被这个触发器截获，并且触发事件的操作（即修改C#）不再进行，由触发器的条件真假值来判断是否执行动作部分的操作。
　　 动作部分由两个SQL语句组成，前一个语句是从关系SC中删除修改前的元组，后一个语句是在关系SC中插入修改后的元组。用这样的方式完成触发事件的操作。