數(shù)據(jù)庫系統(tǒng)概論第五章數(shù)據(jù)庫的完整性課件

數(shù)據(jù)庫系統(tǒng)概論AnIntroductiontoDatabaseSystem第五章數(shù)據(jù)庫完整性2/8/20231AnIntroductiontoDatabaseSystem數(shù)據(jù)庫完整性數(shù)據(jù)庫的完整性數(shù)據(jù)的正確性和相容性數(shù)據(jù)的完整性和安全性是兩個(gè)不同概念數(shù)據(jù)的完整性防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫中存在不正確的數(shù)據(jù)防范對(duì)象：不合語義的、不正確的數(shù)據(jù)數(shù)據(jù)的安全性保護(hù)數(shù)據(jù)庫防止惡意的破壞和非法的存取防范對(duì)象：非法用戶和非法操作2/8/20232AnIntroductiontoDatabaseSystem數(shù)據(jù)庫完整性(續(xù))為維護(hù)數(shù)據(jù)庫的完整性，DBMS必須：提供定義完整性約束條件的機(jī)制提供完整性檢查的方法違約處理2/8/20233AnIntroductiontoDatabaseSystem5.1實(shí)體完整性5.1.1實(shí)體完整性定義5.1.2實(shí)體完整性檢查和違約處理2/8/20235AnIntroductiontoDatabaseSystem5.1.1實(shí)體完整性定義關(guān)系模型的實(shí)體完整性CREATETABLE中用PRIMARYKEY定義單屬性構(gòu)成的碼有兩種說明方法定義為列級(jí)約束條件定義為表級(jí)約束條件對(duì)多個(gè)屬性構(gòu)成的碼只有一種說明方法定義為表級(jí)約束條件

2/8/20236AnIntroductiontoDatabaseSystem實(shí)體完整性定義(續(xù))(1)在列級(jí)定義主碼

CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(20)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20));［例1］將Student表中的Sno屬性定義為碼2/8/20237AnIntroductiontoDatabaseSystem實(shí)體完整性定義(續(xù))CREATETABLESC

(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，

PRIMARYKEY(Sno，Cno)/*只能在表級(jí)定義主碼*/);［例2］將SC表中的Sno，Cno屬性組定義為碼2/8/20239AnIntroductiontoDatabaseSystem5.1實(shí)體完整性5.1.1實(shí)體完整性定義5.1.2實(shí)體完整性檢查和違約處理2/8/202310AnIntroductiontoDatabaseSystem5.1.2實(shí)體完整性檢查和違約處理插入或?qū)χ鞔a列進(jìn)行更新操作時(shí)，RDBMS按照實(shí)體完整性規(guī)則自動(dòng)進(jìn)行檢查。包括：檢查主碼值是否唯一，如果不唯一則拒絕插入或修改檢查主碼的各個(gè)屬性是否為空，只要有一個(gè)為空就拒絕插入或修改2/8/202311AnIntroductiontoDatabaseSystem實(shí)體完整性檢查和違約處理(續(xù))另一個(gè)是索引（如B+樹索引）51719312255166717993371215202530415154656668697184937678792/8/202313AnIntroductiontoDatabaseSystem第五章數(shù)據(jù)庫完整性5.1實(shí)體完整性5.2參照完整性5.3用戶定義的完整性5.4完整性約束命名子句*5.5域中的完整性限制5.6觸發(fā)器5.7小結(jié)2/8/202314AnIntroductiontoDatabaseSystem5.2參照完整性5.2.1參照完整性定義5.2.2參照完整性檢查和違約處理2/8/202315AnIntroductiontoDatabaseSystem參照完整性定義(續(xù))［例3］定義SC中的參照完整性

CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，

PRIMARYKEY(Sno，Cno)，/*在表級(jí)定義實(shí)體完整性*/

FOREIGNKEY(Sno)REFERENCESStudent(Sno)，/*在表級(jí)定義參照完整性*/

FOREIGNKEY(Cno)REFERENCESCourse(Cno)

