南京航空航天大學信息學院計算機系

1、An Introduction to Database System,南京航空航天大學信息學院計算機系,數(shù)據(jù)庫系統(tǒng)概論An Introduction to Database System第十章 數(shù)據(jù)庫完整性,An Introduction to Database System,第十章 數(shù)據(jù)庫完整性,什么是數(shù)據(jù)庫的完整性數(shù)據(jù)的正確性和相容性防止不合語義的數(shù)據(jù)進入數(shù)據(jù)庫。例: 學生的年齡必須是整數(shù)，取值范圍為14--29

2、； 學生的性別只能是男或女； 學生的學號一定是唯一的； 學生所在的系必須是學校開設的系；完整性：否真實地反映現(xiàn)實世界,An Introduction to Database System,完整性控制機制,1.完整性約束條件定義機制2.完整性檢查機制3.違約反應,An Introduction to Database System,完整性約束條件定義,完整性約束條件：數(shù)據(jù)模型的組成部分約束

3、數(shù)據(jù)庫中數(shù)據(jù)的語義DBMS應提供定義數(shù)據(jù)庫完整性約束條件，并把它們作為模式的一部分存入數(shù)據(jù)庫中,An Introduction to Database System,完整性控制機制,檢查用戶發(fā)出的操作請求是否違背了完整性約束條件,An Introduction to Database System,違約反應,如果發(fā)現(xiàn)用戶的操作請求使數(shù)據(jù)違背了完整性約束條件，則采取一定的動作來保證數(shù)據(jù)的完整性。,An Introduction to D

4、atabase System,第十章 數(shù)據(jù)庫完整性,10.1 完整性約束條件10.2 完整性控制10.3 Oracle的完整性10.4 小結,An Introduction to Database System,完整性約束條件（續(xù)）,完整性約束條件作用的對象列：對屬性的取值類型、范圍、精度等的約束條件元組：對元組中各個屬性列間的聯(lián)系的約束關系：對若干元組間、關系集合上以及關系之間的聯(lián)系的約束,An Introduc

5、tion to Database System,完整性約束條件（續(xù)）,靜態(tài)對靜態(tài)對象的約束是反映數(shù)據(jù)庫狀態(tài)合理性的約束動態(tài)對動態(tài)對象的約束是反映數(shù)據(jù)庫狀態(tài)變遷的約束,An Introduction to Database System,完整性約束條件分類,六類完整性約束條件 靜態(tài)列級約束 靜態(tài)元組約束 靜態(tài)關系約束 動態(tài)列級約束 動態(tài)元組約束 動態(tài)關系約束,An Introduction to

6、Database System,完整性約束條件（續(xù)）,對象狀態(tài) 動態(tài)列級約束 動態(tài)元組約束 動態(tài)關系約束 動態(tài) ④ ⑤ ⑥

7、 靜態(tài)列級約束 靜態(tài)元組約束 靜態(tài)關系約束 靜態(tài) ① ② ③   列 元組

8、關系 對象粒度,An Introduction to Database System,完整性約束條件（續(xù)）,1. 靜態(tài)列級約束靜態(tài)列級約束：對的取值域的說明最常見、最簡單、最容易實現(xiàn)的一類完整性約束,An Introduction to Database System,完整性約束條件（續(xù)）,五類靜態(tài)列級約束1) 數(shù)據(jù)類型約束：數(shù)據(jù)的類型、長度、單位、精度等 例：學生姓名的數(shù)據(jù)類型為字符型，長度為82)

9、對數(shù)據(jù)格式的約束例： 學號：前兩位表示入學年份，后四位為順序編號 日期：YY.MM.DD。,An Introduction to Database System,完整性約束條件（續(xù)）,3) 取值范圍或取值集合的約束例：規(guī)定成績的取值范圍為0-100 年齡的取值范圍為14-29 性別的取值集合為[男,女]4) 對空值的約束 空值：未定義或未知的值 空值

10、：與零值和空格不同 有的列允許空值，有的則不允許，如成績可為空值5) 其他約束例：關于列的排序說明，組合列等,An Introduction to Database System,完整性約束條件（續(xù)）,2. 靜態(tài)元組約束 規(guī)定元組的各個列之間的約束關系例：訂貨關系中發(fā)貨量=700元 靜態(tài)元組約束只局限在元組上,An Introduction to Database System,完整性約束條件（續(xù)）,3.

