第6章-數(shù)據(jù)完整性

會(huì)計(jì)學(xué)1第6章---數(shù)據(jù)完整性6.1數(shù)據(jù)完整性概述數(shù)據(jù)完整性有4種類型：實(shí)體完整性(EntityIntegrity)、域完整性(DomainIntegrity)、參照完整性(ReferentialIntegrity)、用戶定義的完整性(User-definedIntegrity)。在SQLServer中可以通過規(guī)則(Rule)、默認(rèn)(Default)、約束(Constraint)和觸發(fā)器(Trigger)等數(shù)據(jù)庫對象來保證數(shù)據(jù)的完整性。第1頁/共42頁6.2使用規(guī)則實(shí)施數(shù)據(jù)完整性6.2.1創(chuàng)建規(guī)則6.2.2查看和修改規(guī)則6.2.3規(guī)則的綁定與松綁6.2.4刪除規(guī)則第2頁/共42頁8.2.1創(chuàng)建規(guī)則例6-1創(chuàng)建雇傭日期規(guī)則hire_date_rule。

CREATERULEhire_date_rule

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

例6-2創(chuàng)建性別規(guī)則sex_rule。

CREATERULEsex_rule

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

6.2.1創(chuàng)建規(guī)則1．用企業(yè)管理器創(chuàng)建規(guī)則

2．用CREATERULE語句創(chuàng)建規(guī)則第3頁/共42頁例6-3創(chuàng)建評分規(guī)則grade_ruleCREATERULEgrade_ruleAS@valuebetween1and100

例6-4創(chuàng)建字符規(guī)則my_character_rule。Createrulemy_character_ruleAs@valuelike'[a-z]%[0-9]'第4頁/共42頁1．用企業(yè)管理器查看和修改規(guī)則2．用系統(tǒng)存儲(chǔ)過程sp_helptext查看規(guī)則6.2.2查看和修改規(guī)則例6-5查看規(guī)則hire_date_rule的文本信息EXECUTEsp_helptexthire_date_rule第5頁/共42頁6.2.3規(guī)則的綁定與松綁所謂綁定就是指定規(guī)則作用于哪個(gè)表的哪一列或哪個(gè)用戶定義數(shù)據(jù)類型。表的一列或一個(gè)用戶定義數(shù)據(jù)類型只能與一個(gè)規(guī)則相綁定，而一個(gè)規(guī)則可以綁定多對象。解除規(guī)則與對象的綁定稱為松綁。1．用企業(yè)管理器管理規(guī)則的綁定和松綁第6頁/共42頁8.2.3規(guī)則的綁定與松綁例6-6將規(guī)則hire_date_rule綁定到employee表的hire_date列上。EXECsp_bindrulehire_date_rule,'employee.hire_date'

例6-7定義用戶定義數(shù)據(jù)類型pat_char，將規(guī)則my_character_rule綁定到pat_var上。EXECsp_addtypepat_char,'varchar(10)','NOTNULL'GOEXECsp_bindrulemy_character_rule,pat_char,'futureonly‘2．用系統(tǒng)存儲(chǔ)過程sp_bindrule綁定規(guī)則第7頁/共42頁8.2.3規(guī)則的綁定與松綁例6-9解除綁定在employee表的hire_date列和用戶定義數(shù)據(jù)類型pat_char上的規(guī)則。

EXECsp_unbindrule'employee.hire_date'

3．用系統(tǒng)存儲(chǔ)過程sp_unbindrule解除規(guī)則的綁定第8頁/共42頁6.2.4刪除規(guī)則使用DROPRULE語句刪除當(dāng)前數(shù)據(jù)庫中的一個(gè)或多個(gè)規(guī)則。注意：在刪除一個(gè)規(guī)則前，必須先將與其綁定的對象解除綁定。例6-10刪除例6-1和6-2中創(chuàng)建的規(guī)則。DROPRULEsex_rule,hire_date_rule

第9頁/共42頁6.3.1創(chuàng)建默認(rèn)值6.3.2查看默認(rèn)值6.3.3默認(rèn)值的綁定與松綁6.3.4刪除默認(rèn)值6.3使用默認(rèn)值實(shí)施數(shù)據(jù)完整性第10頁/共42頁8.3.1創(chuàng)建默認(rèn)值例6-11創(chuàng)建生日默認(rèn)值birthday_defa。CREATEDEFAULTbirthday_defaAS'1978-1-1'

例6-12創(chuàng)建當(dāng)前日期默認(rèn)值today_defa。CREATEDEFAULTtoday_defaASgetdate()1．用企業(yè)管理器創(chuàng)建默認(rèn)值

2．用CREATEDEFAULT語句創(chuàng)建默認(rèn)值6.3.1創(chuàng)建默認(rèn)值第11頁/共42頁1．用企業(yè)管理器查看默認(rèn)值

2．用系統(tǒng)存儲(chǔ)過程sp_helptext查看默認(rèn)值例8-13查看默認(rèn)值today_defa。EXECsp_helptexttoday_defa6.3.2查看默認(rèn)值第12頁/共42頁8.3.3默認(rèn)值的綁定與松綁例6-14綁定默認(rèn)值today_defa到employee表的hire_date列上。EXECsp_bindefaulttoday_defa,'employee.hire_date'6.3.3默認(rèn)值的綁定與松綁1．用企業(yè)管理器管理默認(rèn)值的綁定和松綁2．用sp_bindefault綁定默認(rèn)值第13頁/共42頁8.3.3默認(rèn)值的綁定與松綁例6-15解除默認(rèn)值today_defa與表employee的hire_date列的綁定。EXECsp_unbindefault'employee.hire_date'3．用sp_unbindefault解除默認(rèn)值的綁定第14頁/共42頁8.3使用默認(rèn)值實(shí)施數(shù)據(jù)完整性6.3.4刪除默認(rèn)值使用DROPDEFAULT語句刪除當(dāng)前數(shù)據(jù)庫中的默認(rèn)值。

