第五章數(shù)據(jù)庫(kù)完整性

第五章數(shù)據(jù)庫(kù)完整性數(shù)據(jù)庫(kù)的安全性是盡可能保證非法用戶(hù)不破壞數(shù)據(jù)的正確性。數(shù)據(jù)庫(kù)的完整性是盡可能保證合法用戶(hù)不破壞數(shù)據(jù)的正確性。數(shù)據(jù)庫(kù)的完整性是為了防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義的數(shù)據(jù)。問(wèn)題：你還記得實(shí)體完整性約束和參照完整性約束是怎么回事嗎？為此，DBMS需要完成：提供定義完整性約束條件的機(jī)制提供完整性檢查的方法違約處理完整性分類(lèi)：實(shí)體完整性、參照完整性和用戶(hù)定義完整性。約束約束：就是一種強(qiáng)制性的規(guī)定。1、建立非空約束。altertablecoursealtercolumncreditsmallintnotnull其中，必須給出列的類(lèi)型。SQLServer的六種約束：notnull非空約束check檢查約束unique唯一約束primarykey主碼約束foreignkey外碼約束default默認(rèn)約束約束的建立：在創(chuàng)建表的同時(shí)建立在已有表上建立注意：若表中已有數(shù)據(jù)，則建立約束時(shí)可能會(huì)失敗。2、建立唯一約束。altertablecourseaddconstraintUQ_cnameunique(cname)其中：constraint關(guān)鍵詞即對(duì)該約束進(jìn)行命名，即UQ_cname是約束名。注意：對(duì)每個(gè)約束都進(jìn)行命名是個(gè)好習(xí)慣check約束1、創(chuàng)建表時(shí)建立check約束。

createtablestu(snochar(9)constraintPK_stuprimarykey,namechar(8)notnull,agesmallintconstraintc_age

check(age>0andage<100)

)邏輯表達(dá)式問(wèn)題：此時(shí)age列值可以是空嗎？或createtablestu(snochar(9),namechar(8)notnull,agesmallint,constraintPK_stuprimarykey(sno),

constraintc_agecheck(age>0andage<100))2、在表中增加check約束

altertablestudentaddconstraintc_agecheck(Sage>0andSage<100)check約束可用于建立表中屬性取值上的某些約束關(guān)系。答：可以是空。check約束（續(xù)）3、規(guī)定性別的取值只能是男或女。4、check的表達(dá)式可以包含多個(gè)屬性。如規(guī)定CS系學(xué)生必須25歲以下。問(wèn)題：此時(shí)ssex列值可空嗎？altertablestudentaddconstraintc_ssexcheck(ssexin('男','女'))答：可以是空。問(wèn)題：若要求該列取值同時(shí)不可為空，則如何表達(dá)？altertablestudentaddconstraintc_cs_agecheck(sdept<>'cs'orsage<=25)問(wèn)題：你知道這個(gè)約束條件是怎么得到的嗎？altertablestudentaddconstraintc_ssexcheck(ssexin('男','女')andssexisnotnull)default約束、刪除約束default約束：可以對(duì)列設(shè)置默認(rèn)值。如對(duì)性別列默認(rèn)取值為男。1、創(chuàng)建表時(shí)建立default約束。

createtablestudent(snochar(9)primarykey,ssexchar(2)constraintc_ssex_defdefault'男')2、在表中增加default約束。

altertablestudentaddconstraintc_ssex_defdefault'男'forssex刪除約束：altertable表名dropconstraint約束名如：altertablestudentdropconstraintc_ssex_def注意：表中的約束不能修改，只能刪除后重建。問(wèn)題：下列命名的執(zhí)行結(jié)果是什么？