11、靜態(tài)關系約束 關系的各個元組之間或若干關系之間存在的各種聯(lián)系或約束 常見靜態(tài)關系約束：1) 實體完整性約束2) 參照完整性約束3) 函數(shù)依賴約束4) 統(tǒng)計約束,An Introduction to Database System,函數(shù)依賴約束,關系字段間存在的函數(shù)依賴例：在學生－課程－教師關系 SJT(S,J,T) 的函數(shù)依賴： ( (S,J）→T, T→J ) 主

12、碼： (S, J),An Introduction to Database System,統(tǒng)計約束,定義某個字段值一個關系多個元組的統(tǒng)計值之間的約束關系例：職工平均工資的2倍<=部門經(jīng)理的工資<= 職工平均工資的5倍 職工平均工資值: 統(tǒng)計值,An Introduction to Database System,完整性約束條件（續(xù)）,4. 動態(tài)列級約束 動態(tài)列級約束是修改列定義或列值

13、時應滿足的約束條件,An Introduction to Database System,完整性約束條件（續(xù)）,1) 修改列定義時的約束例：將原來允許空值的列改為不允許空值時: 該列目前已存在空值，則拒絕這種修改 2) 修改列值時的約束修改列值時新舊值之間要滿足的約束條件 例：職工工資調整 >= 原來工資 年齡只能增長,An Introduction to Database

14、System,完整性約束條件（續(xù)）,5. 動態(tài)元組約束 修改元組值: 各個字段之間要滿足的約束條件例: 職工工資調整不得低于其原來工資 + 工齡*1.5,An Introduction to Database System,完整性約束條件（續(xù)）,6. 動態(tài)關系約束 關系變化前后狀態(tài)：限制條件例：事務一致性、原子性等約束條件,An Introduction to Database System,完整性約束條件小結,,,An

15、 Introduction to Database System,第十章 數(shù)據(jù)庫完整性,10.1 完整性約束條件10.2 完整性控制10.3 Oracle的完整性10.4 小結,An Introduction to Database System,10.2 完整性控制,一、DBMS的完整性控制機制二、關系系統(tǒng)三類完整性的實現(xiàn)三、參照完整性的實現(xiàn),An Introduction to Database System,

16、一、DBMS的完整性控制機制,1. 定義功能一個完善的完整性控制機制應該允許用戶定義各類完整性約束條件。,An Introduction to Database System,DBMS的完整性控制機制（續(xù)）,2. 檢查功能立即執(zhí)行的約束(Immediate constraints) 語句執(zhí)行完后立即檢查是否違背完整性約束 延遲執(zhí)行的約束(Deferred constrainsts)完整性檢查延遲到整個事務執(zhí)行結束后進行

17、,An Introduction to Database System,DBMS的完整性控制機制（續(xù)）,例：銀行數(shù)據(jù)庫中“借貸總金額應平衡”的約束 就應該是延遲執(zhí)行的約束從賬號A轉一筆錢到賬號B為一個事務，從賬號A轉出去錢后賬就不平了，必須等轉入賬號B后賬才能重新平衡，這時才能進行完整性檢查。,An Introduction to Database System,DBMS的完整性控制機制（續(xù)）,3. 違約反應 拒絕該

18、操作 其他處理方法,An Introduction to Database System,DBMS的完整性控制機制（續(xù)）,完整性規(guī)則五元組表示:(D，O，A，C，P)D（Data） 約束作用的數(shù)據(jù)對象；O（Operation） 觸發(fā)完整性檢查的數(shù)據(jù)庫操作 當用戶發(fā)出什么操作請求時需要檢查該完整性規(guī)則 是立即檢查還是延遲檢查；A（Assertion） 數(shù)據(jù)對象必須滿足的斷言或語義約束這是規(guī)則的主體； C（Co

