数据库原理课件-05数据库完整性.ppt
1,数据库系统概论An Introduction to Database System第五章 数据库完整性,2,第五章 数据库完整性,数据库中完整性（Integrity）是指数据的正确性、有效性和相容性，防止错误的数据进入数据库。正确性指数据的合法性，例如数值型数据中只能包含数字而不能含字母。有效性指数据是否属于所定义的有效范围，如月份只能用112的正整数表示。相容性指表示同一事实的两个数据应相同，不一致就是不相容。,3,区分数据的完整性和安全性,数据的完整性：保护数据以防止合法用户无意中造成的破坏。防范对象是不合语义的、不正确的数据，防止它们进入数据库。数据的安全性：保护数据以防止非法用户故意造成的破坏。防范对象是非法用户和非法操作，防止他们对数据库数据的非法存取。,4,完整性控制机制,1.完整性约束条件定义机制2.完整性检查机制3.违约反应,5,完整性控制机制,1、完整性约束条件定义机制完整性约束条件：数据模型的组成部分，约束数据库中数据的语义。DBMS应提供定义数据库完整性约束条件，并把它们作为模式的一部分存入数据库中。,返回,6,完整性控制机制,2、完整性检查机制检查用户发出的操作请求是否违背了完整性约束条件。一般在INSERT、UPDATE、DELETE语句执行后开始检查，也可在事务提交时检查。,返回,7,完整性控制机制,3、违约反应如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性。,返回,8,第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户定义的完整性5.4 完整性约束命名子句5.5 域中的完整性限制5.6 触发器,9,5.1 实体完整性,5.1.1 实体完整性定义5.1.2 实体完整性检查和违约处理,返回,10,5.1.1 实体完整性定义,用CREATE TABLE中的PRIMARY KEY来定义。可定义为列级约束条件和表级约束条件,11,实体完整性定义（续）,CREATE TABLE student(sno CHAR(8)PRIMARY KEY,sname CHAR(8)not null,ssex CHAR(2),sage SMALLINT,sdept CHAR(20);,CREATE TABLE student(sno CHAR(8),sname CHAR(8)not null,ssex CHAR(2),sage SMALLINT,sdept CHAR(20),PRIMARY KEY(sno);,对单属性构成的码可定义为列级约束条件也可定义为表级约束条件。,例1 将student表中的sno定义为码。,12,实体完整性定义（续）,例2 将SC表中的sno,cno属性组定义为码。CREATE TABLE SC(sno CHAR(8)NOT NULL,cno CHAR(4)NOT NULL,grade SMALLINT,PRIMARY KEY(sno,cno);对多个属性构成的码只能定义为表级约束条件。,返回,13,5.1.2 实体完整性检查和违约处理,当用PRIMARY KEY短语定义了关系的主码后，每当对表做插入或对主码列更新时，RDBMS会按实体完整性规则自动做如下检查：检查主码值是否唯一，如果不唯一则拒绝插入或修改；检查主码的各个属性是否为空，只要有一个不为空就拒绝插入或修改。,14,实体完整性检查和违约处理（续）,检查记录中主码值是否唯一的方法：全表扫描。依次判断表中每条记录的主码值与将插入记录上的主码值（或修改的新主码值）是否相同。十分耗时。在主码上自动建立索引（如B+树索引）。可大大提高效率。,返回,15,5.2 参照完整性,5.2.1 参照完整性定义在CREATE TABLE中用FOREIGN KEY短语来定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。,16,5.2 参照完整性,例如，关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码。Sno，Cno分别参照引用Student表的主码和Course表的主码 例3定义SC中的参照完整性 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)/*在表级定义参照完整性*/);,17,5.2 参照完整性,5.2.2 参照完整性检查和违约处理,表5.1 可能破坏参照完整性的情况及违约处理,18,参照完整性检查和违约处理(续),当发生不一致时，系统可执行下列策略：拒绝（NO ACTION）执行不允许该操作执行，一般为默认策略。级联（CASCADE）操作当删除或修改了被参照表（如student）的一个元组造成了与参照表（如SC）的不一致，则删除或修改参照表中的所有造成不一致的元组。设置为空值当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。对于参照完整性，除了应定义外码，还应定义外码是否允许空值。,返回,19,5.3 用户定义的完整性,5.3.1 属性上的约束条件的定义列值非空（NOT NULL短语）列值唯一（UNIQUE短语）检查列值是否满足一个布尔表达式（CHECK短语）,20,例5 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。CREATE TABLE SC（Sno CHAR(9)NOT NULL，Cno CHAR(4)NOT NULL，Grade SMALLINT NOT NULL，PRIMARY KEY(Sno，Cno)，/*如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义就不必写了*/）；,21,例6 建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码 CREATE TABLE DEPT(Deptno NUMERIC(2)，Dname CHAR(9)UNIQUE，/*要求Dname列值唯一*/Location CHAR(10)，PRIMARY KEY(Deptno)；,22,例7 Student表的Ssex只允许取“男”或“女”。CREATE TABLE Student(Sno CHAR(9)PRIMARY KEY，Sname CHAR(8)NOT NULL，Ssex CHAR(2)CHECK(Ssex IN(男，女)，/*性别属性Ssex只允许取男或女*/Sage SMALLINT，Sdept CHAR(20);,23,用户定义的完整性（续）,5.3.2 属性上的约束条件检查和违约处理当对表作插入或更新操作时，RDBMS就检查属性是否满足约束条件，若不满足则拒绝执行。5.3.3 元组上的约束条件的定义在CREATE TABLE语句中用CHECK短语定义元组上的约束条件。5.3.4 元组上的约束条件检查和违约处理由RDBMS完成,返回,24,例9 当学生的性别是男时，其名字不能以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两个属性值之间的约束条件*/)；性别是女性的元组都能通过该项检查，因为Ssex=女成立；当性别是男性时，要通过检查则名字一定不能以Ms.打头,25,5.4 完整性约束命名子句,CREATE TABLE语句中提供了完整性约束命名子句CONSTRAINT用来对完整性约束条件命名。1.完整性约束命名子句CONSTRAINT PRIMARY KEY短语|FOREIGN KEY短语|CHECK短语2.修改表中的完整性限制ALTER TABLE DROP,返回,26,完整性约束命名子句(续),例10 建立学生登记表Student，要求学号在9000099999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。CREATE TABLE Student(Sno NUMERIC(6)CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999)，Sname CHAR(20)CONSTRAINT C2 NOT NULL，Sage NUMERIC(3)CONSTRAINT C3 CHECK(Sage 30)，Ssex CHAR(2)CONSTRAINT C4 CHECK(Ssex IN(男，女)，CONSTRAINT StudentKey PRIMARY KEY(Sno)；在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。,27,例13 修改表Student中的约束条件，要求学号改为在900000999999之间，年龄由小于30改为小于40可以先删除原来的约束条件，再增加新的约束条件 ALTER TABLE Student DROP CONSTRAINT C1;ALTER TABLE Student ADD CONSTRAINT C1 CHECK(Sno BETWEEN 900000 AND 999999)，ALTER TABLE Student DROP CONSTRAINT C3;ALTER TABLE Student ADD CONSTRAINT C3 CHECK(Sage 40)；,完整性约束命名子句(续),28,5.6 触发器(Trigger),触发器是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在DBMS核心层进行集中的完整性控制。触发器类似于约束，但是比约束更加灵活，可实施比FOREIGN KEY约束、CHECK约束更为复杂的检查和操作，具有更精细和更强大的数据控制能力。,29,5.6.1 定义触发器,CREATE TRIGGER ON FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEWITH ENCRYPTIONAS batch|IF UPDATE(column)AND|OR UPDATE(column)FOR子句和AFTER子句是同义的。AFTER触发器在触发事件发生以后才被激活，而INSTEAD OF触发器则代替了相应触发事件而被执行（即指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作）。AFTER触发器只能定义在表上，而INSTEAD OF触发器即可定义在表上，也可定义在视图上。,30,定义触发器（续）,INSERT、UPDATE和DELETE三个选项是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。AS后是触发器要执行的操作。CREATE TRIGGER 权限默认授予定义触发器的表所有者、sysadmin 固定服务器角色成员以及 db_owner 和 db_ddladmin 固定数据库角色成员，并且不可转让。,31,定义触发器（续）,当你创建一个触发操作时，通常需说明所指的是触发语句作用之前还是之后的列值。由于此种原因，我们用两个取有特殊名字的虚拟表格来测试触发语句的作用：deleted和inserted（在内存中，触发器完成运行后，这些表就不能再访问）。这些表和指定触发器的表具有相同的结构。deleted表保存受delete和update影响的行的副本。inserted表保存在insert和update之后添加到触发器中的行的副本。,32,创建一个触发器，当向选课表中添加有关95100同学的数据时发出通知select*from sccreate trigger tri1 on sc after insertas if exists(select*from inserted a where a.sno=95100)beginraiserror(为95100添加了数据*,16,1)rollback transactionendinsert into sc(sno,cno,grade)values(95100,3,99)drop trigger tri1,33,-对学生分数表创建一个触发器,当插入或修改学生成绩时，需保证score=chinese+english+mathscreate table fenshu(sname char(8),chinese int,english int,maths int,score int)insert into fenshu values(张三,90,40,80,210)select*from fenshucreate trigger tri1 on fenshu after insert,update as update fenshu set score=chinese+english+maths where sname in(select sname from inserted)insert into fenshu values(李明,56,83,78,null)update fenshu set english=80 where sname=李明,返回,
