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

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

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

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

２完整性約束條件

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

例如:學(xué)生姓名的數(shù)據(jù)類型為字符型，長(zhǎng)度為8。2)數(shù)據(jù)格式的約束。例如：學(xué)號(hào)的格式為YYYYMMXXX。其中YYYY表示入學(xué)年份，MM專業(yè),XXX為順序編號(hào)。3)取值范圍或取值集合的約束。例如:規(guī)定成績(jī)的取值范圍為0-100。4)空值的約束.例:空值：未定義或未知的值。［例1］建立學(xué)生登記表Student，要求學(xué)號(hào)為主鍵，姓名不能取空值，性別只能是“男”或“女”,身份證號(hào)碼唯一，出生日期處于可接受的日期范圍內(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()))表級(jí)CHECK約束列級(jí)CHECK約束刪除約束：altertablestudentdropconstraintck_sex[例2].為Employees數(shù)據(jù)表增加一列，列名為：郵箱，數(shù)據(jù)類型：VarChar(20),并將該列設(shè)置為惟一約束。ALTERTABLEEmployeesADD郵箱VARCHAR(20)CONSTRAINTU_EmailUNIQUE原因是Employees數(shù)據(jù)表已存在n條記錄．增加一列會(huì)自動(dòng)填上NULL［例3］在Sales數(shù)據(jù)庫(kù)中為Employees數(shù)據(jù)表創(chuàng)建名為CK_ENo的CHECK約束，該約束限制“編號(hào)”列中只允許6位數(shù)字（不能為字母）。2.靜態(tài)元組約束:

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

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

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

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

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

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

20 MARKETING DALLAS

FINANCE NEWYORKNotallowed(DEPTNO-20alreadyexists)Notallowed

(DEPTNOisnull)查詢主鍵信息：

sp_pkeystable_nameThePRIMARYKEYConstraint每個(gè)表只有一個(gè)PRIMARYKEY約束，PRIMARYKEY約束的值必須是唯一的，不允許有空值。如何區(qū)別UNIQUE和PRIMARYKEY的不同？[例4].分析下列兩個(gè)SQL語(yǔ)句判斷是否有錯(cuò)？CREATETABLET1(col1intNOTNULL,col2AScol1+1UNIQUE)====================CREATETABLET2(col1intNOTNULL,col2AScol1+1PRIMARYKEY)T2表無(wú)法創(chuàng)建，因?yàn)橐驗(yàn)橹麈I約束，所以SQLServer需要保證計(jì)算的結(jié)果不為NULL該技巧可以使用ISNULL()函數(shù)繞過對(duì)計(jì)算列的計(jì)算，如果計(jì)算結(jié)果為空，將應(yīng)用一個(gè)替換值：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）違約處理可能破壞參照完整性

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

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

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

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]NOACTION：任何企圖刪除或者更新被其他表的外鍵所引用的鍵都將引發(fā)一個(gè)錯(cuò)誤，對(duì)數(shù)據(jù)的改變會(huì)被回滾。NOACTION是默認(rèn)值CASCADE：若父表中的行變化了，則引用表中相應(yīng)的行也自動(dòng)變化［例5］顯式說(shuō)明參照完整性的違約處理示例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)表級(jí)約束級(jí)聯(lián)刪除SC表中相應(yīng)的元組*級(jí)聯(lián)更新SC表中相應(yīng)的元組當(dāng)刪除course表中的元組造成了與SC表不一致時(shí)拒絕刪除當(dāng)更新course表中的cno時(shí)，級(jí)聯(lián)更新SC表中相應(yīng)的元組測(cè)試：當(dāng)刪除course表中的元組造成了與SC表不一致時(shí)拒絕刪除當(dāng)更新course表中的cno時(shí)，級(jí)聯(lián)更新SC表中相應(yīng)的元組１.動(dòng)態(tài)列級(jí)約束:是修改列定義或列值時(shí)應(yīng)滿足的約束條件，包括兩方面：1.修改列定義時(shí)的約束

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

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

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

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

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

列級(jí)

元組級(jí)

關(guān)系級(jí)

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

元組值應(yīng)滿足的條件

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

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

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

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

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

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

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

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

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