19、ndition） 選擇A作用的數(shù)據(jù)對象值的謂詞； P（Procedure） 違反完整性規(guī)則時觸發(fā)的過程。,An Introduction to Database System,DBMS的完整性控制機制（續(xù)）,例1：在“學號不能為空”的約束中D 約束作用的對象為Sno屬性O 插入或修改Student 元組時A Sno不能為空C 無（A可作用于所有記錄的Sno屬性）P 拒絕執(zhí)行該操作,An Introduction t

20、o Database System,DBMS的完整性控制機制（續(xù)）,例2：在“教授工資不得低于1000元”的約束中D 約束作用的對象為工資Sal屬性O 插入或修改職工元組時A Sal不能小于1000C 職稱=′教授′ (A僅作用于職稱=‘教授’的記錄)P 拒絕執(zhí)行該操作,An Introduction to Database System,二、關系系統(tǒng)三類完整性的實現(xiàn),關系數(shù)據(jù)庫系統(tǒng)都提供了定義和檢查實體完

21、整性、參照完整性和用戶定義的完整性的功能違反實體完整性規(guī)則和用戶定義的完整性規(guī)則的操作： 一般是拒絕執(zhí)行違反參照完整性的操作：拒絕執(zhí)行接受這個操作，同時執(zhí)行一些附加的操作，以保證數(shù)據(jù)庫的狀態(tài)正確,An Introduction to Database System,三、參照完整性的實現(xiàn),例:職工－部門數(shù)據(jù)庫包含職工表EMP和部門表DEPT1 DEPT關系的主碼為部門號Deptno2 EMP關系的主碼為職工號Emp

22、no, 外碼為部門號Deptno 稱DEPT為被參照關系或目標關系，EMP為參照關系RDBMS實現(xiàn)參照完整性時需要考慮以下4方面：,An Introduction to Database System,1. 外碼是否可以接受空值的問題,外碼是否能夠取空值：依賴于應用環(huán)境的語義實現(xiàn)參照完整性： 系統(tǒng)提供定義外碼的機制 定義外碼列是否允許空值的機制,An Introduction to Database Sy

23、stem,1. 外碼是否可以接受空值的問題,例1：在職工－部門數(shù)據(jù)庫中， EMP關系包含有外碼Deptno 某元組的這一列若為空值，表示這個職工尚未分配到任何具體的部門工作 和應用環(huán)境的語義是相符,An Introduction to Database System,1. 外碼是否可以接受空值的問題,例2：學生－選課數(shù)據(jù)庫 Student關系為被參照關系，其主碼為Sno。 SC

24、為參照關系，外碼為Sno。 若SC的Sno為空值：表明尚不存在的某個學生，或者某個不知學號的學生，選修了某門課程，其成績記錄在Grade中 與學校的應用環(huán)境是不相符的，因此SC的Sno列不能取空值。,An Introduction to Database System,2.在被參照關系中刪除元組時的問題,出現(xiàn)違約操作的情形： 刪除被參照關系的某個元組（student） 而參照關系有若干元組(SC)的外碼值與

25、被刪除的被參照關系的主碼值相同,An Introduction to Database System,2.在被參照關系中刪除元組時的問題,違約反應：可有三種策略級聯(lián)刪除（CASCADES）受限刪除（RESTRICTED）置空值刪除（NULLIFIES）這三種處理方法，哪一種是正確的，要依應用環(huán)境的語義來定,An Introduction to Database System,2.在被參照關系中刪除元組時的問題,級聯(lián)刪除將參照

26、關系中外碼值與被參照關系中要刪除元組主碼值相對應的元組一起刪除受限刪除當參照關系中沒有任何元組的外碼值與要刪除的被參照關系的元組的主碼值相對應時，系統(tǒng)才執(zhí)行刪除操作，否則拒絕此刪除操作,An Introduction to Database System,2.在被參照關系中刪除元組時的問題,置空值刪除刪除被參照關系的元組，并將參照關系中與被參照關系中被刪除元組主碼值相等的外碼值置為空值。,An Introduction

27、to Database System,2.在被參照關系中刪除元組時的問題,例：要刪除Student關系中Sno=950001的元組， 而SC關系中有4個元組的Sno都等于950001。級聯(lián)刪除：將SC關系中所有4個Sno=950001的元組一起刪除。如果參照關系同時又是另一個關系的被參照關系，則這種刪除操作會繼續(xù)級聯(lián)下去 受限刪除：系統(tǒng)將拒絕執(zhí)行此刪除操作。,An Introduction to Databas

