第5章 数据库完整性(集美大学)

第五章数据库的完整性林颖贤重点：了解DBMS完整性控制机制的三个方面，即完整性约束条件的定义、检查和违约反应。熟练掌握用SQL语言定义关系模式的三个完整性约束以及如何运用触发器、规则实现数据库完整性。难点：参照完整性及触发器.5.1数据库的完整性本节主要内容完整性子系统和完整性规则完整性类型完整性约束条件参照完整性控制SQL中的完整性约束机制数据库完整性1.数据库的完整性是指数据的正确性、有效性和相容性。防止不合语义的数据进入数据库。2.数据的完整性和安全性是两个不同概念数据的完整性防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据防范对象：不合语义的、不正确的数据数据的安全性保护数据库防止恶意的破坏和非法的存取防范对象：非法用户和非法操作3.数据库完整性约束可以通过DBMS或应用程序来实现.基于DBMS的完整性约束作为模式的一部分存入数据库中。通过DBMS实现的数据库完整性按照数据库设计步骤进行设计，而由应用软件实现的数据库完整性则纳入应用软件设计

1完整性子系统和完整性规则数据库完整性是通过DBMS的完整性子系统实现的，它有两个功能：

监督事务的执行，并测试是否违反完整性规则。如有违反，则采取恰当的操作，如拒绝、报告违反情况，改正错误等方法进行处理。完整性规则是由数据库管理员或应用程序员事先向完整性子系统提供有关数据约束的一组规则。由三部分组成:什么时候使用规则进行检查（称为规则的“触发条件”)要检查什么样的错误（称为“约束条件”或“谓词”）如果查出错误，应该怎么办（称为“ELSE子句”，即违反时要做的动作２完整性约束条件完整性约束条件作用的对象可以是关系、元组、列三种。列：对属性的取值类型、范围、精度等的约束条件元组：对元组中各个属性列间的联系的约束关系：对若干元组间、关系集合上以及关系之间的联系的约束完整性约束条件涉及的这三类对象，其状态可以是静态的，也可以是动态的。静态约束:它是反映数据库状态合理性的约束，这是最重要的一类完整性约束。动态约束:它是反映数据库状态变迁的约束。

２完整性约束条件

六类完整性约束条件分类:静态列级约束静态元组约束静态关系约束动态列级约束动态元组约束动态关系约束约束的类型DEFAULT约束CHECK约束PRIMARYKEY约束UNIQUE约束FOREIGNKEY约束级联引用完整性1.静态列级约束是对一个列的取值域的说明，这是最常用也最容易实现的一类完整性约束，包括以下几类：1)数据类型的约束，包括数据的类型、长度、单位、精度等。

例如:学生姓名的数据类型为字符型，长度为8。2)数据格式的约束。例如：学号的格式为YYYYMMXXX。其中YYYY表示入学年份，MM专业,XXX为顺序编号。3)取值范围或取值集合的约束。例如:规定成绩的取值范围为0-100。4)空值的约束.例:空值：未定义或未知的值。［例1］建立学生登记表Student，要求学号为主键，姓名不能取空值，性别只能是“男”或“女”,身份证号码唯一，出生日期处于可接受的日期范围内

createtablestudent(stu_nochar(10)primarykey,stu_namevarchar(10)notnull,stu_idcardchar(18)unique,stu_sexchar(2),

constraintck_sexcheck(stu_sexin('男','女')),stu_birthdatedatetime

constraintck_bir1check(stu_birthdate<getdate()))表级CHECK约束列级CHECK约束删除约束：altertablestudentdropconstraintck_sex[例2].为Employees数据表增加一列，列名为：邮箱，数据类型：VarChar(20),并将该列设置为惟一约束。ALTERTABLEEmployeesADD邮箱VARCHAR(20)CONSTRAINTU_EmailUNIQUE原因是Employees数据表已存在n条记录．增加一列会自动填上NULL［例3］在Sales数据库中为Employees数据表创建名为CK_ENo的CHECK约束，该约束限制“编号”列中只允许6位数字（不能为字母）。2.静态元组约束:

