第五章 完整性 - use

1、第第五五章章 数据库完整性数据库完整性q什么是数据库的完整性什么是数据库的完整性q完整性约束条件完整性约束条件 q完整性控制完整性控制5.1 完整性完整性l什么是数据库的完整性什么是数据库的完整性数据库的完整性是指数据的正确性和相容性，防止不合语义的数据进入数据库。例: 学生的年龄必须是整数，取值范围为14-29； 学生的性别只能是男或女； 学生的学号一定是唯一的； 学生所在的系必须是学校开设的系；数据库是否具备完整性关系到数据库系统能否真实地反映现实世界，因此维护数据库的完整性是非常重要的。5.2 完整性控制完整性控制lDBMS的完整性控制机制的完整性控制机制l关系系统三类完整性的实现关系系

2、统三类完整性的实现5.2.1 DBMS的完整性控制机制的完整性控制机制lDBMS的完整性控制机制的主要功能的完整性控制机制的主要功能定义功能允许用户定义各类完整性约束条件检查功能l检查用户提供的请求是否违背了完整性约束条件违约反应l如果发现用户的操作违背了完整性约束条件, 则采取一定的动作来保证数据的完整性(一般是取消该事务已产生的影响-恢复)5.2.2 DBMS的完整性控制机制的完整性控制机制l违约反应违约反应 拒绝该操作 其他处理方法5.2.2关系系统三类完整性的实现关系系统三类完整性的实现l目前许多关系数据库系统都提供了定义和检查实体完目前许多关系数据库系统都提供了定义和检查实体完整性、

3、参照完整性和用户定义的完整性的功能。整性、参照完整性和用户定义的完整性的功能。l对于对于违反实体完整性违反实体完整性规则和用户定义的完整性规则的规则和用户定义的完整性规则的操作操作一般都是采用拒绝执行一般都是采用拒绝执行的方式进行处理。的方式进行处理。l而对于而对于违反参照完整性违反参照完整性的操作，并不都是简单地拒绝的操作，并不都是简单地拒绝执行，有时还需要采取另一种方法，即接受这个操作，执行，有时还需要采取另一种方法，即接受这个操作，同时执行一些附加的操作，以保证数据库的状态仍然同时执行一些附加的操作，以保证数据库的状态仍然是正确的。是正确的。5.3 Sql Server中完整性的实现中完

