第8 数据完整性

会计学1第8数据完整性

数据完整性概述数据完整性防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据完整性有4种类型：实体完整性(EntityIntegrity)、域完整性(DomainIntegrity)、参照完整性(ReferentialIntegrity)、用户定义的完整性(User-definedIntegrity)。在SQLServer中可以通过各种规则(Rule)、默认(Default)、约束(Constraint)和触发器(Trigger)等数据库对象来保证数据的完整性。第1页/共57页8.1使用规则实施数据完整性8.1.1创建规则8.1.2查看规则8.1.3规则的绑定与松绑8.1.4删除规则第2页/共57页8.1.1创建规则规则(Rule)就是数据库中对存储在表的列或用户定义数据类型中的值的规定和限制。规则是单独存储的独立的数据库对象。规则和约束可以同时使用，表的列可以有一个规则及多个约束。规则与检查约束在功能上相似，但在使用上有所区别。检查约束是在CREATETABLE或ALTERTABLE语句中定义的，嵌入了被定义的表结构，即删除表的时候检查约束也就随之被删除。而规则需要用CREATERULE语句定义后才能使用，是独立于表之外的数据库对象，删除表并不能删除规则，需要用DROPRULE语句才能删除。相比之下，使用在CREATETABLE或ALTERTABLE语句中定义的检查约束是更标准的限制列值的方法，但检查约束不能直接作用于用户定义数据类型。8.2使用规则实施数据完整性第3页/共57页2．用CREATERULE语句创建规则8.2.1创建规则CREATERULE语句用于在当前数据库中创建规则，其语法格式如下：

CREATERULErule_nameAScondition_expression第4页/共57页8.2.1创建规则CREATERULEhire_date_ruleAS@hire_date>='1980-01-01'and@hire_date<=getdate()例8-2创建性别规则sex_rule。CREATERULEsex_ruleAS@sexin('男','女')例8-1创建雇佣日期规则hire_date_rule。第5页/共57页8.2.1创建规则CREATERULEgrade_ruleAS@valuebetween1and100例8-4创建字符规则my_character_rule。Createrulemy_character_ruleAs@valuelike'[a-f]%[0-9]'例8-3创建评分规则grade_rule。第6页/共57页8.2.2查看和修改规则使用sp_helptext系统存储过程可以查看规则的文本信息。例8-5查看规则hire_date_rule的文本信息EXECUTEsp_helptexthire_date_rule运行结果如图所示8.1.2查看规则第7页/共57页8.1.3规则的绑定与松绑需要将规则与数据库表或用户定义对象联系起来，才能发生作用。联系的方法称为绑定，所谓绑定就是指定规则作用于哪个表的哪一列或哪个用户定义数据类型。表的一列或一个用户定义数据类型只能与一个规则相绑定，而一个规则可以绑定多对象，这正是规则的魅力所在。解除规则与对象的绑定称为松绑。第8页/共57页8.2.3规则的绑定与松绑1．用系统存储过程sp_bindrule绑定规则系统存储过程sp_bindrule可以绑定一个规则到表的一个列或一个用户定义数据类型上。其语法格式如下：sp_bindrule[@rulename=]'rule',[@objname=]'object_name'[,[@futureonly=]'futureonly']第9页/共57页8.2.3规则的绑定与松绑EXECsp_bindrulehire_date_rule,'employee.hire_date'

运行结果为：已将规则绑定到表的列上。例8-7定义用户定义数据类型pat_char，将例8-4创建的规则my_character_rule绑定到pat_var上。EXECsp_addtypepat_char,'varchar(10)','NOTNULL'GOEXECsp_bindrulemy_character_rule,pat_char,'futureonly‘

