数据库完整性

五邑大学计算机学院

数据库系统概论

第五章数据库完整性第五章数据库完整性什么是数据库的完整性是指数据的正确性和相容性它防止不合语义的数据进入数据库。例:学生的年龄必须是整数，取值范围为14--29；学生的性别只能是男或女；学生的学号一定是唯一的；学生所在的系必须是学校开设的系；为维护数据库的完整性，RDBMS必须做到：1.提供完整性约束条件定义机制把它们作为模式的一局部定义并存入数据字典中。

2.提供完整性检查机制检查用户发出的操作〔INSERT、UPDATE、DELETE等〕是否违背了完整性约束条件。3.违约处理如果发现用户的操作使数据违背了完整性约束条件，那么采取一定的动作〔如拒绝用户操作〕，来保证数据的完整性。第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.5*域中的完整性限制5.6触发器5.7小结5.1实体完整性实体完整性规那么〔EntityIntegrity〕：假设属性A是根本关系R的主属性，那么属性A不能取空值。关系模型的实体完整性在CREATETABLE中用PRIMARYKEY定义，供用户在建表时指定关系的主码列。在列级使用PRIMARYKEY子句〔单属性〕在表级使用PRIMARYKEY子句〔单属性和多属性〕

实体完整性〔续〕例1：在学生选课数据库中，要定义Student表的Sno属性为主码

CREATETABLEStudent(SnoCHAR(7)PRIMARYKEY,//在列级定义主码

SnameCHAR(8)NOTNULL,SageSMALLINT)或:CREATETABLEStudent(SnoCHAR(7),SnameCHAR(8)NOTNULL,SageSMALLINT,PRIMARYKEY(Sno))//在表级定义主码例2：在SC表中定义(Sno,Cno)为主码

CREATETABLESC(SnoCHAR(7),CnoCHAR(4),GradeSMALLINT,

PRIMARYKEY(Sno,Cno))//只能在表级定义实体完整性〔续〕用户程序对主码列进行更新操作时，系统自动进行完整性检查违约操作使主属性值为空值的操作使主码值在表中不唯一的操作违约处理系统拒绝此操作，从而保证了实体完整性第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.5域中的完整性限制5.6触发器5.7小结5.2参照完整性参照完整性规那么：假设属性组F是根本关系R的外码，它与根本关系S的主码Ks相对应，那么对于R中每个元组在F上的值必须为：或者取空值或者等于S中某个元组的主码值。定义参照完整性，在CREATETABLE中：FOREIGNKEY子句：定义外码列REFERENCES子句：外码相应于那个表的主码1.参照完整性定义例：定义SC为参照关系，(Sno,Cno)是主码，Sno,Cno分别参照引用Student表的主码和Course表的主码。

CREATETABLESC(SnoCHAR(7),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno));2.参照完整性检查和违约处理出现违约操作的情形1：在SC表中插入或修改元组，该元组的Sno值在STUDENT表中找不到相应的对等值出现违约操作的情形2：从STUDENT表中删除或修改一个元组，造成SC表中假设干元组在STUDENT表中找不到相应的对等值违约处理的三种策略1、拒绝执行〔默认策略〕2、级联操作删除或修改被参照表STUDENT的元组，那么同时删除或修改参照关系SC中因此而造成的所有不一致的元组。如要删除Student关系中Sno=950001的元组，而SC关系中有4个元组的Sno都等于950001，那么将SC关系中所有4个Sno=950001的元组一起删除。3、设置为空值删除或修改被参照表的元组，那么参照表中所有造成不一致的元组的对应属性设置为空值。如学生〔学号,姓名,性别,专业号,年龄〕//专业号为空，表示该学专业〔专业号,专业名〕生专业未定

