《约束、默认和规则》PPT课件.ppt

第5章约束、默认和规则,5.1数据完整性5.2约束5.3默认5.4规则,5.1数据完整性,数据库应用程序开发中的一个重要步骤就是设计和实施数据的完整性，并确定实施数据完整性的最佳方案。数据完整性是指数据库中数据的正确性和一致性，是衡量数据库质量的一个重要标准。在使用INSERT、UPDATE和DELETE命令修改数据库中的数据时，数据的完整性可能被破坏，如插入了一个不存在学生的成绩，或将学生的班号修改为一个不存在的班级等。通过定义数据完整性规则，SQLServer可以通过自身提供的完整性规则有效地管理数据的输入，而不必使用额外的应用程序来协助管理，这样一方面可以节省系统开销，另一方面将使数据库中的数据独立于应用程序，使创建开放式数据库系统成为可能。完整性包括：域完整性、实体完整性、参照完整性和用户自定义完整性,5.1数据完整性,5.1.1域完整性域完整性是指给定列的输入有效性。强制域有效性的方法有：通过限制数据类型(包括自定义数据类型)、格式（CHECK约束和规则）或可能的取值范围（FOREIGNKEY约束、CHECK约束、DEFAULT定义、NOTNULL定义和规则）来实现。如：性别字段只能取“男”或“女”；课程成绩取值范围为0100；姓名字段不能为空；性别字段的默认值为“男”等等。,5.1数据完整性,5.1.2实体完整性实体完整性是指表中的每一行必须是惟一的。实体完整性强制表中的所有记录都有一个惟一的标识列，这个惟一标识列可能是一列，也可能有若干列的组合。强制实体完整性的方法有：UNIQUE约束、PRIMARYKEY约束。如：在学生情况表中，学生的学号是惟一的，它与每个学生一一对应；成绩表中，学号+课程号是惟一的，它与每个学生的每门课相对应。,5.1数据完整性,5.1.3参照完整性参照完整性确保数据库中数据的一致性。在SQLServer中，参照完整性基于外键（引用表）与主键（被引用表）之间或外键与惟一键之间的关系（通过FOREIGNKEY和CHECK约束）。通过外键将引用表和被引用表关联起来。参照完整性通过已定义的表间关系，确保键值在所有表中都必须一致，因此不能引用不存在的值（即指向不存在的行）。如果在被引用的表中，某一记录被外部键引用，则该记录就不能删除；若需要更改键值，那么在整个数据库中，对该键值的所有引用都要进行一致的更改，以保证数据的参照完整性。,5.1数据完整性,当设置了参照完整性后，sqlserver将从以下几个方面限制用户对数据库的操作。当主表中没有关联记录时，向一个相关表中添加记录。更改主表中的键值但没有更改相关表的数据，从而使相关表的记录无法在主表中找到相应的关联记录。当从主表中删除记录时，在相关表中仍存在与该记录相匹配的记录。,5.1数据完整性,5.1.4用户定义完整性用户可以根据需要，可以在SQLServer中定义不属于上述标准类别的特定规则的用户完整性定义。所有的完整性类型都支持用户自定义完整性（CREATETABLE中的所有列级和表级约束、存储过程和触发器）。,5.1数据完整性,5.1.5数据完整性的实现方法在SQLServer中，有两种方式可以实现数据完整性：l声明型数据完整性；是在对象创建时通过定义，由系统本身的自动强制功能来实现的包含使用各种约束、默认和规则。声明型数据完整性在实际使用时是作为数据库对象定义的一部分在语法中实现的。l过程型数据完整性。是通过使用脚本语言编写出用于保证数据完整性的脚本（使用触发器或存储过程）来实现的，当这些脚本被执行时就可以强制完整性的实现。,5.2约束,建立和使用约束的目的在于保证数据的完整性，设计表时需要定义列的有效值并通过列中数据、行中数据及表间数据决定如何强制保证数据的完整性，约束定义关于列中允许值的规则，是强制完整性的首选方法。约束是独立于表结构的，它作为数据库定义的一部分在创建表时声明，可以通过企业管理器或ALTERTABLE语句添加或删除。当表被删除时，表所附带的所有约束同时被删除。,5.2约束,5.2.1约束的类型(p72)域完整性(NOTNULL、DEFAULT、CHECK)实体完整性（PRIMARYKEY、UNIQUE）参考完整性（FOREIGNKEY）,5.2约束,5.2.2NOTNULL约束NOTNULL约束又称非空约束，表示使用该约束的列不允许使用空值。若该列是主键，则系统强制主键列为非空约束，而其它列的非空约束必须根据需要加以设置。非空约束可以在CREATETABLE建表时实现，在表创建完成后，也可以使用修改列属性的T-SQL语句完成：ALTERTABLEtable_nameALTERCOLUMNcolumn_namenew_data_typeNULL|NOTNULL【例5-1】将学生信息表（Student）中的学生姓名列强制非空约束。可以通过修改列属性的方法完成：ALTERTABLEStudentALTERCOLUMNStudentNameVARCHAR(10)NOTNULL,5.2约束,5.2.3DEFAULT约束当表中的某列必须有值，并且当使用INSERT命令向数据表插入记录数据时，若用户没有明确指定该列的值，该列也需要有一个明确值的情况下，就需要使用DEFAULT约束。使用了DEFAULT约束的列，SQLServer将根据用户的插入数据，自动维护域完整性：当用户插入时该列有指定值，则该列使用该值插入，否则使用DEFAULT约束中指定的默认值。DEFAULT约束可以使用以下方法实现：l作为表定义的一部分在创建表时创建。l添加到现有表中；l删除现有的DEFAULT定义。ALTERTABLEStudent/*添加DEFAULT约束*/ADDCONSTRAINTDefault_SexDEFAULT男FORSex,5.2约束,5.2.4CHECK约束。CHECK约束用于限制输入到列中的值的范围，从而强制数据库中数据的域完整性。它与FOREIGNKEY约束控制列中数值类似。区别在于它们判断值有效的方法不同：FOREIGNKEY约束从另一个表中获得有效数值列表，而CHECK约束通过逻辑表达式的成立与否加以判断。定义CHECK约束的格式为：CONSTRAINTconstraint_nameCHECKNOTFORREPLICATION(logical_expression),5.2约束,【例5-3】为专业情况表(Speciality)中的专业代码(SpecialityID)列创建一个CHECK约束，将专业代码限定为五位，其中第一位只能是“a”-“z”或“A”-“Z”，其它位为“0”-“9”，使用其它符号将被认为是无效的。USEEducational-选择当前数据库GO-定义CHECK约束ALTERTABLESpecialityADDCONSTRAINTChk_SpecialityIDCHECK(SpecialityIDLIKEA-Z0-90-90-90-9),5.2约束,5.2.5PRIMARYKEY(主键)约束在一个表中，存在着这样的字段，它在每个记录中的值都是惟一的，因此可以用来标识表中的各个不同记录。这样的字段被称为关键字，关键字可以是列或列的组合。例如学生情况表中的StudentID列就是一个关键字，因为每个学生的学号必须是惟一的。当表中有两个或两个以上的列或列的组合满足以上条件时，称这些列或列的组合为候选关键字。当表的关键字多于一个时，可以将其中的一个关键字作为主键(PRIMARYKEY)。,5.2约束,5.2.5PRIMARYKEY(主键)约束当创建或更改表时可通过定义PRIMARYKEY约束来创建主键。PRIMARYKEY约束有以下两种：l列级PRIMARYKEY约束，其定义格式为：CONSTRAINTconstraint_namePRIMARYKEYCLUSTERED|NONCLUSTERED/*是否创建聚集或非聚集索引*/WITHFILLFACTOR=fillfactorONfilegroup|DEFAULTl行级PRIMARYKEY约束，其定义格式为：CONSTRAINTconstraint_namePRIMARYKEYCLUSTERED|NONCLUSTERED(columnASC|DESC,.n)WITHFILLFACTOR=fillfactorONfilegroup|DEFAULT,5.2约束,【例5-4】为成绩表中的学号和课程号列（StudentID+CourseID）添加一个PRIMARYKEY约束。ALTERTABLEGradeADDCONSTRAINTPK_GradePRIMARYKEY(StudentID,CourseID);,5.2约束,5.2.6UNIQUE约束UNIQUE约束确保表中指定列中不出现重复值，即表中任意两行在该列上的值都不允许相同。在创建或更改表时可定义UNIQUE约束。UNIQUE约束有以下两种：l列级UNIQUE约束，其定义格式为：CONSTRAINTconstraint_nameUNIQUECLUSTERED|NONCLUSTEREDWITHFILLFACTOR=fillfactorONfilegroup|DEFAULTl行级UNIQUE约束，其定义格式为：CONSTRAINTconstraint_nameUNIQUECLUSTERED|NONCLUSTERED(columnASC|DESC,.n)WITHFILLFACTOR=fillfactorONfilegroup|DEFAULT,5.2约束,【例5-5】为系情况表(Department)中的系名列(DepartmentName）添加一个UNIQUE约束。ALTERTABLEDepartmentADDCONSTRAINTUNIQUE_DepartmentNameUNIQUE(DepartmentName);,5.2约束,5.2.7FOREIGNKEY约束若列或列的组合不是本表的关键字，而是另一个表的关键字，则称这些列或列的组合是外键(FOREIGNKEY)。一般表与表之间通过主键和外键进行连接，通过它可以强制表与表之间的参照完整性。FOREIGNKEY约束要求列中的每个值在被引用表中对应的被引用列中都存在。如学生情况表(Student)表中的所在班级(ClassID)就是一个外键，它与班级情况表(Class)表中的主键班号(ClassID)进行关联。,5.2约束,l行级FOREIGNKEY约束，其定义格式为：CONSTRAINTconstraint_nameFOREIGNKEY(column,n)/*引用表中的列名或列名的组合*/REFERENCESref_table(ref_column,n)/*被引用表及其列名*/ONDELETECASCADE|NOACTION/*是否级联删除相关的记录*/ONUPDATECASCADE|NOACTION/*是否级联更新相关的记录*/NOTFORREPLICATION,5.2约束,【例5-6】为Student表中的所在班级列(ClassID)添加一个FOREIGNKEY约束。ALTERTABLEStudentADDCONSTRAINTFK_Student_ClassFOREIGNKEY(ClassID)REFERENCESClass(ClassID)ONUPDATECASCADE,5.2约束,5.2.8使用企业管理器管理约束1管理NOTNULL、DEFAULT约束1）启动企业管理器，展开服务器组，再展开要操作的服务器。2）展开“数据库”文件夹，再展开要操作的数据库（Educational）。3）单击展开的“表”项，在右边的“表”列表框中显示出所选数据库中的所有表。4）右击要在其中设置NOTNULL、DEFAULT约束的数据表（Student），在弹出的快捷菜单中选择“设计表”菜单项，出现图52所示的“设计表Student”对话框。5）中间列表中的一行为一列的定义，选中要设置NOTNULL约束的StudentName列，单击行尾“允许空”列，将其中的“”去掉即可实现NOTNULL约束。再次单击就可删除NOTNULL约束。6）单击Sex列，在图52下部的列属性表的“默认值”一栏所对应的输入框中输入Sex列的默认值“男”。去掉默认值时，只需将“默认值”一栏所对应的输入框中的文字删除即可。7）设置完成后，单击工具栏上的“”按钮，保存修改的结果。,5.2约束,5.2.8使用企业管理器管理约束2管理CHECK约束1）按上面的方法进入图5-2所示的表设计器。2）单击工具栏上的“管理约束”按钮，出现如图53所示的“属性”对话框。在“选定的约束”列表框中存放有该表已创建的CHECK约束。3）单击“新建”按钮，新建一个CHECK约束，系统自动为新建的CHECK约束设置一个缺省名，并清空下面的“约束表达式”输入框。4）先在下面的“约束表达式”输入框中输入需创建CHECK约束的约束表达式，如图5-3所示，然后在“约束名”输入框中将缺省名修改为需创建的CHECK约束名。5）若选中“创建中检查现存数据”复选框，则表示当该约束存盘时，系统先检查表中已有的数据是否符合该CHECK约束的条件，如不符合，系统将出现“不能添加约束”错误并拒绝存盘；如符合，则创建CHECK约束成功。否则系统将不检查表中已有的数据，直接创建CHECK约束。6）单击“关闭”按钮，退出“属性”对话框。,5.2约束,5.2.8使用企业管理器管理约束3管理PRIMARYKEY约束1）主键约束的设置非常简单，按上面的方法进入图5-2所示的表设计器。2）若表中列的前面是一个空白的“方块”，表明该列不是“主键”。若需要将某列设置为主键，首先用鼠标单击该列，然后单击工具栏上的“”设置主键按钮，则该列前面的“方块”中将出现主键标志“”，如图5-2所示。3）再次单击工具栏上的“”设置主键按钮，则取消主键的设置。,5.2约束,5.2.8使用企业管理器管理约束4管理UNIQUE约束1）按上面的方法在表设计器中打开系情况表(Department)。2）单击工具栏上的“”管理索引/鍵按钮，出现如图5-4所示的“属性”对话框“索引/鍵”选项页。在“选定的索引”列表框中存放有该表已创建的UNIQUE约束和索引。3）单击“新建”按钮，新建一个UNIQUE约束或索引，系统自动为新建的UNIQUE约束或索引设置一个缺省名，下面的“列名”列表框中自动出现该表的第一个列名。4）在下面的“列名”列表框中选择要进行UNIQUE约束的列名。若该UNIQUE约束有二个或二个以上列组成，则可以在第一个“列名”列表框的下一行中再次选择即可。5）单击并选中“创建UNIQUE”复选框，并选中“约束”单选按钮。6）在“索引名”输入框中输入一个合适的约束名。7）单击“关闭”按钮，退出“属性”对话框。,5.3默认,默认是一种数据库对象，它指定在向表中插入数据时，若用户没有给出某列的相关值，SQLServer自动将该列使用默认值。它可以被绑定到一个或多个列中使用。默认值的定义与表存储在一起，因此可以反复使用。默认值执行与使用CREATETABLE或ALTER语句的DEFAULT关键字创建的默认约束具有相同的功能。默认约束是限制列数据的首选并是标准的方法，因为其定义和表存储在一起，当除去表时，将自动除去默认约束。默认约束与默认不同，默认是数据库对象，必须使用createdefault命令建立，使用dropdefault命令删除。当在多个列中多次使用默认值时，默认就显示出明显的优点了。,5.3默认,5.3.1创建DEFAULT(默认)对象默认是一种数据库对象，可以绑定的一列或多列上，也可以绑定到用户定义的数据类型上。当需要将默认值绑定到列或用户定义数据类型时，就需要创建默认对象。当使用INSERT语句向表中插入数据时，如果绑定有默认值的列或用户定义数据类型没有明确提供数据，系统自动就将默认值插入到对象所绑定的列中(在用户定义数据类型的情况下，插入到使用该数据类型的所有列中)。创建默认对象的SQL语句语法格式为：CREATEDEFAULTdefault/*默认对象名*/ASconstant_expression/*默认对应的常量表达式*/,5.3默认,5.3.2绑定默认当默认对象创建后，并不能直接使用，必须将其绑定到某一列或用户定义的数据类型上方能有效。执行系统存储过程sp_bindefault或使用企业管理器可以绑定默认值。绑定默认的SQL语句语法格式为：sp_bindefaultdefname=default_name,objname=object_name,futureonly=futureonly_flag如：sp_bindefaultDefault_TechnicalPost,Teacher.TechnicalPost,5.3默认,5.3.3删除默认对象默认对象被绑定到一列或用户定义的数据类型后，不能直接删除默认对象，必须先使用存储过程sp_unbindefault解除该默认对象所使用的绑定，然后才能使用DROPDEFAULT语句删除默认对象。解除默认值绑定的存储过程sp_unbindefault的语法格式为：sp_unbindefaultobjname=object_name,futureonly=futureonly_flag删除默认对象的语法格式为：DROPDEFAULTdefault_name,.n,5.3默认,5.3.4使用企业管理器管理默认1创建默认对象1）启动企业管理器。2）展开服务器组，然后展开要操作的服务器。3）展开“数据库”文件夹，再展开要在其中创建DEFAULT对象的数据库（Educational）。4）右击“默认”，在出现的快捷菜单中单击“新建默认”菜单项（如图5-6所示），弹出图5-7所示的“默认属性”对话框。5）在“名称”框中输入该DEFAULT对象的名称“Default_TechnicalPost”。6）在“值”框中输入该DEFAULT对象的值讲师。值可以是常量或表达式。7）单击“确定”按钮，就在当前数据库中创建了一个名为Default_TechnicalPost的默认对象。,5.3默认,5.3.4使用企业管理器管理默认2绑定默认对象1）在企业管理器中展开“数据库”文件夹，再展开要在其中绑定DEFAULT对象的数据库(Educational)。2）单击“默认”，右边将显示该数据库中已存在的默认对象（如图5-8所示）。双击要进行绑定的默认对象，出现与图5-7类似的“默认属性”对话框，只不过名称栏已变灰。3）单击“绑定列”按钮，出现如图5-9所示的“将默认值绑定到列”对话框。4）对话框上部，从“表”下拉式组合框中选择要绑定的表，这里选择Teacher。5）在对话框下部，将左边的“未绑定的列”列表框中的未绑定列的列移到右边的“绑定列”列表框中。6）依次单击“应用”、“确定”按钮，绑定任务完成。,5.3默认,5.3.4使用企业管理器管理默认3解除绑定并删除默认对象1）在企业管理器中进入图5-8，在右边已存在默认对象列表框中找到要解除绑定的默认对象。2）双击要解除绑定的默认对象，出现与图5-7类似的“默认属性”对话框，只不过名称栏已变灰。3）单击“绑定列”按钮，出现如图5-9所示的“将默认值绑定到列”对话框。4）在对话框上部，从“表”下拉式组合框中选择要解除绑定的表，这里选择Teacher，出现如图5-10所示的“绑定或解除绑定”对话框，右边的“绑定列”列表框中将出现所选表中所有已绑定列的列名。5）单击“绑定列”列表框中要解除绑定的列名，这里为TechnicalPost。中间的“删除”按钮将变为有效。6）单击“删除”按钮，原在右边“绑定列”列表框中的TechnicalPost列被删除而移到了左边的“未绑定的列”列表框中。,5.4规则,规则用来验证插入或更新到数据库中的数据的有效性。当绑定了规则的列中数据被插入或更新时，系统首先检查数据是否符合规则的要求，若不符合规则将拒绝执行此插入或更新操作。规则是一种数据库对象，其作用与CHECK约束类似，通过定义的表达式来限制输入到列或用户定义的数据类型中的允许值范围，是实现域完整性的方法之一。CHECK约束是对列中的值进行限制的首选标准方法，可以对一列或多列定义多个约束。而列或用户定义数据类型只能有一个绑定的规则。但是，列可以同时绑定一个规则并定义一个或多个CHECK约束，此时，当插入或更新数据时，系统将检查所有的限制。规则的使用与默认相似，使用前必须先创建规则，然后既可以被绑定到一列或多列上，也可以被绑定到用户定义的数据类型上使用。,5.4规则,5.4.1创建规则规则是一种数据库对象，在使用前必须用CREATERULE命令或通过企业管理器创建。创建规则的CREATERULE命令的语法格式如下：CREATERULErule_name/*rule_name为需创建的规则名称*/AScondition_expression/*定义规则的条件*/,5.4规则,【例5-10】为Educational数据库创建二个规则Rule_Grade、Rule_Sex，分别用于限定成绩和性别的范围。USEEducationalGOCREATERULERule_GradeASGrade=0ANDGrade=100GOCREATERULERule_SexASSexIN(男,女)GO,5.4规则,5.4.2绑定规则与默认对象类似，当规则创建后，并不能直接使用，必须将其绑定到某一列或用户定义的数据类型上方能有效。执行系统存储过程sp_bindrule或使用企业管理器可以绑定规则。绑定规则的SQL语句语法格式为：sp_bindrulerulename=rule_name,objname=object_name,futureonly=futureonly_flag,5.4规则,【例5-10】将例5-11中创建的规则Rule_Grade、Rule_Sex分别绑定到成绩表(Grade)的成绩(Grade)列与学生情况表(Student)的性别(Sex)列。USEEducationalGOEXECsp_bindruleRule_Grade,Grade.GradeEXECsp_bindruleRule_Sex,Student.Sex,5.4规则,5.4.3删除规则与删除默认对象相似，规则被绑定到一列或用户定义的数据类型后，不能直接删除，必须先使用存储过程sp_unbindrule解除该规则所使用的绑定，然后才能使用DROPRULE语句删除规则。解除规则绑定的存储过程sp_unbindrule的语法格式为：sp_unbindruleobjname=object_name,futureonly=futureonly_flag解除绑定后，规则并没有删除，仍然存在于数据库中，要删除规则，还必须使用DROPRULE命令将其删除。删除规则的语法格式为：DROPRULErule_name,.n,