Access数据库第五章.ppt

AnIntroductiontoDatabaseSystems,1,上节回顾,第四章数据库安全性4.2数据库安全性控制授权与回收数据库角色强制存取控制方法4.4审计4.5数据加密4.6统计数据库安全性,AnIntroductiontoDatabaseSystems,2,数据库系统概论AnIntroductiontoDatabaseSystem第五章数据库完整性,东莞理工学院城市学院计算机与信息科学系,AnIntroductiontoDatabaseSystems,3,第五章数据库完整性,定义指数据的正确性和相容性目的为了防止不合语义的数据进入数据库。现实世界的实例：学生的性别只能是男或女；本科生的年龄必须是整数，取值范围为1450；学生的学号一定是唯一的；学生所在的系必须是学校开设的系；,AnIntroductiontoDatabaseSystems,4,第五章数据库完整性,完整性和安全性的区别目的不同完整性是防止数据库中存在不正确的数据;安全性是防止数据库被恶意破坏或非法存取。防范对象不同完整性是防范不合语义、不正确的数据;安全性是防范非法用户和非法操作。,AnIntroductiontoDatabaseSystems,5,数据库完整性,完整性控制机制包括：完整性约束条件定义机制DBMS应提供定义数据库完整性约束条件，并把它们作为模式的一部分存入数据库中完整性检查的方法检查用户发出的操作请求是否违背了完整性约束条件，一般在：INSERT、UPDATE、DELETE语句执行后以及事务提交时检查。,AnIntroductiontoDatabaseSystems,6,数据库完整性,违约处理如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性，包括：拒绝（NOACTION）执行该操作，或级联（CASCADE）执行其他操作。,AnIntroductiontoDatabaseSystems,7,第五章数据库完整性,5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.6触发器5.7小结,AnIntroductiontoDatabaseSystems,8,5.1.1实体完整性定义,在CREATETABLE中用PRIMARYKEY定义，可以是列级约束条件也可以是表级约束条件，但有些时候只能定义表级约束条件。,AnIntroductiontoDatabaseSystems,9,5.1.1实体完整性定义,【例1】将Student表中的Sno属性定义为码CREATETABLEStudent/*列级定义主码*/(SnoCHAR(9)PRIMARYKEY,SnameCHAR(20)NOTNULL,SsexCHAR(2),SageSMALLINT,SdeptCHAR(15);,AnIntroductiontoDatabaseSystems,10,5.1.1实体完整性定义,CREATETABLEStudent/*表级定义主码*/(SnoCHAR(9),SnameCHAR(20)NOTNULL,SsexCHAR(2),SageSMALLINT,SdeptCHAR(15),PRIMARYKEY(Sno)；,AnIntroductiontoDatabaseSystems,11,5.1.1实体完整性定义,【例2】将SC表中的Sno，Cno属性组定义为码CREATETABLESC/*只能在表级定义主码*/(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno);,AnIntroductiontoDatabaseSystems,12,5.1.2实体完整性检查和违约处理,检查当用户程序对基本表插入一条记录或对主码进行更新操作时进行检查。包括：检查主码的值是否唯一，不唯一则拒绝操作；检查主码的各个属性是否为空，只要有一个为空就拒绝操作。,AnIntroductiontoDatabaseSystems,13,第五章数据库完整性,5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.6触发器5.7小结,AnIntroductiontoDatabaseSystems,14,第五章数据库完整性,附：创建Course表的SQL语句CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40),CpnoCHAR(4),CcreditSMALLINT,FOREIGNKEY(Cpno)REFERENCESCourse(Cno);,AnIntroductiontoDatabaseSystems,15,5.2.1参照完整性定义,在CREATETABLE中用FOREIGNKEY定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。,AnIntroductiontoDatabaseSystems,16,5.2.1参照完整性定义,【例3】定义SC中的参照完整性CREATETABLESC(SnoCHAR(9),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno);,AnIntroductiontoDatabaseSystems,17,5.2.2参照完整性检查和违约处理,检查当用户程序对被参照表和参照表时进行增删改操作时进行检查。处理：对参照表的增、改外码值时可能破坏参照完整性，则以拒绝操作处理。对被参照表进行删、修改主码值时可能破坏参照完整性，则以拒绝/级联删除/设置为空值操作处理。,AnIntroductiontoDatabaseSystems,18,5.2.2参照完整性检查和违约处理,【注意】系统默认的处理方式是拒绝处理，如果要让系统采用其他策略，则要在创建表时显式定义。,AnIntroductiontoDatabaseSystems,19,5.2.2参照完整性检查和违约处理,【例4】显式说明参照完整性的违约处理示例CREATETABLESC(SnoCHAR(9),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEYSnoREFERENCESStudent(Sno)ONDELETECASCADEONUPDATECASCADE,FOREIGNKEYCnoREFERENCESCourse(Cno)ONDELETENOACTIONONUPDATECASCADE,);,当删除Student表中的元组时，级联删除SC表中相应的元组,AnIntroductiontoDatabaseSystems,20,第五章数据库完整性,5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.6触发器5.7小结,AnIntroductiontoDatabaseSystems,21,5.3.1属性上的约束条件的定义,在CREATETABLE中根据需要定义，包括：列值非空（NOTNULL）列值唯一（UNIQUE）检查列值是否满足一个布尔表达式（CHECK）,AnIntroductiontoDatabaseSystems,22,5.3.1属性上的约束条件的定义,用CHECK短语指定列值应该满足的条件【例8】SC表的Grade的值应该在0和100之间。CREATETABLESC(SnoCHAR(9)NOTNULL,CnoCHAR(4)NOTNULL,GradeSMALLINTCHECK(Grade=0ANDGrade=100),PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno);,AnIntroductiontoDatabaseSystems,23,5.3.2属性上的约束条件检查和违约处理,检查当往表中插入元组或修改属性的值时进行检查。处理：不满足则以拒绝操作处理。,AnIntroductiontoDatabaseSystems,24,5.3.3元组上的约束条件的定义,用CHECK短语指定表级应该满足的条件【例9】当学生的性别是男时，其名字不能以Ms.打头。CREATETABLEStudent(SnoCHAR(9),SnameCHAR(20)NOTNULL,SsexCHAR(2),SageSMALLINT,SdeptCHAR(15),PRIMARYKEY(Sno),CHECK(Ssex=女ORSnameNOTLIKEMs.%);,AnIntroductiontoDatabaseSystems,25,5.3.4元组上的约束条件检查和违约处理,检查当往表中插入元组或修改属性的值时进行检查。处理：不满足则以拒绝操作处理。,AnIntroductiontoDatabaseSystems,26,第五章数据库完整性,5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.6触发器5.7小结,AnIntroductiontoDatabaseSystems,27,1完整性约束命名子句,在CREATETABLE中提供了完整性约束命名子句：CONSTRAINT。CONSTRAINT格式：CONSTRAINTPRIMARYKEY短语|FOREIGNKEY短语|CHECK短语,AnIntroductiontoDatabaseSystems,28,1完整性约束命名子句,【例10】建立学生登记表Student，要求学号在2010000120102999，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。,AnIntroductiontoDatabaseSystems,29,1完整性约束命名子句,CREATETABLEStudent(SnoNUMERIC(8)CONSTRAINTC1CHECK(SnoBETWEEN2010001AND20102999),SnameCHAR(20)CONSTRAINTC2NOTNULL,SsexCHAR(2)CONSTRAINTC3CHECK(SsexIN(男,女),SageNUMERIC(3)CONSTRAINTC4CHECK(Sage=3000);,AnIntroductiontoDatabaseSystems,31,2修改表中的完整性限制,在ALTERTABLE中修改表中完整性限制。可先删除原来的约束条件再增加新的约束条件。删除：ALTERTABLE表名DROPCONSTRAINT增加：ALTERTABLE表名ADDCONSTRAINTCHECK例子见课本P159例13。,AnIntroductiontoDatabaseSystems,32,SQLServer2000中添加默认值约束,格式：ALTERTABLE用户名.表名ADDCONSTRAINT默认值名DEFAULT具体值FOR字段名删除：ALTERTABLE用户名.表名DROPCONSTRAINT默认值名,AnIntroductiontoDatabaseSystems,33,【例】:ALTERTABLEdbo.StudentADDCONSTRAINTdf_StuDEFAULT计算机系FORSdept;删除：ALTERTABLEdbo.StudentDROPCONSTRAINTdf_Stu;,SQLServer2000中添加默认值约束,AnIntroductiontoDatabaseSystems,34,第五章数据库完整性,5.1实体完整性5.2参照完整性5.3用户定义的完整性5.4完整性约束命名子句5.6触发器5.7小结,AnIntroductiontoDatabaseSystems,35,5.6触发器,触发器（Trigger）是一种特殊类型的存储过程；触发器主要是通过事件进行触发而被执行的；当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLServer就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则；触发器的主要作用就是其能够实现由主码和外码所不能保证的复杂的参照完整性和数据的一致性。,AnIntroductiontoDatabaseSystems,36,5.6.1定义触发器,SQL2000中创建触发器的一般格式：CREATETRIGGER触发器名ON表名|视图名FOR|AFTERINSERT,UPDATE,DELETEASIFUPDATE(列名)AND|ORUPDATE(列名)nSQL语句n,AnIntroductiontoDatabaseSystems,37,5.6.1定义触发器,说明：创建表的用户才可以创建此表的触发器，一个表只能创建一定数量的触发器。表名为触发器的目标表的名称。AFTER：指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。,AnIntroductiontoDatabaseSystems,38,5.6.1定义触发器,触发事件可以是：INSERT、DELETE、UPDATE或上述事件的组合，另外，UPDATE后面还可以有OF，即进一步指明修改哪些列时触发器激活。AS：是触发器要执行的操作。SQL语句：是触发器的条件和操作。触发器条件指定其它准则，以确定DELETE、INSERT或UPDATE语句是否导致执行触发器操作。,AnIntroductiontoDatabaseSystems,39,5.6.1定义触发器,触发器被激活时，只有当触发条件为真时，触发动作体才执行；否则触发动作体不执行。如果省略触发条件，则触发动作体在触发器被激活后立即执行。触发器动作体可以是SQL过程块或对已创建的存储过程的调用。,AnIntroductiontoDatabaseSystems,40,5.6.1定义触发器,IFUPDATE(列名)：测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作。可以指定多列。因为在ON子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。,AnIntroductiontoDatabaseSystems,41,5.6.1定义触发器,【例】定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，则自动改为4000元”。CREATETRIGGERInsert_Or_Update_SalBEFOREINSERTORUPDATEONTeacherFOREACHROWASBEGINIF(new.Job=教授)AND(new.Sal4000)THENnew.Sal:=4000;ENDIF;END,AnIntroductiontoDatabaseSystems,42,5.6.1定义触发器,【例18】SQL2000CREATETRIGGERInsert_Or_Update_SalONTeacherFORINSERT,UPDATEASIFEXISTS(SELECT*FROMTeacherWHEREJob=教授ANDSal4000)UPDATETeacherSETSal=4000;,AnIntroductiontoDatabaseSystems,43,5.6.2激活触发器,【例】Insert触发器CREATETRIGGERinsert_studentONStudentAFTERINSERTASPRINT数据插入成功!;当执行Insert语句时，会激活此触发器：insertintoStudentvalues(201015131,王五,男,20,CS);,AnIntroductiontoDatabaseSystems,44,5.6.2激活触发器,【例】Insert触发器CREATETRIGGERinsert_student_1ONStudentAFTERINSERTASDECLAREaCHAR(9)Selecta=Inserted.SnoFromInsertedPRINT学号为+a+的学生数据插入成功!;,AnIntroductiontoDatabaseSystems,45,5.6.2激活触发器,【例】Update触发器CREATETRIGGERupdate_studentONstudentAFTERUPDATEASSELECT*FROMstudentORDERBYsno;当更新数据时就触发了这个查询语句，就能显示学生表中的学生信息：UpdateStudentSetSag