第五章 数据库完整性Word下载.docx

1、 Ssex CHAR（2）， Sage SMALLINT， Sdept CHAR（20） ）；或者 CREATE TABLE Student （Sno CHAR（9）， Sname CHAR（20）NOT NULL， Ssex CHAR（20）， Sage SMALLINT, Sdept CHAR（20）, PRIMARY KEY （Sno） /*在表级定义主码*/ ）;2. 将SC表中的Sno，Cno属性组定义为码 CREATE TABLE SC （Sno CHAR（9） NOT NULL, Cno CHAR（4） NOT NULL, Grade SMALLINT, PRIMARY KEY（

2、Sno,Cno） /*只能在表级定义主码*/5.1.2 实体完整性检查和违约处理包括：1. 检查主码值是否唯一，如果不唯一则拒绝插入或修改。2. 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。从而保证了实体完整性。全表扫描是十分耗时的。为了避免对基本表进行全表扫描，RDBMS核心一般都在主码上自动建立一个索引，如图5.2的B+树索引。通过索引查找基本表中是否已经存在新的主码值，将大大提高效率。 图5.2 使用索引检查主码唯一性5.2 参照完整性5.2.1 参照完整性定义例如，关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码。Sno，Cno分别参照引用S

3、tudent表的主码和Course表的主码。3定义SC中的参照完整性Grade SMALLINT,PRIMARY KEY（Sno,Cno）, /*在表级定义实体完整性*/FOREIGN KEY（Sno）REFERENCES Student（Sno）, /*在表级定义参照完整性*/FOREIGN KEY（Cno）REFERENCES Course（Cno）；5.2.2参照完整性检查和违约处理一个参照完整性将两个表中的相应元组联系起来了。因此，对被参照表和参照表进行增删操作时有可能破坏参照完整性，必须进行检查。例如，对表SC和Student有四种可能破坏参照完整性的情况，如表5.1所示。1. SC

4、表中增加一个元组，该元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。2. 修改SC表中的一个元组，修改后该元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。3. 从Student表中删除一个元组，造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。4. 修改Student表中的一个元组的Sno属性，造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。 表5.1可能破坏参照完整性的情况及违约处理当上述的不一致发生时，系统就可以采用以下的策略

5、加以处理。1 拒绝（NO ACTION）执行2 级连（CASCADE）操作3 设置为空值（SET-NULL）当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。例如，有下面2个关系 学生（学号，姓名，性别，专业号，年龄） 专业（专业号，专业名）学生关系的“专业号”是外码，因为专业号是专业关系的主码。假设专业表中某个元组被删除，专业号为12，按照设置为了空值策略，就要把学生表中专业号=12的所有元组的专业号设置为空值。这对应了这样的语义：某个专业删除了，该专业的所有学生专业未定，等待重新分配专业。因此对于参照完整性时，除了应该定义外码，还应定义外

6、码列是否允许空值。4 显示说明参照完整性的违约处理事例。 （Sno CHAR （9） NOT NULL, PRIMARY KEY（Sno,Cno）, /*在表级定义实体完整性*/ FOREIGN KEY（Sno）REFERENCES Student（Sno） ON DELETE CASCADE /*当删除Student表中的元组时，级连删除SC表中相应的元组*/ ON UPDATE CASCADE, /*当更新Student表中的Sno时，级连更新SC表中相应的元组*/ FOREIGN KEY（Cno）REFERENCES Course（Cno）/*在表级定义参照完整性*/ ON DELETE

7、 NO ACTION /*当删除course表中的元组造成了与SC表不一致时拒绝删除*/ ON UPDATE CASCADE /*当更新course表中的cno时，级连更新SC表中相应的元组*/从上面的讨论看到RSBMS在实现参照完整性时，除了要提供定义主码、外码的机制外，还需要提供不同的策略供用户选择。选择哪种策略，要根据应用环境的要求确定。5.3用户定义的完整性用户定义的完整性就是针对某个具体应用的数据必须满足的语义要求。目前的RDBMS都提供了定义和检验这类完整性的机制，使用了和实体完整性、参照完整性相同的技术和方法来处理他们，而不必由应用程序来承担这一功能。5.3.1 属性上的约束条件

8、的定义在CREATE TABLE中定义属性的同时可以根据应用要求，定义属性上的约束条件，即属性值限制，包括： 列值非空（NOT NULL短语） 列值唯一（UNIQUE短语） 检查列值是否满足一个布尔表达式（CHECK短语）1. 不允许取空值在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。CREATE TABLE SC （Sno CHAR （9） NOT NULL, /*Sno属性不允许取空值*/ Cno CHAR（4）NOT NULL, /*Cno属性不允许取空值*/ Grade SMALLINT NOT NULL,/*Grade属性不允许取空值*/ PRIMARY KEY（S

