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

第五章數(shù)據(jù)庫完整性§5.1實(shí)體完整性§5.2參照完整性§5.3用戶定義的完整性§5.4完整性約束命名子句§5.6觸發(fā)器數(shù)據(jù)庫完整性數(shù)據(jù)庫的完整性：指數(shù)據(jù)庫的正確性、有效性和相容性，其目的是防止錯誤的數(shù)據(jù)進(jìn)入數(shù)據(jù)庫即完整性體現(xiàn)是數(shù)據(jù)庫能否真實(shí)地反映現(xiàn)實(shí)世界。其中：正確性：指數(shù)據(jù)的合法性有效性：指數(shù)據(jù)是否屬于所定義域的有效范圍相容性：指表示同一事實(shí)的兩個數(shù)據(jù)應(yīng)當(dāng)一致，不一致即是不相容的完整性被破壞的原因：由更新操作不遵守完整性約束條件引起的(1)概念不同：數(shù)據(jù)庫的完整性：是為了防止數(shù)據(jù)庫中存在部分不符合語義的數(shù)據(jù)，防止錯誤信息的輸入和輸出。用以保證數(shù)據(jù)的正確、有效。防范對象是不合語義的數(shù)據(jù)。數(shù)據(jù)庫的安全性：是為了保護(hù)數(shù)據(jù)庫防止惡意的破壞和非法的存取。防范對象是非法用戶和非法操作。(2)聯(lián)系：

二者又是密切相關(guān)的，都是使得系統(tǒng)隨時可正常運(yùn)轉(zhuǎn)，防止機(jī)密信息泄露和破壞。數(shù)據(jù)安全性與完整性區(qū)別DBMS為維護(hù)數(shù)據(jù)庫的完整性所做的工作提供定義完整性約束條件的機(jī)制SQL標(biāo)準(zhǔn)使用DDL來描述完整性提供完整性檢查的方法DBMS中檢查數(shù)據(jù)是否滿足完整性約束條件的機(jī)制違約處理DBMS若發(fā)現(xiàn)用戶的操作違背了完整性約束條件，就采取措施進(jìn)行違約處理§5.1實(shí)體完整性關(guān)系模型的實(shí)體完整性在CREATETABLE中用PRIMARYKEY定義單屬性構(gòu)成的碼：列級約束條件表級約束條件多屬性構(gòu)成的碼：表級約束條件例1

將Student表中的Sno屬性定義為碼

CREATETABLEStudent(SnoCHAR(9),

SnameCHAR(20)NOTNULL,

SsexCHAR(2),SageSMALLINT,

SdeptCHAR(20)

);PRIMARYKEY(Sno)例2將SC表中的Sno,Cno屬性定義為碼

CREATETABLESC(SnoCHAR(9)NOTNULL,

CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno));列級定義主碼表級定義主碼表級定義主碼PRIMARYKEY(Sno)實(shí)體完整性檢查和違約處理用PRIMARY

KEY短語定義了關(guān)系的主碼之后，每當(dāng)用戶程序?qū)颈磉M(jìn)行插入或更新操作之前，RDBMS會對實(shí)體完整性進(jìn)行檢查：檢查主碼值是否唯一，如果不唯一則拒絕插入或修改檢查主碼的各個屬性是否為空，只要有一個為空就拒絕插入或修改檢查主碼值是否唯一的方法：全表掃描為主碼建立一個索引§5.2參照完整性關(guān)系模型的參照完整性在CREATETABLE中用FOREIGNKEY定義外碼，用REFERENCES指明外碼參照哪些表的主碼例3

定義SC中的參照完整性

CREATETABLESC(SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),/*在表級定義實(shí)體完整性*/FOREIGNKEY(Sno)REFERENCESStudent(Sno),

/*在表級定義參照完整性*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*在表級定義參照完整性*/);參照完整性檢查和違約處理對參照表和被參照表進(jìn)行增刪改操作時必須進(jìn)行檢查被參照表（Student）參照表（SC）違約處理可能破壞參照完整性插入元組拒絕可能破壞參照完整性修改外碼值拒絕刪除元組可能破壞參照完整性拒絕/級連刪除/設(shè)置為空值修改主碼值可能破壞參照完整性拒絕/級連刪除/設(shè)置為空值違約處理：拒絕執(zhí)行：不允許操作（系統(tǒng)默認(rèn)策略）級連操作：刪除或修改參照表中的所有造成不一致的元組設(shè)置為空值：將參照表中的所有造成不一致的元組的對應(yīng)屬性設(shè)置為空值(1)外碼允許取空值：