违约处理的三种策略这三种处理方法，哪一种是正确的，要依应用环境的语义来定，如学生－选课数据库，Student关系为被参照关系，其主码为Sno。SC为参照关系，外码为Sno。第一种方法和第二种方法都是可以的。第三种方法不符合应用环境语义。假设SC的Sno为空值，说明尚不存在的某个学生，或者某个不知学号的学生，选修了某门课程，其成绩记录在Grade中。这与学校的应用环境是不相符的，因此SC的Sno列不能取空值。除系统默认策略外，如果想让系统采用其他策略，那么必须在创立表时加以说明。参照完整性违约处理策略说明例：CREATETABLESC(SnoCHAR(7),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),ONDELETECASCADE,ONUPDATECASCADE,FOREIGNKEY(Cno)REFERENCESCourse(Cno),ONDELETEREJECT,ONUPDATECASCADE);REJECT:当删除Course表中的元组而造成与SC表不一致时，拒绝删除。Nullifies:设置空值，如ONDELETENullifies第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.5*域中的完整性限制5.6触发器5.7小结5.3用户定义的完整性用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求。目前RDBMS都提供了定义和检验这类完整性机制。1.属性上约束条件的定义和处理可定义三类属性值约束列值非空〔NOTNULL短语〕列值唯一〔UNIQUE短语〕检查列值是否满足一个布尔表达式〔CHECK短语〕例1：CREATETABLESC(SnoCHAR(7)NOTNULL,//Sno属性不允许取空值

CnoCHAR(4)NOTNULL,//Cno属性不允许取空值

GradeSMALLINT,

…);例2：建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