运行结果如下：已将规则绑定到数据类型。例8-6将例8-1创建的规则hire_date_rule绑定到employee表的hire_date列上。第10页/共57页8.2.3规则的绑定与松绑EXECsp_bindrulesex_rule,'employee.sex'运行结果如下：已将规则绑定到表的列上。例8-8绑定例8-2创建的规则sex_rule到employee表的字段sex。第11页/共57页8.2.3规则的绑定与松绑系统存储过程sp_unbindrule可解除规则与列或用户定义数据类型的绑定，其语法格式如下：sp_unbindrule[@objname=]'object_name'[,[@futureonly=]'futureonly']2．用系统存储过程sp_unbindrule解除规则的绑定第12页/共57页8.2.3规则的绑定与松绑例8-9解除例8-6和例8-7绑定在employee表的hire_date列和用户定义数据类型pat_char上的规则。EXECsp_unbindrule'employee.hire_date'运行结果如下：已解除了表列与规则之间的绑定。EXECsp_unbindrulepat_char,'futureonly'运行结果如下：已解除了数据类型与规则之间的绑定。第13页/共57页8.1.4删除规则使用DROPRULE语句删除当前数据库中的一个或多个规则。其语法格式如下：DROPRULE{rule_name}[,...n]注意：在删除一个规则前，必须先将与其绑定的对象解除绑定。例8-10删除例8-1和8-2中创建的规则。DROPRULEsex_rule,hire_date_rule8.2使用规则实施数据完整性第14页/共57页8.2.1创建默认值8.2.2查看默认值8.2.3默认值的绑定与松绑8.2.4删除默认值8.2使用默认值实施数据完整性第15页/共57页8.3使用默认值实施数据完整性8.2.1创建默认值默认值(Default)是用户输入记录时往没有指定具体数据的列中自动插入的数据。默认值对象与CREATETABLE或ALTERTABLE语句操作表时用默认约束指定的默认值功能相似，两者的区别类似于规则与检查约束在使用上的区别。默认值对象可以用于多个列或用户定义数据类型。表的一列或一个用户定义数据类型只能与一个默认值相绑定。默认值的创建、查看、绑定、松绑和删除等操作可在企业管理器中进行，也可利用Transact-SQL语句进行。第16页/共57页8.3.1创建默认值1.用CREATEDEFAULT语句创建默认值CREATEDEFAULT语句用于在当前数据库中创建默认值对象，其语法格式如下：CREATEDEFAULTdefault_nameASconstant_expression第17页/共57页8.3.1创建默认值CREATEDEFAULTbirthday_defaAS'1978-1-1'例8-12创建当前日期默认值today_defa。CREATEDEFAULTtoday_defaASgetdate()例8-11创建生日默认值birthday_defa。第18页/共57页用系统存储过程sp_helptext查看默认值使用sp_helptext系统存储过程可以查看默认值的细节。例8-13查看默认值today_defa。EXECsp_helptexttoday_defa运行结果如图8-10所示。8.3.2查看默认值8.2.2查看默认值第19页/共57页8.3.3默认值的绑定与松绑1．用sp_bindefault绑定默认值系统存储过程sp_bindefault可以绑定一个默认值到表的一个列或一个用户定义数据类型上。其语法格式如下：sp_bindefault[@defname=]'default',[@objname=]'object_name'[,[@futureonly=]'futureonly']8.2.3默认值的绑定与松绑第20页/共57页8.3.3默认值的绑定与松绑例8-14绑定默认值today_defa到employee表的hire_date列上。 EXECsp_bindefaulttoday_defa,'employee.hire_date'运行结果如下：已将默认值绑定到列。第21页/共57页8.3.3默认值的绑定与松绑2．用sp_unbindefault解除默认值的绑定系统存储过程sp_unbindefault可以解除默认值与表的列或用户定义数据类型的绑定，其语法格式如下：

