SQL Server 2005数据库控制与维护(ppt 65页).ppt

1、第8章,第8章SQLServer2005数据库控制与维护,数据库应用教程,数据库的完整性控制,数据库的安全性管理,数据库备份与还原,第8章SQLServer2005数据库控制与维护,8.1.1数据完整性概述,8.1.2使用约束实施数据完整性控制,8.1.3使用规则实施数据完整性控制,8.1数据库的完整性控制,8.1.4使用默认值实施数据完整性控制,8.1.5使用触发器实施数据完整性控制,8.1.1数据完整性概述,1完整性的概念,数据完整性是指数据的正确性、有效性、一致性和相容性。由于数据库是一个动态的集合，数据不断地被插入和被修改，因此由于主观或客观的原因，可能破坏数据库的数据完整性。,2破坏

2、数据库的数据完整性的情况,（1）无效的数据被添加到数据库中。如：某在教师管理系统中，输入的教师号不存在。,（2）对数据库的修改不一致。如：在两个不同的表中，同一教师的系号不同。,（3）将存在的数据修改为无效的数据。如：将某教师所在的系号修改为并不存在的系。,8.1.1数据完整性概述,3完整性机制,为了保证存放数据的完整性，DBMS应能对数据库进行数据完整性控制。在SQLServer2000中，提供了约束、默认值、规则、触发器等维护机制对数据完整性进行控制。,4完整性分类,在SQLServer中，数据完整性分成四类：实体完整性、域完整性、参照完整性和用户自定义完整性。,8.1.1数据完整性概述,

3、域完整性：也可称为列完整性。域完整性要求：向表中指定列输入的数据必须具有正确的数据类型、格式以及有效的数据范围。例如，在CollegeMIS数据库的SelectCourse表中，向Score（成绩）列输入数据时，不能出现字符，也不能输入小于0或大于100的数值。域完整性的控制方法：主要有CHECK约束、外键约束、默认约束、默认值、非空定义、规则以及在创建表时设置的数据类型。,实体完整性的控制方法：主要有主键约束、惟一索引、惟一约束和指定IDENTITY属性。,5完整性控制方法,参照完整性的控制方法：主要有外键约束，有时利用触发器也可实现参照完整性控制。,8.1.2使用约束实施数据完整性控制,在

4、SQLServer中，可通过约束来实施数据库的数据完整性控制，常用的约束有NOTNULL(非空)约束、CHECK(检查)约束、UNIQUE(惟一)约束、PRIMARYKEY(主键)约束、FOREIGNKEY(外键)约束和DEFAULT(默认)约束。,1约束的建立,【格式】ALTERTABLEtable_nameADDCONSTRAINTconstraint_namePRIMARYKEYCLUSTERED|NONCLUSTERED(column,.n),（1）主键约束的创建,【功能】为table_name作为表名指定的表增加一个由constraint_name指定名称的主键约束。,【例8-1】把

5、CollegeMIS数据库中SelectCourse表中的主键pk_xuanke删除，然后重建。,USECollegeMISGOALTERTABLESelectCourseDROPCONSTRAINTpk_xuankeGOALTERTABLESelectCourseADDCONSTRAINTpk_xuankePRIMARYKEY(StuNo,TeaNo,CourseNo)GO,8.1.2使用约束实施数据完整性控制,1约束的建立,【例8-2】为CollegeMIS数据库中的Department表的DepartName字段创建一个惟一约束，约束名为IX_DepartName。,（2）惟一约束的创建

6、使用企业管理器创建惟一约束,在一张数据表中，有时除主键需要具有唯一性外，还有其他列也需要具有唯一性。例如，在“系部”表中，主键为“系部代码”，但是另外一个字段“系部名称”虽不是主键，也需保证它的唯一性，这时就需要创建表中的唯一约束。,使用对象资源管理器创建唯一约束下面以“系部”表为例，为“系部名称”字段创建唯一约束。操作步骤如下：（1）在“对象资源管理器”窗格中，右击需要设置唯一约束的表（本例为“系部”表），在弹出的快捷菜单中选择“修改”命令，打开“表设计器”窗口。,（2）在“表设计器”窗口中，右击需要设置为唯一约束的字段（本例为“系部名称”字段），在弹出的快捷菜单中选择“索引/键”命令，如图

