数据库原理-第七章(数据完整性)

数据库系统概论AnIntroductiontoDatabaseSystem数据库的完整性AnIntroductiontoDatabaseSystem数据库完整性数据库的完整性数据的正确性和相容性数据的完整性和安全性是两个不同概念数据的完整性防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据防范对象：不合语义的、不正确的数据数据的安全性保护数据库防止恶意的破坏和非法的存取防范对象：非法用户和非法操作AnIntroductiontoDatabaseSystem数据库完整性(续)为维护数据库的完整性，DBMS必须：1.提供定义完整性约束条件的机制2.提供完整性检查的方法3.违约处理AnIntroductiontoDatabaseSystem实体完整性实体完整性定义实体完整性检查和违约处理AnIntroductiontoDatabaseSystem实体完整性定义关系模型的实体完整性CREATETABLE中用PRIMARYKEY定义单属性构成的码有两种说明方法定义为列级约束条件定义为表级约束条件对多个属性构成的码只有一种说明方法定义为表级约束条件

AnIntroductiontoDatabaseSystem实体完整性定义(续)［例1］将Student表中的Sno属性定义为码

(1)在列级定义主码

CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(20)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20));AnIntroductiontoDatabaseSystem实体完整性定义(续)(2)在表级定义主码

CREATETABLEStudent(SnoCHAR(9)，SnameCHAR(20)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)，

PRIMARYKEY(Sno));AnIntroductiontoDatabaseSystem实体完整性定义(续)［例2］将SC表中的Sno，Cno属性组定义为码

CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，

PRIMARYKEY(Sno，Cno)/*只能在表级定义主码*/);AnIntroductiontoDatabaseSystem实体完整性检查和违约处理插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括：1.检查主码值是否唯一，如果不唯一则拒绝插入或修改2.检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改AnIntroductiontoDatabaseSystem实体完整性检查和违约处理(续)检查记录中主码值是否唯一的一种方法是进行全表扫描AnIntroductiontoDatabaseSystem实体完整性检查和违约处理(续)索引

AnIntroductiontoDatabaseSystem参照完整性定义关系模型的参照完整性定义在CREATETABLE中用FOREIGNKEY短语定义哪些列为外码用REFERENCES短语指明这些外码参照哪些表的主码AnIntroductiontoDatabaseSystem参照完整性定义(续)例如，关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码。Sno，Cno分别参照引用Student表的主码和Course表的主码［例3］定义SC中的参照完整性CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，

PRIMARYKEY(Sno，Cno)，/*在表级定义实体完整性*/

FOREIGNKEY(Sno)REFERENCESStudent(Sno)，

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

FOREIGNKEY(Cno)REFERENCESCourse(Cno)

/*在表级定义参照完整性*/);AnIntroductiontoDatabaseSystem参照完整性检查和违约处理可能破坏参照完整性的情况及违约处理被参照表（例如Student）参照表（例如SC）违约处理可能破坏参照完整性插入元组拒绝可能破坏参照完整性修改外码值拒绝删除元组可能破坏参照完整性拒绝/级连删除/设置为空值修改主码值可能破坏参照完整性拒绝/级连修改/设置为空值AnIntroductiontoDatabaseSystem违约处理参照完整性违约处理1.拒绝(NOACTION)执行默认策略2.级联(CASCADE)操作3.设置为空值（SET-NULL）对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值AnIntroductiontoDatabaseSystem违约处理(续)［例4］显式说明参照完整性的违约处理示例CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，PRIMARYKEY（Sno，Cno）， FOREIGNKEY(Sno)REFERENCESStudent(Sno) ONDELETECASCADE/*级联删除SC表中相应的元组*/ONUPDATECASCADE，/*级联更新SC表中相应的元组*/FOREIGNKEY(Cno)REFERENCESCourse(Cno) ONDELETENOACTION /*当删除course表中的元组造成了与SC表不一致时拒绝删除*/ONUPDATECASCADE /*当更新course表中的cno时，级联更新SC表中相应的元组*/)；AnIntroductiontoDatabaseSystem用户定义的完整性用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求RDBMS提供，而不必由应用程序承担AnIntroductiontoDatabaseSystem属性上的约束条件的定义CREATETABLE时定义列值非空（NOTNULL）列值唯一（UNIQUE）检查列值是否满足一个布尔表达式（CHECK）AnIntroductiontoDatabaseSystem属性上的约束条件的定义(续)1.不允许取空值

［例5］在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。CREATETABLESC（SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL，

GradeSMALLINTNOTNULL，