4、整性的实现实体完整性实体完整性参照完整性参照完整性用户定义完整性用户定义完整性5.3.1实体完整性实体完整性用用primary key定义定义lcreate table studentl(lsno char(10) primary key,lsname char(10) not null,lssex char(2),lsage smallint,lsdept char(10)l)linsert into student(sno,sname)lvalues(1,wang)5.3.1实体完整性实体完整性标识列(IDENTITY)CREATE TABLE TC(a int identity(10,2

5、),b char(20)insert into tc(b)values(a)select * from tc5.3.1 实体完整性实体完整性实体完整性检查和违约处理实体完整性检查和违约处理1.检查主码值是否唯一，如果不唯一则拒绝插入检查主码值是否唯一，如果不唯一则拒绝插入或修改。或修改。2.检查主码的各个属性是否为空，只要有一个为检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。空就拒绝插入或修改。5.3.2 参照完整性参照完整性用用foreign key短语定义哪些列为外码，用短语定义哪些列为外码，用references短语指明这些外码参照哪些表的主码短语指明这些外码参照哪些表的主

6、码lcreate table scl(lsno char(10),lcno char(10),lgrade int,lforeign key (sno) references student(sno)l)5.3.2参照完整性参照完整性lcreate table scl(lsno char(10),lcno char(10),lgrade int,lforeign key (sno) references student(sno)lon delete cascadelon update cascadel)5.3.2参照完整性参照完整性ldelete lfrom studentlwhere sno

7、=1lupdate studentlset sno=001lwhere sno=15.3.2 参照完整性参照完整性对被参照表和参照表进行增删改操作时有可能破对被参照表和参照表进行增删改操作时有可能破坏参照完整性，必须进行检查。坏参照完整性，必须进行检查。1.SC表中增加一个元组。表中增加一个元组。2.修改修改SC表中的一个元组。表中的一个元组。3.从从Student表中删除一个元组。表中删除一个元组。4.修改修改Student表中一个元组的表中一个元组的Sno属性。属性。5.3.2 参照完整性参照完整性当上述的不一致发生时，系统可以采用以下的策略加以当上述的不一致发生时，系统可以采用以下的策略

8、加以处理。处理。1.拒绝执行拒绝执行不允许该操作执行。该策略一般设置为默认策略。不允许该操作执行。该策略一般设置为默认策略。2.级联操作级联操作当删除或修改被参照表的一个元组造成了与参照表的不当删除或修改被参照表的一个元组造成了与参照表的不一致，则删除或修改参照表中的所有造成不一致的元组。一致，则删除或修改参照表中的所有造成不一致的元组。5.4 用户定义的完整性用户定义的完整性1.不允许为空不允许为空2.列值唯一列值唯一3.使用使用CHECK短语短语4.元组上的约束条件元组上的约束条件5.完整性约束命名子句完整性约束命名子句6.触发器触发器5.4.1用户定义的完整性用户定义的完整性lCREAT

9、E TABLE T1l(lS CHAR(10) CHECK(S IN (男男,女女)l)linsert into t1lValues(mei)5.4.1用户定义的完整性用户定义的完整性元组上的约束条件定义元组上的约束条件定义lcreate table t2l(la int,lb char(10),lcheck(a10 and b not like 王王%)l)linsert into t2(a,b)lvalues(11,张一张一)lselect * from t25.4.1用户定义的完整性用户定义的完整性完整性约束命名子句完整性约束命名子句lcreate table t3l(lsno intl

10、constraint c1 check(sno between 100 and 999)l)linsert into t3lvalues(1097)5.4.1用户定义的完整性用户定义的完整性修改表中的完整性限制修改表中的完整性限制lalter table t3ldrop constraint c1lalter table t3ladd constraint c1 check(sno between 100 and 999)5.4.2 触发器触发器Student CourseSC在在DB中有这么一个功能，输入一条成绩之后，如果成绩合格，系统中有这么一个功能，输入一条成绩之后，如果成绩合格，系统自

11、动根据课程号得到课程学分，再把课程的学分加到该学生的总学自动根据课程号得到课程学分，再把课程的学分加到该学生的总学分上。分上。 5.4.2 触发器触发器StudentCourseSC在course表中查找1号课程学分25+25.4.2 触发器触发器触发器（trigger）是一种特殊类型的存储过程;它不能由用户直接调用，而是在对表进行操作（insert,update,delete）时,被自动地激活。5.4.2 触发器触发器使用CREATE TRIGGER语句创建触发器语法格式如下：5.4.2 触发器触发器5.4.2 触发器触发器（4）INSTEAD OF：执行本触发器而不：执行本触发器而不是是触

12、发触发操操作作，即触发器替代触发语句的操作，即触发器替代触发语句的操作。每个更新每个更新操作操作（DELETE,INSERT,UPDATE）最多只能定义一个最多只能定义一个INSTEAD OF触发器。触发器。（5）DELETE , INSERT , UPDATE：引发引发触发器执行的操作触发器执行的操作( (触发操作触发操作) )，至少要指定一个选，至少要指定一个选项，若选项多于一个，需用逗号分隔这些选项。项，若选项多于一个，需用逗号分隔这些选项。（6）sql_statement：定义触发器将执行的：定义触发器将执行的SQLSQL语句。语句。5.4.2 触发器触发器在触发器语句中可以使用两个特

13、殊的临时工作表：INSERTED表和DELETED表。这两个表是在用户执行数据的更改操作时，SQL Server自动创建和管理的。这两个表驻留在内存中，其结构同触发器所作用的基本表的结构，并且只可以被触发器使用，当触发器结束时系统自动释放这两个表的空间。5.4.2 触发器触发器Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。deleted、inserted表在执行触发器时记录的发生情况。5.4.2 触发器触发器create trigger InsertScore on SC a

14、fter insertasbegindeclare Icno int;declare Isno int;declare Igrade int;declare Icredit int;5.4.2 触发器触发器select Icno=cno from inserted;select Isno=sno from inserted;select Igrade=grade from inserted;select Icredit=ccredit from course where cno=Icno;if Igrade=60 update student set scredit=scredit+Icred

15、itwhere sno=Isno;end;5.4.2 触发器触发器对选课记录进行修改，如果成绩不在对选课记录进行修改，如果成绩不在0,100之间，之间，则不修改，输出成绩错误，否则，修改。则不修改，输出成绩错误，否则，修改。5.4.2 触发器触发器create trigger UpdateGrade on scinstead of updateasdeclare Ngrade int;declare Nsno int;declare Ncno int;select Ngrade=grade from inserted;select Nsno=sno from inserted;select Ncno=cno from inserted;5.4.2 触发器触发器if Ngrade=0 and Ngrade=100update scset grade=Ngrade where sn