第5章 數(shù)據(jù)庫完整性(集美大學)

第五章數(shù)據(jù)庫的完整性林穎賢重點：了解DBMS完整性控制機制的三個方面，即完整性約束條件的定義、檢查和違約反應。熟練掌握用SQL語言定義關(guān)系模式的三個完整性約束以及如何運用觸發(fā)器、規(guī)則實現(xiàn)數(shù)據(jù)庫完整性。難點：參照完整性及觸發(fā)器.5.1數(shù)據(jù)庫的完整性本節(jié)主要內(nèi)容完整性子系統(tǒng)和完整性規(guī)則完整性類型完整性約束條件參照完整性控制SQL中的完整性約束機制數(shù)據(jù)庫完整性1.數(shù)據(jù)庫的完整性是指數(shù)據(jù)的正確性、有效性和相容性。防止不合語義的數(shù)據(jù)進入數(shù)據(jù)庫。2.數(shù)據(jù)的完整性和安全性是兩個不同概念數(shù)據(jù)的完整性防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫中存在不正確的數(shù)據(jù)防范對象：不合語義的、不正確的數(shù)據(jù)數(shù)據(jù)的安全性保護數(shù)據(jù)庫防止惡意的破壞和非法的存取防范對象：非法用戶和非法操作3.數(shù)據(jù)庫完整性約束可以通過DBMS或應用程序來實現(xiàn).基于DBMS的完整性約束作為模式的一部分存入數(shù)據(jù)庫中。通過DBMS實現(xiàn)的數(shù)據(jù)庫完整性按照數(shù)據(jù)庫設(shè)計步驟進行設(shè)計，而由應用軟件實現(xiàn)的數(shù)據(jù)庫完整性則納入應用軟件設(shè)計

1完整性子系統(tǒng)和完整性規(guī)則數(shù)據(jù)庫完整性是通過DBMS的完整性子系統(tǒng)實現(xiàn)的，它有兩個功能：

監(jiān)督事務(wù)的執(zhí)行，并測試是否違反完整性規(guī)則。如有違反，則采取恰當?shù)牟僮?，如拒絕、報告違反情況，改正錯誤等方法進行處理。完整性規(guī)則是由數(shù)據(jù)庫管理員或應用程序員事先向完整性子系統(tǒng)提供有關(guān)數(shù)據(jù)約束的一組規(guī)則。由三部分組成:什么時候使用規(guī)則進行檢查（稱為規(guī)則的“觸發(fā)條件”)要檢查什么樣的錯誤（稱為“約束條件”或“謂詞”）如果查出錯誤，應該怎么辦（稱為“ELSE子句”，即違反時要做的動作２完整性約束條件完整性約束條件作用的對象可以是關(guān)系、元組、列三種。列：對屬性的取值類型、范圍、精度等的約束條件元組：對元組中各個屬性列間的聯(lián)系的約束關(guān)系：對若干元組間、關(guān)系集合上以及關(guān)系之間的聯(lián)系的約束完整性約束條件涉及的這三類對象，其狀態(tài)可以是靜態(tài)的，也可以是動態(tài)的。靜態(tài)約束:它是反映數(shù)據(jù)庫狀態(tài)合理性的約束，這是最重要的一類完整性約束。動態(tài)約束:它是反映數(shù)據(jù)庫狀態(tài)變遷的約束。

２完整性約束條件

六類完整性約束條件分類:靜態(tài)列級約束靜態(tài)元組約束靜態(tài)關(guān)系約束動態(tài)列級約束動態(tài)元組約束動態(tài)關(guān)系約束約束的類型DEFAULT約束CHECK約束PRIMARYKEY約束UNIQUE約束FOREIGNKEY約束級聯(lián)引用完整性1.靜態(tài)列級約束是對一個列的取值域的說明，這是最常用也最容易實現(xiàn)的一類完整性約束，包括以下幾類：1)數(shù)據(jù)類型的約束，包括數(shù)據(jù)的類型、長度、單位、精度等。