规定元组的各个列之间的约束关系.例：库存关系中出库数量不能大于库存数量3.静态关系约束:

关系的各个元组之间或若干关系之间存在的各种联系或约束.

常见静态关系约束：1)实体完整性约束2)参照完整性约束3)函数依赖约束4)统计约束

关系字段间存在的函数依赖.例：学生关系中snosname

定义某个字段值一个关系多个元组的统计值之间的约束关系例：职工平均工资的2倍<=部门经理的工资<=职工平均工资的5倍.职工平均工资值:统计值

DEPTDEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTONPRIMARYKEYInsertinto

20 MARKETING DALLAS

FINANCE NEWYORKNotallowed(DEPTNO-20alreadyexists)Notallowed

(DEPTNOisnull)查询主键信息：

sp_pkeystable_nameThePRIMARYKEYConstraint每个表只有一个PRIMARYKEY约束，PRIMARYKEY约束的值必须是唯一的，不允许有空值。如何区别UNIQUE和PRIMARYKEY的不同？[例4].分析下列两个SQL语句判断是否有错？CREATETABLET1(col1intNOTNULL,col2AScol1+1UNIQUE)====================CREATETABLET2(col1intNOTNULL,col2AScol1+1PRIMARYKEY)T2表无法创建，因为因为主键约束，所以SQLServer需要保证计算的结果不为NULL该技巧可以使用ISNULL()函数绕过对计算列的计算，如果计算结果为空，将应用一个替换值：CREATETABLET2(col1intNOTNULL,col2ASISNULL(col1+1,0)PRIMARYKEY)

DEPTDEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS...PRIMARY

KEY

EMPEMPNO ENAME JOB ...COMMDEPTNO

7839 KING PRESIDENT 107698 BLAKE MANAGER ２0...FOREIGNKEY

7571 FORD MANAGER ...200 97571 FORD MANAGER ...200InsertintoNotallowed