PRIMARYKEY(Sno，Cno)，/*如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义就不必写了*/）；AnIntroductiontoDatabaseSystem属性上的约束条件的定义(续)2.列值唯一［例6］建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码CREATETABLEDEPT(DeptnoNUMERIC(2)，DnameCHAR(9)UNIQUE，/*要求Dname列值唯一*/LocationCHAR(10)，PRIMARYKEY(Deptno))；AnIntroductiontoDatabaseSystem属性上的约束条件的定义(续)3.用CHECK短语指定列值应该满足的条件［例7］Student表的Ssex只允许取“男”或“女”。CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(8)NOTNULL，SsexCHAR(2)CHECK(SsexIN(‘男’，‘女’))，/*性别属性Ssex只允许取'男'或'女'*/SageSMALLINT，SdeptCHAR(20));AnIntroductiontoDatabaseSystem属性上的约束条件检查和违约处理插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足如果不满足则操作被拒绝执行AnIntroductiontoDatabaseSystem元组上的约束条件的定义在CREATETABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件

AnIntroductiontoDatabaseSystem元组上的约束条件的定义(续)［例9］当学生的性别是男时，其名字不能以Ms.打头。

CREATETABLEStudent(SnoCHAR(9)，SnameCHAR(8)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)，PRIMARYKEY(Sno)，

CHECK(Ssex='女'ORSnameNOTLIKE'Ms.%')/*定义了元组中Sname和Ssex两个属性值之间的约束条件*/)；性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立；当性别是男性时，要通过检查则名字一定不能以Ms.打头AnIntroductiontoDatabaseSystem元组上的约束条件检查和违约处理插入元组或修改属性的值时，RDBMS检查元组上的约束条件是否被满足如果不满足则操作被拒绝执行

AnIntroductiontoDatabaseSystem完整性约束命名子句CONSTRAINT约束CONSTRAINT<完整性约束条件名>［PRIMARYKEY短语|FOREIGNKEY短语|CHECK短语］AnIntroductiontoDatabaseSystem完整性约束命名子句(续)［例10］建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。CREATETABLEStudent(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四个列级约束。AnIntroductiontoDatabaseSystem完整性约束命名子句(续)2.修改表中的完整性限制使用ALTERTABLE语句修改表中的完整性限制AnIntroductiontoDatabaseSystem完整性约束命名子句(续)［例13］修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40可以先删除原来的约束条件，再增加新的约束条件

ALTERTABLEStudentDROPCONSTRAINTC1;

ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN900000AND999999)，

ALTERTABLEStudentDROPCONSTRAINTC3;

ALTERTABLEStudentADDCONSTRAINTC3CHECK(Sage<40)；AnIntroductiontoDatabaseSystem存储过程创建一个存储过程语法：语法CREATEPROCEDURE<procedure_name>[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

ASsql_statement[...n]

AnIntroductiontoDatabaseSystem定义存储过程的几种方式A.使用带有复杂SELECT语句的简单过程下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname='au_info_all'ANDtype='P')DROPPROCEDUREau_info_allGOCREATEPROCEDUREau_info_allASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idGOAnIntroductiontoDatabaseSystemB.使用带有参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。CREATEPROCEDUREau_info@lastnamevarchar(40),@firstnamevarchar(20)ASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idWHEREau_fname=@firstnameANDau_lname=@lastnameGO定义存储过程的几种方式EXECUTEau_info'Dull','Ann'AnIntroductiontoDatabaseSystemC.使用带有通配符参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。CREATEPROCEDUREau_info2@lastnamevarchar(30)='D%',@firstnamevarchar(18)='%'ASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idWHEREau_fnameLIKE@firstnameANDau_lnameLIKE@lastnameGO定义存储过程的几种方式AnIntroductiontoDatabaseSystemD.使用OUTPUT参数OUTPUT参数允许外部过程、批处理或多条Transact-SQL语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程(titles_sum)，并使用一个可选的输入参数和一个输出参数。首先，创建过程：CREATEPROCEDUREtitles_sum@@SUMintOUTPUTASSELECT@@SUM=SUM(price)FROMtitlesWHEREtitleLIKE@@TITLEGO接下来，将该OUTPUT参数用于控制流语言。说明