sp_unbindefault[@objname=]'object_name' [,[@futureonly=]'futureonly']第22页/共57页8.3.3默认值的绑定与松绑例8-15解除默认值today_defa与表employee的hire_date列的绑定。EXECsp_unbindefault'employee.hire_date'运行结果如下：已解除了表列与其默认值之间的绑定。第23页/共57页8.3使用默认值实施数据完整性8.2.4删除默认值可以在SQLServer管理平台中选择默认值，单击右键，从快捷菜单中选择“删除”选项删除默认值，也可以使用DROPDEFAULT语句删除当前数据库中的一个或多个默认值。其语法格式如下：DROPDEFAULT{default_name}[,...n]例8-16删除生日默认值birthday_defa。DROPDEFAULTbirthday_defa第24页/共57页8.3.1主键约束8.3.2外键约束8.3.3惟一性约束8.3.4检查约束8.3.5默认约束8.3使用约束实施数据完整性第25页/共57页8.3.1主键约束约束(Constraint)是SQLServer提供的自动保持数据库完整性的一种机制，它定义了可输入表或表的单个列中的数据的限制条件。使用约束优先于使用触发器、规则和默认值。约束独立于表结构，作为数据库定义部分在CREATETABLE语句中声明，可以在不改变表结构的基础上，通过ALTERTABLE语句添加或删除。当表被删除时，表所带的所有约束定义也随之被删除。第26页/共57页8.4.1主键约束在SQLServer中有6种约束主键约束外键约束惟一性约束检查约束默认约束非空值约束 其中非空值约束已在第5章中做了详细介绍，本节介绍其他5种约束。第27页/共57页8.3.1主键约束表的一列或几列的组合的值在表中惟一地指定一行记录，这样的一列或多列称为表的主键（PrimaryKey，PK），通过它可强制表的实体完整性。主键不允许为空值，且不同两行的键值不能相同。表中可以有不止一个键惟一标识行，每个键都称为侯选键，只可以选一个侯选键作为表的主键，其他侯选键称作备用键。如果一个表的主键由单列组成，则该主键约束可以定义为该列的列约束。如果主键由两个以上的列组成，则该主键约束必须定义为表约束。第28页/共57页8.4.1主键约束定义列级主键约束的语法格式如下：[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED]定义表级主键约束的语法格式如下：[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(column_name[,…n])}第29页/共57页8.4.1主键约束例8-17在Sales数据库中创建customer表，并声明主键约束。CREATETABLESales.dbo.customer(customer_idbigintNOTNULLIDENTITY(0,1)PRIMARYKEY,customer_namevarchar(50)NOTNULL,linkman_namechar(8),addressvarchar(50),telephonechar(12)NOTNULL)第30页/共57页8.4.1主键约束非聚集主键约束若要定义customer_id列为非聚集主键约束，并指定约束名为PK_customer，使用以下语句：