28、e System,2.在被參照關系中刪除元組時的問題,置空值刪除：將SC關系中所有Sno=950001的元組的Sno值置為空值。在學生選課數(shù)據(jù)庫中，顯然第一種方法和第二種方法都是對的。第三種方法不符合應用環(huán)境語義。,An Introduction to Database System,3.在參照關系中插入元組時的問題,出現(xiàn)違約操作的情形需要在參照關系中插入元組，而被參照關系不存在相應的元組違約反應受限插入遞歸插入,An In

29、troduction to Database System,3.在參照關系中插入元組時的問題,受限插入僅當被參照關系中存在相應的元組，其主碼值與參照關系插入元組的外碼值相同時，系統(tǒng)才執(zhí)行插入操作，否則拒絕此操作。 遞歸插入首先向被參照關系中插入相應的元組，其主碼值等于參照關系插入元組的外碼值，然后向參照關系插入元組。,An Introduction to Database System,3.在參照關系中插入元組時的問題,例：

30、向SC關系插入（99001，1，90）元組，而Student關系中尚沒有Sno=99001的學生受限插入：系統(tǒng)將拒絕向SC關系插入（99001，1，90）元組遞歸插入：系統(tǒng)將首先向Student關系插入Sno=99001的元組，然后向SC關系插入（99001，1，90）元組。,An Introduction to Database System,4. 修改被參照關系中主碼的問題,兩種策略(1)不允許修改主碼(2)允許修改主碼

31、,An Introduction to Database System,允許修改主碼策略,違約操作要修改被參照關系中某些元組的主碼值，而參照關系中有些元組的外碼值正好等于被參照關系要修改的主碼值要修改參照關系中某些元組的主碼值，而被參照關系中沒有任何元組的外碼值等于被參照關系修改后的主碼值,An Introduction to Database System,允許修改主碼策略,違約反應 (1)修改的關系是被參照關系：與刪除類似級

32、聯(lián)修改受限修改置空值修改,An Introduction to Database System,允許修改主碼策略,級聯(lián)修改修改被參照關系中主碼值同時，用相同的方法修改參照關系中相應的外碼值。受限修改拒絕此修改操作。只當參照關系中沒有任何元組的外碼值等于被參照關系中某個元組的主碼值時，這個元組的主碼值才能被修改。置空值修改修改被參照關系中主碼值，同時將參照關系中相應的外碼值置為空值。,An Introduction to D

33、atabase System,允許修改主碼策略,例：將Student關系中Sno=950001的元組中Sno值改為960123。而SC關系中有 4個元組的Sno=950001級聯(lián)修改：將SC關系中4個Sno=950001元組中的Sno值也改為960123。如果參照關系同時又是另一個關系的被參照關系，則這種修改操作會繼續(xù)級聯(lián)下去。,An Introduction to Database System,允許修改主碼策略,受限修改：只有S

34、C中沒有任何元組的Sno=950001時，才能修改Student表中Sno=950001的元組的Sno值改為960123。置空值修改：將Student表中Sno=950001的元組的Sno值改為960123。而將S表中所有Sno=950001的元組的Sno值置為空值。在學生選課數(shù)據(jù)庫中只有第一種方法是正確的。,An Introduction to Database System,允許修改主碼策略,違約反應 (2)修改的關系是參照關

35、系：與插入類似受限插入遞歸插入,An Introduction to Database System,參照完整性的實現(xiàn),RDBMS在實現(xiàn)參照完整性時:需要向用戶提供定義主碼、外碼的機制向用戶提供按照自己的應用要求選擇處理依賴關系中對應的元組的方法,An Introduction to Database System,第十章 數(shù)據(jù)庫完整性,10.1 完整性約束條件10.2 完整性控制10.3 Oracle的完整性10

36、.4 小結,An Introduction to Database System,10.3 Oracle的完整性,一、Oracle中的實體完整性二、Oracle中的參照完整性三、Oracle中用戶定義的完整性,An Introduction to Database System,一、ORACLE中的實體完整性,ORACLE在CREATE TABLE語句中提供了PRIMARY KEY子句，供用戶在建表時指定關系的主碼列。在列級

