廈門大學計算機科學系2016版

第五章數據庫完整性

（2016版）

廈門大學計算機科學系本科生課程《數據庫系統原理》林子雨廈門大學計算機科學系E-mail:ziyulin@主頁：/linziyu第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶自定義完整性5.4完整性約束命名子句5.6觸發器5.7小結綜合統一（操縱三級模式）SQL視圖1視圖2基表1基表2基表3基表4存儲文件1存儲文件2外模式模式內模式數據安全數據完整性什么是數據庫的完整性？數據的正確性和相容性防止不合語義的數據進入數據庫。例:學生的年齡必須是整數，取值范圍為14--29；學生的性別只能是男或女；學生的學號一定是唯一的；學生所在的系必須是學校開設的系；完整性：是否真實地反映現實世界什么是完整性控制機制？1.完整性約束條件定義機制2.完整性檢查機制3.違約處理1、完整性約束條件定義完整性約束條件：數據模型的組成部分,約束數據庫中數據的語義DBMS應提供定義數據庫完整性約束條件，并把它們作為模式的一部分存入數據庫中由SQL的DDL語句實現2、完整性檢查機制檢查用戶發出的操作請求是否違背了完整性約束條件在INSERT、UPDATE、DELETE語句執行時進行檢查3、違約處理如果發現用戶的操作請求使數據違背了完整性約束條件，則采取一定的動作來保證數據的完整性。拒絕(NOACTION)、級連(CASCADE)1.完整性約束條件作用的對象列：對屬性的取值類型、范圍、精度等的約束條件元組：對元組中各個屬性列間的聯系的約束關系：對若干元組間、關系集合上以及關系之間的聯系的約束2.完整性約束條件分類靜態對靜態對象的約束是反映數據庫狀態合理性的約束動態對動態對象的約束是反映數據庫狀態變遷的約束第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶自定義完整性5.4完整性約束命名子句5.6觸發器5.7小結5.1實體完整性定義實體完整性：實體完整性規則（EntityIntegrity）若屬性A是基本關系R的主屬性，則屬性A不能取空值在CREATETABLE語句中提供了PRIMARYKEY子句，供用戶在建表時指定關系的主碼列。在列級使用PRIMARYKEY子句在表級使用PRIMARYKEY子句

5.1實體完整性定義例1：在學生選課數據庫中，要定義Student表的Sno屬性為主碼CREATETABLEStudent(SnoCHAR(5)primarykey,SnameVARCHAR(10),SsexCHAR(2),SageINT,SdeptCHAR(2));

5.1實體完整性定義例2：要在SC表中定義(Sno,Cno)為主碼CREATETABLESC(SnoCHAR(5),CnoCHAR(1),GradeINT,primarykey(Sno,Cno));5.1實體完整性定義用戶程序對主碼列進行更新操作時，系統自動進行完整性檢查違約操作使主屬性值為空值的操作使主碼值在表中不唯一的操作違約反應系統拒絕此操作，從而保證了實體完整性5.1實體完整性定義第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶自定義完整性5.4完整性約束命名子句5.6觸發器5.7小結參照完整性：若屬性（或屬性組）F是基本關系R的外碼，它與基本關系S的主碼Ks相對應（基本關系R和S不一定是不同的關系），則對于R中每個元組在F上的值必須為：或者取空值（F的每個屬性值均為空值）或者等于S中某個元組的主碼值。5.2參照完整性規則例：職工－部門數據庫包含職工表EMP和部門表DEPT1)DEPT關系的主碼為部門號Deptno2)EMP關系的主碼為職工號Empno，外碼為部門號Deptno

稱DEPT為被參照關系或目標關系，EMP為參照關系

RDBMS執行參照完整性時需要考慮以下4方面：5.2參照完整性規則1、外碼是否可以接受空值的問題外碼是否能夠取空值：依賴于應用環境的語義實現參照完整性：系統提供定義外碼的機制定義外碼列是否允許空值的機制1、外碼是否可以接受空值的問題（續）例1：在職工－部門數據庫中，

EMP關系包含有外碼Deptno

某元組的這一列若為空值，表示這個職工尚未分配到任何具體的部門工作和應用環境的語義是相符1、外碼是否可以接受空值的問題（續）例2：學生－選課數據庫

Student關系為被參照關系，其主碼為Sno。

SC為參照關系，外碼為Sno。若SC的Sno為空值：表明尚不存在的某個學生，或者某個不知學號的學生，選修了某門課程，其成績記錄在Grade中，與學校的應用環境是不相符的，因此SC的Sno列不能取空值。2、在被參照關系中刪除元組時的問題出現違約操作的情形：

