第6章-数据完整性

会计学1第6章---数据完整性6.1数据完整性概述数据完整性有4种类型：实体完整性(EntityIntegrity)、域完整性(DomainIntegrity)、参照完整性(ReferentialIntegrity)、用户定义的完整性(User-definedIntegrity)。在SQLServer中可以通过规则(Rule)、默认(Default)、约束(Constraint)和触发器(Trigger)等数据库对象来保证数据的完整性。第1页/共42页6.2使用规则实施数据完整性6.2.1创建规则6.2.2查看和修改规则6.2.3规则的绑定与松绑6.2.4删除规则第2页/共42页8.2.1创建规则例6-1创建雇佣日期规则hire_date_rule。

CREATERULEhire_date_rule

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

例6-2创建性别规则sex_rule。

CREATERULEsex_rule

AS@sexin('男','女')

6.2.1创建规则1．用企业管理器创建规则

2．用CREATERULE语句创建规则第3页/共42页例6-3创建评分规则grade_ruleCREATERULEgrade_ruleAS@valuebetween1and100

例6-4创建字符规则my_character_rule。Createrulemy_character_ruleAs@valuelike'[a-z]%[0-9]'第4页/共42页1．用企业管理器查看和修改规则2．用系统存储过程sp_helptext查看规则6.2.2查看和修改规则例6-5查看规则hire_date_rule的文本信息EXECUTEsp_helptexthire_date_rule第5页/共42页6.2.3规则的绑定与松绑所谓绑定就是指定规则作用于哪个表的哪一列或哪个用户定义数据类型。表的一列或一个用户定义数据类型只能与一个规则相绑定，而一个规则可以绑定多对象。解除规则与对象的绑定称为松绑。1．用企业管理器管理规则的绑定和松绑第6页/共42页8.2.3规则的绑定与松绑例6-6将规则hire_date_rule绑定到employee表的hire_date列上。EXECsp_bindrulehire_date_rule,'employee.hire_date'

例6-7定义用户定义数据类型pat_char，将规则my_character_rule绑定到pat_var上。EXECsp_addtypepat_char,'varchar(10)','NOTNULL'GOEXECsp_bindrulemy_character_rule,pat_char,'futureonly‘2．用系统存储过程sp_bindrule绑定规则第7页/共42页8.2.3规则的绑定与松绑例6-9解除绑定在employee表的hire_date列和用户定义数据类型pat_char上的规则。

EXECsp_unbindrule'employee.hire_date'

3．用系统存储过程sp_unbindrule解除规则的绑定第8页/共42页6.2.4删除规则使用DROPRULE语句删除当前数据库中的一个或多个规则。注意：在删除一个规则前，必须先将与其绑定的对象解除绑定。例6-10删除例6-1和6-2中创建的规则。DROPRULEsex_rule,hire_date_rule

第9页/共42页6.3.1创建默认值6.3.2查看默认值6.3.3默认值的绑定与松绑6.3.4删除默认值6.3使用默认值实施数据完整性第10页/共42页8.3.1创建默认值例6-11创建生日默认值birthday_defa。CREATEDEFAULTbirthday_defaAS'1978-1-1'

例6-12创建当前日期默认值today_defa。CREATEDEFAULTtoday_defaASgetdate()1．用企业管理器创建默认值

2．用CREATEDEFAULT语句创建默认值6.3.1创建默认值第11页/共42页1．用企业管理器查看默认值

2．用系统存储过程sp_helptext查看默认值例8-13查看默认值today_defa。EXECsp_helptexttoday_defa6.3.2查看默认值第12页/共42页8.3.3默认值的绑定与松绑例6-14绑定默认值today_defa到employee表的hire_date列上。EXECsp_bindefaulttoday_defa,'employee.hire_date'6.3.3默认值的绑定与松绑1．用企业管理器管理默认值的绑定和松绑2．用sp_bindefault绑定默认值第13页/共42页8.3.3默认值的绑定与松绑例6-15解除默认值today_defa与表employee的hire_date列的绑定。EXECsp_unbindefault'employee.hire_date'3．用sp_unbindefault解除默认值的绑定第14页/共42页8.3使用默认值实施数据完整性6.3.4删除默认值使用DROPDEFAULT语句删除当前数据库中的默认值。