例8-16刪除生日默認(rèn)值birthday_defa。DROPDEFAULTbirthday_defa第15頁/共42頁6.4.1主鍵約束6.4.2外鍵約束6.4.3惟一性約束6.4.4檢查約束6.4.5默認(rèn)約束6.4使用約束實(shí)施數(shù)據(jù)完整性第16頁/共42頁8.4.1主鍵約束在SQLServer中有6種約束主鍵約束外鍵約束惟一性約束檢查約束默認(rèn)約束非空值約束 其中非空值約束已在第5章中做了詳細(xì)介紹，本節(jié)介紹其他5種約束。第17頁/共42頁8.4.1主鍵約束表的一列或幾列的組合的值在表中惟一地指定一行記錄，這樣的一列或多列稱為表的主鍵（PrimaryKey，PK），通過它可強(qiáng)制表的實(shí)體完整性。主鍵不允許為空值，且鍵值不能相同。表中可以有不止一個(gè)鍵惟一標(biāo)識(shí)行，每個(gè)鍵都稱為侯選鍵，只可以選一個(gè)侯選鍵作為表的主鍵，其他侯選鍵稱作備用鍵。如果一個(gè)表的主鍵由單列組成，則該主鍵約束可以定義為該列的列約束。如果主鍵由兩個(gè)以上的列組成，則該主鍵約束必須定義為表約束。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數(shù)據(jù)庫中創(chuàng)建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創(chuàng)建一個(gè)產(chǎn)品信息表goods1，將產(chǎn)品編號(hào)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用商品銷售的時(shí)間和商品類別的組合作為主鍵。CREATETABLEg_order(good_typeint,order_timedatetime,order_numint,CONSTRAINTg_o_keyPRIMARYKEY(good_type,order_time))第22頁/共42頁8.4使用約束實(shí)施數(shù)據(jù)完整性6.4.2外鍵約束外鍵約束定義了表與表之間的關(guān)系。通過外鍵(ForeignKey，F(xiàn)K)可以強(qiáng)制參照完整性。第23頁/共42頁8.4.2外鍵約束級聯(lián)操作SQLServer提供了兩種級聯(lián)操作以保證數(shù)據(jù)完整性：(1)級聯(lián)刪除確定當(dāng)主鍵表中某行被刪除時(shí)，外鍵表中所有相關(guān)行將被刪除。(2)級聯(lián)修改確定當(dāng)主鍵表中某行的鍵值被修改時(shí)，外鍵表中所有相關(guān)行的該外鍵值也將被自動(dòng)修改為新值。第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創(chuàng)建一個(gè)訂貨表sell_order1，與例6-18創(chuàng)建的產(chǎn)品表goods1相關(guān)聯(lián)。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創(chuàng)建表sell_order2，并為goods_id、employee_id、custom_id三列定義外鍵約束。第27頁/共42頁8.4使用約束實(shí)施數(shù)據(jù)完整性6.4.3惟一性約束惟一性(Unique)約束指定一個(gè)或多個(gè)列的組合的值具有惟一性，以防止在列中輸入重復(fù)的值。惟一性約束指定的列可以有NULL屬性。第28頁/共42頁8.4.3惟一性約束定義列級惟一性約束的語法格式如下：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED]惟一性約束應(yīng)用于多列時(shí)的定義格式：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED](column_name[,…n])第29頁/共42頁8.4.3惟一性約束例6-22創(chuàng)建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定義一個(gè)員工信息表employees，其中員工的身份證號(hào)emp_cardid列具有惟一性。第31頁/共42頁8.4使用約束實(shí)施數(shù)據(jù)完整性6.4.4檢查約束檢查(Check)約束對輸入列或整個(gè)表中的值設(shè)置檢查條件，以限制輸入值，保證數(shù)據(jù)庫的數(shù)據(jù)完整性。當(dāng)對具有檢查約束列進(jìn)行插入或修改時(shí)，SQLServer將用該檢查約束的邏輯表達(dá)式對新值進(jìn)行檢查，只有滿足條件(邏輯表達(dá)式返回TRUE)的值才能填入該列，否則報(bào)錯(cuò)?？梢詾槊苛兄付ǘ鄠€(gè)CHECK約束。第32頁/共42頁8.4.4檢查約束定義檢查約束的語法格式：[CONSTRAINTconstraint_name]CHECK[NOTFORREPLICATION](logical_expression)第33頁/共42頁8.4.4檢查約束例6-24更改表employee2以添加未驗(yàn)證檢查約束。ALTERTABLEemployee2WITHNOCHECKADDCONSTRAINTCK_AgeCHECK(DATEDIFF(year,Birth_Date,Hire_Date)>18)第34頁/共42頁8.4.4檢查約束例6-25創(chuàng)建一個(gè)訂貨表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創(chuàng)建transporters表并定義檢查約束第36頁/共42頁8.4使用約束實(shí)施數(shù)據(jù)完整性第37頁/共42頁8.4.5默認(rèn)約束定義默認(rèn)約束的語法格式[CONSTRAINTconstraint_name]DEFAULTconstant_expression[FO