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； 學生的性別只能是男或女； 學生的學號一定是唯一的； 學生所在的系必須是學校開設的系； 完整性：否真實地反映現(xiàn)實世界,An Introduction to Database

2、System,完整性控制機制,1.完整性約束條件定義機制 2.完整性檢查機制 3.違約反應,An Introduction to Database System,完整性約束條件定義,完整性約束條件：數(shù)據(jù)模型的組成部分約束數(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ù)違背了完整性約束條件，則采取一定的動作來

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

4、理性的約束 動態(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 Database System,完整性約束條件（續(xù)）,對象狀態(tài) 動態(tài)列級約束 動態(tài)元組約束 動態(tài)關系約束 動態(tài) 靜態(tài)列級約束 靜態(tài)元組約束 靜態(tài)關系約束 靜態(tài) 列 元組 關系 對象粒度,An Introduction to Database System,完整性約束條件（續(xù)）,1. 靜態(tài)列級約束 靜態(tài)列級

5、約束：對的取值域的說明 最常見、最簡單、最容易實現(xiàn)的一類完整性約束,An Introduction to Database System,完整性約束條件（續(xù)）,五類靜態(tài)列級約束 1) 數(shù)據(jù)類型約束：數(shù)據(jù)的類型、長度、單位、精度等 例：學生姓名的數(shù)據(jù)類型為字符型，長度為8 2) 對數(shù)據(jù)格式的約束 例： 學號：前兩位表示入學年份，后四位為順序編號 日期：YY.MM.DD。,An Introduction to Database System,完整性約束條件（續(xù)）,3) 取值范圍或取值集合的約束 例：規(guī)定成績的取值范圍為0-100 年齡的取值范圍為14-29 性別的取值集合為男,女 4) 對空值的約

6、束 空值：未定義或未知的值 空值：與零值和空格不同 有的列允許空值，有的則不允許，如成績可為空值 5) 其他約束 例：關于列的排序說明，組合列等,An Introduction to Database System,完整性約束條件（續(xù)）,2. 靜態(tài)元組約束 規(guī)定元組的各個列之間的約束關系 例：訂貨關系中發(fā)貨量=700元 靜態(tài)元組約束只局限在元組上,An Introduction to Database System,完整性約束條件（續(xù)）,3. 靜態(tài)關系約束 關系的各個元組之間或若干關系之間存在的各種聯(lián)系或約束 常見靜態(tài)關系約束： 1) 實體完整性約束 2) 參照完整性約束 3) 函數(shù)依賴約束

7、4) 統(tǒng)計約束,An Introduction to Database System,函數(shù)依賴約束,關系字段間存在的函數(shù)依賴 例：在學生課程教師關系 SJT(S,J,T) 的函數(shù)依賴： ( (S,J）T, TJ ) 主碼： (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)列級約束是修改列定義或列

8、值時應滿足的約束條件,An Introduction to Database System,完整性約束條件（續(xù)）,1) 修改列定義時的約束 例：將原來允許空值的列改為不允許空值時: 該列目前已存在空值，則拒絕這種修改 2) 修改列值時的約束 修改列值時新舊值之間要滿足的約束條件 例：職工工資調(diào)整 = 原來工資 年齡只能增長,An Introduction to Database System,完整性約束條件（續(xù)）,5. 動態(tài)元組約束 修改元組值: 各個字段之間要滿足的約束條件 例: 職工工資調(diào)整不得低于其原來工資 + 工齡*1.5,An Introduction to Database Sys

9、tem,完整性約束條件（續(xù)）,6. 動態(tài)關系約束 關系變化前后狀態(tài)：限制條件 例：事務一致性、原子性等約束條件,An Introduction to Database System,完整性約束條件小結,An 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

10、 Database System,一、DBMS的完整性控制機制,1. 定義功能 一個完善的完整性控制機制應該允許用戶定義各類完整性約束條件。,An Introduction to Database System,DBMS的完整性控制機制（續(xù)）,2. 檢查功能 立即執(zhí)行的約束(Immediate constraints) 語句執(zhí)行完后立即檢查是否違背完整性約束 延遲執(zhí)行的約束(Deferred constrainsts) 完整性檢查延遲到整個事務執(zhí)行結束后 進行,An Introduction to Database System,DBMS的完整性控制機制（續(xù)）,例：銀行數(shù)據(jù)庫中“借貸總金額應平

11、衡”的約束 就應該是延遲執(zhí)行的約束 從賬號A轉(zhuǎn)一筆錢到賬號B為一個事務，從賬號A轉(zhuǎn)出去錢后賬就不平了，必須等轉(zhuǎn)入賬號B后賬才能重新平衡，這時才能進行完整性檢查。,An Introduction to Database System,DBMS的完整性控制機制（續(xù)）,3. 違約反應 拒絕該操作 其他處理方法,An Introduction to Database System,DBMS的完整性控制機制（續(xù)）,完整性規(guī)則五元組表示: (D，O，A，C，P) D（Data） 約束作用的數(shù)據(jù)對象； O（Operation） 觸發(fā)完整性檢查的數(shù)據(jù)庫操作 當用戶發(fā)出什么操作請求時需要檢查該完整性規(guī)則 是立即