37、使用PRIMARY KEY子句在表級使用PRIMARY KEY子句,An Introduction to Database System,ORACLE中的實體完整性（續(xù)）,例1：在學生選課數(shù)據(jù)庫中，要定義Student表的Sno屬性為主碼 CREATE TABLE Student (Sno NUMBER(8), Sname VARCHAR(20),

38、 Sage NUMBER(20), CONSTRAINT PK_SNO PRIMARY KEY (Sno)); 或: CREATE TABLE Student (Sno NUMBER(8) PRIMARY KEY , Sname VARCHAR(20), Sage NUMBER

39、(20));,An Introduction to Database System,ORACLE中的實體完整性（續(xù)）,例2：要在SC表中定義(Sno, Cno)為主碼 CREATE TABLE SC (Sno NUMBER(8), Cno NUMBER(2), Grade NUMBER(2), CONSTRAINT PK_SC PRIMARY KEY (Sno, Cno

40、));,An Introduction to Database System,ORACLE中的實體完整性（續(xù)）,用戶程序對主碼列進行更新操作時，系統(tǒng)自動進行完整性檢查違約操作使主屬性值為空值的操作使主碼值在表中不唯一的操作違約反應系統(tǒng)拒絕此操作，從而保證了實體完整性,An Introduction to Database System,二、ORACLE中的參照完整性,定義參照完整性FOREIGN KEY子句：定義外碼列RE

41、FERENCES子句：外碼相應于哪個表的主碼ON DELETE CASCADE子語： 在刪除被參照關系的元組時，同時刪除參照關系中 外碼值等于被參照關系的元組中主碼值的元組,An Introduction to Database System,ORACLE中的參照完整性（續(xù)）,例1：建立表EMP表 CREATE TABLE EMP (Empno NUMBER(4), Ena

42、me VARCHAR(10), Job VERCHAR2(9), Mgr NUMBER(4), Sal NUMBER(7,2), Deptno NUMBER(2), CONSTRAINT FK_DEPTNO FOREIGN KEY (Deptno)

43、 REFERENCES DEPT(Deptno));,An Introduction to Database System,ORACLE中的參照完整性（續(xù)）,或： CREATE TABLE EMP (Empno NUMBER(4), Ename VARCHAR(10), Job VERCHAR2(9), Mgr NUMBER(4),

44、 Sal NUMBER(7,2), Deptno NUMBER(2) CONSTRAINT FK_DEPTNO FOREIGN KEY REFERENCES DEPT(Deptno));,An Introduction to Database System,ORACLE中的參照完整性（續(xù)）,這時EMP表中外碼為Deptno，它相應于DEPT表中的主碼Deptno。

45、當要修改DEPT表中的DEPTNO值時，先要檢查EMP表中有無元組的Deptno值與之對應若沒有，系統(tǒng)接受這個修改操作否則，系統(tǒng)拒絕此操作,An Introduction to Database System,ORACLE中的參照完整性（續(xù)）,當要刪除DEPT表中某個元組時，系統(tǒng)要檢查EMP表，若找到相應元組即將其隨之刪除。當要插入EMP表中某個元組時，系統(tǒng)要檢查DEPT表，先要檢查DEPT表中有無元組的Deptno值與之對應

46、若沒有，系統(tǒng)拒絕此插入操作否則，系統(tǒng)接受此操作,An Introduction to Database System,三、ORACLE中用戶定義的完整性,ORACLE中定義用戶完整性的兩類方法用CREATE TABLE語句在建表時定義用戶完整性約束通過觸發(fā)器來定義用戶的完整性規(guī)則,An Introduction to Database System,ORACLE中用戶定義的完整性（續(xù)）,1. 用CREATE TABLE語句在建表時