insertintostudent(sno,sname,ssex,sage,sdept)values('5','張飛',null,29,'cs')insertintostudent(sno,sname)values('6’,'張小飛')1、規(guī)則的創(chuàng)建

createrulerange_ruleas@range>0and@range<100規(guī)則規(guī)則：可用來(lái)限制屬性取值的范圍，實(shí)現(xiàn)強(qiáng)制數(shù)據(jù)的域完整性，作用類(lèi)似于check約束。規(guī)則名定義規(guī)則的邏輯表達(dá)式。其中的局部變量值為通過(guò)update或insert語(yǔ)句輸入的值。check約束可針對(duì)一個(gè)屬性多次應(yīng)用一個(gè)屬性只能應(yīng)用一個(gè)規(guī)則規(guī)則需要單獨(dú)創(chuàng)建，也只需創(chuàng)建一次，以后可多次應(yīng)用于多個(gè)表createrulelist_ruleas@listin('男','女',null)

createrulepattern_ruleas@valuelike'__-%[0-9]'規(guī)則（續(xù)）該列即受綁定規(guī)則的約束該列解除規(guī)則約束若該規(guī)則已被綁定，則拒絕刪除。2、將規(guī)則綁定到表的列

execsp_bindrule'range_rule','student.sage'execsp_bindrule'range_rule','sc.grade'3、取消表列的規(guī)則綁定execsp_unbindrule'student.sage'4、刪除規(guī)則droprulerange_rule1、默認(rèn)值的創(chuàng)建createdefaultdef_sexas'男'默認(rèn)值默認(rèn)值：作用與default約束相同，用法類(lèi)似于規(guī)則。默認(rèn)名此處為常量表達(dá)式若該列已定義了default約束，則該綁定會(huì)失敗。2、將默認(rèn)值綁定到表的列

execsp_bindefault'def_sex','student.ssex'3、取消表列的默認(rèn)綁定

execsp_unbindefault'student.sdept'4、刪除默認(rèn)值

dropdefaultdef_sex完整性的違約處理1、實(shí)體完整性違約處理：實(shí)體完整性是通過(guò)在表中定義主碼來(lái)實(shí)現(xiàn)。當(dāng)更新操作違反了實(shí)體完整性，則該更新操作被拒絕執(zhí)行3、參照完整性違約處理問(wèn)題：①在SC表插入違反了參照完整性的數(shù)據(jù)時(shí)，你認(rèn)為怎么處理合理？②將1號(hào)學(xué)生從Student表刪除，若他已選課，你認(rèn)為對(duì)SC表怎么處理合理？③將1號(hào)學(xué)生的學(xué)號(hào)改為10號(hào)，你認(rèn)為對(duì)SC表怎么處理合理？④將1號(hào)課從Course表刪除，若2號(hào)課的先行課為1號(hào)，則怎么做合理？⑤將1號(hào)課編號(hào)改為10號(hào)，若2號(hào)課的先行課為1號(hào)，則怎么做合理？2、用戶(hù)定義完整性違約處理：DBMS默認(rèn)采用拒絕執(zhí)行。參照完整性的違約處理createtablesc(snochar(9),cnochar(4),gradesmallint,primarykey(sno,cno),constraintfk_snoforeignkey(sno)referencesstudent(sno)

ondeletecascadeonupdatecascade,constraintfk_cnoforeignkey(cno)referencescourse(cno)

ondeletenoactiononupdatecascade)createtablecourse(cnochar(4)primarykey,cnamechar(40)notnull,cpnochar(4),creditsmallint,constraintfk_cpnoforeignkey(cpno)referencescourse(cno)

ondeletesetnullonupdatecascade)注意：在表自身上建立的外鍵違約處理SQLServer并不接受。參照完整性違約處理有三種方式：拒絕執(zhí)行noaction、級(jí)聯(lián)cascade和設(shè)置為空setnull。默認(rèn)為拒絕執(zhí)行。問(wèn)題：難道沒(méi)辦法實(shí)現(xiàn)這一功能要求嗎？觸發(fā)器觸發(fā)器：是由用戶(hù)定義的，且在關(guān)系表上的一類(lèi)由事件驅(qū)動(dòng)的特殊過(guò)程。問(wèn)題：①這段程序什么時(shí)候被執(zhí)行？②若將事件insert寫(xiě)成delete或update，或它們的組合會(huì)怎樣？③觸發(fā)器有什么用？常用事件：insert、delete、update（即數(shù)據(jù)更新操作）定義觸發(fā)器的一般語(yǔ)法格式。

createtrigger<trigger_name>on{table_name|view_name}{for|after|insteadof}{[insert][,][delete][,][update]}asbeginT-SQLstatementend例如：

createtriggerinsert_studentonstudentforinsertasbeginprint'Hitrigger!'end觸發(fā)器（續(xù)）after：在觸發(fā)事件的SQL語(yǔ)句的所有操作（包括各種約束檢查）都已成功執(zhí)行后觸發(fā)器被觸發(fā)。若使用for關(guān)鍵詞，則默認(rèn)為after。只能定義在表上可為表的同一操作定義多個(gè)該類(lèi)觸發(fā)器insteadof：不執(zhí)行其所觸發(fā)的更新操作，而僅執(zhí)行觸發(fā)器本身。可定義在表和視圖上對(duì)同一觸發(fā)操作只能定義一個(gè)該類(lèi)觸發(fā)器altertrigger

命令用于修改觸發(fā)器正文，格式與createtrigger完全一致。droptriggertrigger_name

刪除觸發(fā)器。execsp_rename'old_name','new_name'更改觸發(fā)器名稱(chēng)。execsp_helptrigger'table_name'查看表中的觸發(fā)器信息。execsp_helptext'trigger_name'查看觸發(fā)器定義信息。disabletrigger<triggername>

on<tablename>禁用觸發(fā)器enabletrigger<triggername>on<tablename>啟用觸發(fā)器觸發(fā)器執(zhí)行順序問(wèn)題：當(dāng)同一個(gè)表上對(duì)同一更新操作定義了多個(gè)觸發(fā)器，它們被激活時(shí)的執(zhí)行順序是什么？execsp_settriggerorder'triggername','value','type'

其中，第2個(gè)參數(shù)可以是first、last、none字符串；第3個(gè)參數(shù)可以是insert、delete、update字符串。該存儲(chǔ)過(guò)程指定要對(duì)表執(zhí)行的第一個(gè)和最后一個(gè)after觸發(fā)器。對(duì)于一個(gè)表，只能為每個(gè)更新操作指定一個(gè)第一個(gè)和最后一個(gè)after觸發(fā)器。如果在同一個(gè)表上還有其他該操作的after觸發(fā)器，這些觸發(fā)器將隨機(jī)執(zhí)行。觸發(fā)器的遞歸觸發(fā)createtabletmpT(idintprimarykeyidentity,numint)在表中創(chuàng)建標(biāo)識(shí)列，用于標(biāo)識(shí)唯一的一行默認(rèn)初值1，步長(zhǎng)1。查看和更改數(shù)據(jù)庫(kù)選項(xiàng)。sp_dboption與sp_configure其中，如果recursivetriggers選項(xiàng)設(shè)為true，則將啟用觸發(fā)器的遞歸觸發(fā)。如果為false（默認(rèn)值）將只禁止直接遞歸。若要禁用間接遞歸，使用sp_configure將nestedtriggers服務(wù)器選項(xiàng)設(shè)置為0。或使用數(shù)據(jù)庫(kù)屬性進(jìn)行設(shè)置。注意：是否進(jìn)行遞歸觸發(fā)取決于應(yīng)用的需要，但一定注意遞歸的出口問(wèn)題。createtriggerins_tmpTontmpTforinsertasbegin insertintotmpT(num)values(3)endinsertintotmpT(num)values(1)select*fromtmpT問(wèn)題：查詢(xún)結(jié)果是什么？這個(gè)功能怎么實(shí)現(xiàn)？要求：將每個(gè)轉(zhuǎn)系學(xué)生的原所在系信息記錄到history_dept_info表中。該表需存儲(chǔ)如下信息：學(xué)號(hào)、學(xué)生原所在系、學(xué)生轉(zhuǎn)入系、轉(zhuǎn)系時(shí)間。createtablehistory_dept_info(idintprimarykeyidentity,snochar(9),old_deptchar(20),new_deptchar(20),datedatetime)內(nèi)置函數(shù)getdate()即可獲得系統(tǒng)當(dāng)前的日期和時(shí)間。分析：①建立history_dept_info表。②觸發(fā)器的觸發(fā)條件是什么？觸發(fā)器中需要做什么？③若將修改數(shù)據(jù)庫(kù)的時(shí)間作為轉(zhuǎn)系時(shí)間，則如何獲取系統(tǒng)當(dāng)前時(shí)間？④觸發(fā)器中怎么能知道sdept屬性修改前和修改后的值得呢？觸發(fā)條件：當(dāng)對(duì)Student表中的Sdept屬性進(jìn)行update時(shí)觸發(fā)。觸發(fā)器動(dòng)作：當(dāng)Sdept屬性修改前和修改后的值一起存入所建新表。deleted表和inserted表在觸發(fā)器執(zhí)行過(guò)程中，SQLServer自動(dòng)建立和管理這兩個(gè)臨時(shí)的虛擬表這兩個(gè)表的結(jié)構(gòu)與激發(fā)觸發(fā)器的更新操作的對(duì)象表結(jié)構(gòu)一致這兩個(gè)表的值包含了在激發(fā)觸發(fā)器的更新操作中插入和刪除的所有記錄這兩個(gè)表可供用戶(hù)查詢(xún)可用這兩個(gè)表在SQL命令與觸發(fā)器之間傳遞數(shù)據(jù)T-SQL語(yǔ)句deleted表inserted表insert空新的行update舊的行新的行delete刪除的行空問(wèn)題：你理解該表的含義嗎？deleted表和inserted表示例觀察觸發(fā)器的運(yùn)行結(jié)果。createtriggerchg_stuonstudentforinsert,delete,updateasbegin select*fromdeleted select*frominsertedendcreatetriggerinstead_chg_courseoncourseinsteadofinsert,delete,updateasbegin select*fromdeleted select*frominsertedendinsertintostudentvalues('10','劉德華','男',23,'CS')updatestudentsetsage=27wheresno='10'deletefromstudentwheresno='10'updatestudentsetsage=25insertintocoursevalues('12','Matlab',null,3)select*fromcourseupdatecoursesetcredit=6wherecno='1'select*fromcoursedeletefromcourseselect*fromcourse轉(zhuǎn)系歷史數(shù)據(jù)的跟蹤存儲(chǔ)createtriggerupd_sdeptonstudentforupdateasbegin ifupdate(sdept) begin declare@stu_snochar(9) declare@o_deptchar(20) declare@n_deptchar(20) set@stu_sno=(selectsnofromdeleted) set@o_dept=(selectsdeptfromdeleted) set@n_dept=(selectsdeptfrominserted) insertintohistory_dept_info(sno,old_dept,new_dept,date)values(@stu_sno,@o_dept,@n_dept,getdate()) endend也可視為觸發(fā)條件：即對(duì)student表sdept屬性u(píng)pdate時(shí)。聲明局部變量，其類(lèi)型應(yīng)與表屬性定義的類(lèi)型一致set為賦值語(yǔ)句將所需信息插入到history_dept_info表問(wèn)題的解決辦法

上例觸發(fā)器適用于一次僅處理一個(gè)學(xué)生的轉(zhuǎn)系操作。當(dāng)全體MA系學(xué)生轉(zhuǎn)入CS系，若執(zhí)行updatestudentsetsdept='CS'wheresdept='IS'語(yǔ)句，該觸發(fā)器會(huì)出錯(cuò)。解決辦法：1、要求應(yīng)用程序每次只執(zhí)行對(duì)一個(gè)學(xué)生的轉(zhuǎn)系更新操作，多個(gè)學(xué)生轉(zhuǎn)系則循環(huán)執(zhí)行。2、修改觸發(fā)器，使之能夠處理批量轉(zhuǎn)系更新操作?？刹捎糜螛?biāo)方式實(shí)現(xiàn)。顯然，該做法會(huì)降低系統(tǒng)效率。這不是個(gè)好主意，合格的程序員不能這樣想問(wèn)題。實(shí)現(xiàn)CS_S視圖的數(shù)據(jù)插入createtriggerinstead_insoncs_sinsteadofinsertasbegin declare@in_snochar(9),@in_snamechar(20),@in_ssexchar(2)declare@in_sagesmallintselect@in_sno=sno,@in_sname=sname,@in_ssex=ssex,@in_sage=sagefrominsertedinsertintostudent(sno,sname,ssex,sage,sdept)values(@in_sno,@in_sname,@in_ssex,@in_sage,'CS')endcreateviewCS_S(sno,sname,ssex,sage)asselectsno,sname,ssex,sagefromstudentwheresdept='cs'問(wèn)題：有辦法實(shí)現(xiàn)在CS_S視圖上插入CS系學(xué)生數(shù)據(jù)嗎？問(wèn)題：你還記得在CS系學(xué)生視圖上插入數(shù)據(jù)所存在的問(wèn)題嗎？辦法：CS_S視圖上針對(duì)insert命令建立替代觸發(fā)器，數(shù)據(jù)的插入由觸發(fā)器實(shí)現(xiàn)實(shí)現(xiàn)Course表的違約處理createtriggerdel_courseoncourseinsteadofdeleteasbegindeclare@del_cnochar(4)set@del_cno=(selectcnofromdeleted)updatecoursesetcpno=nullwherecpno=@del_cnodeletefromcoursewherecno=@del_cnoend問(wèn)題：你還記得在表自身上建立的外鍵違約處理SQLServer并不接受嗎？例如在Course表中刪除1號(hào)課，若2號(hào)課的直接先修課為1號(hào)，則將2號(hào)課的直接先修課設(shè)置為空比較合理。問(wèn)題是如何實(shí)現(xiàn)呢？辦法：在Course表上定義替代觸發(fā)器，上述工作由替代觸發(fā)器完成。顯然，該觸發(fā)器只適合于一次僅刪除一門(mén)課程的delete命令。實(shí)現(xiàn)Course表的違約處理（續(xù)）