第8章 数据完整性

第8章数据完整性1本章内容8.1数据完整性概述8.2使用规则实施数据完整性8.3使用默认值实施数据完整性8.4使用约束实施数据完整性28.1数据完整性概述数据完整性防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据完整性有4种类型：实体完整性(EntityIntegrity)、域完整性(DomainIntegrity)、参照完整性(ReferentialIntegrity)、用户定义的完整性(User-definedIntegrity)。在SQLServer中可以通过各种规则(Rule)、默认(Default)、约束(Constraint)和触发器(Trigger)等数据库对象来保证数据的完整性。38.2使用规则实施数据完整性8.2.1创建规则8.2.2查看和修改规则8.2.3规则的绑定与松绑8.2.4删除规则48.2.1创建规则规则(Rule)就是数据库中对存储在表的列或用户定义数据类型中的值的规定和限制。规则是单独存储的独立的数据库对象。规则和约束可以同时使用，表的列可以有一个规则及多个约束。规则与检查约束在功能上相似，但在使用上有所区别。检查约束是在CREATETABLE或ALTERTABLE语句中定义的，嵌入了被定义的表结构，即删除表的时候检查约束也就随之被删除。而规则需要用CREATERULE语句定义后才能使用，是独立于表之外的数据库对象，删除表并不能删除规则，需要用DROPRULE语句才能删除。相比之下，使用在CREATETABLE或ALTERTABLE语句中定义的检查约束是更标准的限制列值的方法，但检查约束不能直接作用于用户定义数据类型。51．用企业管理器创建规则8.2.1创建规则在企业管理器中选择数据库对象“规则”，单击右键从快捷菜单中选择“新建规则”选项，即会弹出如图所示的“规则属性”对话框。输入规则名称和表达式之后，单击“确定”按钮，即完成规则的创建。62．用CREATERULE语句创建规则8.2.1创建规则CREATERULE语句用于在当前数据库中创建规则，其语法格式如下：

CREATERULErule_nameAS

condition_expressioncondition_expression中的变量必须以@开头，且可以任意命名！78.2.1创建规则例8-2创建性别规则sex_rule。CREATERULEsex_ruleAS@sexin('男','女')例8-1创建雇佣日期规则hire_date_rule。

CREATERULEhire_date_rule

AS

@hire_date>='1980-01-01'and@hire_date<=getdate()

88.2.1创建规则例8-4创建字符规则my_character_rule。CREATERULEmy_character_ruleAS@valuelike'[a-z]%[0-9]'

例8-3创建评分规则grade_rule。

CREATERULEgrade_rule

AS

@valuebetween1and100

91．用企业管理器查看和修改规则在企业管理器的数据库对象中选择“规则”对象，即可从右边的任务板中看到规则的大部分信息，包括规则的名称、所有者、创建时间等。8.2.2查看和修改规则108.2.2查看和修改规则使用sp_helptext

系统存储过程可以查看规则的文本信息。例8-5查看规则hire_date_rule的文本信息EXECUTEsp_helptext

hire_date_rule运行结果如图所示2．用系统存储过程sp_helptext

查看规则118.2.3规则的绑定与松绑需要将规则与数据库表或用户定义对象联系起来，才能发生作用。联系的方法称为绑定，所谓绑定就是指定规则作用于哪个表的哪一列或哪个用户定义数据类型。表的一列或一个用户定义数据类型只能与一个规则相绑定，而一个规则可以绑定多对象，这正是规则的魅力所在。解除规则与对象的绑定称为松绑。128.2.3规则的绑定与松绑在企业管理器中，展开数据库（Sales）文件夹，鼠标单击“规则”选项，在右窗格中选择要进行绑定的规则（hire_date），单击鼠标右键，从快捷菜单中选择“属性”菜单项，打开“规则属性”对话框，如图8-4所示。图中的“绑定UDT(U)”按钮用于绑定规则到用户定义的数据类型，“绑定列(B)”按钮用于绑定规则到表的列。1．用企业管理器管理规则的绑定和松绑138.2.3规则的绑定与松绑在图8-4中单击“绑定UDT(U)”按钮，则出现“将绑定规则到用户定义的数据类型”对话框，如图8-5所示；148.2.3规则的绑定与松绑单击“绑定列(B)”按钮，则出现如图8-6所示的“将绑定规则到列”对话框。在“将规则绑定列”对话框的左边“未绑定的列”列表框中选择一列“添加”到右边“绑定列”列表框中，就实现规则绑定了。同样，去掉“将规则绑定到用户定义的数据类型”对话框的列表框的“绑定”列下的标识或删除“将规则绑定列”对话框的右边“绑定列”列表框的列，就实现了规则的松绑操作。158.2.3规则的绑定与松绑2．用系统存储过程sp_bindrule绑定规则系统存储过程sp_bindrule