customer_idchar(5)CONSTRAINTPK_customerPRIMARYKEYNONCLUSTERED第31页/共57页8.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_quantityfloatNULLCONSTRAINTpk_p_idPRIMARYKEY(goods_id))ON[PRIMARY]第32页/共57页8.4.1主键约束例8-19根据商品销售的时间和商品类别来确定销售的商品的数量。CREATETABLEg_order(good_typeint,order_timedatetime,order_numint,CONSTRAINTg_o_keyPRIMARYKEY(good_type,order_time))第33页/共57页8.4使用约束实施数据完整性8.3.2外键约束外键约束定义了表与表之间的关系。通过将一个表中一列或多列添加到另一个表中，创建两个表之间的连接，这个列就成为第二个表的外键(ForeignKey，FK)，即外键是用于建立和加强两个表数据之间的连接的一列或多列，通过它可以强制参照完整性。第34页/共57页8.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列。第35页/共57页8.4.2外键约束级联操作SQLServer提供了两种级联操作以保证数据完整性：(1)级联删除确定当主键表中某行被删除时，外键表中所有相关行将被删除。(2)级联修改确定当主键表中某行的键值被修改时，外键表中所有相关行的该外键值也将被自动修改为新值。第36页/共57页8.4.2外键约束外键约束与主键约束相同，也分为表约束与列约束。定义表级外键约束的语法格式如下：[CONSTRAINTconstraint_name]FOREIGNKEY(column_name[,…n])REFERENCESref_table[(ref_column[,…n])][ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}]][NOTFORREPLICATION]定义列级外键约束的语法格式如下：[CONSTRAINTconstraint_name][FOREIGNKEY]REFERENCESref_table[NOTFORREPLICATION]第37页/共57页8.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_datedatetimeNOTNULL,send_datedatetimeNULL,arrival_datedatetimeNULL,costmoneyNULL,CONSTRAINTpk_order_idPRIMARYKEY(order_id1),FOREIGNKEY(goods_id)REFERENCESgoods1(goods_id))第38页/共57页8.4.2外键约束CREATETABLEsell_order2(order_id1char(6)PRIMARYKEY,goods_idchar(6)NOTNULLCONSTRAINTFK_goods_idFOREIGNKEY(goods_id)REFERENCESGoods1(goods_id)ONDELETENOACTIONONUPDATECASCADE,employee_idchar(4)NOTNULLFOREIGNKEY(employee_id)REFERENCESemployee(employee_id)ONUPDATECASCADE,customer_idchar(4)NOTNULL,transporter_idchar(4)NOTNULL,order_numfloat,discountfloat,order_datedatetimeNOTNULL,send_datedatetime,arrival_datedatetime,costmoney,CONSTRAINTFK_customer_idFOREIGNKEY(customer_id)REFERENCEScustomer(customer_id))例8-21创建表sell_order2，并为goods_id、employee_id、custom_id三列定义外键约束。第39页/共57页8.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列定义第40页/共57页8.4使用约束实施数据完整性8.3.3惟一性约束惟一性(Unique)约束指定一个或多个列的组合的值具有惟一性，以防止在列中输入重复的值，为表中的一列或者多列提供实体完整性。惟一性约束指定的列可以有NULL属性。主键也强制执行惟一性，但主键不允许空值，故主键约束强度大于惟一约束。因此主键列不能再设定惟一性约束。第41页/共57页8.4.3惟一性约束定义列级惟一性约束的语法格式如下：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED]惟一性约束应用于多列时的定义格式：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED](column_name[,…n])第42页/共57页8.4.3惟一性约束例8-22创建goods2表，使goods_name具有惟一性约束。CREATETABLEgoods2(goods_idchar(6)NOTNULLPRIMARYKEY,goods_namevarchar(50)NOTNULLCONSTRAINTu_goods_nameUNIQUENONCLUSTERED,classification_idchar(6)NOTNULL,unit_pricemoneyNOTNULL,stock_quantityfloatNOTNULL,order_quantityfloat)第43页/共57页8.4.3惟一性约束CREATETABLEemployees(emp_idchar(8),emp_namechar(10),emp_cardidchar(18),CONSTRAINTpk_emp_idPRIMARYKEY(emp_id),CONSTRAINTuk_emp_cardidUNIQUE(emp_cardid))例8-23定义一个员工信息表employees，其中员工的身份证号emp_cardid列具有惟一性。第44页/共57页8.4使用约束实施数据完整性8.3.4检查约束检查(Check)约束对输入列或整个表中的值设置检查条件，以限制输入值，保证数据库的数据完整性。当对具有检查约束列进行插入或修改时，SQLServer将用该检查约束的逻辑表达式对新值进行检查，只有满足条件(逻辑表达式返回TRUE)的值才能填入该列，否则报错。可以为每列指定多个CHECK约束。第45页/共57页8.4.4检查约束定义检查约束的语法格式：[CONSTRAINTconstraint_name]CHECK[NOTFORREPLICATION](logical_expression)第46页/共57页8.4.4检查约束例8-24更改表employee2以添加未验证检查约束。ALTERTABLEemployee2WITHNOCHECKADDCONSTRAINTCK_AgeCHECK(DATEDIFF(year,Birth_Date,Hire_Date)>18)第47页/共57页8.4.4检查约束例8-25创建一个订货表orders，保证各订单的订货量必须不小于10。CREATETABLEorders(order_idchar(8),p_idchar(8),p_namechar(10),quantitysmallintCONSTRAINTchk_quantityCHECK(quantity>=10),CONSTRAINTpk_orders_idPRIMARYKEY(order_id))第48页/共57页8.4.4检查约束CREATETABLEtransporters(transporter_idchar(4)NOTNULL,transport_namevarchar(50),linkman_namechar(8),addressvarchar(50),telephonechar(12)NOTNULLCHECK(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]'))例8-26创建transporters表并定义检查约束第49页/共57页8.4使用约束实施数据完整性8.3.5默认约束默认(Default)约束通过定义列的默认值或使用数据库的默认值对象绑定表的列，以确保在没有为某列指定数据时，来指定列的值。默认值可以是常量，也可以是表达式，还可以为NULL值。第50页/共57页8.4.5默认约束定义默认约束的语法格式[CONSTRAINTconstraint_name]DEFAULTconstant_expression[FORcolumn_name]第51页/共57页8.4.5默认约束例8-27在Sales数据库中，为员工表employee的sex列添加默认约束，默认值是“男”。ALTERTABLEemployeeADDCONSTRAINTsex_defaultDEFAULT'男'FORsex例8-28更改表employee为hire_date列定义默认约束。ALTERTABLEemployeeADDCONSTRAINThire_date_dfDEFAULT(getdate())FORhire_date第52页/共57页8.4.5默认约束例8-29添加具有默认值的可为空的列ALTERTABLEemployeeADDhire_datedatetimeDEFAULT(getdate())WITHVALUES第53页/共57页8.4.5默认约束例8-30使用默认约束。--创建表purchase_orderCREATETABLEpurchase_order(order_id2char(6)NOTNULL,goods_idchar(6)NOTNULL,employee_idchar(4)NOTNULL,supplier_idchar(5)