例如:學生姓名的數(shù)據(jù)類型為字符型，長度為8。2)數(shù)據(jù)格式的約束。例如：學號的格式為YYYYMMXXX。其中YYYY表示入學年份，MM專業(yè),XXX為順序編號。3)取值范圍或取值集合的約束。例如:規(guī)定成績的取值范圍為0-100。4)空值的約束.例:空值：未定義或未知的值。［例1］建立學生登記表Student，要求學號為主鍵，姓名不能取空值，性別只能是“男”或“女”,身份證號碼唯一，出生日期處于可接受的日期范圍內(nèi)

createtablestudent(stu_nochar(10)primarykey,stu_namevarchar(10)notnull,stu_idcardchar(18)unique,stu_sexchar(2),

constraintck_sexcheck(stu_sexin('男','女')),stu_birthdatedatetime

constraintck_bir1check(stu_birthdate<getdate()))表級CHECK約束列級CHECK約束刪除約束：altertablestudentdropconstraintck_sex[例2].為Employees數(shù)據(jù)表增加一列，列名為：郵箱，數(shù)據(jù)類型：VarChar(20),并將該列設(shè)置為惟一約束。ALTERTABLEEmployeesADD郵箱VARCHAR(20)CONSTRAINTU_EmailUNIQUE原因是Employees數(shù)據(jù)表已存在n條記錄．增加一列會自動填上NULL［例3］在Sales數(shù)據(jù)庫中為Employees數(shù)據(jù)表創(chuàng)建名為CK_ENo的CHECK約束，該約束限制“編號”列中只允許6位數(shù)字（不能為字母）。2.靜態(tài)元組約束:

規(guī)定元組的各個列之間的約束關(guān)系.例：庫存關(guān)系中出庫數(shù)量不能大于庫存數(shù)量3.靜態(tài)關(guān)系約束:

關(guān)系的各個元組之間或若干關(guān)系之間存在的各種聯(lián)系或約束.

常見靜態(tài)關(guān)系約束：1)實體完整性約束2)參照完整性約束3)函數(shù)依賴約束4)統(tǒng)計約束

關(guān)系字段間存在的函數(shù)依賴.例：學生關(guān)系中snosname

定義某個字段值一個關(guān)系多個元組的統(tǒng)計值之間的約束關(guān)系例：職工平均工資的2倍<=部門經(jīng)理的工資<=職工平均工資的5倍.職工平均工資值:統(tǒng)計值

DEPTDEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTONPRIMARYKEYInsertinto

20 MARKETING DALLAS

FINANCE NEWYORKNotallowed(DEPTNO-20alreadyexists)Notallowed

(DEPTNOisnull)查詢主鍵信息：

sp_pkeystable_nameThePRIMARYKEYConstraint每個表只有一個PRIMARYKEY約束，PRIMARYKEY約束的值必須是唯一的，不允許有空值。如何區(qū)別UNIQUE和PRIMARYKEY的不同？[例4].分析下列兩個SQL語句判斷是否有錯？CREATETABLET1(col1intNOTNULL,col2AScol1+1UNIQUE)====================CREATETABLET2(col1intNOTNULL,col2AScol1+1PRIMARYKEY)T2表無法創(chuàng)建，因為因為主鍵約束，所以SQLServer需要保證計算的結(jié)果不為NULL該技巧可以使用ISNULL()函數(shù)繞過對計算列的計算，如果計算結(jié)果為空，將應用一個替換值：CREATETABLET2(col1intNOTNULL,col2ASISNULL(col1+1,0)PRIMARYKEY)

DEPTDEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS...PRIMARY

KEY

EMPEMPNO ENAME JOB ...COMMDEPTNO

7839 KING PRESIDENT 107698 BLAKE MANAGER ２0...FOREIGNKEY

7571 FORD MANAGER ...200 97571 FORD MANAGER ...200InsertintoNotallowed