可以绑定一个规则到表的一个列或一个用户定义数据类型上。其语法格式如下：sp_bindrule[@rulename=]'rule',[@objname=]'object_name'[,[@futureonly=]'futureonly']object_name的表示方法为：表名.字段名绑定到自定义数据类型上使用futureonly选项。16例8-7定义用户定义数据类型pat_char，将例8-4创建的规则my_character_rule绑定到pat_var上。EXECsp_addtypepat_char,'varchar(10)','NOTNULL'GOEXECsp_bindrule

my_character_rule,pat_char,'futureonly‘例8-6将例8-1创建的规则hire_date_rule绑定到employee表的hire_date列上。

EXECsp_bindrule

hire_date_rule,'employee.hire_date'17例8-8绑定例8-2创建的规则sex_rule

到employee表的字段sex。

EXECsp_bindrule

sex_rule,'employee.sex'188.2.3规则的绑定与松绑系统存储过程sp_unbindrule可解除规则与列或用户定义数据类型的绑定，其语法格式如下：sp_unbindrule[@objname=]'object_name'[,[@futureonly=]'futureonly']3．用系统存储过程sp_unbindrule

解除规则的绑定object_name的表示方法为：表名.字段名绑定到自定义数据类型上使用futureonly选项。198.2.3规则的绑定与松绑例8-9解除例8-6和例8-7绑定在employee表的hire_date列和用户定义数据类型pat_char上的规则。解除绑定在employee表的hire_date列的规则。EXECsp_unbindrule'employee.hire_date'解除绑定用户定义数据类型pat_char上的规则。EXECsp_unbindrule

pat_char,'futureonly'208.2.4删除规则使用DROPRULE语句删除当前数据库中的一个或多个规则。其语法格式如下：DROPRULE{rule_name}[,...n]注意：在删除一个规则前，必须先将与其绑定的对象解除绑定。例8-10删除例8-1和8-2中创建的规则。DROPRULEsex_rule,hire_date_rule

218.3.1创建默认值8.3.2查看默认值8.3.3默认值的绑定与松绑8.3.4删除默认值8.3使用默认值实施数据完整性228.3使用默认值实施数据完整性8.3.1创建默认值默认值(Default)是用户输入记录时往没有指定具体数据的列中自动插入的数据。默认值对象与CREATETABLE或ALTERTABLE语句操作表时用默认约束指定的默认值功能相似，两者的区别类似于规则与检查约束在使用上的区别。默认值对象可以用于多个列或用户定义数据类型。表的一列或一个用户定义数据类型只能与一个默认值相绑定。默认值的创建、查看、绑定、松绑和删除等操作可在企业管理器中进行，也可利用Transact-SQL语句进行。238.3.1创建默认值8.3.1创建默认值1．用企业管理器创建默认值在企业管理器中选择数据库对象的“默认值”对象，单击右键，从快捷菜单中选择“新建默认值”选项，打开“默认属性”对话框，如图8-7所示。输入默认值名称和值表达式之后，单击“确定”按钮，即完成默认值的创建。248.3.1创建默认值2．用CREATEDEFAULT语句创建默认值CREATEDEFAULT语句用于在当前数据库中创建默认值对象，其语法格式如下：CREATEDEFAULTdefault_nameASconstant_expression258.3.1创建默认值例8-11创建生日默认值birthday_defa。

CREATEDEFAULTbirthday_defaAS'1978-1-1'例8-12创建当前日期默认值today_defa。CREATEDEFAULTtoday_defaASgetdate()261．用企业管理器查看默认值在企业管理器中选择数据库对象的“默认值”对象，即可从右边的任务板中看到默认值的大部分信息，如图8-8所示。8.3.2查看默认值8.3使用默认值实施数据完整性278.3.2查看默认值选择要查看的默认值，单击右键，从快捷菜单中选择“属性”选项，就会出现图8-9所示的“默认属性”对话框，可以从中编辑默认值的值表达式。282．用系统存储过程sp_helptext