/*在表級(jí)定義參照完整性*/);例如，關(guān)系SC中一個(gè)元組表示一個(gè)學(xué)生選修的某門課程的成績，（Sno，Cno）是主碼。Sno，Cno分別參照引用Student表的主碼和Course表的主碼2/8/202317AnIntroductiontoDatabaseSystem5.2參照完整性5.2.1參照完整性定義5.2.2參照完整性檢查和違約處理2/8/202318AnIntroductiontoDatabaseSystem參照完整性檢查和違約處理可能破壞參照完整性的情況及違約處理被參照表（例如Student）參照表（例如SC）違約處理可能破壞參照完整性插入元組拒絕可能破壞參照完整性修改外碼值拒絕刪除元組可能破壞參照完整性拒絕/級(jí)連刪除/設(shè)置為空值修改主碼值可能破壞參照完整性拒絕/級(jí)連修改/設(shè)置為空值2/8/202319AnIntroductiontoDatabaseSystem違約處理(續(xù))CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，PRIMARYKEY（Sno，Cno）， FOREIGNKEY(Sno)REFERENCESStudent(Sno) ONDELETECASCADE/*級(jí)聯(lián)刪除SC表中相應(yīng)的元組*/ ONUPDATECASCADE，/*級(jí)聯(lián)更新SC表中相應(yīng)的元組*/FOREIGNKEY(Cno)REFERENCESCourse(Cno) ONDELETENOACTION /*當(dāng)刪除course表中的元組造成了與SC表不一致時(shí)拒絕刪除*/ ONUPDATECASCADE /*當(dāng)更新course表中的cno時(shí)，級(jí)聯(lián)更新SC表中相應(yīng)的元組*/)；［例4］顯式說明參照完整性的違約處理示例2/8/202321AnIntroductiontoDatabaseSystem第五章數(shù)據(jù)庫完整性5.1實(shí)體完整性5.2參照完整性5.3用戶定義的完整性5.4完整性約束命名子句*5.5域中的完整性限制5.6觸發(fā)器5.7小結(jié)2/8/202322AnIntroductiontoDatabaseSystem5.3用戶定義的完整性用戶定義的完整性就是針對(duì)某一具體應(yīng)用的數(shù)據(jù)必須滿足的語義要求RDBMS提供，而不必由應(yīng)用程序承擔(dān)2/8/202323AnIntroductiontoDatabaseSystem5.3.1屬性上的約束條件的定義CREATETABLE時(shí)定義列值非空（NOTNULL）列值唯一（UNIQUE）檢查列值是否滿足一個(gè)布爾表達(dá)式（CHECK）2/8/202325AnIntroductiontoDatabaseSystem屬性上的約束條件的定義(續(xù))不允許取空值

［例5］在定義SC表時(shí)，說明Sno、Cno、Grade屬性不允許取空值。CREATETABLESC（SnoCHAR(9)NOTNULL， CnoCHAR(4)NOTNULL， GradeSMALLINTNOTNULL， PRIMARYKEY(Sno，Cno)，/*如果在表級(jí)定義實(shí)體完整性，隱含了Sno，Cno不允許取空值，則在列級(jí)不允許取空值的定義就不必寫了*/）；2/8/202326AnIntroductiontoDatabaseSystem5.3用戶定義的完整性5.3.1屬性上的約束條件的定義5.3.2屬性上的約束條件檢查和違約處理

5.3.3元組上的約束條件的定義5.3.4元組上的約束條件檢查和違約處理2/8/202329AnIntroductiontoDatabaseSystem5.3.2屬性上的約束條件檢查和違約處理插入元組或修改屬性的值時(shí)，RDBMS檢查屬性上的約束條件是否被滿足如果不滿足則操作被拒絕執(zhí)行

2/8/202330AnIntroductiontoDatabaseSystem5.3用戶定義的完整性5.3.1屬性上的約束條件的定義5.3.2屬性上的約束條件檢查和違約處理5.3.3元組上的約束條件的定義

5.3.4元組上的約束條件檢查和違約處理2/8/202331AnIntroductiontoDatabaseSystem5.3.3元組上的約束條件的定義在CREATETABLE時(shí)可以用CHECK短語定義元組上的約束條件，即元組級(jí)的限制同屬性值限制相比，元組級(jí)的限制可以設(shè)置不同屬性之間的取值的相互約束條件

2/8/202332AnIntroductiontoDatabaseSystem元組上的約束條件的定義(續(xù))CREATETABLEStudent

(SnoCHAR(9)，SnameCHAR(8)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)，PRIMARYKEY(Sno)，