47、定義用戶完整性約束可定義三類完整性約束列值非空（NOT NULL短語）列值唯一（UNIQUE短語）檢查列值是否滿足一個布爾表達式（CHECK短語）,An Introduction to Database System,ORACLE中用戶定義的完整性（續(xù)）,例1：建立部門表DEPT，要求部門名稱Dname列 取值唯一，部門編號Deptno列為主碼CREATE TABLE DEPT(Deptno NUMBER

48、,Dname VARCHAR(9) CONSTRAINT U1 UNIQUE,Loc VARCHAR(10),CONSTRAINT PK_DEPT PRIMARY KEY (Deptno));其中 CONSTRAINT U1 UNIQUE 表示約束名為U1，該約束要求Dname列值唯一。,An Introduction to Database System,ORACLE中用戶定義的完整性（續(xù)）,例2： 建立學生登記

49、表Student，要求學號在 900000至999999之間，年齡<29，性別 只能是‘男’或‘女’，姓名非空CREATE TABLE Student (Sno NUMBER(5) CONSTRAINT C1 CHECK (Sno BETWEEN 10000 AND 99999),

50、 Sname VARCHAR(20) CONSTRAINT C2 NOT NULL, Sage NUMBER(3) CONSTRAINT C3 CHECK (Sage < 29), Ssex VARCHAR(2) CONSTRAINT C4 CHECK (Ssex IN ('男', '女'));,An Int

51、roduction to Database System,ORACLE中用戶定義的完整性（續(xù)）,例3 ：建立職工表EMP，要求每個職工的應發(fā)工資不得超過3000元。 應發(fā)工資實際上就是實發(fā)工資列Sal與扣除項Deduct之和。 CREATE TABLE EMP (Eno NUMBER(4) Ename VARCHAR(10), Job VARCHAR(8),

52、 Sal NUMBER(7,2), Deduct NUMBER(7,2) Deptno NUMBER(2), CONSTRAINTS C1 CHECK (Sal + Deduct <=3000));,An Introduction to Database System,ORACLE中用戶定義的完整性（續(xù)）,2. 通過觸發(fā)器來定義用戶的完整性規(guī)則定義其它的完整性約束時

53、，需要用數(shù)據(jù)庫觸發(fā)器（Trigger）來實現(xiàn)。數(shù)據(jù)庫觸發(fā)器：一類靠事務驅動的特殊過程一旦由某個用戶定義，任何用戶對該數(shù)據(jù)的增、刪、改操作均由服務器自動激活相應的觸發(fā)子，在核心層進行集中的完整性控制定義數(shù)據(jù)庫觸發(fā)器的語句 CREATE [OR REPLACE] TRIGGER,An Introduction to Database System,ORACLE中用戶定義的完整性（續(xù)）,例4： 為教師表Teacher定

54、義完整性規(guī)則 “教授的工資不得低于800元，如果低于800元，自動改為800元”,An Introduction to Database System,ORACLE中用戶定義的完整性（續(xù)）,CREATE TRIGGER UPDATE_SAL BEFORE INSERT OR UPDATE OF Sal, Pos ON Teacher FOR EACH ROW WHEN (:new.Pos='教授&#

55、39;) BEGIN IF :new.sal<800 THEN :new.Sal:=800; END IF; END;,An Introduction to Database System,Oracle完整性小結,ORACLE提供定義完整性約束條件CREATE TABLE語句CREATE TRIGGER語句

56、可以定義很復雜的完整性約束條件ORACLE自動執(zhí)行相應的完整性檢查 對于違反完整性約束條件的操作： 拒絕執(zhí)行或者執(zhí)行事先定義的操作,An Introduction to Database System,第十章 數(shù)據(jù)庫完整性,10.1 完整性約束條件10.2 完整性控制10.3 Oracle的完整性10.4 小結,An Introduction to Database System,10.4 小結,數(shù)據(jù)

57、庫的完整性是為了保證數(shù)據(jù)庫中存儲的數(shù)據(jù)是正確的，所謂正確的是指符合現(xiàn)實世界語義的。DBMS完整性實現(xiàn)的機制完整性約束定義機制完整性檢查機制違背完整性約束條件時DBMS應采取的動作,An Introduction to Database System,小結,完整性機制的實施會極大地影響系統(tǒng)性能不同的數(shù)據(jù)庫產品對完整性的支持策略和支持程度是不同的許多數(shù)據(jù)庫管理系統(tǒng)對完整性機制的支持比對安全性的支持要晚得多也弱得多數(shù)據(jù)庫廠商