實(shí)現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)相關(guān)完整性規(guī)則。觸發(fā)器可以對(duì)數(shù)據(jù)庫(kù)中相關(guān)的表進(jìn)行連環(huán)更新。例如，在auths表author_code列上的刪除觸發(fā)器可導(dǎo)致相應(yīng)刪除在其它表中的與之匹配的行。自動(dòng)計(jì)算數(shù)據(jù)值，如果數(shù)據(jù)的值達(dá)到了一定的要求，則進(jìn)行特定的處理。例如，如果公司的帳號(hào)上的資金低于5萬(wàn)元?jiǎng)t立即給財(cái)務(wù)人員發(fā)送警告數(shù)據(jù)。DELETED和INSERTED是SQLServer為觸發(fā)器創(chuàng)建的概念上的表。該表在結(jié)構(gòu)上同觸發(fā)器定義于其上的表相似，并且保存了可能被用戶的行動(dòng)改變的舊的或者新的行中的值。該表將跟蹤在Transact-SQL中的行一級(jí)的變化。當(dāng)一個(gè)INSERT、UPDATE、或者DELETE語(yǔ)句在SQLServer中執(zhí)行的時(shí)候，行被加入到觸發(fā)器表中，而且是同時(shí)加入到INSERTED和DELETED表中。INSERTED和DELETED表同觸發(fā)器表是一樣的。它們有同樣的列名和數(shù)據(jù)類型。例如，如果在GRADE表中放置一個(gè)觸發(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ā)器行動(dòng)。INSERTED表同INSERT和UPDATE語(yǔ)句一起使用。DELETED表則和DELETE以及UPDATE語(yǔ)句一起使用。UPDATE語(yǔ)句使用INSERTED和DELETED表，這是因?yàn)檫M(jìn)行UPDATE操作時(shí)，SQLServer總是要?jiǎng)h除舊的行，填入新的行。因此，執(zhí)行UPDATE時(shí)，INSERTED表中的行總是DELETED表中的行的副本。[例1].某學(xué)生退學(xué),將此學(xué)生從S表中刪除的同時(shí),將SC表中相關(guān)數(shù)據(jù)刪除.CREATETRIGGERTRI_S1ONSFORDELETEASDELETESCFROMSCA,DELETEDBWHEREA.STU_NO=B.STU_NO指定的數(shù)據(jù)行被用戶從基本表中刪除,然后轉(zhuǎn)移到DELETE表中[例2]:在SC表上建立一個(gè)觸發(fā)器,其作用是不允許對(duì)SCORE作修改CREATETRIGGERTRI_SC1ONSCFORUPDATEASIFUPDATE(SCORE)BEGINRAISERROR(‘不允許修改’,10,1)ROLLBACKTRANSACTIONEND[例3]在學(xué)生表創(chuàng)建一個(gè)afterupdate觸發(fā)器,當(dāng)在學(xué)生表上更新記錄時(shí)屏幕上顯示有多少條記錄被更新。CREATETRIGGERTRI_Stu2ONStudentAFTERUPDATEASdeclare@akchar(20)select@ak=str(@@rowcount,4)+'個(gè)記錄被更新'print@akreturnupdatestudentsetstu_age=20wherestu_no='20026102'[例4]定義一個(gè)觸發(fā)器：規(guī)定在星期六或星期日或者在某些方面8點(diǎn)至17點(diǎn)以外時(shí)間不能更新SC表系統(tǒng)檢查當(dāng)時(shí)的系統(tǒng)時(shí)間，如是周六或周日，或者不是8點(diǎn)至17點(diǎn)，系統(tǒng)會(huì)拒絕執(zhí)行用戶的更新操作，并提示出錯(cuò)信息CREATETRIGGERtri_scONStudentFORINSERT,UPDATE,DELETEASIF(datename(weekday,getdate())IN('星期六','星期日'))OR(datename(hh,getdate())NOTBETWEEN8AND16)BEGINprint‘不允許修改數(shù)據(jù)'

ROLLBACKTRANSACTIONEND[例5]、設(shè)計(jì)一個(gè)觸發(fā)器，要求在修改s表時(shí)，不允許修改后的年齡小于原來(lái)的年齡。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)建一個(gè)觸發(fā)器。無(wú)論何時(shí)訂購(gòu)產(chǎn)品（無(wú)論何時(shí)向OrderDetails表中插入一條記錄），這個(gè)觸發(fā)器都將更新Products表中的一列(UnitsInStock)。用原來(lái)的值減去訂購(gòu)的數(shù)量值即為新值。[例7]：將創(chuàng)建一個(gè)觸發(fā)器，無(wú)論何時(shí)刪除一個(gè)產(chǎn)品類別（即從Categories表中刪除一條記錄），該觸發(fā)器都會(huì)更新Products表中的Discontinued列。所有受影響的產(chǎn)品都標(biāo)記為1，標(biāo)示不再使用這些產(chǎn)品了。CREATETRIGGERCategory_DeleteONCategoriesFORDELETEASUPDATEPSETDiscontinued=1FROMProductsASPINNERJOINdeletedASdONP.CategoryID=d.CategoryID[例8]你在開發(fā)某公司的管理信息系統(tǒng)，其中要跟蹤經(jīng)理的購(gòu)買情況。每個(gè)經(jīng)理在預(yù)算表中都有一個(gè)記錄。預(yù)算表包含列“經(jīng)理代號(hào)”、列“預(yù)算總金額”、列“現(xiàn)有預(yù)算金額”；購(gòu)買表包含列“購(gòu)買代號(hào)”、列“購(gòu)買金額”，列“經(jīng)理代號(hào)”。每次購(gòu)買都要與“現(xiàn)有預(yù)算金額”作比較，當(dāng)該次購(gòu)買的“購(gòu)買金額”小于“現(xiàn)有預(yù)算金額”時(shí)，才允許插入到購(gòu)買表中（一次購(gòu)買只插入一個(gè)記錄），同時(shí)自動(dòng)更改預(yù)算表的“現(xiàn)有預(yù)算金額”。請(qǐng)?jiān)谫?gòu)買表上編寫一個(gè)觸發(fā)器，完成該任務(wù)。CREATETRIGGERPurchase_InsertON購(gòu)買表FORINSERTASBEGINIF判斷是否只插入一次BEGINRAISERROR('一次購(gòu)買只允許插入一個(gè)記錄！',16,1)ROLLBACKTRANSACTIONENDIF“現(xiàn)有預(yù)算金額”小于”購(gòu)買金額””BEGINRAISERROR('現(xiàn)有預(yù)算金額不足支付購(gòu)買金額！',16,1)ROLLBACKTRANSACTIONEND

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