9、no，Cno），/*如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值则在列级不允许取空值的定义就不必写了*/ .2. 列值唯一建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码。 CREATE TABLE DEPT （Deptno NUMERIC（2）, Dname CHAR（9） UNIQUE, /*要求Dname列值唯一*/ Location CHAR（10）, PRIMARY KEY （Deptno）3. 用CHECK短语指定列值应该满足的条件 Student表的Ssex只允许取“男”或“女”（Sno CHAR（9） PRIMARY KEY, /

10、*在列级定义主码*/Sname CHAR（8） NOT NULL, /*Sname属性不允许取空值*/Ssex CHAR（2） CHECK（Ssex in （男或女）,/*性别属性Ssex只允许取男或女*/Sage SMALLINT,Sdept CHAR（20）;SC表的Grade的值应该在0到100之间。 （Sno CHAR（9）NOT NULL, Cno CHAR（4）NOT NULL, Grade SMALLINT CHECK （Grade=0 AND Grade =100）, PRIMARY KEY （Sno,Cno）,FOREIGN （Sno） PEFERENCES Student（

11、Sno）,FOREIGN （Cno）REFERENCES Course（Cno）5.3.2 属性上的约束条件检查和违约处理当往表中插入元组或修改属性的值时，RDSMS就检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行。5.3.3元组上的约束条件的定义与属性上约束条件的定义类似，在CREATE TABLE语句中可以用CHECK短语定义元组上的约束条件，即元组级的限制。同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件。例9 当学生的性别是男孩时，其名字不能以Ms.打头。 （Sno CHAR（9）, Sname CHAR（8） NOT NULL, Ssex CHAR（

12、2）, PRIMARY KEY （Sno）, CHECK （Ssex= 女 OR Sname NOT LIKEMs.%/*定义了元组中Sname和Ssex两个属性值之间的约束条件*/5.3.3元组上的约束条件检查和违约处理当往表中插入元组或修改属性的值时，RDBMS就检查元组上的约束条件是否被满足，如果不满足则操作被拒绝执行。5.4完整性约束命名子句1. 完整性的约束命名子句CONSTRAINT完整性约束条件名PRIMARY KEY短语|FOREIGN KEY短语|CHECK短语例 10 建立学生登记表Student，要求学号在90000-99999之间，姓名不能取空值，年龄小于30，性别只能

13、是“男”或“女”。 （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=3000）2. 修改表中的完整性限制我们可以使用ALTER TABLE 语句修改表中的完整性限制。（1） 去掉例10student表中对性别的限制。 ALTER TABLE Student DROP CONSTRAINT C4；（2）修改表Student中的约束条件，要求学号改为在9

14、00000-999999之间，年龄由小于30改为小于40。可以先删除原来的约束条件，再增加新的约束条件。 DROP CONSTRAINT C1; ADD CONSTRAINT C1 CHECK （Sno BETWEEN 900000 AND 999999）, DROP CONSTRAINT C3;ADD CONSTRAINT C3 CHECK （Sage 40） *5.5域中的完整性限制 SQL支持域的概念，并可以用CREATE DOMAIN语句建立一个域应该满足的完整性约束条件 1.建立一个性别域，并声明性别域的取值范围 CREATE DOMAIN GenderDomain CHAR（2）

15、CHECK（VALUE IN（男,女）;2.对Ssex的说明可以改写为 Ssex GenderDomain3.建立一个性别域GenderDomain，并对其中的限制命名。 CONSTRAINT GD CHECK（VALUE IN（男,女）;4.删除域GenderDomain的限制条件GD。 ALTER DOMAIN Genderdomain DROP CONSTRAINT GD;5.在域GenderDomain上增加限制条件GDDALTER DOMAIN GenderdomainADD CONSTRAINT GDD CHECK （VALUE IN（1,0）;这样，通过例16和例17，就把性别的

16、取值范围由（男,女） 改为（1,0）。 5.6 触发器触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程。触发器类似于约束；一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在DBMS核心层进行集中的完整性控制。不同的RDBMS实现的触发器语法也会有所不同。请读者在上机实验时注意阅读实验系统的使用说明。5.6.1 定义触发器建立触发器命令其一般格式为CREATE TRIGGER BEFORE|AFTER ONFOR EACH ROW|STATEMENTWHEN 触发动作体各部分语法进行详细说明：1. 表的拥有者即创建表的用户才可以在表上创建触发器，并且一