例8-16删除生日默认值birthday_defa。DROPDEFAULTbirthday_defa第15页/共42页6.4.1主键约束6.4.2外键约束6.4.3惟一性约束6.4.4检查约束6.4.5默认约束6.4使用约束实施数据完整性第16页/共42页8.4.1主键约束在SQLServer中有6种约束主键约束外键约束惟一性约束检查约束默认约束非空值约束 其中非空值约束已在第5章中做了详细介绍，本节介绍其他5种约束。第17页/共42页8.4.1主键约束表的一列或几列的组合的值在表中惟一地指定一行记录，这样的一列或多列称为表的主键（PrimaryKey，PK），通过它可强制表的实体完整性。主键不允许为空值，且键值不能相同。表中可以有不止一个键惟一标识行，每个键都称为侯选键，只可以选一个侯选键作为表的主键，其他侯选键称作备用键。如果一个表的主键由单列组成，则该主键约束可以定义为该列的列约束。如果主键由两个以上的列组成，则该主键约束必须定义为表约束。6.4.1主键约束第18页/共42页8.4.1主键约束定义列级主键约束的语法格式如下：[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED]定义表级主键约束的语法格式如下：[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(column_name[,…n])}第19页/共42页8.4.1主键约束例6-17在Sales数据库中创建customer表，并声明主键约束。CREATETABLESales.dbo.customer(customer_idbigintNOTNULLIDENTITY(0,1)PRIMARYKEY,customer_namevarchar(50)NOTNULL,linkman_namechar(8),addressvarchar(50),telephonechar(12)NOTNULL)第20页/共42页8.4.1主键约束例6-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]第21页/共42页8.4.1主键约束例6-19用商品销售的时间和商品类别的组合作为主键。CREATETABLEg_order(good_typeint,order_timedatetime,order_numint,CONSTRAINTg_o_keyPRIMARYKEY(good_type,order_time))第22页/共42页8.4使用约束实施数据完整性6.4.2外键约束外键约束定义了表与表之间的关系。通过外键(ForeignKey，FK)可以强制参照完整性。第23页/共42页8.4.2外键约束级联操作SQLServer提供了两种级联操作以保证数据完整性：(1)级联删除确定当主键表中某行被删除时，外键表中所有相关行将被删除。(2)级联修改确定当主键表中某行的键值被修改时，外键表中所有相关行的该外键值也将被自动修改为新值。第24页/共42页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]第25页/共42页8.4.2外键约束例6-20创建一个订货表sell_order1，与例6-18创建的产品表goods1相关联。CREATETABLEsell_order1(order_id1char(6)NOTNULL,goods_idchar(6)NOTNULL,employee_idchar(4)NOTNULL,customer_idchar(4)NOTNULL,order_numfloatNULL,order_datedatetimeNOTNULL,costmoneyNULL,CONSTRAINTpk_order_idPRIMARYKEY(order_id1),FOREIGNKEY(goods_id)REFERENCESgoods1(goods_id))第26页/共42页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,arrival_datedatetime,CONSTRAINTFK_customer_idFOREIGNKEY(customer_id)REFERENCEScustomer(customer_id))例6-21创建表sell_order2，并为goods_id、employee_id、custom_id三列定义外键约束。第27页/共42页8.4使用约束实施数据完整性6.4.3惟一性约束惟一性(Unique)约束指定一个或多个列的组合的值具有惟一性，以防止在列中输入重复的值。惟一性约束指定的列可以有NULL属性。第28页/共42页8.4.3惟一性约束定义列级惟一性约束的语法格式如下：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED]惟一性约束应用于多列时的定义格式：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED](column_name[,…n])第29页/共42页8.4.3惟一性约束例6-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)第30页/共42页8.4.3惟一性约束CREATETABLEemployees(emp_idchar(8),emp_namechar(10),emp_cardidchar(18),CONSTRAINTpk_emp_idPRIMARYKEY(emp_id),CONSTRAINTuk_emp_cardidUNIQUE(emp_cardid))例6-23定义一个员工信息表employees，其中员工的身份证号emp_cardid列具有惟一性。第31页/共42页8.4使用约束实施数据完整性6.4.4检查约束检查(Check)约束对输入列或整个表中的值设置检查条件，以限制输入值，保证数据库的数据完整性。当对具有检查约束列进行插入或修改时，SQLServer将用该检查约束的逻辑表达式对新值进行检查，只有满足条件(逻辑表达式返回TRUE)的值才能填入该列，否则报错。可以为每列指定多个CHECK约束。第32页/共42页8.4.4检查约束定义检查约束的语法格式：[CONSTRAINTconstraint_name]CHECK[NOTFORREPLICATION](logical_expression)第33页/共42页8.4.4检查约束例6-24更改表employee2以添加未验证检查约束。ALTERTABLEemployee2WITHNOCHECKADDCONSTRAINTCK_AgeCHECK(DATEDIFF(year,Birth_Date,Hire_Date)>18)第34页/共42页8.4.4检查约束例6-25创建一个订货表orders，保证各订单的订货量必须不小于10。CREATETABLEorders(order_idchar(8),p_idchar(8),p_namechar(10),quantitysmallintCONSTRAINTchk_quantityCHECK(quantity>=10),CONSTRAINTpk_orders_idPRIMARYKEY(order_id))第35页/共42页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]'))例6-26创建transporters表并定义检查约束第36页/共42页8.4使用约束实施数据完整性第37页/共42页8.4.5默认约束定义默认约束的语法格式[CONSTRAINTconstraint_name]DEFAULTconstant_expression[FO