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

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

《第五章 数据库完整性Word下载.docx》由会员分享，可在线阅读，更多相关《第五章 数据库完整性Word下载.docx（13页珍藏版）》请在冰豆网上搜索。

SsexCHAR

（2），

SageSMALLINT，

SdeptCHAR（20）

）；

或者

CREATETABLEStudent

（SnoCHAR（9），

SnameCHAR（20）NOTNULL，

SsexCHAR（20），

SageSMALLINT,

SdeptCHAR（20）,

PRIMARYKEY（Sno）/*在表级定义主码*/

）;

2.将SC表中的Sno，Cno属性组定义为码

CREATETABLESC

（SnoCHAR（9）NOTNULL,

CnoCHAR（4）NOTNULL,

GradeSMALLINT,

PRIMARYKEY（Sno,Cno）/*只能在表级定义主码*/

5.1.2实体完整性检查和违约处理

包括：

1.检查主码值是否唯一，如果不唯一则拒绝插入或修改。

2.检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。

从而保证了实体完整性。

全表扫描是十分耗时的。

为了避免对基本表进行全表扫描，RDBMS核心一般都在主码上自动建立一个索引，如图5.2的B+树索引。

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

图5.2使用索引检查主码唯一性

5.2参照完整性

5.2.1参照完整性定义

例如，关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码。

Sno，Cno分别参照引用Student表的主码和Course表的主码。

3．定义SC中的参照完整性

GradeSMALLINT,

PRIMARYKEY（Sno,Cno）,/*在表级定义实体完整性*/

FOREIGNKEY（Sno）REFERENCESStudent（Sno）,

/*在表级定义参照完整性*/

FOREIGNKEY（Cno）REFERENCESCourse（Cno）

）；

5.2.2参照完整性检查和违约处理

一个参照完整性将两个表中的相应元组联系起来了。

因此，对被参照表和参照表进行增删操作时有可能破坏参照完整性，必须进行检查。

例如，对表SC和Student有四种可能破坏参照完整性的情况，如表5.1所示。

1.SC表中增加一个元组，该元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。

2.修改SC表中的一个元组，修改后该元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。

3.从Student表中删除一个元组，造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。

4.修改Student表中的一个元组的Sno属性，造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组，其Sno属性的值与之相等。

表5.1可能破坏参照完整性的情况及违约处理

当上述的不一致发生时，系统就可以采用以下的策略加以处理。

1．拒绝（NOACTION）执行

2．级连（CASCADE）操作

3．设置为空值（SET-NULL）

当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。

例如，有下面2个关系

学生（学号，姓名，性别，专业号，年龄）

专业（专业号，专业名）

学生关系的“专业号”是外码，因为专业号是专业关系的主码。

假设专业表中某个元组被删除，专业号为12，按照设置为了空值策略，就要把学生表中专业号=12的所有元组的专业号设置为空值。

这对应了这样的语义：

某个专业删除了，该专业的所有学生专业未定，等待重新分配专业。

因此对于参照完整性时，除了应该定义外码，还应定义外码列是否允许空值。

4．显示说明参照完整性的违约处理事例。

（SnoCHAR（9）NOTNULL,

PRIMARYKEY（Sno,Cno）,/*在表级定义实体完整性*/

FOREIGNKEY（Sno）REFERENCESStudent（Sno）

ONDELETECASCADE/*当删除Student表中的元组时，级连删除SC表中相应的元组*/

ONUPDATECASCADE,/*当更新Student表中的Sno时，级连更新SC表中相应的元组*/

FOREIGNKEY（Cno）REFERENCESCourse（Cno）

/*在表级定义参照完整性*/

ONDELETENOACTION

/*当删除course表中的元组造成了与SC表不一致时拒绝删除*/

ONUPDATECASCADE/*当更新course表中的cno时，级连更新SC表中相应的元组*/

从上面的讨论看到RSBMS在实现参照完整性时，除了要提供定义主码、外码的机制外，还需要提供不同的策略供用户选择。

选择哪种策略，要根据应用环境的要求确定。

5.3用户定义的完整性

用户定义的完整性就是针对某个具体应用的数据必须满足的语义要求。

目前的RDBMS都提供了定义和检验这类完整性的机制，使用了和实体完整性、参照完整性相同的技术和方法来处理他们，而不必由应用程序来承担这一功能。

5.3.1属性上的约束条件的定义

在CREATETABLE中定义属性的同时可以根据应用要求，定义属性上的约束条件，即属性值限制，包括：

●列值非空（NOTNULL短语）

●列值唯一（UNIQUE短语）

●检查列值是否满足一个布尔表达式（CHECK短语）

1.不允许取空值

在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

CREATETABLESC

（SnoCHAR（9）NOTNULL,/*Sno属性不允许取空值*/

CnoCHAR（4）NOTNULL,/*Cno属性不允许取空值*/

GradeSMALLINTNOTNULL,/*Grade属性不允许取空值*/

PRIMARYKEY（Sno，Cno），/*如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值则在列级不允许取空值的定义就不必写了*/

.