查看默认值使用sp_helptext系统存储过程可以查看默认值的细节。例8-13查看默认值today_defa。EXECsp_helptext

today_defa运行结果如下8.3.2查看默认值298.3.3默认值的绑定与松绑1．用企业管理器管理默认值的绑定和松绑在企业管理器中，选择要进行绑定设置的默认值，单击右键，从快捷菜单中选择“属性”选项，打开“默认属性”对话框，参见图8-9。图8-9中的“绑定UDT(U)”按钮用于将默认值绑定到用户定义数据类型，“绑定列(B)”按钮用于将默认值绑定到表的列。单击“绑定UDT(U)”按钮，则出现如图8-11所示的“将绑定默认值到用户定义的数据类型”对话框8.3使用默认值实施数据完整性308.3.3默认值的绑定与松绑单击“绑定列(B)”按钮，则出现如图8-12所示的“将绑定默认值到表的列”对话框。管理默认值与用户定义数据类型以及表的列之间的绑定和松绑与规则相同。318.3.3默认值的绑定与松绑2．用sp_bindefault

绑定默认值系统存储过程sp_bindefault

可以绑定一个默认值到表的一个列或一个用户定义数据类型上。其语法格式如下：sp_bindefault[@defname=]'default',[@objname=]'object_name'[,[@futureonly=]'futureonly']object_name的表示方法为：表名.字段名绑定到自定义数据类型上使用futureonly选项。328.3.3默认值的绑定与松绑例8-14绑定默认值today_defa

到employee表的hire_date列上。

EXECsp_bindefault

today_defa,'employee.hire_date'338.3.3默认值的绑定与松绑3．用sp_unbindefault

解除默认值的绑定系统存储过程sp_unbindefault

可以解除默认值与表的列或用户定义数据类型的绑定，其语法格式如下：

sp_unbindefault[@objname=]'object_name' [,[@futureonly=]'futureonly']object_name的表示方法为：表名.字段名绑定到自定义数据类型上使用futureonly选项。348.3.3默认值的绑定与松绑例8-15解除默认值today_defa与表employee的hire_date

列的绑定。

EXECsp_unbindefault'employee.hire_date'358.3使用默认值实施数据完整性8.3.4删除默认值可以在企业管理器中选择默认值，单击右键，从快捷菜单中选择“删除”选项删除默认值，也可以使用DROPDEFAULT语句删除当前数据库中的一个或多个默认值。其语法格式如下：DROPDEFAULT{default_name}[,...n]例8-16删除生日默认值birthday_defa。DROPDEFAULTbirthday_defa368.4.1主键约束8.4.2外键约束8.4.3惟一性约束8.4.4检查约束8.4.5默认约束8.4使用约束实施数据完整性378.4使用约束实施数据完整性约束(Constraint)是SQLServer提供的自动保持数据库完整性的一种机制，它定义了可输入表或表的单个列中的数据的限制条件。使用约束优先于使用触发器、规则和默认值。约束独立于表结构，作为数据库定义部分在CREATETABLE语句中声明，可以在不改变表结构的基础上，通过ALTERTABLE语句添加或删除。当表被删除时，表所带的所有约束定义也随之被删除。388.4.1主键约束在SQLServer中有6种约束主键约束外键约束惟一性约束检查约束默认约束非空值约束 其中非空值约束已在第5章中做了详细介绍，本节介绍其他5种约束。398.4.1主键约束表的一列或几列的组合的值在表中惟一地指定一行记录，这样的一列或多列称为表的主键（PrimaryKey，PK），通过它可强制表的实体完整性。主键不允许为空值，且不同两行的键值不能相同。表中可以有不止一个键惟一标识行，每个键都称为侯选键，只可以选一个侯选键作为表的主键，其他侯选键称作备用键。如果一个表的主键由单列组成，则该主键约束可以定义为该列的列约束。如果主键由两个以上的列组成，则该主键约束必须定义为表约束。8.4.1主键约束408.4.1主键约束定义列级主键约束的语法格式如下：[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED]定义表级主键约束的语法格式如下：[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(column_name[,…n])}418.4.1主键约束例8-17在Sales数据库中创建customer表，并声明主键约束。

CREATETABLESales.dbo.customer(customer_id

bigintNOTNULLIDENTITY(0,1)PRIMARYKEY,

customer_namevarchar(50)NOTNULL,

linkman_namechar(8),addressvarchar(50),telephonechar(12)NOTNULL)428.4.1主键约束非聚集主键约束主键约束默认为聚集的，若要定义customer_id列为非聚集主键约束，并指定约束名为PK_customer，使用以下语句：

