SQL 第06章 表的创建与管理(续)

第6章

表的创建和管理

6.1创建表6.2表结构的修改6.3表的重命名与删除6.4向表中添加、更新、删除数据6.5数据完整性的概念与实施方法—P63

练习返回目录26.5数据完整性的概念与实验方法6.5.1数据完整性概念数据完整性是指数据的正确性、完备性和一致性，是衡量数据库质量好坏的重要标准。在用INSERT、DELETE、UPDATE语句修改数据库内容时，数据的完整性可能会遭到破坏。可能会存在下列情况：无效的数据被添加到数据库的表中。如：将学生考试成绩输入成负数；SQLServer提供了对数据库中表、列实施数据完整性的方法。对表进行设计数据完整性有两个重要内容：标识列的有效值和确定如何强制列中的数据完整性。31.域完整性域完整性是指一个列的输入有效性，是否允许空值。强制域完整性的方法有：限制类型（通过设定列的数据类型）、格式（通过CHECK约束和规则）或可能值的范围通过FOREIGNKEY约束、CHECK约束、DEFAULT定义、NOTNULL定义和规则。42.实体完整性实体完整性是指保证表中所有的行唯一。实体完整性要求表中的所有行都有一个唯一标识符。这个唯一标识符可能是一列，也可能是几列的组合，称之为主键。也就是说，表中主键在所有行上必须取值唯一。强制实体完整性的方法有：索引、UNIQUE约束、PRIMARYKEY约束或IDENTITY属性。53.参照完整性参照完整性也叫引用完整性。参照完整性总是保证主关键字（被引用表）和外部关键字（引用表）之间的参照关系。它涉及两个或两个以上表数据的一致性维护。66.5.2数据完整性实施方法1.声明型数据完整性声明型数据完整性一般在对象创建时定义，由SQLServer强制实施，通常使用约束、缺省值和规则来实现。实现基本数据完整性的首选方法是使用声明型数据完整性。声明型数据完整性作为数据库对象说明的一部分在语法中实现，在CREATETABLE和ALTERTABLE定义中使用CONSTRAINT、DEFAULT等语句限制表中的值。76.5.2数据完整性实施方法2.过程型数据完整性过程型数据完整性是指由某个过程引发而实施的数据完整性。一般先写出实施数据完整性的条件，再写出强制该条件所执行的用于保证数据完整性的脚本。通常由触发器和存储过程实现。过程型数据完整性也可以在客户机和服务器上使用其它编程语言和工具实现。86.5.3约束1.约束的定义和类型约束是SQLServer提供的自动保持数据库完整性的一种方法。列级约束：列级约束是行定义的一部分，只能够应用在一列上。表级约束：表级约束的定义独立于列的定义，可以应用在一个表中的多列上。96.5.3约束1.约束的定义和类型约束有六种类型：非空约束、缺省约束、检查约束、主键约束、唯一约束、外键约束（参照约束）。非空约束（NOTNULL）：表中的某些列必须存在有效值，不允许有空值出现。这是最简单的数据完整性约束，可在建表时将该列声明为NOTNULL即可。缺省约束（DEFALUTCONSTRAINTS）：当向数据库中的表插入数据时，如果用户没有明确给出某列的值，SQLServer自动为该列输入指定值。10检查约束（CHECKCONSTRAINTS）：限制插入列中的值的范围。主键约束（PRIMARYKEYCONSTRAINTS）：要求主键的列上没有两行具有相同值，也没有空值。唯一约束（UNIQUECONSTRAINTS）：要求表中所有行在指定的列上没有完全相同的列值。外键约束（FROEIGNKEYCONSTRAINTS）：要求正被插入或更新的列（外键）的新值，必须在被参照表（主表）的相应列（主键）中已经存在。11不同的约束强制不同类型的数据完整性。表6-2给出了两者的对应关系。完整性类型约束类型域完整性非空约束DEFAULTCHECK实体完整性PRIMARYKEYUNIQUE参照完整性FOREIGNKEY122.使用T-SQL语言创建、管理约束(1)使用CREATETABLE语句创建约束使用CREATETABLE语句创建约束的一般语法如下：CREATETABLEtable_name（column_namedata_type[[CONSTRAINTconstraint_name]{PRIMARYKEY[CLUSTERED|NONCLUSTERED]|UNIQUE[CLUSTERED|NONCLUSTERED]|[FOREIGNKEY]REFERENCESref_table[(ref_column)]|DEFAULTconstant_expression|CHECK(logical_expression)}][,...n])13其中：•table_name：创建约束所在的表的名称。•column_name：列名。•data_type：数据类型。•constraint_name：约束名。在创建、修改、实现约束时注意以下几点：①可以在已有的表上创建、修改、删除约束，而不必删除并重建表。②可以在应用程序中创建错误检查逻辑，测试是否违反约束。③在给表添加约束时，SQL将验证表中已有数据是否满足正在添加的约束。14⑵缺省约束缺省约束是指当向数据库中的表插入数据时，如果用户没有明确给出某列的值时，SQLServer自动为该列输入的值，缺省约束用于实现域的完整性。创建DEFAULT约束时应考虑以下因素：DEFAULT约束只能用于INSERT语句不能用于具有IDENTITY属性的列每列只能有一个DEFAULT约束15⑵缺省约束【例6-8】