學(xué)生(學(xué)號，姓名，性別，專業(yè)號，年齡)

專業(yè)(專業(yè)號，專業(yè)名)

學(xué)生關(guān)系(參照)→專業(yè)關(guān)系(被參照)(2)外碼不允許取空值：

學(xué)生關(guān)系(被參照)←選課關(guān)系(參照)對于參照完整性，除應(yīng)該定義外碼，還應(yīng)定義外碼是否允許取空值。一般地，系統(tǒng)采用默認(rèn)方式，即拒絕。如果想讓系統(tǒng)采用其它方式則必須在建表之時顯式的說明外碼能否接受空值的問題修改修改例4

顯式說明參照完整性的違約處理示例

CREATETABLESC(SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),/*在表級定義實(shí)體完整性*/FOREIGNKEY(Sno)REFERENCESStudent(Sno)/*在表級定義參照完整性*/ONDELETECASCADE

/*當(dāng)刪除Student表中的元組時，級連刪除SC表中相應(yīng)的元組*/

ONUPDATECASCADE，

/*當(dāng)更新Student表中的Sno時，級連更新SC表中相應(yīng)的元組*/FOREIGNKEY(Cno)REFERENCESCourse(Cno),/*在表級定義參照完整性*/

ONDELETENOACTION

/*當(dāng)刪除Course表中的元組造成了與SC表不一致時拒絕刪除*/

ONUPDATECASCADE，

/*當(dāng)更新Course表中的Cno時，級連更新SC表中相應(yīng)的元組*/);§5.3用戶定義的完整性在CREATETABLE中定義屬性的同時可以根據(jù)應(yīng)用要求，定義屬性上的約束條件，即屬性值限制，包括：列值非空（NOT

NULL）列值唯一（UNIQUE）檢查列值是否滿足一個布爾表達(dá)式（CHECK短語）1.不允許取空值例5在定義SC表時，說明Sno、Cno、Grade屬性不允許取空值

CREATETABLESC(SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL,GradeSMALLINT

NOTNULL,PRIMARYKEY(Sno,Cno)/*在表級定義實(shí)體完整性*/);2.列值唯一例6建立部門表DEPT，要求部門名稱Dname列取值唯一，部門編號Deptno列為主碼

CREATETABLEDEPT(DeptnoNUMERIC(2)，

DnameCHAR(9)UNIQUE,

/*要求Dname列值唯一*/LocationCHAR(10),PRIMARYKEY(Deptno)/*在表級定義實(shí)體完整性*/);3.用CHECK短語指定列值應(yīng)該滿足的條件例7Student表的Ssex只允許取“男”和“女”。

CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，

SnameCHAR(8)NOTNULL,

SsexCHAR(2)CHECK(SsexIN(‘男’,‘女’)),SageSMALLINT,

SdeptCHAR(20));注：當(dāng)往表中插入元組或修改屬性的值時，RDBMS就會檢查屬性上的約束條件是否被滿足，如果不滿足則操作被拒絕元組上的條件的定義：在CREATE

TABLE語句中可以用CHECK短語定義元組上的約束，即元組級的限制。同屬性值限制相比，元組級的限制可以設(shè)置不同屬性之間的取值的相互約束條件例9當(dāng)學(xué)生的性別是男時，其名字不能以Ms.打頭