17、个表上只能创建一定数量的触发器。2. 触发器名：触发器名可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须是唯一的；并且触发器名和必须在同一模式下。3. 表名：当这个表的数据发生变化时，将激活定义在该表上相应的触发器，因此，该表也称为触发器的目标表。4. 触发事件：触发事件可以是INSERT、DELETE或UPDATE，也可以是这几个事件的组合，如INSERT OR DELETE等。UPDATE后面还可以有OF触发列.，即进一步修改那些列时触发器激活。5. 触发器类型：触发器按照所触发动作的间隔尺寸可以分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STA

18、TEMENT）。例如，假设在例11的TEACHER表上创建了一个AFTER UPDATE触发器。如果表TEACHER有1000行，执行如下语句：UPDATE TEACHER SET Deptone =5;如果该触发器为语句级触发器，那么执行完语句后，触发动作只发生一次。如果是行动级触发器，触发动作将执行1000次。6. 触发条件：触发器被激活时，只有当触发条件为真时触发动作体才执行；否则触发动作体不执行。如果省略WHEN触发条件，则触发动作体在触发器激活后立即执行。7. 触发动作体：触发动作体既可以是一个匿名PL/SQL过程块，也可以是对已创建存储过程的调用。如果是行级触发器，在两种情况下，用

19、户都可以在过程体中使用NEW和OLD引用UPDATE/INSERT事件之后的新值和UPDATE/INSERT事件之前的旧值。如果是语句级触发器，则不能在触发动作体中使用NEW和OLD进行引用。如果触发动作体执行失败，激活触发器的时间就会中止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化。例18定义一个BEFORE行级触发器，为教师表TEACHER定义完整性规则“接受的工资不得低于4000元，如果低于4000元，自动改为4000元”。CREATE TRIGGER Insert_Or_Sal /*在教师表Teacher上定义触发器*/ BEFORE INSERT OR UPDATE

20、ON Teacher /*触发事件是插入或更新操作*/ FOR EACH ROW /*这是行级触发器*/ AS BEGIN /*定义触发动作体，这是一个PL/SQL过程块*/ If （new.job=教授） AND （new.Sal4000） THEN New.Sal=4000; /*因为是行级触发器，可在过程体中使用插入或更新操作后的新值*/ END IF; END; /*触发动作体结束*/例19定义AFTER行级触发器，当教师表Teacher的工资发生变化以后就自动在工资变化表Sal_log中增加一条相应的记录。 首先建立工资变化表Sal_log. CREATE TABLE Sal_log

21、 （Eno NUMERIC（4） referencr teacher（eno）, Username char（10）, Date TIMESTAMP） CREATE TRIGGER Insert_Sal /*建立了一个触发器*/ AFTER INSERT ON Teacher /*触发事件是INSERT*/ FOR EACH ROW AS BEGIN INSERT INTO Sal_log VALUES（ New.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP）; END;CREATE TRIGGER Update_Sal /*建立了一个触发器*/ AFT

22、ER UPDATE ON Teacher /*触发事件是UPDATE*/ IF（new.Salold.Sal） THEN INSERT INTO Sal_logVALUES（New.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP）；5.6.2激活触发器 触发器的执行，是由触发事件激活的，并由数据库服务器自动执行的。一个数据表上可能定义了多个触发器，比如多个BEFORE触发器，多个AFTER触发器等。同一个表上的多个触发器激活时遵循如下的执行顺序：（1） 执行该表上的BEFORE触发器；（2） 激活触发器的SQL语句；（3） 执行该表上的AFTER触发器。对

23、于同一个表上的多个BEFORE（AFTER）触发器，遵循“谁先创建谁执行”的原则，即按照触发器创建的时间先后顺序执行。有些RDBMS是按照触发器名称的字母排序顺序执行触发器。 例20执行修改某个教师工资的SQL语句，激活上述定义的触发器。UPDATE Teacher SET Sal = 800 WHERE Ename = 陈平；执行顺序是：（1） 执行触发器Insert_Or_Update_Sal;（2） 执行SQL语句“UPDATE Teacher SET Sal = 800 WHERE Ename = 陈平；”（3） 执行触发器Insert_Sal;（4） 执行触发器Update_Sal。5.6.3删除触发器 SQL语法如下： DROP TRIGGER ON ； 删除教师表Teacher上的触发器Insert_Sal. DROP TRIGGER Insert_Sal ON Teacher;