为数据库JWGL中表teacher的SEX（性别）字段创建一个缺省约束，缺省值为1（男）USEJWGLGOALTERTABLEteacherADDCONSTRAINT

default_sexDEFAULT1FORSEX16⑶检查约束检查约束用来指定某列可取值的清单、可取值的集合或可取值的范围。检查约束主要用于实现域完整性。创建CHECK约束时应考虑以下因素：当向数据库中的表执行插入或更新操作时，检查插入的新列值是否满足CHECK约束条件不能在具有IDENTITY属性，或具有timestamp或uniqueidentifier数据类型的列上放置CHECK约束CHECK条件不能含有子查询17⑶检查约束【例6-9】

为数据库JWGL中表student_course的GRADE（成绩）字段创建一个检查约束，使得GRADE的值在0-100之间。USEJWGLGOALTERTABLEstudent_courseADDCONSTRAINTcheck_gradeCHECK(grade>=0andgrade<=100)18⑷主键约束主键约束保证某一列或一组列值的组合相对于表中的每一行都是唯一的，主键约束创建在表的主键列上，它对实现实体完整性更加有用。主键约束的作用就是为表创建主键。创建PRIMARYKEY约束时应考虑以下因素：每个表只能有一个主键，并且必须有一个主键;不允许有空值;参照约束使用它作为维护参照完整性的参考点;创建主键时，在创建主键的列上创建了一个唯一索引，可以是聚簇(cù)索引，也可以是非聚簇索引，默认是聚簇索引。19Clustered—聚簇索引按物理顺序储存，占用空间小，检索速度快，但影响数据插入、更新、删除的效率Nonclustered－－非聚簇索引按逻辑顺序储存，占用空间大，检索数度慢，但对数据插入、更新、删除的影响小。一张表只能有一个聚簇索引（一般是主键），可以有多个非聚簇索引。20⑷主键约束【例6-10】

将数据库JWGL中teacher表的teacher_id（教师号）字段设为主键。USEJWGLGOALTERTABLEteacherADDCONSTRAINTPK_teacher_idPRIMARYKEY

clustered(teacher_id)21⑸唯一约束唯一约束限制表中指定列上所有的非空值必须唯一，即表中任意两行在指定列上都不允许有相同的值。创建UNIQUE约束时应考虑以下因素：一个表可以放置多个UNIQUE约束允许有空值创建唯一索引时强制UNIQUE约束22⑸唯一约束【例6-11】

将数据库JWGL中department表的department_name（部门名称）字段设为唯一约束。USEJWGLGOALTERTABLEdepartmentADDCONSTRAINTunique_department_nameUNIQUEnonclustered(department_name)23唯一约束和主键约束的区别：唯一约束与主键约束都为指定的列建立唯一索引，即不允许唯一索引的列上有相同的值。主键约束限制更严格，不但不允许有重复值，而且也不允许有空值。唯一约束允许有空值。