12、檢查還是延遲檢查； A（Assertion） 數(shù)據(jù)對象必須滿足的斷言或語義約束這是規(guī)則的主體； C（Condition） 選擇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 to Database System,DBMS的完整性控制機制（續(xù)）,

13、例2：在“教授工資不得低于1000元”的約束中 D 約束作用的對象為工資Sal屬性 O 插入或修改職工元組時 A Sal不能小于1000 C 職稱=教授 (A僅作用于職稱=教授的記錄) P 拒絕執(zhí)行該操作,An Introduction to Database System,二、關系系統(tǒng)三類完整性的實現(xiàn),關系數(shù)據(jù)庫系統(tǒng)都提供了定義和檢查實體完整性、參照完整性和用戶定義的完整性的功能 違反實體完整性規(guī)則和用戶定義的完整性規(guī)則的操作： 一般是拒絕執(zhí)行 違反參照完整性的操作： 拒絕執(zhí)行 接受這個操作，同時執(zhí)行一些附加的操作，以保證數(shù)據(jù)庫的狀態(tài)正確,An Introduction to Databas

14、e System,三、參照完整性的實現(xiàn),例:職工部門數(shù)據(jù)庫包含職工表EMP和部門表DEPT 1 DEPT關系的主碼為部門號Deptno 2 EMP關系的主碼為職工號Empno, 外碼為部門號Deptno 稱DEPT為被參照關系或目標關系，EMP為參照關系 RDBMS實現(xiàn)參照完整性時需要考慮以下4方面：,An Introduction to Database System,1. 外碼是否可以接受空值的問題,外碼是否能夠取空值：依賴于應用環(huán)境的語義 實現(xiàn)參照完整性： 系統(tǒng)提供定義外碼的機制 定義外碼列是否允許空值的機制,An Introduction to Database System,1. 外

15、碼是否可以接受空值的問題,例1：在職工部門數(shù)據(jù)庫中， EMP關系包含有外碼Deptno 某元組的這一列若為空值，表示這個職工尚未分配到任何具體的部門工作 和應用環(huán)境的語義是相符,An Introduction to Database System,1. 外碼是否可以接受空值的問題,例2：學生選課數(shù)據(jù)庫 Student關系為被參照關系，其主碼為Sno。 SC為參照關系，外碼為Sno。 若SC的Sno為空值：表明尚不存在的某個學生，或者某個不知學號的學生，選修了某門課程，其成績記錄在Grade中 與學校的應用環(huán)境是不相符的，因此SC的Sno列不能取空值。,An Introduction to Da

16、tabase System,2.在被參照關系中刪除元組時的問題,出現(xiàn)違約操作的情形： 刪除被參照關系的某個元組（student） 而參照關系有若干元組(SC)的外碼值與被刪除的被參照關系的主碼值相同,An Introduction to Database System,2.在被參照關系中刪除元組時的問題,違約反應：可有三種策略 級聯(lián)刪除（CASCADES） 受限刪除（RESTRICTED） 置空值刪除（NULLIFIES） 這三種處理方法，哪一種是正確的，要依應用環(huán)境的語義來定,An Introduction to Database System,2.在被參照關系中刪除元組時的問題,級聯(lián)刪除

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

18、組的Sno都等于950001。 級聯(lián)刪除：將SC關系中所有4個Sno=950001的元組一起刪除。如果參照關系同時又是另一個關系的被參照關系，則這種刪除操作會繼續(xù)級聯(lián)下去 受限刪除：系統(tǒng)將拒絕執(zhí)行此刪除操作。,An Introduction to Database System,2.在被參照關系中刪除元組時的問題,置空值刪除：將SC關系中所有Sno=950001的元組的Sno值置為空值。 在學生選課數(shù)據(jù)庫中，顯然第一種方法和第二種方法都是對的。第三種方法不符合應用環(huán)境語義。,An Introduction to Database System,3.在參照關系中插入元組時的問題,出現(xiàn)違約操作的情

19、形 需要在參照關系中插入元組，而被參照關系不存在相應的元組 違約反應 受限插入 遞歸插入,An Introduction to Database System,3.在參照關系中插入元組時的問題,受限插入 僅當被參照關系中存在相應的元組，其主碼值與參照關系插入元組的外碼值相同時，系統(tǒng)才執(zhí)行插入操作，否則拒絕此操作。 遞歸插入 首先向被參照關系中插入相應的元組，其主碼值等于參照關系插入元組的外碼值，然后向參照關系插入元組。,An Introduction to Database System,3.在參照關系中插入元組時的問題,例：向SC關系插入（99001，1，90）元組，而Student關系中尚