(DEPTNO-9doesnotexistintheDEPTtableAllowed

TheFOREIGNKEYConstraint外碼是否可以接受空值的問題參照完整性檢查和違約處理可能破壞參照完整性的情況及違約處理被參照表（例如Student）參照表（例如SC）違約處理可能破壞參照完整性

插入元組拒絕可能破壞參照完整性

修改外碼值拒絕刪除元組可能破壞參照完整性拒絕/級連刪除/設(shè)置為空值修改主碼值可能破壞參照完整性拒絕/級連修改/設(shè)置為空值拒絕刪除置空值刪除級聯(lián)引用完整性FOREIGNKEY約束包含一個CASCADE選項，允許對一個定義了UNIQUE或者PRIMARYKEY約束的列的值的修改自動傳播到引用它的外鍵上，這個動作稱為級聯(lián)引用完整性．語法：[CONSTRAINT約束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表[(引用列[,…n])].

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]NOACTION：任何企圖刪除或者更新被其他表的外鍵所引用的鍵都將引發(fā)一個錯誤，對數(shù)據(jù)的改變會被回滾。NOACTION是默認值CASCADE：若父表中的行變化了，則引用表中相應的行也自動變化［例5］顯式說明參照完整性的違約處理示例createtableSC(Stu_nochar(8),Cou_nochar(3),GradetinyintCONSTRAINTpk_scPRIMARYKEY(Stu_no,Cou_no),foreignkey(Stu_no)referencesStudent(Stu_no)

ONDELETECASCADE

ONUPDATECASCADE,foreignkey(Cou_no)referencesCourse(Cou_no)

ONDELETENOACTION

ONUPDATECASCADE)表級約束級聯(lián)刪除SC表中相應的元組*級聯(lián)更新SC表中相應的元組當刪除course表中的元組造成了與SC表不一致時拒絕刪除當更新course表中的cno時，級聯(lián)更新SC表中相應的元組測試：當刪除course表中的元組造成了與SC表不一致時拒絕刪除當更新course表中的cno時，級聯(lián)更新SC表中相應的元組１.動態(tài)列級約束:是修改列定義或列值時應滿足的約束條件，包括兩方面：1.修改列定義時的約束

例如，將允許空值的列改為不允許空值時，如果該列目前已存在空值，則拒絕這種修改。2.修改列值時的約束

修改列值有時需要參照其舊值，并且新舊值之間需要滿足某種約束條件。例如，職工工資調(diào)整不得低于其原來工資，學生年齡只能增長等等。例如:3.動態(tài)約束２.動態(tài)元組約束:修改元組值:各個字段之間要滿足的約束條件．例如

例:職工工資調(diào)整不得低于其原來工資+工齡*1.5３.

動態(tài)關(guān)系約束

關(guān)系變化前后狀態(tài)：限制條件例：事務(wù)一致性、原子性等約束條件思考題如何在“圖書管理”數(shù)據(jù)庫上規(guī)定“可借圖書冊數(shù)”，限制學生借閱圖書的冊數(shù)在０～15冊之間．完整性約束條件小結(jié)粒度狀態(tài)

列級

元組級

關(guān)系級

靜態(tài)列定義·類型·格式·值域·空值

元組值應滿足的條件

實體完整性約束參照完整性約束函數(shù)依賴約束統(tǒng)計約束動態(tài)改變列定義或列值元組新舊值之間應滿足的約束條件

關(guān)系新舊狀態(tài)間應滿足的約束條件

正確性。數(shù)據(jù)的合法性有效性。數(shù)據(jù)是否在有效范圍內(nèi)相容性。指同一個事實的兩個數(shù)據(jù)應該一致。例如:1)無效的數(shù)據(jù)被添加到數(shù)據(jù)庫中,如:某定單所指的產(chǎn)品不存在.2)對數(shù)據(jù)庫的修改不一致,如:為某產(chǎn)品增加了一份定單,但卻沒有調(diào)整產(chǎn)品的庫存信息.3)將存在的數(shù)據(jù)修改為無效的數(shù)據(jù),如:將某學生的班號修改為并不存在的班級.例：創(chuàng)建職工表EMP，滿足下列約束:1）部門編號的值必須在范圍10至100內(nèi)2）雇員的職稱只能為下列之一:“技術(shù)員”、“工程師“、”高級工程師”3）2002年之前雇用的每個雇員的工資必須超過4000元。createtableemp(編號char(6)primarykey,姓名varchar(10)notnull,職稱varchar(12)CONSTRAINTck_zccheck(職稱in('技術(shù)員','工程師','高級工程師')),部門編號intCONSTRAINTck_deptcheck(部門編號between10and100or部門編號isnull),參加工作時間datetime,工資money,

CONSTRAINTCK_salcheck(YEAR(參加工作時間)>=2002or工資>4000))5.6觸發(fā)器