唯一约束与主键约束产生的索引可以是聚簇索引也可以是非聚簇索引，但在缺省情况下唯一约束产生非聚簇索引，主键约束产生聚簇索引。24⑹外键约束通过外键约束强制参照完整性。SQLServer提供了外键/主键值约束。即满足以下两点：①存在外键时，被参照表中这一行不能删除。②向子表插入记录或更新子表中外键值的前提是，必须保证这个外键值与主表中主键的某个值相等或者该外键值为空，否则不允许插入或修改外键值。创建FOREIGNKEY约束时应考虑以下因素：它提供一列或多列数据的参照完整性。FOREIGNKEY约束不自动创建索引。但如果考虑数据库的多表连接，建议为外键创建一个索引，以提高连接性能主键与外键的数据类型和长度必须一致，或系统可转换25【例6-12】为表teacher创建外键department_id，外键department_id参考表department中的主键department_id。USEJWGLGOALTERTABLEteacherADDCONSTRAINTFK_department_idFOREIGNKEY(department_id)REFERENCESdepartment(department_id)GO263.使用SQLServerManagementStudio管理约束（P63~P80）⑴使用SQLServerManagementStudio创建缺省约束使用SQLServerManagementStudio创建缺省约束的具体步骤如下：①启动SQLServerManagementStudio。②分别点击“数据库”、“JWGL”、“表”，SQLServerManagementStudio右边区域显示数据库JWGL中所有的表。③右击要设置缺省的表（假设要设置缺省的表为teacher），在系统弹出的快捷菜单上点击“设计表”，④点击设置缺省的列，在下面列属性设置栏的默认值输入框中，输入对应缺省值即可。⑵使用SQLServerManagementStudio创建检查约束⑶使用SQLServerManagementStudio创建主键约束⑷使用SQLServerManagementStudio创建唯一性约束⑸使用SQLServerManagementStudio创建外键约束276.5.4使用默认值1.默认值的定义默认值是数据库对象之一，它指定在向数据库中的表插入数据时，如果用户没有明确给出某列的值，SQLServer自动为该列使用此默认值。它是实现数据完整性的方法之一。在关系数据库中，每个列都必须包含有值，即使这个值是个空值。对于不接受空值的列，就必须为该列输入某个非空值，要么由用户明确输入，要么由SQLServer输入默认值。默认值可用于表中的列和用户定义数据类型。请注意，缺省约束中使用的缺省值可以由用户创建时输入，也可以通过绑定已创建的默认值来指定。286.5.4使用默认值1.默认值的定义在创建默认值时，请考虑以下几点：①默认值需和它要绑定的列或用户定义数据类型具有相同的数据类型。②默认值需符合该列的所有规则。③默认值缺省还需符合所有CHECK约束。创建、管理默认值可以用T-SQL语言也可以用SQLServerManagementStudio。292.使用T-SQL语言创建默认值：⑴创建默认值的语法CREATEDEFAULTdefault_nameASconstant_expression其中：•default_name：新建默认值的名称，它必须遵循SQLServer标识符的命名规则。•constant_expression：默认值default_name的值，是一个常数表达式，在这个表达式中不能含有任何列名或其他数据库对象名，但可使用不涉及数据库对象的SQLServer内部函数。默认值创建后，可以使用系统存储过程sp_helptext可以查看用于定义一个默认值的SQL脚本。其语法是：

sp_helptextdefault_name30⑵默认值的绑定默认值创建之后，只有将其绑定到某个列或用户自定义数据类型才能有效。使用系统存储过程sp_bindefault可实现与表中的列及用户自定义数据类型的绑定。一个默认值可以绑定到多个列或用户自定义数据类型。绑定默认值的语法是：

sp_bindefaultdefault_name,‘object_name’其中：•default_name：由CREATEDEFAULT语句创建的默认值名字，它将与指定的列或用户定义数据类型相绑定。•object_name：要与该默认值相绑定的列名或用户自定义数据类型名。如果指定的是表中的列，其格式为‘table_name.column’；否则被认为是用户自定义数据类型名。31默认值绑定的几点考虑：①绑定的默认值只适用于受INSERT语句影响的行。②不能将默认值绑定到系统数据类型或timestamp列。③若绑定了一个默认值到一用户定义数据类型，又给使用该数据类型的列绑定了一个不同的默认值或规则，则绑定到列的默认值和规则有效。32⑶默认值绑定的解除一个默认值被绑定到表中的列或用户自定义的数据类型之后，可使用系统存储过程sp_unbinddefault解除其绑定。绑定解除后，作为数据库对象的默认值仍然存在于数据库中。解除默认值绑定的语法是：

sp_unbindefault‘object_name’其中：•object_name：要解除和默认值绑定的表的列名或用户自定义数据类型名。如果指定的是表中的列，其格式为“table_name.column”，否则被认为是用户定义数据类型名。33⑷默认值绑定的删除如果一个默认值不再使用了，可以用DROP语句将其删除。删除默认值的语法是：DROPDEFAULTdefault_name其中：•default_name：要删除默认值的名称。在删除默认值时，应注意以下几点：①DROP将默认值从数据库中删除。②如果要删除一个默认值，必须解除这个默认值的所有绑定，否则该默认值就不能被删除掉。③在一个批中不能既定义默认值又同时使用默认值。④只有默认值的创建者可以删除默认值。34【例6-13】