customer_idchar(5)CONSTRAINTPK_customerPRIMARYKEYNONCLUSTERED438.4.1主键约束例8-18创建一个产品信息表goods1，将产品编号goods_id列声明为主键。

CREATETABLEgoods1(goods_idchar(6)NOTNULL,

goods_namevarchar(50)NOTNULL,

classification_idchar(6)NOTNULL,

unit_pricemoneyNOTNULL,

stock_quantityfloatNOTNULL,

order_quantityfloatNULL

CONSTRAINTpk_p_idPRIMARYKEY(goods_id))ON[PRIMARY]448.4.1主键约束例8-19根据商品销售的时间和商品类别来确定销售的商品的数量。

CREATETABLEg_order(good_type

int,

order_time

datetime,

order_num

int,

CONSTRAINTg_o_keyPRIMARYKEY(good_type,order_time))458.4使用约束实施数据完整性8.4.2外键约束外键约束定义了表与表之间的关系。通过将一个表中一列或多列添加到另一个表中，创建两个表之间的连接，这个列就成为第二个表的外键(ForeignKey，FK)，即外键是用于建立和加强两个表数据之间的连接的一列或多列，通过它可以强制参照完整性。468.4.2外键约束例如，Sales数据库中的employee、sell_order、goods这3个表之间存在以下逻辑联系：sell_order(销售订单)表中employee_id(员工编号)列的值必须是employee表employee_id列中的某一个值，因为签订销售订单的人必须是当前公司员工；而sell_order表中goods_id(货物编号)列的值必须是goods(货物)表的goods_id列中的某一个值，因为销售订单上售出的只能是已知货物。因此，在sell_order表上应建立两个外键约束FK_sell_order_employee和FK_sell_order_goods来限制sell_order表employee_id列和goods_id列的值必须分别来自employee表的employee_id列及goods表的goods_id列。478.4.2外键约束级联操作SQLServer提供了两种级联操作以保证数据完整性：(1)级联删除确定当主键表中某行被删除时，外键表中所有相关行将被删除。(2)级联修改确定当主键表中某行的键值被修改时，外键表中所有相关行的该外键值也将被自动修改为新值。488.4.2外键约束外键约束与主键约束相同，也分为表约束与列约束。定义列级外键约束的语法格式如下：[CONSTRAINTconstraint_name][FOREIGNKEY]REFERENCESref_table[(ref_column[,…n])][NOTFORREPLICATION]定义表级外键约束的语法格式如下：[CONSTRAINTconstraint_name]FOREIGNKEY(column_name[,…n])REFERENCESref_table[(ref_column[,…n])][ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}]][NOTFORREPLICATION]498.4.2外键约束例8-20创建一个订货表sell_order1，与例8-18创建的产品表goods1相关联。

CREATETABLEsell_order1(order_id1char(6)NOTNULL,

goods_idchar(6)NOTNULL,

employee_idchar(4)NOTNULL,

customer_idchar(4)NOTNULL,

transporter_idchar(4)NOTNULL,

order_numfloatNULL,discountfloatNULL,

order_date

datetimeNOTNULL,

send_date

datetimeNULL,

arrival_date

datetimeNULL,costmoneyNULL,

CONSTRAINTpk_order_idPRIMARYKEY(order_id1),

FOREIGNKEY(goods_id)REFERENCESgoods1(goods_id))508.4.2外键约束例8-21创建表sell_order2，并为goods_id、employee_id、custom_id三列定义外键约束。

CREATETABLEsell_order2(order_id1char(6)PRIMARYKEY,

goods_idchar(6)NOTNULL

CONSTRAINTFK_goods_idFOREIGNKEY(goods_id)REFERENCESGoods1(goods_id)ONDELETENOACTIONONUPDATECASCADE,

employee_idchar(4)NOTNULL

FOREIGNKEY(employee_id)REFERENCESemployee(employee_id)ONUPDATECASCADE,

customer_idchar(4)NOTNULL,

transporter_idchar(4)NOTNULL,

order_numfloat,discountfloat,

order_date

datetimeNOTNULL,

send_date

datetime,

arrival_date

datetime,costmoney,

CONSTRAINTFK_customer_idFOREIGNKEY(customer_id)REFERENCEScustomer(customer_id))518.4.2外键约束employee_id列的外键应当定义如下：CONSTRAINTFK_sell_order_employee