觸發(fā)器是一種特殊的存儲過程，它在插入，刪除或修改特定表中的數(shù)據(jù)時觸發(fā)執(zhí)行，它比數(shù)據(jù)庫本身標準的功能有更精細和更復雜的數(shù)據(jù)控制能力。數(shù)據(jù)庫觸發(fā)器有以下的作用：

安全性?？梢曰跀?shù)據(jù)庫的值使用戶具有操作數(shù)據(jù)庫的某種權(quán)利。

可以基于時間限制用戶的操作，例如不允許下班后和節(jié)假日修改數(shù)據(jù)庫數(shù)。

可以基于數(shù)據(jù)庫中的數(shù)據(jù)限制用戶的操作，例如不允許股票的價格的升幅一次超過10%。

實現(xiàn)復雜的數(shù)據(jù)完整性規(guī)則。實現(xiàn)非標準的數(shù)據(jù)完整性檢查和約束。觸發(fā)器可產(chǎn)生比規(guī)則更為復雜的限制。與規(guī)則不同，觸發(fā)器可以引用列或數(shù)據(jù)庫對象。例如，觸發(fā)器可回退任何企圖吃進超過自己保證金的期貨。

實現(xiàn)復雜的非標準的數(shù)據(jù)庫相關(guān)完整性規(guī)則。觸發(fā)器可以對數(shù)據(jù)庫中相關(guān)的表進行連環(huán)更新。例如，在auths表author_code列上的刪除觸發(fā)器可導致相應刪除在其它表中的與之匹配的行。自動計算數(shù)據(jù)值，如果數(shù)據(jù)的值達到了一定的要求，則進行特定的處理。例如，如果公司的帳號上的資金低于5萬元則立即給財務(wù)人員發(fā)送警告數(shù)據(jù)。DELETED和INSERTED是SQLServer為觸發(fā)器創(chuàng)建的概念上的表。該表在結(jié)構(gòu)上同觸發(fā)器定義于其上的表相似，并且保存了可能被用戶的行動改變的舊的或者新的行中的值。該表將跟蹤在Transact-SQL中的行一級的變化。當一個INSERT、UPDATE、或者DELETE語句在SQLServer中執(zhí)行的時候，行被加入到觸發(fā)器表中，而且是同時加入到INSERTED和DELETED表中。INSERTED和DELETED表同觸發(fā)器表是一樣的。它們有同樣的列名和數(shù)據(jù)類型。例如，如果在GRADE表中放置一個觸發(fā)器，那么INSERTED和DELETED就有這樣的結(jié)構(gòu)。GRADEINSERTEDDELETEDSNCHAR(9)SNCHAR(9)SNCHAR(9)

CODEVARCHAR(4)CODEVARCHAR(4)CODEVARCHAR(4)

GRADEVARCHAR(2)GRADEVARCHAR(2)GRADEVARCHAR(2)INSERTED和DELETED表可以被觸發(fā)器檢查以確定要執(zhí)行什么樣的觸發(fā)器行動。INSERTED表同INSERT和UPDATE語句一起使用。DELETED表則和DELETE以及UPDATE語句一起使用。UPDATE語句使用INSERTED和DELETED表，這是因為進行UPDATE操作時，SQLServer總是要刪除舊的行，填入新的行。因此，執(zhí)行UPDATE時，INSERTED表中的行總是DELETED表中的行的副本。[例1].某學生退學,將此學生從S表中刪除的同時,將SC表中相關(guān)數(shù)據(jù)刪除.CREATETRIGGERTRI_S1ONSFORDELETEASDELETESCFROMSCA,DELETEDBWHEREA.STU_NO=B.STU_NO指定的數(shù)據(jù)行被用戶從基本表中刪除,然后轉(zhuǎn)移到DELETE表中[例2]:在SC表上建立一個觸發(fā)器,其作用是不允許對SCORE作修改CREATETRIGGERTRI_SC1ONSCFORUPDATEASIFUPDATE(SCORE)BEGINRAISERROR(‘不允許修改’,10,1)ROLLBACKTRANSACTIONEND[例3]在學生表創(chuàng)建一個afterupdate觸發(fā)器,當在學生表上更新記錄時屏幕上顯示有多少條記錄被更新。CREATETRIGGERTRI_Stu2ONStudentAFTERUPDATEASdeclare@akchar(20)select@ak=str(@@rowcount,4)+'個記錄被更新'print@akreturnupdatestudentsetstu_age=20wherestu_no='20026102'[例4]定義一個觸發(fā)器：規(guī)定在星期六或星期日或者在某些方面8點至17點以外時間不能更新SC表系統(tǒng)檢查當時的系統(tǒng)時間，如是周六或周日，或者不是8點至17點，系統(tǒng)會拒絕執(zhí)行用戶的更新操作，并提示出錯信息CREATETRIGGERtri_scONStudentFORINSERT,UPDATE,DELETEASIF(datename(weekday,getdate())IN('星期六','星期日'))OR(datename(hh,getdate())NOTBETWEEN8AND16)BEGINprint‘不允許修改數(shù)據(jù)'