(DEPTNO-9doesnotexistintheDEPTtableAllowed

TheFOREIGNKEYConstraint外码是否可以接受空值的问题参照完整性检查和违约处理可能破坏参照完整性的情况及违约处理被参照表（例如Student）参照表（例如SC）违约处理可能破坏参照完整性

插入元组拒绝可能破坏参照完整性

修改外码值拒绝删除元组可能破坏参照完整性拒绝/级连删除/设置为空值修改主码值可能破坏参照完整性拒绝/级连修改/设置为空值拒绝删除置空值删除级联引用完整性FOREIGNKEY约束包含一个CASCADE选项，允许对一个定义了UNIQUE或者PRIMARYKEY约束的列的值的修改自动传播到引用它的外键上，这个动作称为级联引用完整性．语法：[CONSTRAINT约束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表[(引用列[,…n])].

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]NOACTION：任何企图删除或者更新被其他表的外键所引用的键都将引发一个错误，对数据的改变会被回滚。NOACTION是默认值CASCADE：若父表中的行变化了，则引用表中相应的行也自动变化［例5］显式说明参照完整性的违约处理示例createtableSC(Stu_nochar(8),Cou_nochar(3),GradetinyintCONSTRAINTpk_scPRIMARYKEY(Stu_no,Cou_no),foreignkey(Stu_no)referencesStudent(Stu_no)

ONDELETECASCADE

ONUPDATECASCADE,foreignkey(Cou_no)referencesCourse(Cou_no)

ONDELETENOACTION

ONUPDATECASCADE)表级约束级联删除SC表中相应的元组*级联更新SC表中相应的元组当删除course表中的元组造成了与SC表不一致时拒绝删除当更新course表中的cno时，级联更新SC表中相应的元组测试：当删除course表中的元组造成了与SC表不一致时拒绝删除当更新course表中的cno时，级联更新SC表中相应的元组１.动态列级约束:是修改列定义或列值时应满足的约束条件，包括两方面：1.修改列定义时的约束

例如，将允许空值的列改为不允许空值时，如果该列目前已存在空值，则拒绝这种修改。2.修改列值时的约束

修改列值有时需要参照其旧值，并且新旧值之间需要满足某种约束条件。例如，职工工资调整不得低于其原来工资，学生年龄只能增长等等。例如:3.动态约束２.动态元组约束:修改元组值:各个字段之间要满足的约束条件．例如

例:职工工资调整不得低于其原来工资+工龄*1.5３.

动态关系约束

关系变化前后状态：限制条件例：事务一致性、原子性等约束条件思考题如何在“图书管理”数据库上规定“可借图书册数”，限制学生借阅图书的册数在０～15册之间．完整性约束条件小结粒度状态

列级

元组级

关系级

静态列定义·类型·格式·值域·空值

元组值应满足的条件

实体完整性约束参照完整性约束函数依赖约束统计约束动态改变列定义或列值元组新旧值之间应满足的约束条件

关系新旧状态间应满足的约束条件

正确性。数据的合法性有效性。数据是否在有效范围内相容性。指同一个事实的两个数据应该一致。例如:1)无效的数据被添加到数据库中,如:某定单所指的产品不存在.2)对数据库的修改不一致,如:为某产品增加了一份定单,但却没有调整产品的库存信息.3)将存在的数据修改为无效的数据,如:将某学生的班号修改为并不存在的班级.例：创建职工表EMP，满足下列约束:1）部门编号的值必须在范围10至100内2）雇员的职称只能为下列之一:“技术员”、“工程师“、”高级工程师”3）2002年之前雇用的每个雇员的工资必须超过4000元。createtableemp(编号char(6)primarykey,姓名varchar(10)notnull,职称varchar(12)CONSTRAINTck_zccheck(职称in('技术员','工程师','高级工程师')),部门编号intCONSTRAINTck_deptcheck(部门编号between10and100or部门编号isnull),参加工作时间datetime,工资money,

CONSTRAINTCK_salcheck(YEAR(参加工作时间)>=2002or工资>4000))5.6触发器

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。数据库触发器有以下的作用：

安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数。

可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。

实现复杂的数据完整性规则。实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。

实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。DELETED和INSERTED是SQLServer为触发器创建的概念上的表。该表在结构上同触发器定义于其上的表相似，并且保存了可能被用户的行动改变的旧的或者新的行中的值。该表将跟踪在Transact-SQL中的行一级的变化。当一个INSERT、UPDATE、或者DELETE语句在SQLServer中执行的时候，行被加入到触发器表中，而且是同时加入到INSERTED和DELETED表中。INSERTED和DELETED表同触发器表是一样的。它们有同样的列名和数据类型。例如，如果在GRADE表中放置一个触发器，那么INSERTED和DELETED就有这样的结构。GRADEINSERTEDDELETEDSNCHAR(9)SNCHAR(9)SNCHAR(9)

CODEVARCHAR(4)CODEVARCHAR(4)CODEVARCHAR(4)