刪除被參照關系的某個元組（student）而參照關系有若干元組(SC)的外碼值與被刪除的被參照關系的主碼值相同2、在被參照關系中刪除元組時的問題（續）違約反應：可有三種策略受限刪除（NOACTION）級聯刪除（CASCADE）置空值刪除（NULLIFIES） 這三種處理方法，哪一種是正確的，要依應用環境的語義來定2、在被參照關系中刪除元組時的問題（續）受限刪除當參照關系中沒有任何元組的外碼值與要刪除的被參照關系的元組的主碼值相對應時，系統才執行刪除操作，否則拒絕此刪除操作CREATETABLESC(SnoCHAR(5)foreignkeyreferencesStudent(Sno),CnoCHAR(1),GradeINT);2、在被參照關系中刪除元組時的問題（續）級聯刪除將參照關系中外碼值與被參照關系中要刪除元組主碼值相對應的元組一起刪除CREATETABLESC(SnoCHAR(5)foreignkeyreferencesStudent(Sno)ONDELETECASCADE,CnoCHAR(1),GradeINT);2、在被參照關系中刪除元組時的問題（續）置空值刪除刪除被參照關系的元組，并將參照關系中與被參照關系中被刪除元組主碼值相等的外碼值置為空值。3、在參照關系中插入元組時的問題出現違約操作的情形需要在參照關系中插入元組，而被參照關系不存在相應的元組違約反應受限插入遞歸插入

3、在參照關系中插入元組時的問題（續）受限插入僅當被參照關系中存在相應的元組，其主碼值與參照關系插入元組的外碼值相同時，系統才執行插入操作，否則拒絕此操作。遞歸插入首先向被參照關系中插入相應的元組，其主碼值等于參照關系插入元組的外碼值，然后向參照關系插入元組。3、在參照關系中插入元組時的問題（續）例：向SC關系插入（99001，1，90）元組，而Student關系中尚沒有Sno=99001的學生受限插入：系統將拒絕向SC關系插入（99001，1，90）元組遞歸插入：系統將首先向Student關系插入Sno=99001的元組，然后向SC關系插入（99001，1，90）元組。結論：參照完整性的執行RDBMS在執行參照完整性時:需要向用戶提供定義主碼、外碼的機制向用戶提供按照自己的應用要求選擇處理依賴關系中對應的元組的方法第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶自定義完整性5.4完整性約束命名子句5.6觸發器5.7小結1.用CREATETABLE語句在建表時定義用戶完整性約束可定義三類完整性約束列值非空（NOTNULL短語）列值唯一（UNIQUE短語）檢查列值是否滿足一個布爾表達式（CHECK短語）5.3用戶定義的完整性5.3用戶定義的完整性約束命名Constraint<約束名>[Primarykey…|ForeignKey…|Check

…]修改約束AlterTable<表名>

Add|DropConstraint<約束名>5.3用戶定義的完整性第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶自定義完整性5.4完整性約束命名子句5.6觸發器5.7小結5.4完整性約束命名子句SQL還在CREATETABLE語句中提供了完整性約束命名子句CONSTRAINT，用來對完整性約束條件命名。從而可以靈活地增加、刪除一個完整性約束條件。1、完整性約束命名子句CONSTRAINT<完整性約束條件名>[PRIMARYKEY短語|FOREIGNKEY短語|CHECK短語]2、修改表中的完整性限制使用ALTERTABLE語句修改表中的完整性限制第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶自定義完整性5.4完整性約束命名子句5.6觸發器5.7小結5.6觸發器通過觸發器來定義復雜的完整性規則定義其它的完整性約束時，需要用數據庫觸發器（Trigger）來實現。數據庫觸發器：一類靠事務驅動的特殊過程一旦由某個用戶定義，任何用戶對該數據的增、刪、改操作均由服務器自動激活相應的觸發子，在核心層進行集中的完整性控制定義數據庫觸發器的語句

CREATETRIGGER<觸發器名>ON<表名>FOR{INSERT|DELETE|UPDATE}AS<觸發動作體>刪除DROPTRIGGER<觸發器名>5.6觸發器5.6觸發器理解觸發器里面的兩個臨時的表：Deleted,InsertedDeleted與Inserted分別表示觸發事件的表“舊的一條記錄”和“新的一條記錄”一個數據庫系統中有兩個虛擬表用于存儲在表中記錄改動的信息5.6觸發器虛擬表Inserted

虛擬表Deleted在表記錄新增時

存放新增的記錄

不存儲記錄

修改時

存放用來更新的新記錄

存放更新前的記錄

刪除時

不存儲記錄