2.列值唯一

建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码。

CREATETABLEDEPT

（DeptnoNUMERIC

（2）,

DnameCHAR（9）UNIQUE,/*要求Dname列值唯一*/

LocationCHAR（10）,

PRIMARYKEY（Deptno）

3.用CHECK短语指定列值应该满足的条件

Student表的Ssex只允许取“男”或“女”

（SnoCHAR（9）PRIMARYKEY,/*在列级定义主码*/

SnameCHAR（8）NOTNULL,/*Sname属性不允许取空值*/

SsexCHAR

（2）CHECK（Ssexin（‘男’或‘女’））,

/*性别属性Ssex只允许取男或女*/

SageSMALLINT,

SdeptCHAR（20）

）;

SC表的Grade的值应该在0到100之间。

（SnoCHAR（9）NOTNULL,

CnoCHAR（4）NOTNULL,

GradeSMALLINTCHECK（Grade>

=0ANDGrade<

=100）,

PRIMARYKEY（Sno,Cno）,

FOREIGN（Sno）PEFERENCESStudent（Sno）,

FOREIGN（Cno）REFERENCESCourse（Cno）

5.3.2属性上的约束条件检查和违约处理

当往表中插入元组或修改属性的值时，RDSMS就检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行。

5.3.3元组上的约束条件的定义

与属性上约束条件的定义类似，在CREATETABLE语句中可以用CHECK短语定义元组上的约束条件，即元组级的限制。

同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件。

[例9]当学生的性别是男孩时，其名字不能以Ms.打头。

（SnoCHAR（9）,

SnameCHAR（8）NOTNULL,

SsexCHAR

（2）,

PRIMARYKEY（Sno）,

CHECK（Ssex=‘女’ORSnameNOTLIKE‘Ms.%’

/*定义了元组中Sname和Ssex两个属性值之间的约束条件*/

5.3.3元组上的约束条件检查和违约处理

当往表中插入元组或修改属性的值时，RDBMS就检查元组上的约束条件是否被满足，如果不满足则操作被拒绝执行。

5.4完整性约束命名子句

1.完整性的约束命名子句

CONSTRAINT〈完整性约束条件名〉[PRIMARYKEY短语|FOREIGNKEY短语|CHECK短语]

[例10]建立学生登记表Student，要求学号在90000-99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

（SnoNUMERIC（6）

CONSTRAINTC1CHECK（SnoBETWEEN90000AND99999）,

SnameCHAR（20）

CONSTRAINTC2NOTNULL,

SageNUMERIC（3）

CONSTRAINTC3CHECK（Sage<

30）,

SsexCHAR

（2）

CONSTRAINTC4CHECK（SsexIN（‘男’，‘女’）），

CONSTRAINTStudentKeyPRIMARYKEY（Sno）

在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。

[例11]建立教师表TEACHER，要求每个教师的应发工资不低于3000元。

应发工资实际上就是实发工资列Sal与扣除项Deduct之和。

CREATETABLETEACHER

（EnoNUMERIC（4）PRIMARYKEY,

EnameCHAR（10）,

JobCHAR（8）,

SalNUMERIC（7,2）,

DeductNUMERIC（7,2）,

DeptnoNUMERIC

（2）,

CONSTRAINTEMPFKeyFOREIGNKEY（Deptno）REFERENCESDEPT（Deptno）,

CONSTRAINTC1CHECK（Sal+Deduct>

=3000）

2.修改表中的完整性限制

我们可以使用ALTERTABLE语句修改表中的完整性限制。

（1）去掉[例10]student表中对性别的限制。

ALTERTABLEStudent

DROPCONSTRAINTC4；

（2）修改表Student中的约束条件，要求学号改为在900000-999999之间，年龄由小于30改为小于40。

可以先删除原来的约束条件，再增加新的约束条件。

DROPCONSTRAINTC1;

ADDCONSTRAINTC1CHECK（SnoBETWEEN900000AND999999）,

DROPCONSTRAINTC3;

ADDCONSTRAINTC3CHECK（Sage<

40）

*5.5域中的完整性限制

SQL支持域的概念，并可以用CREATEDOMAIN语句建立一个域应该满足的完整性约束条件

1.建立一个性别域，并声明性别域的取值范围

CREATEDOMAINGenderDomainCHAR

（2）

CHECK（VALUEIN（‘男’,’女’））;

2.对Ssex的说明可以改写为

SsexGenderDomain

3.建立一个性别域GenderDomain，并对其中的限制命名。

CONSTRAINTGDCHECK（VALUEIN（‘男’,’女’））;

4.删除域GenderDomain的限制条件GD。

ALTERDOMAINGenderdomain

DROPCONSTRAINTGD;

5.在域GenderDomain上增加限制条件GDD

ALTERDOMAINGenderdomain

ADDCONSTRAINTGDDCHECK（VALUEIN（‘1’,’0’））;

这样，通过[例16]和[例17]，就把性别的取值范围由（‘男’,’女’）改为（‘1’,’0’）。

5.6触发器

触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程。

触发器类似于约束；

