第8章 数据保护(数据库完整性).ppt

第第8章章数据库完整性数据库完整性约束约束 8.1默认值默认值8.2规则规则8.31数据库的完整性n数据库的完整性数据库的完整性是指数据的正确性、有效性是指数据的正确性、有效性和相容性，是为了防止数据库中存在不合语义和相容性，是为了防止数据库中存在不合语义的数据，防止错误信息的输入与输出造成无效的数据，防止错误信息的输入与输出造成无效操作或错误结果。完整性措施的防范对象是不操作或错误结果。完整性措施的防范对象是不合语义的数据。合语义的数据。n例：性别只能是例：性别只能是“男男”或或“女女”，学号必须，学号必须唯一。唯一。n系统提供多种强制数据完整性的机制，保证系统提供多种强制数据完整性的机制，保证数据库中数据的质量。数据库中数据的质量。2数据完整性类型数据完整性类型根据数据完整性措施所作用的数据库对象和范围不同，可以根据数据完整性措施所作用的数据库对象和范围不同，可以将数据完整性进行分类。将数据完整性进行分类。完整性约束作用的对象：完整性约束作用的对象：n关系关系(表表)：若干元组间，关系之间的联系的约束；若干元组间，关系之间的联系的约束；n元组：元组：元组中各个字段间的联系的约束；元组中各个字段间的联系的约束；n列：列：列的类型、取值范围、精度、唯一性、为空性、默列的类型、取值范围、精度、唯一性、为空性、默认定义、认定义、CHECK约束、主键约束、外键约束。约束、主键约束、外键约束。数据完整性约束分类：数据完整性约束分类：n实体（表）完整性实体（表）完整性n域（列）完整性域（列）完整性n参照完整性（引用完整性）参照完整性（引用完整性）n用户自定义完整性用户自定义完整性31、实体完整性、实体完整性又称为行完整性。它把表中的每行都看作一个又称为行完整性。它把表中的每行都看作一个实体，要求所有行都具有唯一标识。实体，要求所有行都具有唯一标识。实体完整性作实体完整性作用对象是列用对象是列，强制表的标识符列或主键的完整性，强制表的标识符列或主键的完整性（在（在SQLServer中，可以通过建立中，可以通过建立PRIMARYKEY约束、约束、UNIQUE约束、约束、IDENTITY等措施来等措施来实施实体完整性）。实施实体完整性）。例如，对例如，对“客户信息表客户信息表”，客户编号就可以作为主键，客户编号就可以作为主键，每个客户的编号能够惟一地确定该客户对应的记录信息，每个客户的编号能够惟一地确定该客户对应的记录信息，那么在输入数据时，则不能有相同客户编号的记录存在，那么在输入数据时，则不能有相同客户编号的记录存在，通过对客户编号这一字段建立主键约束，可实现通过对客户编号这一字段建立主键约束，可实现“客户信客户信息表息表”的实体完整性。的实体完整性。42、域完整性、域完整性域完整性又称为列完整性。它要求表中指定列域完整性又称为列完整性。它要求表中指定列的数据具有正确的数据类型的数据具有正确的数据类型（通过定义数据类型）（通过定义数据类型）、数、数据格式据格式（通过（通过CHECK约束和规则实现）约束和规则实现）和有效的数据范围和有效的数据范围（通过（通过FOREIGNKEY约束、约束、CHECK约束、约束、NOTNULL、DEFAULT定义和规则实现）定义和规则实现）。例如对于课程表例如对于课程表“COURSE1”，学生的某门课学生的某门课程的学分应在程的学分应在10分以内，为了对学分这一数据项输分以内，为了对学分这一数据项输入的数据进行限制，可以定义该表的学分字段的约入的数据进行限制，可以定义该表的学分字段的约束条件。束条件。5例例1定义定义“COURSE1”以及学分字段的约束条件以及学分字段的约束条件CREATETABLECOURSE1(课程号课程号CHAR(6)NOTNULL,课程名课程名CHAR(18)NOTNULLUNIQUE,学分学分TINYINTCHECK(学分学分=0and学分学分=0)21例例5n使用使用CREATETABLE语句创建语句创建“客户客户”表，同时创建检表，同时创建检查约束，定义查约束，定义“邮政编码邮政编码”列的值是由列的值是由6位数字组成的字位数字组成的字符串。符串。CREATETABLE客户客户(记录编号记录编号intIDENTITY(1,1),客户单位客户单位varchar(50)NOTNULL,地址地址varchar(100)NOTNULL,邮政编码邮政编码varchar(10)NOTNULL,CONSTRAINTPK_客户客户PRIMARYKEY(记录编号记录编号),CONSTRAINTIX_客户客户UNIQUE(客户单位客户单位),CONSTRAINTCK_客户客户CHECK(邮政编码邮政编码LIKE0-90-90-90-90-90-9)22例例6n使用使用CREATETABLE语句创建语句创建“联系人联系人”表，同表，同时创建检查约束，定义时创建检查约束，定义“性别性别”列的值只能是列的值只能是“男男”或或“女女”。CREATETABLE联系人联系人(记录编号记录编号intIDENTITY(1,1),姓名姓名varchar(50)NOTNULL,性别性别char(2)NOTNULL,电话电话varchar(50)NOTNULL,CONSTRAINTPK_联系人联系人PRIMARYKEY(记录编号记录编号),CONSTRAINTIX_联系人联系人UNIQUE(姓名姓名),CONSTRAINTCK_联系人联系人CHECK(性别性别IN(男男,女女)可以省略，按可以省略，按系统默认命名系统默认命名238.1.4外部键约束外部键约束n外部键约束是用于建立两个表之间的一列或多列外部键约束是用于建立两个表之间的一列或多列之间的联系。通过将当前表中的某一列或某几列之间的联系。通过将当前表中的某一列或某几列关联到另一个表的主键列，可创建两个表之间的关联到另一个表的主键列，可创建两个表之间的连接。当前表中的列就成为外部键。连接。当前表中的列就成为外部键。n外部键约束可以确保添加到外部键表中的任何行外部键约束可以确保添加到外部键表中的任何行的外部键值在主表中都存在相应主键值，以保证的外部键值在主表中都存在相应主键值，以保证数据的参照完整性。数据的参照完整性。n两种创建外部键约束的方法两种创建外部键约束的方法使用企业管理器创建外部键约束使用企业管理器创建外部键约束使用使用T-SQL语句创建外部键约束语句创建外部键约束24例例7使用企业管理器创建外部键约束使用企业管理器创建外部键约束n在在“XJGL”数据库中，将数据库中，将“STUDENT”表的表的“SNO”列设置为主键，与列设置为主键，与“SC”表中的表中的“SNO”列列与与“STUDENT”表的表的“SNO”对应，在企业管理器对应，在企业管理器中为它们创建外键约束。中为它们创建外键约束。n在企业管理器中右击在企业管理器中右击“SC”表，在弹出菜单中选表，在弹出菜单中选择择“设计表设计表”，打开表设计器。，打开表设计器。n在设计表对话框中右击鼠标，在弹出菜单中选择在设计表对话框中右击鼠标，在弹出菜单中选择“关系关系”，打开，打开“属性属性”对话框，如下页图所示，对话框，如下页图所示，在该对话框中可以查看和管理关系。在该对话框中可以查看和管理关系。2526使用使用T-SQL语句创建外部键约束语句创建外部键约束n语法：语法：CONSTRAINT约束名约束名FOREIGNKEY(列名列名1,列名列名2,.,列名列名n)REFERENCES关联表关联表(关联列名关联列名1,关联列名关联列名2,.,关联列名关联列名n)如果不指定约束名，则系统会自动分配一个名称。如果不指定约束名，则系统会自动分配一个名称。FOREIGNKEY关键字指定当前创建的约束类型为关键字指定当前创建的约束类型为外部键约束。外部键约束。REFERENCES关键字指定与当前创关键字指定与当前创建或修改的表相关联的表和列。建或修改的表相关联的表和列。27createtableSTUDENT1(snochar(4)primarykey,sdeptnamechar(10)GOcreatetableSC1(snochar(4),cnochar(8),FOREIGNKEY(sno)REFERENCESstudent1(sno)go例例8使用使用SQL语句创建外部键约束语句创建外部键约束28级联删除和修改（级联删除和修改（cascade）createtableSTUDENT2(snochar(4)primarykey,sdeptnamechar(10)GOcreatetableSC2(snochar(4),cnochar(8),FOREIGNKEY(sno)REFERENCESstudent2(sno)ondeletecascade)-ondeletecascade级联删除级联删除goinsertstudent2values(0001,a)insertstudent2values(0002,b)insertsc2values(0001,1)insertsc2values(0001,2)insertsc2values(0002,2)select*fromstudent2select*fromsc2godeletefromstudent2wheresno=0001goselect*fromstudent2select*fromsc229使用约束的注意事项使用约束的注意事项n不需要删除和重建表就可以创建、修改和删除约不需要删除和重建表就可以创建、修改和删除约束束n必须在应用程序和事务处理中建立错误检查逻辑，必须在应用程序和事务处理中建立错误检查逻辑，以检测是否违反了约束以检测是否违反了约束n向表添加约束时，向表添加约束时，SQLServer将验证现有数据将验证现有数据n在创建约束时应该对其命名在创建约束时应该对其命名308.2默认值默认值n默认值可以为指定列定义一个默认值。在输入数默认值可以为指定列定义一个默认值。在输入数据时，如果没有输入该列的值，则将该列的值设据时，如果没有输入该列的值，则将该列的值设置为默认值。置为默认值。n有两种方法使用默认值有两种方法使用默认值n在创建表时，指定默认值（企业管理器和在创建表时，指定默认值（企业管理器和SQL语句）语句）n使用使用CREATEDEFAULT语句创建默认对象语句创建默认对象31在创建表时，指定默认值（在创建表时，指定默认值（SQL语句）语句）例例9:CREATETABLEAAA(A1CHAR(4)DEFAULT1111)在在ALTERTABLE语句中，可以使用语句中，可以使用CONSTRAINT关键字定义默认约束，基本语法关键字定义默认约束，基本语法如下：如下：CONSTRAINT约束名约束名DEFAULT约束表达式约束表达式FOR列名列名如果不指定约束名，则系统会自动分配一个名称。如果不指定约束名，则系统会自动分配一个名称。32例例10:n使用使用CREATETABLE语句创建语句创建“测试测试”表，同时表，同时定义定义“记录名称记录名称”列的默认约束为列的默认约束为“默认名称默认名称”。CREATETABLE测试测试(记录编号记录编号int,记录名称记录名称varchar(50),PRIMARYKEY(记录编号记录编号),UNIQUE(记录名称记录名称)ALTERTABLE测试测试ADDCONSTRAINTDE_测试测试DEFAULT默认名称默认名称FOR记录名称记录名称338.2.2使用默认对象使用默认对象默认对象是单独存储的默认对象是单独存储的,删除表时删除表时DEFAULT约束约束会自动删除会自动删除,但默认对象不会被删除但默认对象不会被删除.默认对象创建默认对象创建后后,需要绑定到某列或用户自定义数据类型上需要绑定到某列或用户自定义数据类型上.1.创建创建2.绑定绑定3.重命名重命名4.解除绑定解除绑定5.删除删除341.创建创建n企业管理器中创建企业管理器中创建nSQL语句创建语句创建例例11:CREATEDEFAULT性别值性别值AS男男352.绑定绑定将将默认对象默认对象“性别值性别值”绑定到绑定到STUDENT表的表的SSEX字段字段n企业管理器中绑定企业管理器中绑定nSQL语句创建语句创建例例12:EXECSP_BINDEFAULT性别值性别值,STUDENT.SSEX363.重命名重命名n企业管理器中重命名企业管理器中重命名nSQL语句创建语句创建例例13:EXECSP_RENAME性别值性别值,性别默认值性别默认值374.解除绑定解除绑定n企业管理器中解除绑定企业管理器中解除绑定nSQL语句创建语句创建例例14:EXECSP_UNBINDEFAULTSTUDENT.SSEX385.删除删除n企业管理器中删除企业管理器中删除nSQL语句创建语句创建例例15:DROPDEFAULT性别默认值性别默认值398.3规则规则1.规则的概念规则的概念2.创建规则创建规则3.绑定规则绑定规则4.解除绑定规则解除绑定规则5.删除规则删除规则401.规则的概念规则的概念n规则（规则（Rules）是一种是一种SQLServer对象，它用于执对象，它用于执行一些与行一些与CHECK约束相同的功能。约束相同的功能。CHECK约束约束比规则更简明，一个列只能应用一个规则，但是比规则更简明，一个列只能应用一个规则，但是却可以应用多个却可以应用多个CHECK约束。约束。411.创建规则创建规则n在企业管理器中的左侧窗格中展开指定的数据库，在企业管理器中的左侧窗格中展开指定的数据库，选择选择“规则规则”项，可以在右侧窗格中查看当前数项，可以在右侧窗格中查看当前数据库的规则对象。据库的规则对象。42n右键单击右侧窗格的空白区域，在弹出菜单中选右键单击右侧窗格的空白区域，在弹出菜单中选择择“新建规则新建规则”，打开，打开“规则属性规则属性”对话框。对话框。43例例16:n在企业管理器中创建规则在企业管理器中创建规则“性别规则性别规则”，指定变，指定变量量SSEX的取值只能为的取值只能为男男或或女女。性别规则性别规则SsexIN(男男,女女)44例例17:使用使用CREATERULE语句创建规则语句创建规则n使用使用CREATERULE语句创建规则语句创建规则“分数规则分数规则”，指定变量，指定变量score的范围是的范围是0100。CREATERULE分数规则分数规则ASscoreBETWEEN0AND100452.绑定规则绑定规则n绑定规则是指将已经存在的规则应用到列或用户绑定规则是指将已经存在的规则应用到列或用户自定义的数据类型中。自定义的数据类型中。n两种绑定规则的方法两种绑定规则的方法使用企业管理器绑定规则使用企业管理器绑定规则使用系统存储过程绑定规则使用系统存储过程绑定规则46使用企业管理器绑定规则使用企业管理器绑定规则n在企业管理器中，将创建的规则在企业管理器中，将创建的规则“性别规则性别规则”绑绑定到表定到表“STUDENT”表的表的“SSEX”列。列。n在企业管理器中用鼠标右键单击在企业管理器中用鼠标右键单击“性别规则性别规则”对对象，在弹出的快捷菜单中选择象，在弹出的快捷菜单中选择“属性属性”，打开，打开“性别规则性别规则”属性对话框：属性对话框：47例例18:使用企业管理器绑定规则使用企业管理器绑定规则48使用系统存储过程绑定规则使用系统存储过程绑定规则n使用存储过程使用存储过程sp_bindrule也可以将规则绑定到列也可以将规则绑定到列或用户自定义的数据类型。或用户自定义的数据类型。sp_bindrulerulename=规则名规则名,objname=对象名对象名49例例19:n使用存储过程使用存储过程sp_bindrule将创建的将创建的“分数规则分数规则”绑定到表绑定到表“SC”的的“GRADE”列。列。EXECsp_bindrule分数规则分数规则,SC.GRADE执行的结果如下：执行的结果如下：已将规则绑定到表的列上。已将规则绑定到表的列上。503.解除绑定规则解除绑定规则n使用使用“将规则绑定到列将规则绑定到列”对话框中对话框中n使用存储过程使用存储过程sp_unbindrule解除规则的绑定：解除规则的绑定：EXECsp_unbindruleSC.GRADE514.删除规则删除规则n在企业管理器中，右键单击指定的规则，在弹出在企业管理器中，右键单击指定的规则，在弹出菜单中选择菜单中选择“删除删除”按钮，可以删除指定的规则按钮，可以删除指定的规则对象。对象。n使用使用DROPRULE语句也可以从当前数据库中删语句也可以从当前数据库中删除一个或多个规则：除一个或多个规则：DROPRULE分数规则分数规则52