OUTPUT变量必须在创建表和使用该变量时都进行定义。参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用@@SUM=variable形式）。DECLARE@@TOTALCOSTintEXECUTEtitles_sum@@TOTALCOSTOUTPUTIF@@TOTALCOST<200BEGINPRINT‘总价少于200元！'ENDELSERTRIM(CAST(@@TOTALCOSTASvarchar(20)))定义存储过程的几种方式AnIntroductiontoDatabaseSystem基本编程技术局部变量声明：所有在存储过程中的局部变量都必须在使用之前声明，并且名称必须唯一；变量的适用范围是从声明开始到过程语句结束的所有代码；存储过程中可以适用（begin….end）语句来嵌套块，但是对变量的作用范围没有影响。Declare@<变量名><数据类型>例如：declare@rowCntint赋值语句1、可以使用set语句对一个变量或参数赋值set@rowCnt=102、可以使用select语句对多个变量赋值（主要用于对表中数据列）CreateprocedureGet_discount@custIDint,@Namevarchar(20),@DiscountintAsselect@Name=name,@discount=discountFromcustomerwhereCustId=@custIdAnIntroductiontoDatabaseSystem显示消息Print语句语序返回任何字符表达式，包括文字、字符参数和变量Print@rowCnt条件执行：if….elseif(@discount>90and@discount<100)beginprint‘普通会员’endelseif(@discount>70and@discount<80)beginprint‘高级会员’end基本编程技术AnIntroductiontoDatabaseSystemWhile循环createproceduretestasdeclare@countint,declare@limitintset@count=0set@limit=10while(@count<@limit)beginprint‘循环继续！’

set@count=@count+1end基本编程技术AnIntroductiontoDatabaseSystem使用游标关系数据库中的操作会对整个行集产生影响。由SELECT语句返回的行集包括所有满足该语句WHERE子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。AnIntroductiontoDatabaseSystem1、声明游标2、打开游标3、从游标中提取行，有选择的修改或者删除所选择的行4、关闭游标5、释放游标使用游标的步骤AnIntroductiontoDatabaseSystem声明游标DECLAREcursor_nameCURSOR

FORselect_statement

[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]列1：createprocedurelistcustasdeclare@custIdintdeclare@cusNamevarchar(30)declareCustCursorCursorForselectcustId,NamefromcustomerorderbycustIdforreadonlyopencustCursorwhile(0=0)beginfetchnextfromcustCursorinto@custId,@custNameif(@@fetch_status<>0)break/*0表示成功，-1表示没有未读的行，-2表示行已被删除print‘客户编号：’+@custId’+’---’+’客户姓名：’+@cusNameendClosecustCursoDeallocatecustCursorAnIntroductiontoDatabaseSystem游标的类型1、可滚动游标：可以在Cursor关键字前加Scroll关键字declarecustCursorScrollCursorforselectcustId,NamefromcustomerorderbycustIdforreadonlyNext下一行Prior前一行First第一行Last最后一行AbsolutenN>0定位到从开始的第n行N=0没有返回的行N<0定位到末尾前的第n行RelativenN<-1定位到当前行前的第n行N=-1同prior相同N=0定位到当前行（重读）N=1与next相同N>1定位到当前行之后的第n行AnIntroductiontoDatabaseSystemDeclare@custIdintDeclare@discountdec(5,3)DeclarecustCursorCursorforselectcustId,discountfromcustomerorderbycustIdforupdateofdiscountOpencustCursor游标的类型—可更新游标While(0=0)BeginfetchnextfromcustCursorinto@custId,@discountif(@@fetch_Status=0)beginif(@discount=0.0)begindeletefromcustomerwherecurrentofCustCursorendelseif(@discount>0.5)beginupdatecustomersetdiscount=0.5wherecurrentofcustCursorendendEndClosecustCursorDeallocatecustCursorAnIntroductiontoDatabaseSystem触发器触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程由服务器自动激活可以进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力

AnIntroductiontoDatabaseSystem定义触发器CREATETRIGGER语法格式

CREATETRIGGER<触发器名>

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

<触发动作体>

AnIntroductiontoDatabaseSystem定义触发器(续)定义触发器的语法说明:1.创建者：表的拥有者2.触发器名3.表名：触发器的目标表4.触发事件：INSERT、DELETE、UPDATE5.触发器类型行级触发器（FOREACHROW）语句级触发器（FOREACHSTATEMENT）AnIntroductiontoDatabaseSystem定义触发器(续)例如,假设在［例11］的TEACHER表上创建了一个AFTERUPDATE触发器。如果表TEACHER有1000行，执行如下语句：UPDATETEACHERSETDeptno=5;

如果该触发器为语句级触发器，那么执行完该语句后，触发动作只发生一次如果是行级触发器，触发动作将执行1000次AnIntroductiontoDatabaseSystem定义触发器(续)6.触发条件触发条件为真省略WHEN触发条件7.触发动作体触发动作体可以是一个匿名PL/SQL过程块也可以是对已创建存储过程的调用AnIntroductiontoDatabaseSystem定义触发器(续)［例18］定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。CREATETRIGGERInsert_Or_Update_Salforupdate,insertas declare@enonumeric(4)declare@salnumeric(7,2)select@eno=eno,@sal=salfrom