第7章 数据完整性

AnIntroductiontoDatabaseSystems,SQLServer数据库应用,AnIntroductiontoDatabaseSystems,2/80,第7章数据完整性,7.1使用约束实施数据完整性7.2使用规则实施数据完整性7.3关系图7.4C#操作规则,数据完整性分类,实体完整性,域完整性,参照完整性,用户定义完整性,唯一索引PRIMARYKEY约束UNIQUE约束列的IDENTITY属性,FOREIGNKEY约束CHECK约束DEFAULT定义NOTNULL定义和规则,主键（PRIMARYKEY）约束外键（FOREIGNKEY）约束,（元组的唯一）,（取值的范围）,（数据的相关）,AnIntroductiontoDatabaseSystems,4/48,7.1使用约束实施数据完整性,约束是SQLServer提供的自动保持数据库完整性的一种方法。它是数据库服务器强制用户必须遵从的业务逻辑。它通过限制字段中的数据、记录中的数据和表之间的数据来保证数据的完整性。在数据库设计器中创建约束时，约束遵从用于创建和更改表的ANSI标准。SQLServer2008提供了下列约束机制来强制列中数据的完整性。（1）PRIMARYKEY约束；（2）UNIQUE约束；（3）CHECK约束；（4）DEFAULT定义；（5）允许空值；（6）FOREIGNKEY约束。,AnIntroductiontoDatabaseSystems,5/48,7.1.1PRIMARYKEY约束PRIMARYKEY约束是指通过表的主键实现的实体完整性约束。为了能够唯一确定表中的数据行，通常会通过一列或几列来唯一地标识表中的行。这样的一列或多列称为表的主键（PRIMARYKEY）。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,6/48,1使用表设计器建立PRIMARYKEY约束【例题7-1】在“教学管理数据库”中“教师基本信息表”中的“序号”和“职工号”列构成了针对此表的复合PRIMARYKEY约束。这确保了“序号”和“职工号”的组合是唯一的，如图7-1所示。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,7/48,（1）在“对象资源管理器”窗口中，依次展开数据库、“教学管理信息数据库”、表节点，右键单击“教师任课信息表”表，在弹出的快捷菜单中单击“设计”命令，打开“表设计器”对话框。（2）在“表设计器”中，同时选中“序号”和“职工号”字段。（3）在弹出的快捷菜单中单击“设置主键”命令。（4）设置完成后，关闭“表设计器”对话框，完成复合PRIMARYKEY约束创建。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,8/48,2使用SQL语句建立PRIMARYKEY约束（1）创建表的同时建立PRIMARYKEY约束。【例题7-2】在“教学管理数据库”建立一个SC表，定义SNO，CNO共同组成SC的主键，程序清单如下：CREATETABLESC(SNOCHAR(5)NOTNULL,CNOCHAR(5)NOTNULL,SCORENUMERIC(3),CONSTRAINTSC_PRIMPRIMARYKEY(SNO,CNO),7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,9/48,（2）在已经存在的表中创建主键约束的语法：ALTERTABLEtable_nameADDCONSTRAINTconstraint_namePRIMARYKEYCLUSTERED|NONCLUSTERED(column,n)其中：constraint_name：指主键约束名称。CLUSTERED：表示在该列上建立聚集索引。NONCLUSTERED：表示在该列上建立非聚集索引。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,10/48,7.1.3CHECK约束CHECK约束（检查约束）的主要作用是限制输入到一列或多列中数据的可能值，从而来保证SQLServer数据库中数据的域完整性。例如，可以在建立用户使用库时，强制用户的密码在10位长度以上。CHECK（核查）约束通过检查输入表列的数据的值来维护值域的完整性。核查约束通过对一个逻辑表达式的结果进行判断来对数据进行核查。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,11/48,注意：可以在一列上设置多个核查约束，也可以将一个核查约束应用于多列。当一列受多个核查约束控制时，所有的约束按照创建的顺序，依次进行数据有效性的核查。一般来说，可以在下面两种情况下，设置核查约束无效：在执行INSERT语句或UPDATE语句过程：事先知道对数据的增加或修改将违反核查约束的规定，但这些操作又是必须的。在复制进行时，在进行不同服务器间的复制操作的过程中，由于两个服务器之间设置的核查约束不一致，如果不事先使核查约束无效，则有可能使某些数据无法进行复制。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,12/48,1使用表设计器建立CHECK约束【例题7-6】在“教学管理数据库”中“学生成绩表”表设置一个成绩约束，定义“成绩”字段的取值范围为0到100之间，如图7-3所示。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,13/48,图7-3创建约束对话框,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,14/48,2使用SQL语句建立CHECK约束用Transact-SQL语句创建检查约束。其语法形式如下：CONSTRAINTconstraint_nameCHECKNOTFORREPLICATION(logical_expression),7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,15/48,【例题7-7】在“教学管理数据库”中建立一个成绩SC表，定义SCORE的取值范围为0到100之间,CREATETABLESC(SNOCHAR(5),CNOCHAR(5),SCORENUMERIC(5,1)CONSTRAINTSCORE_CHKCHECK(SCORE=0ANDSCORE=100),AnIntroductiontoDatabaseSystems,16/48,7.1.2UNIQUE约束UNIQUE约束（唯一性约束）主要用于非主键列，以保证一列或者多列的组合值具有唯一性，以防止在列中输入重复值。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,17/48,利用表设计器建立UNIQUE约束【例题7-3】在“教师基本信息表”信息表中，“职工号”列已设置为主键，但是其中还包括“电子信箱”列，由于“电子信箱”不可能出现重复，所以可以在此列上建立UNIQUE约束，确保不会输入重复的“电子信箱”。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,18/48,（1）在“对象资源管理器”窗口中，依次展开数据库、“教学管理信息数据库”、表节点，右键单击“教师基本信息表”表，在弹出的快捷菜单中单击“设计”命令，打开“表设计器”对话框。在“表设计器”中，右键单击任意字段，在弹出的快捷菜单中单击“索引/键”命令，打开“索引/键”对话框。（2）单击“添加”命令按钮，系统给出系统默认的惟一约束名：“IX_教师基本信息表”，显示在“选定的主/惟一或索引”列表框中，单击选中惟一约束名“IX_教师基本信息表”，在其右侧的“属性”窗口中，可以修改约束名称，设置约束列等。（3）单击“属性”窗口中“常规”中的“列”属性，在其右侧出现“”按钮，单击该按钮，打开“索引列”对话框，在列名下拉列表框中选择“电子信箱”，在排序顺序中选择“降序”，设置创建惟一约束的列名，如图7-2所示。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,19/48,图7-2创建惟一性约束对话框,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,20/48,（4）设置完成后，单击“确定”按钮，回到“索引/键”对话框，修改“常规”属性中“是惟一的”属性值为“是”，最后，关闭“索引/键”对话框和“表设计器”对话框，保存设置，完成惟一约束创建。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,21/48,使用SQL语句建立UNIQUE约束（1）使用Transact-SQL语句完成惟一性约束的操作，其语法形式如下：CONSTRAINTconstraint_nameUNIQUECLUSTERED|NONCLUSTERED（column_name,n）,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,22/48,【例题7-4】在“教学管理数据库”中创建一个“test”表，其中“类型,时间”字段具有惟一性。,CREATETABLEtest(编号intCONSTRAINTprimPRIMARYKEY,名称char(20),类型char(20),时间datetime,CONSTRAINTuniq_eventUNIQUE(类型,时间)GO,AnIntroductiontoDatabaseSystems,23/48,（2）为存在的表创建惟一约束，其语法格式如下：ALTERTABLEtable_nameADDCONSTRAINTunique_nameUNIQUECLUSTERED|NONCLUSTERED(column,n),7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,24/48,【例题7-5】在“教学管理数据库”库中，为“教师基本信息表”表中的“姓名”字段创建一个惟一约束,ALTERTABLE教师基本信息表ADDCONSTRAINTuk_nameUNIQUENONCLUSTERED(姓名)GO,AnIntroductiontoDatabaseSystems,25/48,7.1.4DEFAULT定义在向数据表中插入数据时，一行数据中的每列均必须有值，即使该值是NULL。有可能会发生这样的情况，必须向表中加载一行数据但不知道某一列的值，或者该值尚不存在。如果在这种情况下，列允许为空值，那么就可以为行加载空值。但是有时候不希望有为空的列，就可以为该列定义DEFAULT定义。例如，通常为数值列指定零作为默认值，为字符串列指定“N/A”作为默认值。默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定值。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,26/48,默认约束可以包括常量、函数、不带变元的内建函数或者空值。使用默认约束时，应该注意以下几点：（1）每个字段只能定义一个默认约束；（2）如果定义的默认值长于其对应字段的允许长度，那么输入到表中的默认值将被截断；（3）不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上；（4）如果字段定义为用户定义的数据类型，而且有一个默认绑定到这个数据类型上，则不允许该字段有默认约束。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,27/48,1使用表设计器建立DEFAULT约束【例题7-8】在“教学管理数据库”中，为“教师基本信息表”中性别字段设置默认值为“男”。其操作步骤如下：（1）启动SQLServerManagementStudio，在“对象资源管理器”窗口中，依次展开数据库“教学管理数据库”、表节点。（2）右键单击“教师基本信息表”表，在弹出的快捷菜单中选择“设计”命令，打开“表设计器”对话框，如图7-4所示。（3）单击需要设置默认的列（如：性别），在下面列属性设置栏的“默认值或绑定”选项对应的输入框中，输入默认值即可（如：男）。（4）设置完成后，关闭表设计器。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,28/48,2使用SQL语句建立DEFAULT约束创建默认约束的Transact-SQL语句操作法。其语法形式如下：CONSTRAINTconstraint_nameDEFAULTconstraint_expressionFORcolumn_name【例题7-9】在“教学管理数据库”中，为“教师基本信息表”中“性别”字段创建默认约束，默认值为“男”。ALTERTABLE教师基本信息表ADDCONSTRAINTdf_性别DEFAULT男FOR性别GO,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,29/48,7.1.5允许空值列的空值约束定义了表中数据行的特定列是否可以指定为空值。空值（NULL）不同于零（0）、空白或长度为零的字符串（如），在一般情况下，如果在输入数据的时候不输入该列的值，则表示为空值。因此，出现NULL通常表示值未知或未定义。例如，在AdventureWorks数据库的Production.Product表的SellEndDate列中的空值，不是表示商品没有销售结束日期，而是表示该日期未知或尚为设置。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,30/48,指定某一列不允许空值有助于维护数据的完整性，因为这样可以确保行中的列永远包含数据。如果不允许空值，用户向表中输入数据时必须在列中输入一个值，否则数据库将不接收该表行。在通常情况下，建议避免允许空值，因为空值会使查询和更新变得复杂，使用户在操作数据的时候变得更加困难。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,31/48,空值（NULL）约束用来控制是否允许该字段的值为NULL。NULL值不是0也不是空白，更不是填入字符串的“NULL”字符串，而是表示“不知道”、“不确定”或“没有数据”的意思。当某一字段的值一定要输入才有意义的时候，则可以设置为NOTNULL。如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,32/48,空值（NULL）约束只能用于定义列约束。创建空值（NULL）约束常用的操作方法有如下两种：1在SQLServer管理平台中添加空值（NULL）约束。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,33/48,【例题7-10】在“教学管理数据库”中，为“学生基本信息表”中“学号”字段不能为空。,其操作步骤如下：（1）启动SQLServerManagementStudio，在“对象资源管理器”窗口中，依次展开数据库“教学管理数据库”、表节点。（2）右键单击“学生基本信息表”表，在弹出的快捷菜单中选择“设计”命令，打开“表设计器”对话框，如图7-5所示。（3）单击需要设置“允许空”的列复选框（如：学号），使其为未选中状态。（4）设置完成后，关闭表设计器。,AnIntroductiontoDatabaseSystems,34/48,图7-5设置空值对话框,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,35/48,2使用Transact-SQL语句设置空值（NULL）约束使用Transact-SQL语句设置空值（NULL）约束，其语法形式如下：CONSTRAINTNULL|NOTNULL,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,36/48,【例题7-11】在“教学管理数据库”中，建立一个S表，对SNO字段进行NOTNULL约束。,CREATETABLES(SNOCHAR(10)CONSTRAINTS_CONSNOTNULL,SNVARCHAR(20),AGEINT,SEXCHAR(2)DEFAULT男,DEPTVARCHAR(20),AnIntroductiontoDatabaseSystems,37/48,7.1.6FOREIGNKEY约束FOREIGNKEY约束（外键约束）是用于建立和加强两个表数据之间的链接关系的一列或多列。当创建或修改表时可通过定义FOREIGNKEY约束来创建外键。在外键引用中，当一个表的列被引用作为另一个表的主键值的列时，就在两表之间创建了链接。这个列就成为第二个表的外键。FOREIGNKEY约束并不仅仅可以与另一表的PRIMARYKEY约束相链接，它还可以定义为引用另一表的UNIQUE约束。FOREIGNKEY约束可以包含空值，但是，如果任何组合FOREIGNKEY约束的列包含空值，则将跳过组成FOREIGNKEY约束的所有值的验证。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,38/48,使用SQLServerManagementStudio建立FOREIGNKEY约束【例题7-12】在“教学管理数据库”数据库中，为“教师任课信息表”表的“课程号”列创建外键约束，从而保证在“教师任课信息表”表中输入有效的“课程号”。其操作步骤如下：,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,39/48,（1）启动SQLServerManagementStudio，在“对象资源管理器”窗口中，依次展开数据库、“教学管理数据库”、表节点。（2）右键单击“教师任课信息表”表，在弹出的快捷菜单中选择“设计”命令，打开“表设计器”对话框。在“表设计器”中，右键单击任意字段，在弹出的快捷菜单中单击“关系”命令，打开“外键关系”对话框，如图7-6所示。（3）单击“添加”命令按钮，系统给出默认的外键约束名：“FK_教师任课信息表_教师基本信息表”，显示在“选定的关系”列表中。（4）单击“FK_教师任课信息表_教师基本信息表”外键约束名，在其右侧的“属性”窗口中单击“表和列规范”属性，然后，再单击该属性右侧的“”按钮，打开“表和列”对话框，如图7-7所示。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,40/48,（5）在“表和列”对话框中，修改外键的名称，选择主键表及表中的主键，以及外键表中的外键，修改后结果如图7-7所示。单击“确定”命令按钮，回到“外键关系”对话框，如图7-8所示。（6）单击“关闭”按钮，完成外键的设置。,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,41/48,图7-6选择创建外键约束的字段,图7-7创建外键约束对话框,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,42/48,2使用SQL语句建立FOREIGNKEY约束使用Transact-SQL语句设置外部键约束，其语法形式如下：CONSTRAINTconstraint_nameFOREIGNKEY（column_name,n）REFERENCESref_table（ref_column,n）,7.1使用约束实施数据完整性,AnIntroductiontoDatabaseSystems,43/48,【例题7-13】在“教学管理数据库”中，建立一个SC表，定义SNO,CNO为SC的外部键。,CREATETABLESC(SNOCHAR(5)NOTNULLCONSTRAINTS_FOREFOREIGNKEYREFERENCESS(SNO),CNOCHAR(5)NOTNULLCONSTRAINTC_FOREFOREIGNKEYREFERENCESC(CNO),SCORENUMERIC(3),CONSTRAINTS_C_PRIMPRIMARYKEY(SNO,CNO),AnIntroductiontoDatabaseSystems,44/48,【例题7-14】在“教学管理数据库”的“教师任课信息表”表上，为“课程号”字段创建一个外键约束，从而保证输入有效的“课程号”代码。,ALTERTABLE教师任课信息表ADDCONSTRAINTfk_课程号FOREIGNKEY(课程号)REFERENCES学生课程信息表(课程号)GO,AnIntroductiontoDatabaseSystems,45/48,7.2使用规则实施数据完整性,规则是