存放被刪除的記錄例.學生成績不低于60分,低于60分自動賦為60分CreateTriggerchggradeonSCforinsertasupdateSCsetgrade=60whereexists(select*frominsertedwhereinserted.Sno=SC.Snoandinserted.Cno=SC.Cnoandinserted.grade<60)5.6觸發器課堂作業計算機（sdept為’CS’）學生成績不低于60分,低于60分自動賦為60分課堂作業createtriggerT1onscforinsertasupdatescsetgrade=60whereexists(select*fromstudentwheresdept='CS'andstudent.sno=sc.snoandexists(select*frominsertedwhereinserted.sno=sc.snoando=oandinserted.grade<60))計算機（sdept為’CS’）學生成績不低于60分,低于60分自動賦為60分例.數學是必修課，所有學生都必須選，學生退學則將成績置0createTriggerselcouonstudentforinsertasinsertintoSC(Sno,Cno)selectSno,'2'frominsertedcreateTriggersleftonstudentfordeleteasupdateSCsetGrade=0whereSnoin(selectSnofromdeleted)5.6觸發器5.6觸發器例子：創建觸發器，當更新student表中的學號時，也同時更新sc表中的學號5.6觸發器createtriggerT2onstudentforupdateasifupdate(Sno)beginupdatescsetsc.sno=i.snofromsc,insertedi,deleteddwheresc.sno=d.snoend創建觸發器，當更新student表中的學號時，也同時更新sc表中的學號5.6觸發器例子：創建一個觸發器，當刪除student表中的某條記錄時，也同時刪除sc表中的記錄5.6觸發器創建一個觸發器，當刪除student表中的某條記錄時，也同時刪除sc表中的記錄createtriggerT3onstudentfordeleteasdeletescfromsc,deleteddwheresc.sno=d.sno5.7斷言使用斷言（assertion）來指定更具一般性地約束可以定義涉及多個表或聚集操作的比較復雜的完整性約束任何對斷言中所涉及關系的操作都會出發關系數據庫管理系統對斷言的檢查任何使得斷言不為真的操作都會被拒絕執行5.7斷言語句格式：createassertion<斷言名><check子句>5.7斷言例：限制數據庫課程最多60名學生選修createassertionasse_sc_db_numcheck(60>=(selectcount(*)fromcourse,scWhereSo=oandame=‘數據庫’))5.7斷言例：限制每一門課程最多60名學生選修createassertionasse_sc_cnum1Check(60>=ALL(selectcount(*)fromscgroupbycno))5.7斷言例：限制每個學期每一門課最多60名學生選修修改sc表，增加一個“學期(TERM)”屬性Createassertionasse_sc_cnum2Check(60>=ALL(selectcount(*)fromscgroupbycno,term))第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶自定義完整性5.4完整性約束命名子句5.6觸發器5.7小結5.7小結數據庫的完整性是為了保證數據庫中存儲的數據是正確的，所謂正確的是指符合現實世界語義的。DBMS完整性實現的機制完整性約束定義機制完整性檢查機制違背完整性約束條件時DBMS應采取的動作5.7小結完整性機制的實施會極大地影響系統性能不同的數據庫產品對完整性的支持策略和支持程度是不同的許多數據庫管理系統對完整性機制的支持比對安全性的支持要晚得多也弱得多數據庫廠商對完整性的支持越來越好，不僅在能保證實體完整性和參照完整性而且能在DBMS核心定義、檢查和保證用戶定義的完整性約束條件第5版教材第173頁第6題作業附錄：本章常用SQL語句altertablestudentaltercolumnsnochar(5)notnullaltertablestudentaddprimarykey(Sno)altertablescaltercolumnsnochar(5)notnullaltertablescaddforeignkey(Sno)referencesstudent(sno)altertablecoursealtercolumnCnochar(1)notnullaltertablecourseaddprimarykey(Cno)altertablescaddforeignkey(Cno)referencescourse(Cno)execsp_helpconstraintcoursesp_helpconstraintstudentaltertablescdropconstraintFK__SC__Sno__07020F21altertablescaddconstraintFK1foreignkey(Sno)referencesstudent(Sno)altertablescaddconstraintFK2foreignkey(Cno)referencescourse(Cno)altertablestudentaddconstraintPK1primarykey(Sno)altertablestudentdropconstraintPK1附錄：主講教師單位：廈門大學計算機科學系E-mail:ziyulin@個人網頁：/linziyu數據庫實驗室網站：主講教師：林子雨林子雨，男，1978年出生，博士（畢業于北京大學），現為廈門大學計算機科學系助理教授（講師），曾任廈門大學信息科學與技術學院院長