第5章 数据完整性

第五章数据完整性

数据完整性的概念

约束管理

默认管理

规则管理

完整性技术比较研究

习题1/14/20231一、数据完整性的概念所谓完整性，就是指存储在数据库中数据的一致性和正确性。数据完整性是衡量数据库中数据质量好坏的一种标志，是确保数据库中数据一致、正确以及符合企业规则的一种思想，是使无序的数据条理化，确保正确的数据被存放在正确的位置的一种手段，实施数据库完整性的目的是确保数据的质量。所以，满足完整性要求的数据具有以下3个特点：①数据的值正确无误。②数据的存在必须确保同一表格数据之间的和谐关系。③数据的存在必须能确保维护不同表格数据之间的和谐关系。在MicrosoftSQLServer系统中，从维护数据完整性的意义上来看，数据完整性分为四类：实体完整性、域完整性、参考完整性、用户定义的完整性。1/14/202321.实体完整性实体完整性，也可以称为行完整性，规定表的每一行在表中是惟一的实体。实体就是数据库所要表示的一个实际的物体或事件。实体完整性要求每个实体都保持惟一性，因此，要求表中的所有行有一个惟一的标识符，这种标识符一般称为主键值。实体完整性要求数据库表中的每一条记录都是惟一的，即表中没有重复的记录。因此，这就要求所有数据记录中至少必须有一个字段的内容是不能重复的。1/14/202332．域完整性（值域完整性）域完整性，也可以称为列完整性，是指给定列的输入有效性，即数据库表中的列必须满足某种特定的数据类型或约束。指定一个数据集对某一个列是否有效和确定是否允许空值。域完整性通常是通过有效性检查来实现的，并且还可以通过限制数据类型、格式或者可能的取值范围来实现。因此，强制域有效性的方法有：限制类型（通过数据类型）、格式（通过CHECK约束和规则）或可能值的范围（通过FOREIGNKEY约束、CHECK约束、DEFAULT定义、NOTNULL定义和规则）。 1/14/202343.参照完整性（引用完整性）参照完整性是在插入或删除数据时，维护表格间数据一致性的手段。参照完整性确保在不同表之间的关键性数据保持一致（涉及两个或两个以上表的数据的一致性维护），防止了数据丢失或无意义的数据在数据库中扩散。当添加、删除或修改数据库表中的记录时，可以借助引用完整性来保证相关联的表之间的数据一致性。在SQLServer中，参照完整性一般建立在主键与外键之间的关系，或者外键与唯一索引之间关系的基础上，它确保了有主关键字的表中对应其它表的外部关键字的行存在。1/14/202351/14/202364.用户定义的完整性这是由用户定义的完整性。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。SQLServer提供了定义和检验这类完整性的机制，以便用统一的系统方法来处理它们，而不是用应用程序来承担这一功能。用户定义完整性可以定义不属于其他任何完整性分类的特定业务规则，其它的完整性类型都支持用户定义的完整性。用户定义的完整性主要是体现实际运用的业务规则，可以通过前面三种完整性的实施而得到维护。例如：在titles表中，规定商业类用书的title_id的前两个字符必须是BU等。1/14/20237实现数据完整性在MicrosoftSQLServer系统中，可以使用两种方式实现数据完整性，即声明数据完整性和过程数据完整性。声明数据完整性（说明性引用数据完整性）就是通过在对象定义中定义的数据标准来实现数据完整性，在插入、修改和删除数据时由系统本身自动强制来实现的。声明数据完整性的方式包括使用各种约束、缺省和规则。过程数据完整性（引用的行动完整性）是通过在脚本语言中定义的数据完整性标准来实现的。在执行这些脚本的过程中，由脚本中定义的强制完整性的实现。过程数据完整性的方式包括使用视图、触发器和存储过程等，其中视图和存储过程不能自动执行。1/14/20238实现数据完整性的途径数据完整性类型实施途径数据完整性类型实施途径实体完整体性主键约束PRIMARYKEY域完整性默认值约束Default惟一性约束UniqueKey检查约束Check索引UniqueIndex外键约束ForeignKey标识列IdentityColumn数据类型DataType参照完整性外键约束ForeignKey规则Rule检查约束Check用户定义完整性Rule、Triggers、Procedure触发器TriggersCreateTable中的全部列级和表级约束系统存储过程StoredProcedure1/14/20239二、约束管理约束是通过限制列中、行中和表之间数据输入值的范围来保证数据完整性的非常有效的方法。约束可以分为两种：①列级约束：是行定义的一部分，只能够应用在一列上。②表级约束：其定义独立于列的定义，可以应用在一个表中的多个列上。约束的类型定义约束默认管理检查约束管理主键约束管理惟一性约束管理外键约束管理约束类型综合例题1/14/202310约束的类型完整性类型约束类型描述域完整性DEFAULT(缺省)在使用INSERT语句插入数据时，如果某个列的值没有明确提供，则将定义的缺省值插入到该列中CHECK(检查)指定某一个列中的可保存值的范围实体完整性PRIMARYKEY(主键)每一行的惟一标识符，确保用户不能输入冗余值和确保创建索引，提高性能，不允许空值UNIQUE(惟一性)防止出现冗余值，并且确保创建索引，提高性能。允许空值参考完整性FOREIGNKEY(外键)定义一列或几列，其值与本表或者另外一个表的主键值匹配约束是保证数据完整性的有效方法。约束可以确保把有效的数据输入到列中和维护表和表之间的特定关系。每一种数据完整性类型，例如实体完整性、域完整性、参考完整性，都由不同的约束类型来保障。下表描述了不同类型的约束和完整性之间的关系。1/14/202311定义约束定义约束从无到有的创建约束，这种操作可以使用CREATETABLE语句或ALTERTABLE语句完成。使用CREATETABLE语句表示在创建表的时候定义约束，使用ALTERTABLE语句表示在已有的表中添加约束。如果表中已经有数据，那么也可以增加约束。1/14/202312CREATETABLE语句CREATETABLEtable_name(column_namedata_type[CONSTRAINTconstraint_name{PRIMARYKEY[CLUSTERED|NONCLUSTERED]|UNIQUE[CLUSTERED|NONCLUSTERED]|FOREIGNKEYREFERENCESref_table(ref_colunm)|DEFAULTconstant_expression|CHECKlogical_expression}]使用ALTERTABLE语句定义约束的语法与使用CREATETABLE语句定义约束的语法类似。1/14/202313【示例】定义一个主键约束CREATETABLEstudents(student_idintNOTNULLCONSTRAINTPK_student_idPRIMARYKEY,student_namevarchar(10)NOTNULL,student_genderchar(1)NOTNULL,student_birthdaydatetimeNOTNULL)1/14/202314当定义约束或修改约束的定义时，应该考虑下列因素：不必删除表，就可以直接创建、修改和删除约束的定义。应该在应用程序中增加错误检查机制，测试数据是否与约束相冲突。当在表上增加索引时，SQLSERVER系统检查表中的数据是否与约束冲突。当创建约束时，应该指定约束的名称。否则，系统将要提供复杂的、系统自动生成的名称。对于一个数据库来说，约束名称必须是惟一的。一般来说，约束的名称格式应该是：约束类型简称_表名_列号_代号1/14/202315约束信息的查看查看约束信息的方法为：（1）执行系统存储过程，如：sp_help或sp_helpconstraint。（2）查询信息模式视图，如：check_constraints、referential_constraints或table_constraints。（3）查看一些系统表，例如约束的定义存储在下列系统表中：sysconstraints、sysreferences和syscomments。1/14/202316（1）使用sp_helpconstraint可得到数据库中某一个表中的全部约束信息。【例】显示pubs数据库中authors表中的全部约束信息USEpubsEXECsp_helpconstraintauthors1/14/202317（2）syscomments系统表包含了每一个视图、规则、缺省、触发器、检查约束、缺省约束和存储过程等信息。在该系统表中的文本列上记录了这些对象的原始定义语句。使用该系统表，可以查看有关约束的定义信息。【例】显示pubs数据库中syscomments系统表的内容USEpubsSELECTid,text,texttype,languageFROMsyscomments1/14/202318（3）sysreferences系统表记录了与外键约束有关的信息。【例】显示pubs数据库中sysreferences系统表的内容USEpubsSELECT*FROMsysreferences1/14/202319默认（DEFAULT）约束管理当使用INSERT语句插入数据时，如果某一个列没有指定数据，那么默认约束就在该列中输入一个值。因此，默认约束保证了域完整性。DEFAULT约束用于指定一个字段的默认值。它的作用是：当向表中插入数据时，如果用户没有给某一字段输入数据，则系统自动将默认值作为该字段的数据内容。向表中添加数据时，如果没有输入字段值，则此字段的值可能是下面几种情况：●此字段定义了默认值，则此字段的内容为默认值；●此字段未定义默认值，而且允许为NULL值，则NULL值将成为该字段的内容；●此字段未定义默认值，也不允许为NULL值，保存时将会出现错误信息，而且添加数据操作失败。1/14/202320使用CREATETABLE语句创建DEFAULT约束在CREATETABLE语句中添加DEFAULT子句，DEFAULT子句格式如下：[CONSTRAINTconstraint_name]DEFAULTconstant_expression其中：constraint_name为约束名；constant_expression为常量表达式。【示例】

将入学日期字段的默认值设置为2003-9-1CREATETABLEstud_new(学号char(8)NOTNULL,姓名char(8)NOTNULL,入学日期datetimeCONSTRAINTdtDEFAULT'2003-9-1')1/14/202321使用企业管理器创建DEFAULT约束1/14/202322当使用默认约束时，需要考虑下列一些因素：默认约束只能应用于INSERT语句。每一个列只能定义一个默认约束。默认约束不能放在有IDENTITY属性的列上或者数据类型为timestamp的列上。默认约束允许指定一些由系统函数提供的值。这些系统函数包括USER、CURRENT_USER、SESSION_USER、SYSTEM_USER、CURRENT_TIMESTAMP等。1/14/202323检查（CHECK）约束管理检查约束用来限制用户输入某一个列的数据，即在该列中只能输入指定范围的数据。检查约束非常类似于WHERE子句，其共同之处就是指定可接受数据的条件。检查约束与外键约束的相同之处，在于都是通过检查数据的值的合理性来实现数据完整性的维护。但是，外键约束是从另一张表上获得合理的数据，而检查约束则是通过对一个逻辑表达式的结果进行判断来对数据进行检查。检查约束可以用来限制字段上可以接受的数据值。检查约束使用逻辑表达式来限制字段上可以接受的数据值。检查约束通过检查输入表中字段的的数据值来维护域完整性。1/14/202324使用检查约束的方式有：●在创建表格时，定义检查约束，检查约束是表格定义的一部分。●在已经建立的表上添加检查约束。●修改或删除表上已经定义的检查约束。1/14/202325使用CREATETABLE语句创建检查约束使用CREATETABLE语句创建表时，可以通过在该语句中添加一个CHECK子句创建检查约束。该子句的语法格式如下： [CONSTRAINTcheck_name]CHECK(check_criterial)其中，check_name为约束名，check_criterial为约束条件。【示例】将“学号”和“课程编号”两个字段的组合设置为主键，再将“成绩”字段的取值限制在0到100之间USElxCREATETABLE学生成绩( 学号char(8)NOTNULL, 课程编号char(3)NOTNULL, 成绩realNOTNULL

CONSTRAINTCK_CJCHECK(成绩>=0AND成绩<=100),

CONSTRAINTpk_xkPRIMARYKEY(学号,课程编号))

1/14/202326【例】定义一个检查约束ALTERTABLEstudentsADDCONSTRAINTCHECK_student_denderCHECK(student_gender='F'ORstudent_gender='M')【示例】创建名为chk_id的约束CREATETABLEcust_sample(cust_id intPRIMARYKEY,cust_namechar(50),cust_addresschar(50),cust_credit_limit money,

constraintchk_idcheck(cust_idbetween0and10000))1/14/202327当使用检查约束时，需要考虑以下因素：当执行INSERT语句或UPDATE语句时，该约束验证相应的数据是否满足检查约束的条件。检查约束可以参考本表中的其他列。例如，在employee表中包含出生日期（birthdate）列和雇用日期（hiredate）列，那么birthdate列可以引用hiredate列，使得birthdate列的数据小于hiredate列的数据。检查约束不能放在有IDENTITY属性的列上或者数据类型为timestamp的列上。因为这两种列都会自动插入数据。检查约束不能包含子查询语句。1/14/202328删除检查约束的语法是：ALTERTABLEtable_nameDROP{[CONSTRAINT]check_name}[,...n]向表添加检查约束的语法是：ALTERTABLEtable_name{ADDcolumn_name

column_definition

CHECK(check_criterial)}[,...n]使所有的约束或指定的约束无效的语法是：ALTERTABLEtable_name{CHECK|NOCHECK}CONSTRAINT{ALL|constraint_name[,...n]}1/14/202329使用企业管理器创建检查约束1/14/202330主键（PRIMARYKEY）约束管理一个表通常可以通过一个字段（或多个字段组合）的数据来惟一标识表中的每一行，这个字段（或字段组合）被称为表的主键。为了有效实现数据的管理，每个表都应该有主键，且只能有一个主键。建议使用一个小的整数列作为主键。如果要求表中的一个字段（或几个字段的组合）具有不重复的值，而且不允许为NULL值，就应当将这个字段(或字段组合)设置为表的主键。设置主键后，系统会检查该字段（或字段组合）的输入值是否符合这个约束条件，从而来维护数据的完整性，并且也可以减少输入错误数据的概率。主键约束在表中定义一个主键值，这是惟一确定表中每一行数据的标识符。在所有的约束类型中，主键约束是最重要的一种约束类型，也是使用最为广泛的约束类型。该约束强制实体完整性。1/14/202331如果不在主键字段中输入数据或输入的数据在前面已经输入过，则这条记录将被拒绝。另外，当主键是由多个字段组合而成时，某个字段的数据可以出现重复，但是这几个字段的组合值必须是惟一的。使用主键约束的方式有：●在创建表格时，定义主键，主键是表格定义的一部分。●在一个没有定义主键的表上添加主键约束。●修改或删除表上已经定义的主键。1/14/202332在CREATETABLE语句中添加PRIMARY子句，PRIMARY子句格式如下： [CONSTRAINTconstraint_name] PRIMARYKEY[CLUSTERED|NONCLUSTERED] [(column_name,...n)]使用CONSTRAINT关键字时，可以显式地对约束命名。如果省略这个关键字，则由系统自动地对约束命名。主键字具有索引作用，用来实现对表内数据的快速查询。索引类型可以是CLUSTERED(聚集的)或NONCLUSTERED(非聚集的)，默认类型为CLUSTERED(聚集的)。使用CREATETABLE语句创建主键约束1/14/202333①若用单个字段作为主键，则直接在该字段定义中加入PRIMARYKEY子句，但不能指定字段名；【示例】用单个字段作为主键 CREATETABLElx1( 学号char(8)NOTNULLPRIMARYKEY, 姓名char(8)NOTNULL, 入学日期datetime)【示例】用单个字段作为主键 CREATETABLElx1( 学号char(8)NOTNULL

CONSTRAINTpk_ID

PRIMARYKEY, 姓名char(8)NOTNULL, 入学日期datetime)1/14/202334一个表中可以有一个以上的列组合，这些组合能惟一标识表中的行，这任意一种列组合被成为候选键（candidatekey）。数据库管理员从候选键中选择一个（也只能挑选一个）合适的作为表的主键。【示例】在part_sample表中，part_nmbr和part_name都可以是候选键，但是只将part_nmbr选作主键。USEpubsCREATETABLEpart_sample(part_nmbrintPRIMARYKEY,part_namechar(30),part_weightdecimal(6,2),part_colorchar(15))1/14/202335②若用几个字段的组合作为主键，则在各个字段定义后面加上PRIMARYKEY子句，并指定组成主键的各个字段名。【示例】用几个字段的组合作为主键，建立表级主键CREATETABLE成绩(学号char(8)NOTNULL, 课程编号char(3)NOTNULL, 成绩real,

CONSTRAINTpk_x_kPRIMARYKEY(学号,课程编号))1/14/202336删除主键的语法是：ALTERTABLEtable_nameDROP{[CONSTRAINT]primarykey_name}[,...n]向表添加主键约束的语法是：ALTERTABLEtable_nameADD[CONSTRAINTprimarykey_name]

PRIMARYKEY[CLUSTERED|NONCLUSTERED](column_name[,...n])1/14/202337查看指定表上的主键信息利用系统存储过程sp_pkeys，可以浏览指定表上的主键信息。语法为：sp_pkeystable_name【示例】USEpubsEXECsp_pkeyspart_sampleGO1/14/202338通过企业管理器创建/取消主键约束1/14/202339惟一性（UNIQUE）约束管理惟一性约束主要用来确保不受主键约束的列上的数据的惟一性。即：保证在不是主键的字段上不会出现重复的数据。当表中已经有了一个主键约束时，如果需要在其他列上实现实体完整性，由于表中不能有两个或两个以上的主键约束，那么可以通过创建惟一性约束来实现。一般把唯一性约束称为候选的主键约束。使用惟一性约束的方式有：●在创建表格时，定义惟一性约束，惟一性约束是表格定义的一部分。●在已经有数据但是没有重复值的列或列的集合上添加惟一性约束。●修改或删除表上已经定义的惟一性约束。1/14/202340使用惟一性约束和主键约束都可以保证数据的惟一性，但它们之间有两个明显的区别：●惟一性约束主要用在非主键的一列或多列上要求数据惟一的情况。●定义了惟一性约束的字段的数据可以为NULL值，而定义了主键约束的字段的数据不能为NULL值。●可以在同一个表上设置多个惟一性约束，而在一个表上只能设置一个主键。1/14/202341通过在CREATETABLE语句中添加一个UNIQUE子句，也可以设置惟一性约束，该子句的语法格式如下：

CONSTRAINTconstraint_nameUNIQUE[CLUSTERED|NONCLUSTERED](column[,…n])如果要对单个字段创建惟一性约束，则直接在字段定义中添加UNIQUE子句；如果要对几个字段的组合创建惟一性约束，则在各个字段定义后面加上UNIQUE子句。【例】将“教材编号”设置为主键，将“教材名”设置为惟一性约束USElxCREATETABLE教材(教材编号char(8)NOTNULLCONSTRAINTPK_JPRIMARYKEY,教材名char(20)NULLCONSTRAINTUN_JUNIQUENONCLUSTERED,出版社char(30),定价real)使用CREATETABLE语句创建惟一性约束1/14/202342删除惟一性约束的语法是：ALTERTABLEtable_nameDROP{[CONSTRAINT]unique_name}[,...n]向表添加惟一性键约束的语法是：ALTERTABLEtable_nameADD[CONSTRAINTunique_name]

UNIQUE[CLUSTERED|NONCLUSTERED](column_name[,...n])1/14/202343

使用企业管理器创建惟一性约束1/14/202344外键（FOREIGNKEY）约束管理外键约束用于维护同一数据库中两表之间的一致性关系。如果希望一个表中的字段(或字段组合)与其他表中的主键字段(或具有惟一性约束的字段，或字段组合)相关，这个字段(或字段组合)就成为前一个表中的外键。外键约束将限制破坏这种关联的操作。外键约束强制参考完整性。外键约束定义一个列或多个列，这些列可以参考同一个表或另外一个表中的主键约束列或惟一性约束列。实际上，通过创建外键约束可以实现表和表之间的依赖关系。在SQLServer关系型数据库管理系统中，表和表之间存在着大量的关系，这些关系都是由主键约束和外键约束共同实现的。1/14/202345以SQLServer提供的pubs数据库中的publisgers表和titles表为例。外键的作用不只是对输入自身表格的数据进行限制，同时也限制了对主键所在表的数据进行修改。当主键所在的表的数据被另一张表的外键所引用时，用户将无法对主键里的数据进行修改或删除，除非事先删除或修改引用的数据。1/14/202346如果定义了两个表之间的参照完整性，则要求：（1）从表不能引用不存在的键值。例如，CJB表中行记录出现的学号必须是XSB表中已存在的学号。（2）如果主表中的键值更改了，那么在整个数据库中，对从表中该键值的所有引用要进行一致的更改。例如，如果对XSB表中的某一学号修改，则CJB表中所有对应学号也要进行相应的修改。（3）如果主表中没有关联的记录，则不能将记录添加到从表。如果要删除主表中的某一记录，则应先删除从表中与该记录匹配的相关记录。1/14/202347使用CREATETALBE语句创建外键约束该子句的语法格式如下：

[CONSTRAINTconstraint_name] FOREIGNKEY(column_name[,...n]) REFERENTCESref_table(ref_column[,...n])其中，constraint_name为约束名，column_name为字段名，ref_table为引用表，ref_column为引用表中的具有主键或惟一性属性的字段。在FOREIGNKEY关键字后面的列名是外键约束所在的表中的列名，REFERENCES关键字后面的表名和列名是主键约束所在的表名和列名。若外键由一个字段组成，则该字段名可以省略，可将FOREIGNKEY子句跟在该字段定义的后面；若外键由多个字段组合而成，则该字段名列表不能省略，应将FOREIGNKEY子句放在所有字段定义的后面。1/14/202348【示例】创建外键约束CREATEDATABASE商品销售/*创建一个名为“商品销售”的数据库。*/GOUSE商品销售CREATETABLE商品(/*创建一个名为“商品”的主键表*/商品编号char(8)NOTNULLCONSTRAINTPK_SPRIMARYKEY,/*设置商品编号字段为主键*/品名char(16),单价real)GOCREATETABLE销售(/*创建一个名称为“销售”的外键表*/订单编号char(8)NOTNULL,商品编号char(8)NOTNULL

CONSTRAINTFK_SFOREIGNKEYREFERENCES商品(商品编号),/*设置商品编号字段为外键*/数量int,金额real,

CONSTRAINTPK_D_SPRIMARYKEY(订单编号,商品编号))/*设置“订单编号”和“商品编号”两个字段的组合为主键*/GO1/14/202349【示例】建立外键约束CREATEDATABASEFK/*创建数据库*/GOUSEFKCREATETABLEpub(/*创建描述出版商的表pub*/pub_idvarchar(20)PRIMARYKEY,pub_namevarchar(50),addressvarchar(20),cityvarchar(10),statechar(2),countrychar(10))GOCREATETABLEauthor(/*创建描述作者的表author*/author_idvarchar(20)PRIMARYKEY,author_namevarchar(50),phonevarchar(20),zipcodechar(10))GO1/14/202350CREATETABLEbook(/*创建描述书的表*/title_idintPRIMARYKEY,title_namevarchar(50),autor_idvarchar(20)CONSTRAINTFK_auidFOREIGNKEYREFERENCESauthor(author_id),

/*列级外键限制author_id*/pub_idvarchar(20)CONSTRAINTFK_pubFOREIGNKEYREFERENCESpub(pub_id))

/*表级外键限制pub_id*/GO1/14/202351外键约束可以引用同一个数据库中同一张表里的列，称为自引用表。【示例】参考同一表的外键约束USElibraryALTERTABLEemployee(emp_numintNOTNULL

CONSTRAINTpk_emp_numPRIMARYKEY,mgr_numintNOTNULL

CONSTRAINTfk_mgr_numREFERENCESemployee(emp_num))注意：在这个外键约束中，由于参考了同一个表，所以只使用REFERENCES子句，没有使用FOREIFNKEY子句。1/14/202352创建外键时注意：●外键字段与主键字段的数据类型应当匹配，字段长度应当相等，字段名称可以相同也可以不同，两个表必须位于同一个数据库内。●具有主键约束或惟一性约束的表为主键表，另一个则为外键表。1/14/202353删除外键约束的语法是：ALTERTABLEtable_nameDROP{[CONSTRAINT]foreignkey_name}[,...n]向表添加外键约束的语法是：ALTERTABLEtable_name{ADD

{

column_name

column_definition[CONSTRAINTconstraint_name]

[[FOREIGNKEY]REFERENTCESref_table[(ref_column)]]}[,...n]

1/14/202354【示例】在juvenile表中增加一个外键约束该约束把juvenile表中的成年人代号与adult表中的成年人代号关联起来，在这两个表之间创建了一种制约关系。USElibraryALTERTABLEjuvenileADDCONSTRAINTFK_adult_menbernoFOREIFNKEY(adult_menberno)REFERENCESadule(menber_no)1/14/202355使用sp_fkeys系统存储过程查看有关外键的信息，语法为：

sp_fkeysprimary_table_name【示例】查看引用表pub的主键的外键信息USEFKEXECsp_fkeyspubGO查看外键信息1/14/202356使用企业管理器创建外键约束1/14/202357

图表允许以一种图形化的方式来管理和使用数据库的表格、字段、索引和约束等。通过图表，用户可以浏览数据库中的表格和表格的关系，也可以对数据库的结构作复杂的操作。使用数据库关系图管理外键约束1/14/2023581/14/2023591/14/202360综合例题createdatabase教学成绩管理数据库gocreatetable班级信息表(编号char(8)primarykey,--定义主键名称nvarchar(15)notnull,入学日期datetimenotnull,专业编号char(6))gocreatetable学生信息表(学号char(6)primarykey,姓名nchar(4)notnull,性别nchar(1)check(性别in('女','男')),出生日期datetime,民族nvarchar(8)default('汉族'),籍贯nvarchar(20)default('陕西省'),家庭地址nvarchar(20),邮政编码char(6),宿舍号intidentity(1001,1)notnull,--创建标识列宿舍电话varchar(8)constraintck_宿舍电话check(宿舍电话like'8155[0-9][0-9][0-9][0-9]'),身份证号char(18)notnullunique,政治面貌nvarchar(5)check(政治面貌in('中共党员','共青团员','群众','其他')),班级编号char(8)references班级信息表(编号)onupdatecascade,--onupdatecascade表示级联更新，即参照表（父表）中更新被引用主键数据时，也将在引用表（子表）中更新引用外键数据。备注varchar(200),简历ntext,照片image)1/14/202361三、默认值管理默认（DEFAULT，缺省）是一种数据库对象，是往用户输入记录时没有指定具体数据的列中自动插入的数据。默认值对象可以用于多个列或用户自定义数据类型，当使用INSERT语句向表中插入数据时，如果没有为绑定有默认值的列或者数据类型指定数据，那么系统将自动地把指定的默认值插入到相应的位置。当默认值定义之后，可以反复使用。默认值定义是限制列数据的首选并且标准的方法，因为定义和表存储在一起，当除去表时，将自动除去默认值定义。然而，当在多个列中多次使用默认值时，默认值也有其优点。定义默认默认值绑定解除默认值绑定删除默认使用企业管理器管理默认1/14/202362在定义默认时，定义的默认值必须与列的数据类型一致，并且不能与表中的各种约束定义相违背。默认值是一个向后兼容的功能，它执行一些与使用ALTER或CREATETABLE语句的DEFAULT关键字创建的默认值定义相同的功能。使用CREATEDEFAULT创建默认值。语法格式为：

CREATEDEFAULTdefault_name

AS

constant_expression定义默认1/14/202363当创建默认对象时，需要考虑以下因素：作为默认的值必须对绑定到该列或者数据类型上的各种约束定义是有效的。即该值必须是一个合法量。如果定义的默认值大于它随后要绑定的表列的允许长度，那么它将被截断。因此，要确保列的数据库类型和大小要与将要创建的默认值相匹配。如果在数据类型或者列上已经绑定了一个默认，那么就不能在该列上创建一个默认约束。也就是说，在一个列上，只能有一个默认值。1/14/202364【示例】创建字符默认值unknownUSEpubsCREATEDEFAULTphonedfltAS'unknown'GO【示例】使用CREATEDEFAULT创建默认值对象USEpubsCREATEDEFAULT入馆时间ASgetdate()GO1/14/202365默认值绑定将默认值捆绑到列或用户自定义数据类型上，它就可以为列和用户自定义数据类型提供默认值。默认值和表列的绑定通过sp_bindefault系统存储过程实现。语法格式为：sp_bindefault[@defname=]'default_name',

[@objname=]'object_name'

[,[@futureonly=]'futureonly_flag']

1/14/202366A.将默认值捆绑到数据库表中的列上假定已经用CREATEDEFAULT语句在当前数据库中定义了名为today的默认值，此示例将默认值绑定到employees表的hiredate列。当将行添加到employees表而且没有提供hiredate列的数据时，列取得默认值today的值。【示例】USEpubsEXECsp_bindefault'today','employee.hire_date'GO【示例】创建和绑定一个默认USElibraryCREATEDEFAULTphone_no_defaultAS'(010)00000000'GOEXECsp_bindefault'phone_no_default','customers.phone_no_col‘GO1/14/202367

【例如】将前面创建的默认值捆绑到列上USEpubsEXECsp_bindefault'入馆时间','employee.hire_date'1/14/202368B.将默认值绑定到用户定义的数据类型假定存在命名为def_ssn的默认值和命名为ssn的用户定义数据类型，此示例将默认值def_ssn绑定到用户定义的数据类型ssn中。在创建表时，所有指派了用户定义数据类型ssn的列都将继承默认值。类型ssn的现有列也继承默认值def_ssn，除非为futureonly_flag值指定了futureonly，或者在列上直接绑定了默认值。绑定到列的默认值始终优先于绑定到数据类型的默认值。【示例】USEmasterEXECsp_bindefault'def_ssn','ssn'GO1/14/202369C.使用futureonly_flag示例将默认值def_ssn绑定到用户定义的数据类型ssn。因为已指定futureonly，所以不影响类型ssn的现有列。【示例】USEmasterEXECsp_bindefault'def_ssn','ssn','futureonly'GO1/14/202370解除默认值绑定使用sp_unbindefault在当前数据库中为列或用户定义数据类型解除默认值绑定。语法格式为：

sp_unbindefault[@objname=]'对象名'

[,[@futureonly=]'futureonly_flag']1/14/202371a.为列解除默认值绑定为表employees的hiredate列解除默认值绑定。EXECsp_unbindefault'employees.hire_date'b.为用户定义数据类型解除默认值绑定为用户定义数据类型ssn解除默认值绑定。这将为该数据类型的现有列和将来的列解除绑定。EXECsp_unbindefault'ssn'c.使用futureonly_flag为用户定义数据类型ssn解除默认值绑定，现有的ssn列不受影响。EXECsp_unbindefault'ssn','futureonly'1/14/202372删除默认如果要删除一个默认值对象，首先应解除默认值对象与用户定义类型及表字段的捆绑关系，然后才能删除当前数据库中的一个或多个默认值对象。使用DROPDEFAULT从当前数据库中删除一个或多个用户定义的默认值。语法格式为：

DROPDEFAULTdefault_name

[,...n]如果默认值没有绑定到列或用户定义的数据类型，可以很容易地使用DROPDEFAULT将其除去。【示例】删除默认值USElibraryDROPDEFAULTphone_no_defaultGO【例如】解除默认值并删除USE学生图书借阅管理EXECsp_unbindefault'图书信息.入馆时间'DROPDEFAULT入馆时间1/14/202373使用企业管理器管理默认值A.使用企业管理器创建默认值B.使用企业管理器查看默认C.使用企业管理器对默认绑定与松绑D.用企业管理器管理删除默认1/14/202374四、规则管理规则是一种数据库对象，是一组使用T-SQL书写的条件语句，就是数据库中对存储在表的列或用户自定义数据类型中的值的规定和限制。规则是单独存储的独立的数据库对象。规则可以被绑定到一个或多个列上，还可以被绑定到用户自己定义的数据类型上。规则与其作用的表或用户自定义数据类型是相互独立的，即表或用户自定义对象的删除、修改不会对与之相连的规则产生影响。当用户向绑定有规则的数据列上插入或修改值时，如果表中有绑定有规则对象的列或数据类型，那么规则会检测修改值的完整性。当某个规则定义之后，可以反复使用。创建规则捆绑规则解除规则绑定删除规则规则的优先级使用企业管理器管理规则1/14/202375创建规则使用T-SQL语句CREATERULE可以创建规则。语法格式为：

CREATERULErule_name

AScondition_expression或rule_decription条件表达式是定义规则的条件。描述规则的条件表达式中可以包含诸如算术运算符、关系运算符和谓词（如IN、LIKE、BETWEEN）之类的关键字。总之，只要是在查询的WHERE子句中任何有效的表达式都可以用作规则。注：①规则不能引用表中的数据列或其他数据库对象。可以包含不引用数据库对象的内置函数。②条件表达式包含一个局部变量，用来指定满足规则的条件，每个局部变量以且必须以@符号开头，该变量代表在修改该列的记录时用户输入的数值。1/14/202376【例】在“学生图书借阅管理”数据库中定义规则“电话号码”，限制学生的电话号码必须是8位的数字。USE学生图书借阅管理GOCREATERULE电话号码AS@valelike'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'【例】在“学生图书借阅管理”数据库中定义规则“可借图书册数”，限制学生借阅图书的册数在“0~5”之间。USE学生图书借阅管理GOCREATERULE可借图书册数AS@vale>=0AND@vale<=51/14/202377【示例】创建一个规则，用以限制插入该规则所绑定的列中的整数范围。createrulerange_ruleas@range>=$1000and@range<$20000【示例】创建一个规则，用以将输入到该规则所绑定的列中的实际值限制为只能是该规则中列出的有限几个值中。createrulelist_ruleas@listin('1389','0736','0877')【示例】创建一个遵循这种模式的规则：任意两个字符的后面跟一个连字符和任意多个。createrulepattern_ruleas@valuelike'__-%[0-9]'1/14/202378捆绑规则创建规则后，规则仅仅只是一个存在于数据库中的对象，并未发生作用。需要将规则绑定规则与数据库表或用户自定义对象联系起来，才能达到创建规则的目的。联系的方法称为“绑定”。所谓绑定就是指定规则作用于哪个表的哪一列或哪个用户自定义数据类型。表的一列或一个用户自定义数据类型只能与一个规则相绑定，而一个规则可以绑定多个对象，这正是规则的魅力所在。使用系统存储过程sp_bindrule，可以将规则捆绑到列或用户自定义数据类型上。语法格式为：

sp_bindrule[@rulename=]'rule_name',[@objname=]'object_name'[,[@futureonly=]'futureonly_flag']1/14/202379说明：futureonly_flag仅当将规则绑定到用户定义的数据类型时才使用。futureonly_flag的数据类型为varchar(15)，默认值为NULL。如果futureonly_flag被设置为future_only时，所有的表列不管是以后才创建的或绑定的，或者已经存在的都将受到这个规则的约束。如果futureonly_flag被设置为NULL，已经存在的使用了这个用户自定义类型的表列，将不受这个规则的约束。使用的语法为：sp_bindrulerule_name

,'table_name.column_name'或sp_bindrulerule_name,'user_defind_datatype'[,'futureonly_flag']1/14/2023801、将规则绑定到列【示例】创建和绑定规则USEcompanyCREATERULErule_employee_ageAS(@employee_age>=18AND@employee_age<=35)GOsp_bindrule'rule_employee_age','employee.age'1/14/2023812、将规则绑定到用户定义的数据类型【示例】假设存在名为rule_ssn的规则和名为ssn的用户定义数据类型，此示例将rule_ssn绑定到ssn。在CREATETABLE语句中，类型ssn的列继承rule_ssn规则。类型ssn的现有列也继承rule_ssn规则，除非为futureonly_flag指定了futureonly或者在ssn上直接绑定了规则。绑定到列的规则始终优先于绑定到数据类型的规则。USEmasterEXECsp_bindrule'rule_ssn','ssn'GO1/14/2023823、使用futureonly_flag【示例】将rule_ssn规则绑定到用户定义数据类型ssn。因为已指定futureonly，所以不影响类型ssn的现有列。USEmasterEXECsp_bindrule'rule_ssn','ssn','futureonly'GO4、使用分隔标识符【示例】显示在object_name中分隔标识符的使用USEmaste