FOREIGNKEY(department_id,employee_id)REFERENCESemployee(department_id,employee_id)例如，如果employee表的主键是由department_id和employee_id组成的复合主键，则sell_order表中应增加相同类型的department_id列定义528.4使用约束实施数据完整性8.4.3惟一性约束惟一性(Unique)约束指定一个或多个列的组合的值具有惟一性，以防止在列中输入重复的值，为表中的一列或者多列提供实体完整性。惟一性约束指定的列可以有NULL属性。主键也强制执行惟一性，但主键不允许空值，故主键约束强度大于惟一约束。因此主键列不能再设定惟一性约束。538.4.3惟一性约束定义列级惟一性约束的语法格式如下：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED]惟一性约束应用于多列时的定义格式：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED](column_name[,…n])548.4.3惟一性约束例8-22创建goods2表，使goods_name具有惟一性约束。

CREATETABLEgoods2(goods_idchar(6)NOTNULL

PRIMARYKEY,

goods_namevarchar(50)NOTNULL

CONSTRAINTu_goods_nameUNIQUENONCLUSTERED,

classification_idchar(6)NOTNULL,

unit_pricemoneyNOTNULL,

stock_quantityfloatNOTNULL,

order_quantityfloat)558.4.3惟一性约束例8-23定义一个员工信息表employees，其中员工的身份证号emp_cardid列具有惟一性。

CREATETABLEemployees(emp_idchar(8),

emp_namechar(10),

emp_cardidchar(18),

CONSTRAINTpk_emp_idPRIMARYKEY(emp_id),

CONSTRAINTuk_emp_cardidUNIQUE(emp_cardid))568.4使用约束实施数据完整性8.4.4检查约束检查(Check)约束对输入列或整个表中的值设置检查条件，以限制输入值，保证数据库的数据完整性。当对具有检查约束列进行插入或修改时，SQLServer将用该检查约束的逻辑表达式对新值进行检查，只有满足条件(逻辑表达式返回TRUE)的值才能填入该列，否则报错。可以为每列指定多个CHECK约束。578.4.4检查约束定义检查约束的语法格式：[CONSTRAINTconstraint_name]CHECK[NOTFORREPLICATION](logical_expression)588.4.4检查约束例8-24更改表employee2以添加未验证检查约束。

ALTERTABLEemployee2WITHNOCHECKADDCONSTRAINTCK_AgeCHECK(DATEDIFF(year,Birth_Date,Hire_Date)>18)598.4.4检查约束例8-25创建一个订货表orders，保证各订单的订货量必须不小于10。

CREATETABLEorders(order_idchar(8),

p_idchar(8),

p_namechar(10),quantitysmallint,

CONSTRAINTchk_quantityCHECK(quantity>=10),

CONSTRAINTpk_orders_idPRIMARYKEY(order_id)

)60例8-26创建transporters表并定义检查约束CREATETABLEtransporters(transporter_idchar(4)NOTNULL,

transport_namevarchar(50),

linkman_namechar(8),addressvarchar(50),telephonechar(12)NOTNULL

CHECK(telephoneLIKE'0[1-9][0-9][0-9]-[1-9][0-9][0-9][0-9][0-9][0-9][0-9]'ORtelephoneLIKE'0[1-9][0-9]-[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))618.4使用约束实施数据完整性8.4.5默认约束默认(Default)约束通过定义列的默认值或使用数据库的默认值对象绑定表的列，以确保在没有为某列指定数据时，来指定列的值。默认值可以是常量，也可以是表达式，还可以为NULL值。628.4.5默认约束定义默认约束的语法格式[CONSTRAINTconstraint_name]DEFAULTconstant_expression[FORcolumn_name]638.4.5默认约束例8-27在Sales数据库中，为员工表employee的sex列添加默认约束，默认值是“男”。

ALTERTABLEemployeeADDCONSTRAINTsex_defaultDEFAULT'男'FORsex例8-28更改表employee为hire_date列定义默认约束。ALTERTABLEemployeeADDCONSTRAINThire_date_dfDEFAULT(getdate())FORhire_date648.4.5默认约束例8-29添加具有默认值的可为空的列ALTERTABLEemployeeADDhire_date

datetimeDEFAULT(getdate())WITHVALUES658.4.5默认约束例8-30使用默认约束。

--创建表purchase_orderCREATETABLEpurchase_o