CREATETABLEStudent(SnoCHAR(9)，

SnameCHAR(8)NOTNULL,

SsexCHAR(2),SageSMALLINT,

SdeptCHAR(20)PRIMARYKEY(Sno)CHECK(Ssex=‘女’)ORSnameNOTLIKE‘Ms.%’

/*定義了元組中Sname和Ssex兩個屬性值之間的約束條件*/);當(dāng)往表中插入元組或修改屬性的值時，RDBMS就會檢查元組上的約束條件是否被滿足，如果不滿足則操作被拒絕完整性約束條件分類及含義粒度狀態(tài)屬性列級元組級關(guān)系表級靜態(tài)靜態(tài)屬性列級約束屬性列定義數(shù)據(jù)類型約束數(shù)據(jù)格式約束值域約束空值約束是規(guī)定一個元組的各個列值之間應(yīng)滿足的條件的約束是在一個關(guān)系的各個元組之間或若干個關(guān)系之間存在的各種聯(lián)系或約束實(shí)體完整性約束參照完整性約束函數(shù)依賴約束統(tǒng)計(jì)約束動態(tài)是修改列定義或列值時的約束是在修改某個元組時對元組的新舊值之間應(yīng)滿足的約束是對某個關(guān)系的新、舊狀態(tài)間應(yīng)滿足的約束條件SQL在CREATE

TABLE語句中提供了完整性約束命名子句CONSTRAINT，用來對完整性約束條件命名，從而可以靈活地增刪一個完整性約束條件1.完整性約束命名子句格式

CONSTRAINT＜完整性約束條件名＞[PRIMARYKEY短語|FOREIGNKEY|CHECK短語]例10

建立學(xué)生登記表Student，要求學(xué)號在90000~99999之間，姓名不能取空值，年齡小于30，性別只能是“男”或“女”CREATETABLEStudent(SnoNUMERIC(6)CONSTRAINTC1CHECK(SnoBETWEEN90000AND99999)，

SnameCHAR(20)CONSTRAINTC2NOTNULL,SageNUMERIC(3)CONSTRAINTC3CHECK(Sage<30),

SsexCHAR(2)CONSTRAINTC4CHECK(SsexIN(‘男’,’女’)),CONSTRAINTStudentKeyPRIMARYKEY(Sno));§5.4完整性約束命名子句例11

建立教師表TEACHER，要求每個教師的應(yīng)發(fā)工資不低于3000元。應(yīng)發(fā)工資實(shí)際上就是實(shí)發(fā)工資列Sal與扣除項(xiàng)Deduct之和

CREATETABLETEACHER(EnoNUMERIC(6)PRIMARYKEY,

EnameCHAR(10),JobCHAR(8),SalNUMERIC(7,2),DeductNUMERIC(7,2),

DeoptnoNUMERIC(7,2),CONSTRAINTEMPFKeyFOREIGNKEY(Deptno)REFERENCESDEPT(Deptno),CONSTRAINTC1CHECK(Sal+Deduct>=3000));外碼約束EMPFKey,列級約束C12.修改表中的完整性限制應(yīng)用ALTER

TABLE語句修改表中的完整性例12

去掉例10中對表Student中對性別的限制

ALTER

TABLE

StudentDROPCONSTRAINTC4;例13

修改表Student中的約束條件，要求學(xué)號改為在900000~999999之間，年齡由小于30改為小于40ALTER

TABLE

StudentDROPCONSTRAINTC1;ALTER

TABLE

StudentADDCONSTRAINTC1CHECK(SnoBETWEEN90000AND99999)；ALTER

TABLE

StudentDROPCONSTRAINTC3;ALTER

TABLE