CHECK(Ssex='女'ORSnameNOTLIKE'Ms.%')/*定義了元組中Sname和Ssex兩個(gè)屬性值之間的約束條件*/)；性別是女性的元組都能通過該項(xiàng)檢查，因?yàn)镾sex=‘女’成立；當(dāng)性別是男性時(shí)，要通過檢查則名字一定不能以Ms.打頭［例9］當(dāng)學(xué)生的性別是男時(shí)，其名字不能以Ms.打頭。2/8/202333AnIntroductiontoDatabaseSystem5.3用戶定義的完整性5.3.1屬性上的約束條件的定義5.3.2屬性上的約束條件檢查和違約處理5.3.3元組上的約束條件的定義5.3.4元組上的約束條件檢查和違約處理2/8/202334AnIntroductiontoDatabaseSystem5.3.4元組上的約束條件檢查和違約處理插入元組或修改屬性的值時(shí)，RDBMS檢查元組上的約束條件是否被滿足如果不滿足則操作被拒絕執(zhí)行

2/8/202335AnIntroductiontoDatabaseSystem第五章數(shù)據(jù)庫完整性5.1實(shí)體完整性5.2參照完整性5.3用戶定義的完整性5.4完整性約束命名子句*5.5域中的完整性限制5.6觸發(fā)器5.7小結(jié)2/8/202336AnIntroductiontoDatabaseSystem5.4完整性約束命名子句CONSTRAINT約束CONSTRAINT<完整性約束條件名>［PRIMARYKEY短語|FOREIGNKEY短語|CHECK短語］2/8/202337AnIntroductiontoDatabaseSystem完整性約束命名子句(續(xù))CREATETABLEStudent(SnoNUMERIC(6)CONSTRAINTC1CHECK(SnoBETWEEN90000AND99999)，SnameCHAR(20)CONSTRAINTC2NOTNULL，SageNUMERIC(3)CONSTRAINTC3CHECK(Sage<30)，SsexCHAR(2)CONSTRAINTC4CHECK(SsexIN('男'，'女'))，CONSTRAINTStudentKeyPRIMARYKEY(Sno))；在Student表上建立了5個(gè)約束條件，包括主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個(gè)列級(jí)約束。［例10］建立學(xué)生登記表Student，要求學(xué)號(hào)在90000~99999之間，姓名不能取空值，年齡小于30，性別只能是“男”或“女”。2/8/202338AnIntroductiontoDatabaseSystem完整性約束命名子句(續(xù))修改表中的完整性限制使用ALTERTABLE語句修改表中的完整性限制2/8/202339AnIntroductiontoDatabaseSystem完整性約束命名子句(續(xù))可以先刪除原來的約束條件，再增加新的約束條件

ALTERTABLEStudentDROPCONSTRAINTC1;

ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN900000AND999999);

ALTERTABLEStudentDROPCONSTRAINTC3;

ALTERTABLEStudentADDCONSTRAINTC3CHECK(Sage<40)；［例13］修改表Student中的約束條件，要求學(xué)號(hào)改為在900000~999999之間，年齡由小于30改為小于402/8/202340AnIntroductiontoDatabaseSystem第五章數(shù)據(jù)庫完整性5.1實(shí)體完整性5.2參照完整性5.3用戶定義的完整性5.4完整性約束命名子句*5.5域中的完整性限制5.6觸發(fā)器5.7小結(jié)2/8/202341AnIntroductiontoDatabaseSystem5.5域中的完整性限制SQL支持域的概念，并可以用CREATEDOMAIN語句建立一個(gè)域以及該域應(yīng)該滿足的完整性約束條件。［例14］建立一個(gè)性別域，并聲明性別域的取值范圍［例15］建立一個(gè)性別域GenderDomain，并對(duì)其中的限制命名CREATEDOMAINGenderDomainCHAR(2)CHECK(VALUEIN('男'，'女'));這樣［例10］中對(duì)Ssex的說明可以改寫為SsexGenderDomainCREATEDOMAINGenderDomainCHAR(2)

CONSTRAINTGDCHECK(VALUEIN('男'，'女'));2/8/202342AnIntroductiontoDatabaseSystem域中的完整性限制(續(xù))ALTERDOMAINGenderDomainDROPCONSTRAINTGD;［例16］刪除域GenderDomain的限制條件GD。

ALTERDOMAINGenderDomainADDCONSTRAINTGDDCHECK(VALUEIN('1'，'0'));通過［例16］和［例17］，就把性別的取值范圍由('男'，'女')改為('1'，'0')［例17］在域GenderDomain上增加限制條件GDD。2/8/202343AnIntroductiontoDatabaseSystem第五章數(shù)據(jù)庫完整性5.1實(shí)體完整性5.2參照完整性5.3用戶定義的完整性5.4完整性約束命名子句*5.5域中的完整性限制5.6觸發(fā)器5.7小結(jié)2/8/202344AnIntroductiontoDatabaseSystem觸發(fā)器觸發(fā)器（Trigger）是用戶定義在關(guān)系表上的一類由事件驅(qū)動(dòng)的特殊過程由服務(wù)器自動(dòng)激活可以進(jìn)行更為復(fù)雜的檢查和操作，具有更精細(xì)和更強(qiáng)大的數(shù)據(jù)控制能力