GRADEVARCHAR(2)GRADEVARCHAR(2)GRADEVARCHAR(2)INSERTED和DELETED表可以被触发器检查以确定要执行什么样的触发器行动。INSERTED表同INSERT和UPDATE语句一起使用。DELETED表则和DELETE以及UPDATE语句一起使用。UPDATE语句使用INSERTED和DELETED表，这是因为进行UPDATE操作时，SQLServer总是要删除旧的行，填入新的行。因此，执行UPDATE时，INSERTED表中的行总是DELETED表中的行的副本。[例1].某学生退学,将此学生从S表中删除的同时,将SC表中相关数据删除.CREATETRIGGERTRI_S1ONSFORDELETEASDELETESCFROMSCA,DELETEDBWHEREA.STU_NO=B.STU_NO指定的数据行被用户从基本表中删除,然后转移到DELETE表中[例2]:在SC表上建立一个触发器,其作用是不允许对SCORE作修改CREATETRIGGERTRI_SC1ONSCFORUPDATEASIFUPDATE(SCORE)BEGINRAISERROR(‘不允许修改’,10,1)ROLLBACKTRANSACTIONEND[例3]在学生表创建一个afterupdate触发器,当在学生表上更新记录时屏幕上显示有多少条记录被更新。CREATETRIGGERTRI_Stu2ONStudentAFTERUPDATEASdeclare@akchar(20)select@ak=str(@@rowcount,4)+'个记录被更新'print@akreturnupdatestudentsetstu_age=20wherestu_no='20026102'[例4]定义一个触发器：规定在星期六或星期日或者在某些方面8点至17点以外时间不能更新SC表系统检查当时的系统时间，如是周六或周日，或者不是8点至17点，系统会拒绝执行用户的更新操作，并提示出错信息CREATETRIGGERtri_scONStudentFORINSERT,UPDATE,DELETEASIF(datename(weekday,getdate())IN('星期六','星期日'))OR(datename(hh,getdate())NOTBETWEEN8AND16)BEGINprint‘不允许修改数据'

ROLLBACKTRANSACTIONEND[例5]、设计一个触发器，要求在修改s表时，不允许修改后的年龄小于原来的年龄。createtriggerupdateage2onstudentforupdateasbegindeclare@oldage

int,@newage

intifupdate(age)begin

select@oldage=agefromdeletedselect@newage=agefrominsertedif@oldage>@newagebegin

raiserror('updateisfail!',10,1)rollbacktransactionendendendCREATETRIGGEROrdDet_InsertON[OrderDetails]FORINSERTASUPDATEPSETUnitsInStock=P.UnitsInStock-I.QuantityFROMProductsASPINNERJOINInsertedASIONP.ProductID=I.ProductID[例6]：将创建一个触发器。无论何时订购产品（无论何时向OrderDetails表中插入一条记录），这个触发器都将更新Products表中的一列(UnitsInStock)。用原来的值减去订购的数量值即为新值。[例7]：将创建一个触发器，无论何时删除一个产品类别（即从Categories表中删除一条记录），该触发器都会更新Products表中的Discontinued列。所有受影响的产品都标记为1，标示不再使用这些产品了。CREATETRIGGERCategory_DeleteONCategoriesFORDELETEASUPDATEPSETDiscontinued=1FROMProductsASPINNERJOINdeletedASdONP.CategoryID=d.CategoryID[例8]你在开发某公司的管理信息系统，其中要跟踪经理的购买情况。每个经理在预算表中都有一个记录。预算表包含列“经理代号”、列“预算总金额”、列“现有预算金额”；购买表包含列“购买代号”、列“购买金额”，列“经理代号”。每次购买都要与“现有预算金额”作比较，当该次购买的“购买金额”小于“现有预算金额”时，才允许插入到购买表中（一次购买只插入一个记录），同时自动更改预算表的“现有预算金额”。请在购买表上编写一个触发器，完成该任务。CREATETRIGGERPurchase_InsertON购买表FORINSERTASBEGINIF判断是否只插入一次BEGINRAISERROR('一次购买只允许插入一个记录！',16,1)ROLLBACKTRANSACTIONENDIF“现有预算金额”小于”购买金额””BEGINRAISERROR('现有预算金额不足支付购买金额！',16,1)ROLLBACKTRANSACTIONEND

修改预算表中的现有预算金额END16表明错误可由用户修正。CREATETRIGGERPurchase_InsertON购买表FORINSERTASBEGINIF(SELECTcount(*)FROMInserted)<>1)BEGINRAISERROR('一次购买只允许插入一个记录！',16,1)ROLLBACKTRANSACTIONENDIF(SELECTa.现有预算金额-b.购买金额FROM预算表ASaINNERJOINInsertedASbONa.经理代号=b.经理代号)