CREATETABLEDEPT(DeptnoNUMBER,DnameVARCHAR(9)UNIQUE,//Dname列值唯一LocVARCHAR(10),

PRIMARYKEY(Deptno));例3：建立学生登记表Student，要求学号在90000至99999之间，年龄<29，性别只能是‘男’或‘女’，姓名非空CREATETABLEStudent(SnoNUMBER(5)CHECK(SnoBETWEEN90000AND99999),SnameVARCHAR(10)NOTNULL,SageNUMBER(3)CHECK(Sage<29),SsexVARCHAR(2)CHECK(SsexIN('男','女'));当输入数据时，RDBMS检查属性上约束条件是否被满足，如不满足，那么拒绝执行。2.元组上约束条件的定义和处理例1：当学生的性别为男时，其名字不能以Ms.打头。CREATETABLEStudent(SnoCHAR(7),SnameCHAR(8)NOTNULL,SsexCHAR(2),SageSMALLINT,SdeptCHAR(20),PRIMARYKEY(Sno),CHECK(Ssex=‘女’ORSnameNOTLIKE‘Ms.%’));当输入或修改数据时，RDBMS检查元组上约束条件是否被满足，如不满足，那么拒绝执行。第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.5*域中的完整性限制5.6触发器5.7小结5.4完整性约束命名子句1.完整性约束命名子句CONSTRAINT<完整性约束条件名>[PRIMARYKEY短语|FOREIGNKEY短语|CHECK短语]例1：在学生选课数据库中，要定义Student表的Sno属性为主码

CREATETABLEStudent(SnoNUMBER(8),SnameVARCHAR(20),SageNUMBER(20),

CONSTRAINTPK_SNOPRIMARYKEY(Sno));

完整性约束命名子句

例2：建立学生登记表Student，要求学号在90000至99999之间，年龄<29，性别只能是‘男’或‘女’，姓名非空.CREATETABLEStudent(SnoNUMBER(5)

CONSTRAINTC1CHECK(SnoBETWEEN90000AND99999),SnameVARCHAR(10)CONSTRAINTC2NOTNULL,SageNUMBER(3)CONSTRAINTC3CHECK(Sage<29),SsexVARCHAR(2)

CONSTRAINTC4CHECK(SsexIN('男','女'));2.改表中的完整性约束例：

ALTERTABLEStudentDROPCONSTRAINTC1,C3;ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN10000AND99999),ADDCONSTRAINTC3CHECK(Sage<40);第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.5*域中的完整性限制5.6触发器5.7小结5.5域中的完整性限制

可以用CREATEDOMAIN语句建立一个域以及该域满足的完整性约束条件例1：建立一个性别域，并声明性别域的取值范围

CREATEDOMAINGenderDomainCHAR(2)CHECK(VALUEIN(‘男’，‘女’));例2：建立一个性别域，并对其中的限制命名CREATEDOMAINGenderDomainCHAR(2)CONSTRAINT

GDCHECK(VALUEIN(‘男’，‘女’));Student表中Ssex字段重新定义为:SsexGenderDomain

5.5*域中的完整性限制例3：删除性别域的限制命名ALTERDOMAINGenderDomainDROPCONSTRAINT

GD;例4：在性别域上增加限制条件GDDALTERDOMAINGenderDomainADDCONSTRAINT

GDDCHECK(VALUEIN(‘1’，‘0’));第五章数据库完整性5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.5域中的完整性限制5.6触发器5.7小结5.6触发器5.6.1定义触发器

5.6.2激活触发器

5.6.3删除触发器

触发器〔Trigger〕是用户定义在关系表上的一类由事件驱动的特殊过程当事件发生时，由效劳器自动执行触发器的功能可以进行更为复杂的检查和操作，提供更精细和更强大的数据控制能力5.6.1定义触发器CREATETRIGGER语法格式

CREATETRIGGER<触发器名>{BEFORE|AFTER}<触发事件>ON<表名>FOREACH{ROW|STATEMENT}

［WHEN<触发条件>］

<触发动作体>定义触发器(续)定义触发器的语法说明:1.创立者：表的拥有者2.触发器名3.表名：触发器的目标表4.触发事件：INSERT、DELETE、UPDATE5.触发器类型行级触发器〔FOREACHROW〕语句级触发器〔FOREACHSTATEMENT〕

定义触发器(续)例如,假设在TEACHER表上创立了一个AFTERUPDATE触发器。如果表TEACHER有1000行，执行如下语句：UPDATETEACHERSETDeptno=5;如果该触发器为语句级触发器，那么执行完该语句后，触发动作只发生一次如果是行级触发器，触发动作将执行1000次，因为涉及到1000行。定义触发器(续)6.触发条件触发条件为真，触发器激活时执行触发动作体省略WHEN触发条件，那么触发动作体在触发器激活后立即执行。7.触发动作体触发动作体可以是一个匿名PL/SQL过程块也可以是对已创立存储过程的调用定义触发器(续)［例18］定义一个BEFORE行级触发器，为教师表Teacher定义完整性规那么“教授的工资不得低于4000元，如果低于4000元，自动改为4000元〞。CREATETRIGGERInsert_Or_Update_SalBEFOREINSERTORUPDATEONTeacher/*触发事件是插入或更新操作*/FOREACHROW/*行级触发器*/ASBEGIN/*定义触发动作体，是PL/SQL过程块*/IF(new.Job='教授')AND(new.Sal<4000)THENnew.Sal:=4000;ENDIF;END; 定义触发器(续)［例19］定义AFTER行级触发器，当教师表Teacher的工资发生变化后就自动在工资变化表Sal_log中增加一条相应记录。首先建立工资变化表Sal_logCREATETABLESal_log〔主码：Eno+Date)(EnoNUMERIC(4)，SalNUMERIC(7，2)，Usernamechar(10)，DateTIMESTAMP)；定义触发器(续)[例19]〔续〕CREATETRIGGERInsert_Sal AFTERINSERTONTeacher/*触发事件是INSERT*/FOREACHROWASBEGININSERTINTOSal_logVALUES(new.Eno，new.Sal，new.Username，new.Date);END;定义触发器(续)[例19]〔续〕CREATETRIGGERUpdate_Sal AFTERUPDATEONTeacher/*触发事件是UPDATE*/FOREACHROWASBEGINIF(new.Sal<>old.Sal)THENINSERTINTOSal_logVALUES(new.Eno，new.Sal，new.Username，new.Date);ENDIF;END;5.6触发器5.6.1定义触发器

5.6.2激活触发器

5.6.3删除触发器

5.6.2激活触发器触发器的执行，是由触发事件激活的，并由数据库效劳器自动执行一个数据表上可能定义了多个触发器同一个表上的多个触发器激活时遵循如下的执行顺序：〔1