2/8/202345AnIntroductiontoDatabaseSystem5.6觸發(fā)器5.6.1定義觸發(fā)器

5.6.2激活觸發(fā)器

5.6.3刪除觸發(fā)器

2/8/202346AnIntroductiontoDatabaseSystem5.6.1定義觸發(fā)器CREATETRIGGER語法格式

CREATETRIGGER<觸發(fā)器名>{BEFORE|AFTER}<觸發(fā)事件>ON<表名>FOREACH{ROW|STATEMENT}［WHEN<觸發(fā)條件>］<觸發(fā)動(dòng)作體>2/8/202347AnIntroductiontoDatabaseSystem定義觸發(fā)器(續(xù))定義觸發(fā)器的語法說明:創(chuàng)建者：表的擁有者觸發(fā)器名表名：觸發(fā)器的目標(biāo)表觸發(fā)事件：INSERT、DELETE、UPDATE2/8/202348AnIntroductiontoDatabaseSystem定義觸發(fā)器(續(xù))觸發(fā)器類型行級(jí)觸發(fā)器（FOREACHROW）語句級(jí)觸發(fā)器（FOREACHSTATEMENT）例如,假設(shè)在［例11］的TEACHER表上創(chuàng)建了一個(gè)AFTERUPDATE觸發(fā)器。如果表TEACHER有1000行，執(zhí)行如下語句：

UPDATETEACHERSETDeptno=5;

如果該觸發(fā)器為語句級(jí)觸發(fā)器，那么執(zhí)行完該語句后，觸發(fā)動(dòng)作只發(fā)生一次如果是行級(jí)觸發(fā)器，觸發(fā)動(dòng)作將執(zhí)行1000次2/8/202349AnIntroductiontoDatabaseSystem定義觸發(fā)器(續(xù))

觸發(fā)條件觸發(fā)條件為真省略WHEN觸發(fā)條件

觸發(fā)動(dòng)作體觸發(fā)動(dòng)作體可以是一個(gè)匿名PL/SQL過程塊也可以是對(duì)已創(chuàng)建存儲(chǔ)過程的調(diào)用2/8/202350AnIntroductiontoDatabaseSystem定義觸發(fā)器(續(xù))CREATETRIGGERInsert_Or_Update_SalBEFOREINSERTORUPDATEONTeacher/*觸發(fā)事件是插入或更新操作*/FOREACHROW/*行級(jí)觸發(fā)器*/ASBEGIN/*定義觸發(fā)動(dòng)作體，是PL/SQL過程塊*/IF(new.Job='教授')AND(new.Sal<4000)THENnew.Sal:=4000;ENDIF;END; ［例18］定義一個(gè)BEFORE行級(jí)觸發(fā)器，為教師表Teacher定義完整性規(guī)則“教授的工資不得低于4000元，如果低于4000元，自動(dòng)改為4000元”。2/8/202351AnIntroductiontoDatabaseSystem定義觸發(fā)器(續(xù))首先建立工資變化表Sal_log

CREATETABLESal_log(EnoNUMERIC(4)referencesteacher(eno)，SalNUMERIC(7，2)，Usernamechar(10)，DateTIMESTAMP)；［例19］定義AFTER行級(jí)觸發(fā)器，當(dāng)教師表Teacher的工資發(fā)生變化后就自動(dòng)在工資變化表Sal_log中增加一條相應(yīng)記錄2/8/202352AnIntroductiontoDatabaseSystem定義觸發(fā)器(續(xù))[例19]（續(xù)）CREATETRIGGERInsert_Sal

AFTERINSERTONTeacher /*觸發(fā)事件是INSERT*/FOREACHROWASBEGININSERTINTOSal_logVALUES(new.Eno，new.Sal，CURRENT_USER，CURRENT_TIMESTAMP);END;CREATETRIGGERUpdate_Sal

AFTERUPDATEONTeacher /*觸發(fā)事件是UPDATE*/FOREACHROWASBEGINIF(new.Sal<>old.Sal)THENINSERTINTOSal_logVALUES(

new.Eno，new.Sal，CURRENT_USER，CURRENT_TIMESTAMP);ENDIF;END;2/8/202353AnIntroductiontoDatabaseSystem5.6觸發(fā)器5.6.1