7、6-7所示，也可以直接单击工具栏中的“管理索引和键”按钮，打开“索引/键”对话框，如图6-8所示。,图6-7选择“索引/键”命令,图6-8“索引/键”对话框,（3）在打开的“索引/键”对话框中，单击“添加”按钮，结果如图6-9所示。,图6-9单击“添加”按钮创建唯一约束,（4）设置好相关选项后，单击“关闭”按钮，完成唯一约束的创建。这时，不只是该表的主键必须为唯一，并且被设置为唯一约束的字段同样也必须为唯一。,8.1.2使用约束实施数据完整性控制,1约束的建立,【格式】ALTERTABLEtable_nameADDCONSTRAINTconstraint_nameUNIQUECLUSTERED

8、|NONCLUSTERED(column,.n),（2）惟一约束的创建使用Transact-SQL语句,【功能】为table_name作为表名指定的表增加一个由constraint_name指定名称的惟一约束。说明：惟一约束默认的索引类型为NONCLUSTERED。,8.1.2使用约束实施数据完整性控制,1约束的建立,【例8-3】通过Transact-SQL语句为CollegeMIS数据库中的Department表的Telephone字段创建一个惟一约束，约束名为IX_Telephone。,（2）惟一约束的创建使用Transact-SQL语句,USECollegeMISGOALTERTABLE

9、DepartmentADDCONSTRAINTIX_TelphoneUNIQUE(Telephone)GO,8.1.2使用约束实施数据完整性控制,1约束的建立,【例8-4】为CollegeMIS数据库中的“课程表”的“学分”字段创建一个检查约束，约束名为CK_Grade。检查条件只允许为17分，不允许小于1分的学分和大于7分的学分出现。,（3）检查约束的创建使用对象资源管理器,检查约束对输入的数据的值做检查，可以限定数据输入，从而维护数据的域完整性。,操作步骤如下：（1）在“对象资源管理器”窗格中，右击需要设置唯一约束的表（本例为“课程表”），在弹出的快捷菜单中选择“修改”命令，打开“表设计器

10、”窗口。（2）在“表设计器”窗口中右击需要创建检查约束的字段（本例为“学分”字段），在弹出的快捷菜单中选择“CHECK约束”命令，如图6-11所示，打开“CHECK约束”对话框。,图6-11选择“CHECK约束”命令,（3）在“CHECK约束”对话框中，单击“添加”按钮，然后在“(名称)”文本框中输入检查约束名称，在约束“表达式”文本框中输入约束条件，这里输入“(学分=1AND学分=7)”，如图6-12所示。,图6-12设置“CHECK约束”条件,（4）单击“关闭”按钮关闭对话框，完成检查约束的创建。注意：如果表中原来就有数据，并且数据类型或范围与所创建的约束相冲突，那么约束将不能成功创建。,

11、8.1.2使用约束实施数据完整性控制,1约束的建立,【格式】ALTERTABLEtable_nameADDCONSTRAINTconstraint_nameCHECK(logical_expression),（3）检查约束的创建使用Transact-SQL语句,【功能】为table_name作为表名指定的表增加一个由constraint_name指定名称的检查约束。说明：“logical_expression”是检查约束的检查条件，通常是一个关系表达式或逻辑表达式。,8.1.2使用约束实施数据完整性控制,1约束的建立,【例8-5】使用Transact-SQL语句，为CollegeMIS数据库中