20、沒有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)允許修改主碼,An Introduction to Database System,允許修改主碼策略,違約操作 要修改被參照關系中某些元組的主碼值，而參照關系中有些元組的外碼值正好等于被參照關系要修改的主碼值 要修改參照關系中某些元組的主碼值，

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

22、空值。,An Introduction to Database 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,允許修改主碼策略,受限修改：只有SC中沒有任何元組的Sno=950001時，才能修改Student表中Sno=950001的元組的Sno值改為960

23、123。 置空值修改：將Student表中Sno=950001的元組的Sno值改為960123。而將S表中所有Sno=950001的元組的Sno值置為空值。 在學生選課數(shù)據(jù)庫中只有第一種方法是正確的。,An Introduction to Database System,允許修改主碼策略,違約反應 (2) 修改的關系是參照關系：與插入類似 受限插入 遞歸插入,An Introduction to Database System,參照完整性的實現(xiàn),RDBMS在實現(xiàn)參照完整性時: 需要向用戶提供定義主碼、外碼的機制 向用戶提供按照自己的應用要求選擇處理依賴關系中對應的元組的方法,An Introd

24、uction to Database System,第十章 數(shù)據(jù)庫完整性,10.1 完整性約束條件 10.2 完整性控制 10.3 Oracle的完整性 10.4 小結,An Introduction to Database System,10.3 Oracle的完整性,一、Oracle中的實體完整性 二、Oracle中的參照完整性 三、Oracle中用戶定義的完整性,An Introduction to Database System,一、ORACLE中的實體完整性,ORACLE在CREATE TABLE語句中提供了PRIMARY KEY子句，供用戶在建表時指定關系的主碼列。 在列級使用PR

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

26、age NUMBER(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);,An Introduction to Database System,ORACLE中的實體完整性（續(xù)）,用戶程序?qū)χ鞔a列進行更新操作時，系統(tǒng)自動進行完整性檢查 違約操作 使主屬性值為空值的操作 使主碼值在

27、表中不唯一的操作 違約反應 系統(tǒng)拒絕此操作，從而保證了實體完整性,An Introduction to Database System,二、ORACLE中的參照完整性,定義參照完整性 FOREIGN KEY子句：定義外碼列 REFERENCES子句：外碼相應于哪個表的主碼 ON DELETE CASCADE子語： 在刪除被參照關系的元組時，同時刪除參照關系中 外碼值等于被參照關系的元組中主碼值的元組,An Introduction to Database System,ORACLE中的參照完整性（續(xù)）,例1：建立表EMP表 CREATE TABLE EMP (Empno NUMBER(4),

28、Ename VARCHAR(10), Job VERCHAR2(9), Mgr NUMBER(4), Sal NUMBER(7,2), Deptno NUMBER(2), CONSTRAINT FK_DEPTNO FOREIGN KEY (Deptno) 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), Sal NU

29、MBER(7,2), Deptno NUMBER(2) CONSTRAINT FK_DEPTNO FOREIGN KEY REFERENCES DEPT(Deptno);,An Introduction to Database System,ORACLE中的參照完整性（續(xù)）,這時EMP表中外碼為Deptno，它相應于DEPT表中的主碼Deptno。 當要修改DEPT表中的DEPTNO值時，先要檢查EMP表中有無元組的Deptno值與之對應 若沒有，系統(tǒng)接受這個修改操作 否則，系統(tǒng)拒絕此操作,An Introduction to Database System,ORACLE中的參照完整性（續(xù)）,

30、當要刪除DEPT表中某個元組時，系統(tǒng)要檢查EMP表，若找到相應元組即將其隨之刪除。 當要插入EMP表中某個元組時，系統(tǒng)要檢查DEPT表，先要檢查DEPT表中有無元組的Deptno值與之對應 若沒有，系統(tǒng)拒絕此插入操作 否則，系統(tǒng)接受此操作,An Introduction to Database System,三、ORACLE中用戶定義的完整性,ORACLE中定義用戶完整性的兩類方法 用CREATE TABLE語句在建表時定義用戶完整性約束 通過觸發(fā)器來定義用戶的完整性規(guī)則,An Introduction to Database System,ORACLE中用戶定義的完整性（續(xù)）,1. 用CRE

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

32、T PRIMARY KEY (Deptno); 其中 CONSTRAINT U1 UNIQUE 表示約束名為U1， 該約束要求Dname列值唯一。,An Introduction to Database System,ORACLE中用戶定義的完整性（續(xù)）,例2： 建立學生登記表Student，要求學號在 900000至999999之間，年齡29，性別 只能是男或女，姓名非空 CREATE TABLE Student (Sno NUMBER(5) CONSTRAINT C1 CHECK (Sno BETWEEN 10000 AND 99999), Sname VARCHAR(20) CONSTR

33、AINT C2 NOT NULL, Sage NUMBER(3) CONSTRAINT C3 CHECK (Sage 29), Ssex VARCHAR(2) CONSTRAINT C4 CHECK (Ssex IN (男, 女);,An Introduction 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), 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ā)器