一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在DBMS核心层进行集中的完整性控制。

不同的RDBMS实现的触发器语法也会有所不同。

请读者在上机实验时注意阅读实验系统的使用说明。

5.6.1定义触发器

建立触发器命令其一般格式为

CREATETRIGGER<

触发器名>

{BEFORE|AFTER}<

触发事件>

ON<

表名>

FOREACH{ROW|STATEMENT}

[WHEN<

触发条件>

]

〈触发动作体〉

各部分语法进行详细说明：

1.表的拥有者即创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器。

2.触发器名：

触发器名可以包含模式名，也可以不包含模式名。

同一模式下，触发器名必须是唯一的；

并且触发器名和<

必须在同一模式下。

3.表名：

当这个表的数据发生变化时，将激活定义在该表上相应<

的触发器，因此，该表也称为触发器的目标表。

4.触发事件：

触发事件可以是INSERT、DELETE或UPDATE，也可以是这几个事件的组合，如INSERTORDELETE等。

UPDATE后面还可以有OF 

〈触发列…….〉，即进一步修改那些列时触发器激活。

5.触发器类型：

触发器按照所触发动作的间隔尺寸可以分为行级触发器（FOREACHROW）和语句级触发器（FOREACHSTATEMENT）。

例如，假设在[例11]的TEACHER表上创建了一个AFTERUPDATE触发器。

如果表TEACHER有1000行，

执行如下语句：

UPDATETEACHERSETDeptone=5;

如果该触发器为语句级触发器，那么执行完语句后，触发动作只发生一次。

如果是行动级触发器，触发动作将执行1000次。

6.触发条件：

触发器被激活时，只有当触发条件为真时触发动作体才执行；

否则触发动作体不执行。

如果省略WHEN触发条件，则触发动作体在触发器激活后立即执行。

7.触发动作体：

触发动作体既可以是一个匿名PL/SQL过程块，也可以是对已创建存储过程的调用。

如果是行级触发器，在两种情况下，用户都可以在过程体中使用NEW和OLD引用UPDATE/INSERT事件之后的新值和UPDATE/INSERT事件之前的旧值。

如果是语句级触发器，则不能在触发动作体中使用NEW和OLD进行引用。

如果触发动作体执行失败，激活触发器的时间就会中止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化。

[例18]定义一个BEFORE行级触发器，为教师表TEACHER定义完整性规则“接受的工资不得低于4000元，如果低于4000元，自动改为4000元”。

CREATETRIGGERInsert_Or_Sal/*在教师表Teacher上定义触发器*/

BEFOREINSERTORUPDATEONTeacher/*触发事件是插入或更新操作*/

FOREACHROW/*这是行级触发器*/

ASBEGIN/*定义触发动作体，这是一个PL/SQL过程块*/

If（new.job=’教授’）AND（new.Sal<

4000）THEN

New.Sal=4000;

/*因为是行级触发器，可在过程体中使用插入或更新操作后的新值*/

ENDIF;

END;

/*触发动作体结束*/

[例19]定义AFTER行级触发器，当教师表Teacher的工资发生变化以后就自动在工资变化表Sal_log中增加一条相应的记录。

首先建立工资变化表Sal_log.

CREATETABLESal_log

（EnoNUMERIC（4）referencrteacher（eno）,

Usernamechar（10）,

DateTIMESTAMP）

CREATETRIGGERInsert_Sal/*建立了一个触发器*/

AFTERINSERTONTeacher/*触发事件是INSERT*/

FOREACHROW

ASBEGIN

INSERTINTOSal_logVALUES（

New.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP）;

END;

CREATETRIGGERUpdate_Sal/*建立了一个触发器*/

AFTERUPDATEONTeacher/*触发事件是UPDATE*/

IF（new.Sal<

>

old.Sal）THEN

INSERTINTOSal_log

VALUES（New.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP）；

5.6.2激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行的。

一个数据表上可能定义了多个触发器，比如多个BEFORE触发器，多个AFTER触发器等。

同一个表上的多个触发器激活时遵循如下的执行顺序：

（1）执行该表上的BEFORE触发器；

（2）激活触发器的SQL语句；

（3）执行该表上的AFTER触发器。

对于同一个表上的多个BEFORE（AFTER）触发器，遵循“谁先创建谁执行”的原则，即按照触发器创建的时间先后顺序执行。

有些RDBMS是按照触发器名称的字母排序顺序执行触发器。

[例20]执行修改某个教师工资的SQL语句，激活上述定义的触发器。

UPDATETeacherSETSal=800WHEREEname=‘陈平’；

执行顺序是：

（1）执行触发器Insert_Or_Update_Sal;

（2）执行SQL语句“UPDATETeacherSETSal=800WHEREEname=‘陈平’；

”

（3）执行触发器Insert_Sal;

（4）执行触发器Update_Sal。

5.6.3删除触发器

SQL语法如下：

DROPTRIGGER<

ON<

；

删除教师表Teacher上的触发器Insert_Sal.

DROPTRIGGERInsert_SalONTeacher;