12、的Student表的Birthday字段创建一个检查约束，约束名为CK_Birthday。检查条件为Birthday字段的值必须小于当前日期。,（3）检查约束的创建使用Transact-SQL语句,USECollegeMISGOALTERTABLEStudentADDCONSTRAINTCK_BirthdayCHECK(Birthday0BEGINPRINT该教师在选课表中已经有授课信息，不能删除！ROLLBACKTRANSACTIONENDELSEPRINT记录删除成功GO,【例8-17】为CollegeMIS数据库中的表Teacher表建立一个名为Update_Teacher的Update

13、触发器。当用户修改Teacher表的TeaID的值时，提示用户不能修改身份证号。,USECollegeMISGOCREATETRIGGERUpdate_TeacherONTeacherFORUPDATEASIFUPDATE(TeaID)BEGINPRINT不能修改教师的身份证号ROLLBACKTRANSACTIONENDGO,8.1.5使用触发器实施数据完整性控制,3查看触发器信息,（1）使用对象资源管理器查看触发器信息,（2）使用系统存储过程查看触发器信息,可以使用系统存储过程sp_help、sp_helptext和sp_helptrigger来查看触发器信息。sp_help和sp_help

14、text的使用方法，在前面的各章已经多次讲解。调用sp_helptrigger的语句格式如下：EXECsp_helptriggertable_nameINSERT,UPDATE,DELETE,例如：USECollegeMISGOEXECsp_helptriggerTeacherGO,8.1.5使用触发器实施数据完整性控制,4修改触发器,（1）使用对象资源管理器修改触发器,（2）使用Transact-SQL语句修改触发器,修改触发器的定义，可以使用Transact-SQL语句：ALTERTRIGGER,命令中有许多选项，含义与CREATETRIGGER含义相同。,语法：ALTERTRIGGERt

15、rigger_nameON(table|view)WITHENCRYPTION(FOR|AFTER|INSTEADOF)INSERT,DELETE,UPDATENOTFORREPLICATIONASsql_statement,n|(FOR|AFTER|INSTEADOF)INSERT,UPDATENOTFORREPLICATIONASIFUPDATE(column)AND|ORUPDATE(column),n|IF(COLUMNS_UPDTED()bitwise_operatorupdated_bitmask)(comparison_operator)column_bitmask,nsql_s

16、tatement,n其中的参数与创建触发器语句中的参数相同。,8.1.5使用触发器实施数据完整性控制,5禁止或启用触发器,【格式】ALTERTABLEENABLE|DISABLEtrigger_name,【功能】对由“trigger_name”作为名称指定的触发器禁止或启用。,8.1.5使用触发器实施数据完整性控制,6删除触发器,（1）使用对象资源管理器删除触发器信息,（2）使用Transact-SQL语句删除触发器,【格式】DROPTRIGGERtrigger_name,n,【功能】删除由“trigger_name”作为名称指定的触发器，可以删除多个触发器，触发器名之间用“,”隔开。,【功能

17、】把例8-16创建的名为delete_Teacher的触发器删除。,USECollegeMISIFEXISTS(SELECTnameFROMsysobjectsWHEREname=delete_TeacherANDtype=TR)DROPTRIGGERdelete_TeacherGO,嵌套触发器,在触发器中可以包含影响另外一个表的INSERT、UPDATE或者DELETE语句，这就是嵌套触发器。具体来说就是，如果表A上的触发器在执行时引发了表B上的触发器，而表B上的触发器又激活表C上的触发器，表C的触发器又激活表D的触发器所有触发器一起触发。这些触发器不会形成无限循环，SQLServer规定触发器最多可以嵌套至32层。如果允许使用嵌套触发器，且链中的一个触发器开始一个无限循环，如果超出嵌套级，触发器将被终止执行。正确地使用套嵌触发器，可以执行一些有用的日常工作，但是嵌套触发器的任意层中发生错误，则整个事务都将取消，且所有的数据修改都将取消。一般情况下，在触发器中包含PRINT语句，用以确定错误发生的位置。,在默认情况下，系统允许嵌套，但是可以使用sp_config系统存储过程或“嵌套触发器”服务器配置选项修改是否允许嵌套。1使用系统存储过程改变嵌套使用sp_config系统存储过程设置是否允许嵌套的语法格式如下：EXECsp_configurenestedtrigger,0|1其