为数据库JWGL创建一个名为profession_default，值为“讲师”的默认值，并绑定到表teacher的profession列，然后解除这个绑定，绑定解除后将此默认值删除。USEJWGLGOCREATEDEFAULTprofession_defaultAS'讲师'GOsp_bindefaultprofession_default,'fession'GOsp_unbindefault'fession'DROPDEFAULTprofession_defaultGO356.5.5规则1.规则的定义规则是数据库对象之一。它指定向表的某列（或使用与该规则绑定的用户定义数据类型的所有列）插入或更新数据时，限制输入新值的取值范围。一个规则可以是：值的清单或值的集合;值的范围;必须满足的单值条件;用like子句定义的编辑掩码;366.5.5规则1.规则的定义规则是实现域完整性的方法之一。规则用来验证一个数据库中的数据是否处于一个指定的值域范围内，是否与特定的格式相匹配。当数据库中数据值被更新或被插入时，就要检查新值是否遵循规则，如果不符合规则就拒绝执行此更新或插入的操作。37规则可用于表中列或用户定义数据类型。规则在实现功能上等同于CHECK约束。用CREATERULE语句创建规则，然后用sp_bindrule把它绑定至一列或用户定义的数据类型。使用系统存储过程sp_helptext查看用于定义一个规则或缺省的SQL脚本。其语法是：

sp_helptextrule_name38在创建规则时，应考虑以下几点：①缺省情况下，规则将检查创建和绑定规则之前输入的数据。②规则可以绑定到一列、多列或数据库中的用户定义数据类型。③规则要求一个值在一定范围内，并与特定格式相匹配。④在一个列上至多有一个规则起作用，如果有多个规则与一列相绑定，那么只有最后绑定到该列的规则才是有效的。39规则与CHECK约束的区别1）CHECK约束比规则更简明，一个列只能应用一个规则，但是却可以应用多个CHECK约束。2）CHECK约束可作为CREATETABLE语句的一部分进行指定，而规则以单独的对象创建，然后绑定到列上。3）CHECK约束比规则优先级别高。4）一个规则可以跟多个列绑定，CHECK约束只能用于一个列，如需再用，需重新定义。402.使用T-SQL语言创建、管理规则⑴创建规则的语法是：CREATERULErule_nameAScondition_expression•rule_name：创建规则的名称，应遵循SQLServer标识符的命名规范。•condition_expression：指明定义规则的条件，在这个条件表达式中不能包含列名或其他数据库对象名，但它带有一个@为前缀的参数（即参数的名字必须以@为第一个字符），也称空间标志符(spaceholder)。412.使用T-SQL语言创建、管理规则⑵规则的绑定规则创建之后，只有将其绑定到某个列或用户自定义数据类型才能有效。使用系统存储过程sp_binderule可实现与表中的列及用户自定义数据类型的绑定。绑定规则的语法是：

sp_bindrulerule_name,object_name42其中：•rule_name

：由CREATERULE语句创建的规则名字，它将与指定的列或用户定义数据类型相捆绑。•object_name

：指定要与该规则相绑定的列名或用户定义数据类型名。如果指定的是表中的列，其格式为“table.column”，否则被认为是用户定义数据类型名。绑定时应注意如下几点：①绑定的缺省只适用于受INSERT语句影响的行。②绑定的规则只适用于受INSERT和UPDATE语句影响的行。③不能将缺省或规则绑定到系统数据类型或timestamp列。43⑶解除规则使用系统存储过程sp_unbindrule可以解除由sp_bindrule绑定到列或用户定义数据类型的规则。但被解除的规则仍然存在于数据库中。解除规则的语法如下：

sp_unbindrulerule_name,object_name其中：•rule_name：由CREATERULE语句创建的规则名字，它将与指定的列或用户定义数据类型相捆绑。•object_name：指定要解除与该规则相绑定的列名或用户定义数据类型名。如果指定的是表中的列，其格式为table.column；否则被认为是用户定义数据类型名。44⑷删除规则使用DROPRULE可删除由CREATERULE命令创建的规则。删除规则的语法如下：

DROPRULE[owner.]rule_name其中：•rule_name：要解除的规则。在删除规则时，应注意以下几点：①使用DROP可将规则从数据库中删除。②在删除规则之前需先从列中或用户定义数据类型中解除绑定。③只有规则定义者可以删除规则。45【例6-14】

为数据