StudentADDCONSTRAINTC3CHECK(Sage<40)；引入觸發(fā)器原因：在很多情況下，當(dāng)一個完整性約束被違背時，DBMS除了終止事務(wù)外，還需要執(zhí)行一些其他操作。為了在數(shù)據(jù)庫完整性約束被違背時能夠及時執(zhí)行必要的操作，人們提出了觸發(fā)器技術(shù)§5.6觸發(fā)器§5.6觸發(fā)器功能：主要功能：能夠?qū)崿F(xiàn)由主碼和外碼所不能保證的、復(fù)雜的參照完整性強(qiáng)化約束：強(qiáng)制實(shí)現(xiàn)業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性跟蹤變化：可以跟蹤數(shù)據(jù)庫內(nèi)的數(shù)據(jù)變化，阻止非法更新和刪除級聯(lián)運(yùn)行：可以監(jiān)測數(shù)據(jù)庫內(nèi)的操作，并自動將影響蔓延到多個表存儲過程的調(diào)用：為了響應(yīng)數(shù)據(jù)庫更新，觸發(fā)器可以調(diào)用一個或多個存儲過程，甚至可調(diào)用DBMS外的外部過程§5.6觸發(fā)器觸發(fā)器（Trigger）：是用戶定義在關(guān)系表上的一類由事件驅(qū)動的特殊過程，或者說是一個能由系統(tǒng)自動執(zhí)行對數(shù)據(jù)庫修改的語句，亦稱為主動規(guī)則一旦定義，任何用戶對表的增刪改操作均由服務(wù)器自動激活相應(yīng)的觸發(fā)器，在DBMS核心層進(jìn)行集中的完整性控制，它類似于約束，但較之約束更加靈活，具有更精細(xì)和更強(qiáng)大的數(shù)據(jù)控制能力觸發(fā)器結(jié)構(gòu)：一個觸發(fā)器由兩部分組成：（1）觸發(fā)事件：指對數(shù)據(jù)庫的更新等操作，在這些操作進(jìn)行時，觸發(fā)器將被激發(fā)。（2）觸發(fā)器條件和動作：條件準(zhǔn)則，以確定更新語句是否導(dǎo)致執(zhí)行觸發(fā)器動作。條件成立，則由DBMS執(zhí)行觸發(fā)器動作。這些動作可以是一系列對數(shù)據(jù)庫的操作觸發(fā)器的定義

CREATE

TRIGGER＜觸發(fā)器名＞｛BEFORE|AFTER|＜觸發(fā)事件＞ON＜表名＞

FOR

EACH｜ROW｜STATEMENT｝

[WHEN＜觸發(fā)條件＞]＜觸發(fā)動作體＞其中：1.表的擁有者即創(chuàng)建表的用戶才可以在表上創(chuàng)建觸發(fā)器，并且一個表上只能創(chuàng)建一定數(shù)量的觸發(fā)器2.觸發(fā)器名：可以包含模式名，也可以不包含模式名。同一模式下，觸發(fā)器名必須是唯一的；并且觸發(fā)器名和<表名>必須在同一模式下3.表名：當(dāng)這個表的數(shù)據(jù)發(fā)生變化時，將激活定義在該表相應(yīng)<觸發(fā)事件>的觸發(fā)器，該表也稱為觸發(fā)器的目標(biāo)表觸發(fā)器的定義

CREATE

TRIGGER＜觸發(fā)器名＞｛BEFORE|AFTER|＜觸發(fā)事件＞ON＜表名＞

FOR

EACH｜ROW｜STATEMENT｝

[WHEN＜觸發(fā)條件＞]＜觸發(fā)動作體＞4.觸發(fā)事件：可以是INSERT，DELETE或UPDATE，也可以是這幾個事件的組合。UPDATE后面還可以有OF<觸發(fā)列，…>，即進(jìn)一步指明修改哪些列時觸發(fā)器激活5.觸發(fā)器類型：行級觸發(fā)器和語句級觸發(fā)器6.觸發(fā)條件：只有當(dāng)觸發(fā)條件為真時，觸發(fā)動作才執(zhí)行，反之不執(zhí)行7.觸發(fā)動作體：可以是一個匿名PL/SQL過程塊，也可以是對已創(chuàng)建存儲過程的調(diào)用例18

定義一個BEFORE行級觸發(fā)器，為老師表Teacher定義完整性規(guī)則“教授的工資不得低于4000元，如果低于4000元，自動改為4000元”

CREATETRIGGERInsert_Or_Update_SalBEFOREINSERTORUPDATEONTeacherFOREACHROWASBEGIN

IF(new.pJob=‘教授’)AND(new.Sal<4000)THEN

new.Sal:=4000;ENDIF;END;例19

定義AFTER行級觸發(fā)，當(dāng)教師表Teacher的工資發(fā)生變化后就自動在工資變化表Sal_log中增加一條相應(yīng)記錄

CREATETABLESal_log(EnoNUMERIC(4)referenceteacher(eno),SalNUMERIC(7,2),Usernamechar(10),DateTIMESTAMP);

CREATETRIGGERInsert_SalAFTERINSERTONTeacherFOREACHROWASBEGININSERTINTOSal_log

VALUES(new.Eno,new.Sal,CURRENT_USER,CUR