ROLLBACKTRANSACTIONEND[例5]、設(shè)計一個觸發(fā)器，要求在修改s表時，不允許修改后的年齡小于原來的年齡。createtriggerupdateage2onstudentforupdateasbegindeclare@oldage

int,@newage

intifupdate(age)begin

select@oldage=agefromdeletedselect@newage=agefrominsertedif@oldage>@newagebegin

raiserror('updateisfail!',10,1)rollbacktransactionendendendCREATETRIGGEROrdDet_InsertON[OrderDetails]FORINSERTASUPDATEPSETUnitsInStock=P.UnitsInStock-I.QuantityFROMProductsASPINNERJOINInsertedASIONP.ProductID=I.ProductID[例6]：將創(chuàng)建一個觸發(fā)器。無論何時訂購產(chǎn)品（無論何時向OrderDetails表中插入一條記錄），這個觸發(fā)器都將更新Products表中的一列(UnitsInStock)。用原來的值減去訂購的數(shù)量值即為新值。[例7]：將創(chuàng)建一個觸發(fā)器，無論何時刪除一個產(chǎn)品類別（即從Categories表中刪除一條記錄），該觸發(fā)器都會更新Products表中的Discontinued列。所有受影響的產(chǎn)品都標記為1，標示不再使用這些產(chǎn)品了。CREATETRIGGERCategory_DeleteONCategoriesFORDELETEASUPDATEPSETDiscontinued=1FROMProductsASPINNERJOINdeletedASdONP.CategoryID=d.CategoryID[例8]你在開發(fā)某公司的管理信息系統(tǒng)，其中要跟蹤經(jīng)理的購買情況。每個經(jīng)理在預算表中都有一個記錄。預算表包含列“經(jīng)理代號”、列“預算總金額”、列“現(xiàn)有預算金額”；購買表包含列“購買代號”、列“購買金額”，列“經(jīng)理代號”。每次購買都要與“現(xiàn)有預算金額”作比較，當該次購買的“購買金額”小于“現(xiàn)有預算金額”時，才允許插入到購買表中（一次購買只插入一個記錄），同時自動更改預算表的“現(xiàn)有預算金額”。請在購買表上編寫一個觸發(fā)器，完成該任務(wù)。CREATETRIGGERPurchase_InsertON購買表FORINSERTASBEGINIF判斷是否只插入一次BEGINRAISERROR('一次購買只允許插入一個記錄！',16,1)ROLLBACKTRANSACTIONENDIF“現(xiàn)有預算金額”小于”購買金額””BEGINRAISERROR('現(xiàn)有預算金額不足支付購買金額！',16,1)ROLLBACKTRANSACTIONEND

修改預算表中的現(xiàn)有預算金額END16表明錯誤可由用戶修正。CREATETRIGGERPurchase_InsertON購買表FORINSERTASBEGINIF(SELECTcount(*)FROMInserted)<>1)BEGINRAISERROR('一次購買只允許插入一個記錄！',16,1)ROLLBACKTRANSACTIONENDIF(SELECTa.現(xiàn)有預算金額-b.購買金額FROM預算表ASaINNERJOINInsertedASbONa.經(jīng)理代號=b.經(jīng)理代號)