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

1、An Introduction to Database System 第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 主講教師：姜季春主講教師：姜季春 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 v數(shù)據(jù)庫完整性的概念數(shù)據(jù)庫完整性的概念 vRDBMS數(shù)據(jù)庫完整性的實現(xiàn)機制，包括實體完數(shù)據(jù)庫完整性的實現(xiàn)機制，包括實體完 整性、參照完整性和用戶定義完整性的定義機制整性、參照完整性和用戶定義完整性的定義機制 、完整性檢查機制和違背完整性約束條件時、完整性檢查機制和違背完整性約束條件時 RDBMS采取的預防措施采取的預防措施 v觸發(fā)器的概念和在數(shù)據(jù)庫完整性檢查中的應用觸發(fā)器的概念和在數(shù)據(jù)庫完整性檢查中的應用 An Introdu

2、ction to Database System An Introduction to Database System 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 v數(shù)據(jù)庫的完整性數(shù)據(jù)庫的完整性:數(shù)據(jù)的數(shù)據(jù)的正確性正確性和和相容性相容性 正確性正確性:數(shù)據(jù)是符合現(xiàn)實世界語義，反映了當前實際狀況的數(shù)據(jù)是符合現(xiàn)實世界語義，反映了當前實際狀況的 相容性相容性:數(shù)據(jù)庫同一對象在不同關系表中的數(shù)據(jù)是符合邏輯的數(shù)據(jù)庫同一對象在不同關系表中的數(shù)據(jù)是符合邏輯的 例例: 學生的年齡必須是整數(shù)，取值范圍為學生的年齡必須是整數(shù)，取值范圍為14-29 學生的性別只能是男或女學生的性別只能是男或女 學生的學號一定是唯一的學生的學號一定是

3、唯一的 學生所在的系必須是學校開設的系學生所在的系必須是學校開設的系 完整性完整性：是否真實地反映現(xiàn)實世界：是否真實地反映現(xiàn)實世界 An Introduction to Database System 數(shù)據(jù)庫完整性（續(xù)）數(shù)據(jù)庫完整性（續(xù)） v數(shù)據(jù)的數(shù)據(jù)的完整性完整性和和安全性安全性是兩個不同概念是兩個不同概念 數(shù)據(jù)的完整性數(shù)據(jù)的完整性 防止數(shù)據(jù)庫中存在防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)不符合語義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫中存，也就是防止數(shù)據(jù)庫中存 在在不正確的數(shù)據(jù)不正確的數(shù)據(jù) 防范對象防范對象：不合語義的、不正確的數(shù)據(jù)：不合語義的、不正確的數(shù)據(jù) 數(shù)據(jù)的安全性數(shù)據(jù)的安全性 保護數(shù)據(jù)庫防止保護數(shù)據(jù)庫防

4、止惡意的破壞惡意的破壞和和非法的存取非法的存取 防范對象防范對象：非法用戶和非法操作：非法用戶和非法操作 An Introduction to Database System 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性(續(xù)續(xù)) 為維護數(shù)據(jù)庫的完整性，為維護數(shù)據(jù)庫的完整性，DBMS必須：必須： n1.提供定義完整性約束條件的機制提供定義完整性約束條件的機制 n2.提供完整性檢查的方法提供完整性檢查的方法 n3.違約處理違約處理 An Introduction to Database System 第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 5.1 實體完整性實體完整性 5.2 參照完整性參照完整性 5.3 用戶定義的

5、完整性用戶定義的完整性 5.4 完整性約束命名字句完整性約束命名字句 *5.5 域中的完整性限制域中的完整性限制 5.6 觸發(fā)器觸發(fā)器 5.7 小結小結 An Introduction to Database System 5.1 實體完整性實體完整性 v5.1.1 實體完整性定義實體完整性定義 v5.1.2 實體完整性檢查和違約處理實體完整性檢查和違約處理 An Introduction to Database System 5.1.1 實體完整性定義實體完整性定義 v 關系模型的關系模型的實體完整性實體完整性 CREATE TABLE中用中用PRIMARY KEY定義定義 v 單屬性單屬性

6、構成的碼有構成的碼有兩種兩種說明方法說明方法 定義為定義為列級列級約束條件約束條件 定義為定義為表級表級約束條件約束條件 v 對對多個屬性多個屬性構成的碼只有構成的碼只有一種一種說明方法說明方法 定義為定義為表級表級約束條件約束條件 An Introduction to Database System 實體完整性定義實體完整性定義(續(xù)續(xù)) 例例1 將將Student表中的表中的Sno屬性屬性定義為碼定義為碼 (1)在在列級列級定義主碼定義主碼 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(20) NOT NULL， Ssex

7、 CHAR(2) ， Sage SMALLINT， Sdept CHAR(20); An Introduction to Database System 實體完整性定義實體完整性定義(續(xù)續(xù)) (2)在在表級表級定義主碼定義主碼 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20)， PRIMARY KEY (Sno) ); An Introduction to Database System 實體完整性定義實體完整性定義(續(xù)續(xù)) 例例2將將

8、SC表中的表中的Sno，Cno屬性組屬性組定義為碼定義為碼 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno，Cno) /*只能在表級定義主碼只能在表級定義主碼*/ ); An Introduction to Database System 5.1 實體完整性實體完整性 v5.1.1 實體完整性定義實體完整性定義 v5.1.2 實體完整性檢查和違約處理實體完整性檢查和違約處理 An Introduction to Database System 5.1.2

9、實體完整性檢查和違約處理實體完整性檢查和違約處理 v 插入插入或對或對主碼列進行更新主碼列進行更新操作時，操作時，RDBMS按照按照實體完整實體完整 性規(guī)則性規(guī)則自動進行檢查。包括：自動進行檢查。包括： 1. 檢查檢查主碼值是否唯一主碼值是否唯一，如果不唯一則，如果不唯一則拒絕拒絕插入或修改插入或修改 2. 檢查檢查主碼的各個屬性是否為空主碼的各個屬性是否為空，只要有一個為空就，只要有一個為空就拒絕拒絕 插入或修改插入或修改 v 違約反應違約反應 系統(tǒng)拒絕此操作，從而保證了系統(tǒng)拒絕此操作，從而保證了實體完整性實體完整性 An Introduction to Database System 實體

10、完整性檢查和違約處理實體完整性檢查和違約處理(續(xù)續(xù)) v 檢查記錄中檢查記錄中主碼值主碼值是否是否唯一唯一的一種方法是進行的一種方法是進行全表掃描全表掃描 v 依次判斷表中每一條記錄的主碼值與將插入記錄上的主碼值（或者修依次判斷表中每一條記錄的主碼值與將插入記錄上的主碼值（或者修 改的新主碼值）是否相同改的新主碼值）是否相同 An Introduction to Database System 實體完整性檢查和違約處理實體完整性檢查和違約處理(續(xù)續(xù)) v索引索引 例如，例如， 新插入記錄的主碼值是新插入記錄的主碼值是25 l通過主碼索引，從通過主碼索引，從B+樹的根結點開始查找樹的根結點開始查

11、找 l讀取讀取3個結點個結點：根結點根結點（51）、）、中間結點中間結點（12 30）、）、葉結點葉結點 （15 20 25） l該主碼值已經(jīng)存在，不能插入這條記錄該主碼值已經(jīng)存在，不能插入這條記錄 An Introduction to Database System 第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 5.1 實體完整性實體完整性 5.2 參照完整性參照完整性 5.3 用戶定義的完整性用戶定義的完整性 5.4 完整性約束命名字句完整性約束命名字句 *5.5 域中的完整性限制域中的完整性限制 5.6 觸發(fā)器觸發(fā)器 5.7 小結小結 An Introduction to Database S

12、ystem 5.2 參照完整性參照完整性 v5.2.1 參照完整性定義參照完整性定義 v5.2.2 參照完整性檢查和違約處理參照完整性檢查和違約處理 An Introduction to Database System 5.2.1 參照完整性定義參照完整性定義 v關系模型的關系模型的參照完整性參照完整性定義定義 在在CREATE TABLE中用中用FOREIGN KEY短語短語定義哪些定義哪些 列為列為外碼外碼 用用REFERENCES短語短語指明這些外碼參照哪些表的指明這些外碼參照哪些表的主碼主碼 An Introduction to Database System 參照完整性定義參照完整性

13、定義(續(xù)續(xù)) 例如，關系例如，關系SC中一個元組表示一個學生選修的某門課程的成績，中一個元組表示一個學生選修的某門課程的成績， （Sno，Cno）是主碼。）是主碼。Sno，Cno分別分別參照引用參照引用Student表的表的 主碼和主碼和Course表的主碼表的主碼 例例3 定義定義SC中的參照完整性中的參照完整性 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno， Cno)， /*在表級定義實體完整性在表級定義實體完整性*/ FOREIGN KEY (Sn

14、o) REFERENCES Student(Sno)， /*在表級定義參照完整性在表級定義參照完整性*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表級定義參照完整性在表級定義參照完整性*/ ); An Introduction to Database System 5.2 參照完整性參照完整性 v5.2.1 參照完整性定義參照完整性定義 v5.2.2 參照完整性檢查和違約處理參照完整性檢查和違約處理 參照完整性檢查和違約處理參照完整性檢查和違約處理 v一個參照完整性將兩個表中的相應元組聯(lián)系起來一個參照完整性將兩個表中的相應元組聯(lián)系起來 v對被參照

15、表和參照表進行增刪改操作時有可能破對被參照表和參照表進行增刪改操作時有可能破 壞參照完整性，必須進行檢查壞參照完整性，必須進行檢查 An Introduction to Database System 參照完整性檢查和違約處理參照完整性檢查和違約處理（續(xù)續(xù)） v例如，對例如，對表表SC和和Student有四種可能破壞參照完有四種可能破壞參照完 整性的情況整性的情況 : SC表中增加一個元組表中增加一個元組，該元組的，該元組的Sno屬性的值在表屬性的值在表 Student中找不到一個元組，其中找不到一個元組，其Sno屬性的值與之相等屬性的值與之相等 修改修改SC表中的一個元組表中的一個元組，修改

16、后該元組的，修改后該元組的Sno屬性的屬性的 值在表值在表Student中找不到一個元組，其中找不到一個元組，其Sno屬性的值與屬性的值與 之相等之相等 An Introduction to Database System 參照完整性檢查和違約處理參照完整性檢查和違約處理（續(xù)續(xù)） v例如，對例如，對表表SC和和Student有四種可能破壞參照完有四種可能破壞參照完 整性的情況整性的情況 （續(xù)）（續(xù)）: 從從Student表中刪除一個元組表中刪除一個元組，造成，造成SC表中某些元組表中某些元組 的的Sno屬性的值在表屬性的值在表Student中找不到一個元組，其中找不到一個元組，其 Sno屬性的

17、值與之相等屬性的值與之相等 修改修改Student表中一個元組的表中一個元組的Sno屬性屬性，造成，造成SC表中表中 某些元組的某些元組的Sno屬性的值在表屬性的值在表Student中找不到一個元中找不到一個元 組，其組，其Sno屬性的值與之相等屬性的值與之相等 An Introduction to Database System An Introduction to Database System 參照完整性檢查和違約處理參照完整性檢查和違約處理 可能破壞參照完整性的情況及違約處理可能破壞參照完整性的情況及違約處理 被參照表（例如被參照表（例如Student）參照表（例如參照表（例如SC）違

18、約處理違約處理 可能破壞參照完整性可能破壞參照完整性 插入元組插入元組拒絕拒絕 可能破壞參照完整性可能破壞參照完整性 修改外碼值修改外碼值拒絕拒絕 刪除元組刪除元組 可能破壞參照完整性可能破壞參照完整性拒絕拒絕/級聯(lián)刪除級聯(lián)刪除/設置為空值設置為空值 修改主碼值修改主碼值 可能破壞參照完整性可能破壞參照完整性拒絕拒絕/級聯(lián)修改級聯(lián)修改/設置為空值設置為空值 An Introduction to Database System 參照完整性檢查和違約處理參照完整性檢查和違約處理(例例) 學學 號號 Sno 姓姓 名名 Sname 性性 別別 Ssex 年年 齡齡 Sage 所所 在在 系系 Sde

19、pt 200215121 200215122 200215123 200215125 李勇李勇 劉晨劉晨 王敏王敏 張立張立 男男 女女 女女 男男 20 19 18 19 CS CS MA IS 學學 號號 Sno 課程號課程號 Cno 成績成績 Grade 200215121 200215121 200215121 200215122 200215122 1 2 3 2 3 92 85 88 90 80 Student（被參照表）（被參照表） SC （參照表）（參照表） 學學 號號 Sno 課程號課程號 Cno 成績成績 Grade 200215124 3 75 插入選課記錄插入選課記錄

20、An Introduction to Database System 參照完整性檢查和違約處理參照完整性檢查和違約處理(例例) 學學 號號 Sno 姓姓 名名 Sname 性性 別別 Ssex 年年 齡齡 Sage 所所 在在 系系 Sdept 200215121 200215122 200215123 200215125 李勇李勇 劉晨劉晨 王敏王敏 張立張立 男男 女女 女女 男男 20 19 18 19 CS CS MA IS 學學 號號 Sno 課程號課程號 Cno 成績成績 Grade 200215121 200215121 200215121 200215122 200215122

21、 1 2 3 2 3 92 85 88 90 80 Student（被參照表）（被參照表） SC （參照表）（參照表） 學號改為學號改為200215124 An Introduction to Database System 參照完整性檢查和違約處理參照完整性檢查和違約處理(例例) 學學 號號 Sno 姓姓 名名 Sname 性性 別別 Ssex 年年 齡齡 Sage 所所 在在 系系 Sdept 200215121 200215122 200215123 200215125 李勇李勇 劉晨劉晨 王敏王敏 張立張立 男男 女女 女女 男男 20 19 18 19 CS CS MA IS 學學

22、號號 Sno 課程號課程號 Cno 成績成績 Grade 200215121 200215121 200215121 200215122 200215122 1 2 3 2 3 92 85 88 90 80 Student（被參照表）（被參照表） SC （參照表）（參照表） 刪除學號為刪除學號為200215122 的學生記錄的學生記錄 An Introduction to Database System 參照完整性檢查和違約處理參照完整性檢查和違約處理(例例) 學學 號號 Sno 姓姓 名名 Sname 性性 別別 Ssex 年年 齡齡 Sage 所所 在在 系系 Sdept 20021512

23、1 200215122 200215123 200215125 李勇李勇 劉晨劉晨 王敏王敏 張立張立 男男 女女 女女 男男 20 19 18 19 CS CS MA IS 學學 號號 Sno 課程號課程號 Cno 成績成績 Grade 200215121 200215121 200215121 200215122 200215122 1 2 3 2 3 92 85 88 90 80 Student（被參照表）（被參照表） SC （參照表）（參照表） 將學號將學號200215122 改為改為200215124 An Introduction to Database System 違約處理違約

24、處理 v 參照完整性違約處理參照完整性違約處理 1. 拒絕拒絕(NO ACTION)執(zhí)行執(zhí)行 默認策略默認策略 2. 級聯(lián)級聯(lián)(CASCADE)操作操作 當刪除或修改當刪除或修改被參照表被參照表的一個元組造成了與參照表的不一致，則的一個元組造成了與參照表的不一致，則 刪除或修改刪除或修改參照表參照表中的所有造成不一致的元組中的所有造成不一致的元組 3. 設置為空值（設置為空值（SET-NULL） 當刪除或修改當刪除或修改被參照表被參照表的一個元組時造成了不一致，則將的一個元組時造成了不一致，則將參照表參照表 中的所有造成不一致的元組的中的所有造成不一致的元組的對應屬性對應屬性設置為設置為空值空

25、值 對于參照完整性，除了應該定義外碼，還應定義對于參照完整性，除了應該定義外碼，還應定義外碼列外碼列 是否允許空值是否允許空值 An Introduction to Database System 違約處理違約處理(續(xù)續(xù)) 例例 要要刪除刪除Student關系中關系中Sno=200215122的元組，的元組， 而而SC關系中有關系中有2個元組的個元組的Sno都等于都等于200215122。 級聯(lián)刪除級聯(lián)刪除 將將SC關系中所有關系中所有2個個Sno=200215122的元組一的元組一 起刪除。起刪除。如果參照關系同時又是另一個關系的如果參照關系同時又是另一個關系的 被參照關系被參照關系，則這種

26、刪除操作會繼續(xù)級聯(lián)下去，則這種刪除操作會繼續(xù)級聯(lián)下去 An Introduction to Database System 參照完整性檢查和違約處理參照完整性檢查和違約處理(例例) 學學 號號 Sno 姓姓 名名 Sname 性性 別別 Ssex 年年 齡齡 Sage 所所 在在 系系 Sdept 200215121 200215122 200215123 200215125 李勇李勇 劉晨劉晨 王敏王敏 張立張立 男男 女女 女女 男男 20 19 18 19 CS CS MA IS 學學 號號 Sno 課程號課程號 Cno 成績成績 Grade 200215121 200215121 20

27、0215121 200215122 200215122 1 2 3 2 3 92 85 88 90 80 Student（被參照表）（被參照表） SC （參照表）（參照表） 刪除學號為刪除學號為200215122 的學生記錄的學生記錄 級聯(lián)刪除學號為級聯(lián)刪除學號為200215122 的選課記錄的選課記錄 An Introduction to Database System 置空值置空值：將：將SC關系中所有關系中所有Sno=200215122的的 元組的元組的Sno值置為空值值置為空值 學學 號號 Sno 姓姓 名名 Sname 性性 別別 Ssex 年年 齡齡 Sage 所所 在在 系系 S

28、dept 200215121 200215122 200215123 200215125 李勇李勇 劉晨劉晨 王敏王敏 張立張立 男男 女女 女女 男男 20 19 18 19 CS CS MA IS 學學 號號 Sno 課程號課程號 Cno 成績成績 Grade 200215121 200215121 200215121 200215122 200215122 1 2 3 2 3 92 85 88 90 80 Student（被參照表）（被參照表） SC （參照表）（參照表） 刪除學號為刪除學號為200215122 的學生記錄的學生記錄 將學號將學號200215122置空置空 在學生選課數(shù)據(jù)

29、庫中，顯然第在學生選課數(shù)據(jù)庫中，顯然第 一種方法是對的。第二種方法一種方法是對的。第二種方法 不符合應用環(huán)境語義不符合應用環(huán)境語義 An Introduction to Database System 違約處理違約處理(續(xù)續(xù)) 例例 將將Student關系中關系中Sno=200215122的元組中的元組中 Sno值改為值改為200215124。而。而SC關系中有關系中有 2個元個元 組的組的Sno=200215122。 級聯(lián)修改級聯(lián)修改 將將SC關系中個關系中個Sno=200215122元組中的元組中的Sno 值也改為值也改為200215124。如果參照關系同時又是如果參照關系同時又是 另一個

30、關系的被參照關系另一個關系的被參照關系，則這種修改操作會，則這種修改操作會 繼續(xù)級聯(lián)下去繼續(xù)級聯(lián)下去 An Introduction to Database System 參照完整性檢查和違約處理參照完整性檢查和違約處理(例例) 學學 號號 Sno 姓姓 名名 Sname 性性 別別 Ssex 年年 齡齡 Sage 所所 在在 系系 Sdept 200215121 200215122 200215123 200215125 李勇李勇 劉晨劉晨 王敏王敏 張立張立 男男 女女 女女 男男 20 19 18 19 CS CS MA IS 學學 號號 Sno 課程號課程號 Cno 成績成績 Grad

31、e 200215121 200215121 200215121 200215122 200215122 1 2 3 2 3 92 85 88 90 80 Student（被參照表）（被參照表） SC （參照表）（參照表） 學號改為學號改為200215124 將學號將學號200215122級聯(lián)修改為級聯(lián)修改為 200215124 An Introduction to Database System 置空值修改置空值修改：將：將Student表中表中Sno=200215122的元組的元組 的的Sno值改為值改為200215124。而將。而將SC表中所有表中所有 Sno=200215122的元組的的

32、元組的Sno值置為空值值置為空值 學學 號號 Sno 姓姓 名名 Sname 性性 別別 Ssex 年年 齡齡 Sage 所所 在在 系系 Sdept 200215121 200215122 200215123 200215125 李勇李勇 劉晨劉晨 王敏王敏 張立張立 男男 女女 女女 男男 20 19 18 19 CS CS MA IS 學學 號號 Sno 課程號課程號 Cno 成績成績 Grade 200215121 200215121 200215121 200215122 200215122 1 2 3 2 3 92 85 88 90 80 Student（被參照表）（被參照表） S

33、C （參照表）（參照表） 學號改為學號改為200215124 將學號將學號200215122置空置空 在學生選課數(shù)據(jù)庫中只有在學生選課數(shù)據(jù)庫中只有 第一種方法是正確的第一種方法是正確的 An Introduction to Database System 違約處理違約處理(續(xù)續(xù)) 例例4 顯式說明參照完整性的違約處理示例顯式說明參照完整性的違約處理示例 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY（Sno，Cno），）， FOREIGN KEY (Sno) REF

34、ERENCES Student(Sno) ON DELETE CASCADE /*級聯(lián)刪除級聯(lián)刪除SC表中相應的元組表中相應的元組*/ ON UPDATE CASCADE， /*級聯(lián)更新級聯(lián)更新SC表中相應的元組表中相應的元組*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE NO ACTION /*當刪除當刪除Course 表中的元組造成了與表中的元組造成了與SC表不一致時表不一致時拒絕刪除拒絕刪除*/ ON UPDATE CASCADE /*當更新當更新course表中的表中的Cno時，時，級聯(lián)更新級聯(lián)更新SC表中相應的元組表中相應的

35、元組*/ )； An Introduction to Database System 第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 5.1 實體完整性實體完整性 5.2 參照完整性參照完整性 5.3 用戶定義的完整性用戶定義的完整性 5.4 完整性約束命名字句完整性約束命名字句 *5.5 域中的完整性限制域中的完整性限制 5.6 觸發(fā)器觸發(fā)器 5.7 小結小結 An Introduction to Database System 5.3 用戶定義的完整性用戶定義的完整性 v用戶定義的完整性就是針對用戶定義的完整性就是針對某一具體應用某一具體應用的數(shù)據(jù)的數(shù)據(jù) 必須滿足的必須滿足的語義要求語義要求 vR

36、DBMS提供，而不必由應用程序承擔提供，而不必由應用程序承擔 An Introduction to Database System 5.3 用戶定義的完整性用戶定義的完整性 v5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義 v5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 v5.3.3 元組上的約束條件的定義元組上的約束條件的定義 v5.3.4元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理 An Introduction to Database System 5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義 vCREATE TABLE時

37、定義時定義 列值非空（列值非空（NOT NULL） 列值唯一（列值唯一（UNIQUE） 檢查列值是否滿足一個布爾表達式（檢查列值是否滿足一個布爾表達式（CHECK） An Introduction to Database System 屬性上的約束條件的定義屬性上的約束條件的定義(續(xù)續(xù)) v 1.不允許取空值不允許取空值 例例5 在定義在定義SC表時，說明表時，說明Sno、Cno、Grade屬性不允許取空值。屬性不允許取空值。 CREATE TABLE SC （Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT NOT NULL

38、， PRIMARY KEY (Sno， Cno)， /* 如果在表級定義如果在表級定義實體完整性實體完整性，隱含了，隱含了Sno，Cno不允許取空值，不允許取空值， 則在列級不允許取空值的定義就不必寫了則在列級不允許取空值的定義就不必寫了 * / ）；）； An Introduction to Database System 屬性上的約束條件的定義屬性上的約束條件的定義(續(xù)續(xù)) v 2.列值唯一列值唯一 例例6 建立部門表建立部門表DEPT，要求部門名稱，要求部門名稱Dname列取值唯一，部門編列取值唯一，部門編 號號Deptno列為主碼列為主碼 CREATE TABLE DEPT ( Dep

39、tno NUMERIC(2)， Dname CHAR(9) UNIQUE，/*要求要求Dname列值唯一列值唯一*/ Location CHAR(10)， PRIMARY KEY (Deptno) )； An Introduction to Database System 屬性上的約束條件的定義屬性上的約束條件的定義(續(xù)續(xù)) v 3. 用用CHECK短語短語指定列值應該滿足的條件指定列值應該滿足的條件 例例7 Student表的表的Ssex只允許取只允許取“男男”或或“女女”。 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(

40、8) NOT NULL， Ssex CHAR(2) CHECK (Ssex IN (男男，女女) ) ， /*性別屬性性別屬性Ssex只允許取只允許取男男或或女女 */ Sage SMALLINT， Sdept CHAR(20) ); An Introduction to Database System 5.3 用戶定義的完整性用戶定義的完整性 v5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義 v5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 v5.3.3 元組上的約束條件的定義元組上的約束條件的定義 v5.3.4元組上的約束條件檢查和違約處理元組上的約束條

41、件檢查和違約處理 An Introduction to Database System 5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 v插入元組插入元組或或修改屬性修改屬性的值時，的值時，RDBMS檢查檢查屬性屬性 上上的約束條件是否被滿足的約束條件是否被滿足 v如果不滿足則操作被拒絕執(zhí)行如果不滿足則操作被拒絕執(zhí)行 An Introduction to Database System 5.3 用戶定義的完整性用戶定義的完整性 v5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義 v5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 v5.

42、3.3 元組上的約束條件的定義元組上的約束條件的定義 v5.3.4元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理 An Introduction to Database System 5.3.3 元組上的約束條件的定義元組上的約束條件的定義 v 在在CREATE TABLE時可以用時可以用CHECK短語定義短語定義元組上元組上的約的約 束條件，即束條件，即元組級的限制元組級的限制 v 同同屬性值限制相比屬性值限制相比，元組級元組級的限制可以設置的限制可以設置不同屬性不同屬性之間之間 的的取值的相互約束條件取值的相互約束條件 An Introduction to Database

43、System 元組上的約束條件的定義元組上的約束條件的定義(續(xù)續(xù)) 例例9 當學生的當學生的性別性別是男時，其是男時，其名字名字不能以不能以Ms.打頭。打頭。 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(8) NOT NULL， Ssex CHAR(2)， Sage SMALLINT， Sdept CHAR(20)， PRIMARY KEY (Sno)， CHECK (Ssex=女女 OR Sname NOT LIKE Ms.%) /*定義了元組中定義了元組中Sname和和 Ssex兩個屬性值之間的約束條件兩個屬性值之間的約束條件*/ )； 性別

44、是女性的元組都能通過該項檢查，因為性別是女性的元組都能通過該項檢查，因為Ssex=女女成立成立 當性別是男性時，要通過檢查則名字一定不能以當性別是男性時，要通過檢查則名字一定不能以Ms.打頭打頭 An Introduction to Database System 5.3 用戶定義的完整性用戶定義的完整性 v5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義 v5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 v5.3.3 元組上的約束條件的定義元組上的約束條件的定義 v5.3.4 元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理 An Introdu

45、ction to Database System 5.3.4 元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理 v 插入元組插入元組或或修改屬性修改屬性的值時，的值時，RDBMS檢查檢查元組上元組上的約束的約束 條件是否被滿足條件是否被滿足 v 如果不滿足則操作被拒絕執(zhí)行如果不滿足則操作被拒絕執(zhí)行 An Introduction to Database System 第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 5.1 實體完整性實體完整性 5.2 參照完整性參照完整性 5.3 用戶定義的完整性用戶定義的完整性 5.4 完整性約束命名子句完整性約束命名子句 *5.5 域中的完整性限制域中

46、的完整性限制 5.6 觸發(fā)器觸發(fā)器 5.7 小結小結 靈活地增加、刪除靈活地增加、刪除 完整性約束條件完整性約束條件 An Introduction to Database System 5.4 完整性約束命名子句完整性約束命名子句 vCONSTRAINT 約束約束 CONSTRAINT PRIMARY KEY短語短語 |FOREIGN KEY短語短語 |NOT NULL短語短語 |UNIQUE短語短語 |CHECK短語短語 An Introduction to Database System 完整性約束命名子句完整性約束命名子句(續(xù)續(xù)) 例例10 建立學生登記表建立學生登記表Student，

47、要求學號在，要求學號在9000099999之間，姓名不能之間，姓名不能 取空值，年齡小于取空值，年齡小于30，性別只能是，性別只能是“男男”或或“女女”。 CREATE TABLE Student (Sno NUMERIC(6) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999)， Sname CHAR(20) CONSTRAINT C2 NOT NULL， Sage NUMERIC(3) CONSTRAINT C3 CHECK (Sage =3000) ); 完整性約束命名子句完整性約束命名子句(續(xù)續(xù)) v 練習練習1：建立一個：建立一個Cou

48、rse表，用完整性約束命名子句定義主碼、外碼、課程表，用完整性約束命名子句定義主碼、外碼、課程 名唯一，學分取自集合名唯一，學分取自集合1，2，3，4，5。 CREATE TABLE Course (Cno CHAR(4) ， Cname CHAR(40)， Cpno CHAR(4) ， Ccredit SMALLINT， CONSTRAINT C1 PRIMARY KEY(Cno), CONSTRAINT C2 UNIQUE(Cname), CONSTRAINT C3 Ccredit IN(1,2,3,4,5)， CONSTRAINT C4 FOREIGN KEY (Cpno) REFERE

49、NCES Course(Cno) ); An Introduction to Database System 完整性約束命名子句完整性約束命名子句(續(xù)續(xù)) v 練習練習2：建立一個：建立一個SC表，用完整性約束命名子句定義主碼、外碼、表，用完整性約束命名子句定義主碼、外碼、C 成績的取值在成績的取值在0到到100之間。之間。 CREATE TABLE SC (Sno CHAR(9)， Cno CHAR(4)， Grade SMALLINT， CONSTRAINT C1 PRIMARY KEY (Sno，Cno)， CONSTRAINT C2 FOREIGN KEY (Sno) REFERENC

50、ES Student(Sno)， CONSTRAINT C3 FOREIGN KEY (Cno) REFERENCES Course(Cno), CONSTRAINT C4 CHECK (Grade BETWEEN 0 AND 100) ) An Introduction to Database System An Introduction to Database System 完整性約束命名子句完整性約束命名子句(續(xù)續(xù)) v2. 修改修改表中表中的完整性限制的完整性限制 使用使用ALTER TABLE語句修改表中的完整性限語句修改表中的完整性限 制制 An Introduction to D

51、atabase System 完整性約束命名子句完整性約束命名子句(續(xù)續(xù)) 例例13 修改表修改表Student中的約束條件，要求學號改為中的約束條件，要求學號改為 在在900000999999之間，年齡由小于之間，年齡由小于30改為小于改為小于40 n可以先刪除原來的約束條件，再增加新的約束條件可以先刪除原來的約束條件，再增加新的約束條件 ALTER TABLE Student DROP CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999); ALTER TABLE

52、 Student DROP CONSTRAINT C3; ALTER TABLE Student ADD CONSTRAINT C3 CHECK (Sage 40)； An Introduction to Database System 第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 5.1 實體完整性實體完整性 5.2 參照完整性參照完整性 5.3 用戶定義的完整性用戶定義的完整性 5.4 完整性約束命名字句完整性約束命名字句 *5.5 域中的完整性限制域中的完整性限制 5.6 觸發(fā)器觸發(fā)器 5.7 小結小結 An Introduction to Database System 觸發(fā)器觸發(fā)器 v觸發(fā)

53、器觸發(fā)器（Trigger）是用戶定義在關系表上的一類）是用戶定義在關系表上的一類 由由事件驅動事件驅動的特殊過程的特殊過程 由服務器自動激活由服務器自動激活一旦定義了觸發(fā)器，任何用戶一旦定義了觸發(fā)器，任何用戶 對對表的增、刪、改表的增、刪、改操作均由服務器自動激活相應的觸操作均由服務器自動激活相應的觸 發(fā)器，發(fā)器，在在DBMS核心層進行集中的完整性控制核心層進行集中的完整性控制 可以進行更為復雜的檢查和操作，具有更精細和更強可以進行更為復雜的檢查和操作，具有更精細和更強 大的數(shù)據(jù)控制能力大的數(shù)據(jù)控制能力 觸發(fā)器（續(xù)）觸發(fā)器（續(xù)） 觸發(fā)器觸發(fā)器又叫做又叫做事件事件-條件條件-動作動作（event

54、-condition- action）規(guī)則）規(guī)則 當特定的系統(tǒng)事件發(fā)生時，對規(guī)則的條件進行檢當特定的系統(tǒng)事件發(fā)生時，對規(guī)則的條件進行檢 查，如果條件成立則執(zhí)行規(guī)則中的動作，否則不查，如果條件成立則執(zhí)行規(guī)則中的動作，否則不 執(zhí)行該動作執(zhí)行該動作 規(guī)則中的動作體可以很復雜，通常是一段規(guī)則中的動作體可以很復雜，通常是一段SQL存存 儲過程儲過程 An Introduction to Database System An Introduction to Database System 5.6 觸發(fā)器觸發(fā)器 v5.6.1 定義觸發(fā)器定義觸發(fā)器 v5.6.2 激活觸發(fā)器激活觸發(fā)器 v5.6.3 刪除觸發(fā)器

55、刪除觸發(fā)器 An Introduction to Database System 5.6.1 定義觸發(fā)器定義觸發(fā)器 vCREATE TRIGGER語法格式語法格式 CREATE TRIGGER BEFORE | AFTER ON FOR EACH ROW | STATEMENT WHEN An Introduction to Database System 定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù)) v定義觸發(fā)器的定義觸發(fā)器的語法說明語法說明: 1. 創(chuàng)建者：表的創(chuàng)建者：表的擁有者擁有者 2. 觸發(fā)器名觸發(fā)器名 3. 表名：觸發(fā)器的目標表表名：觸發(fā)器的目標表 4. 觸發(fā)事件：觸發(fā)事件：INSERT、DELE

56、TE、UPDATE 5. 觸發(fā)器類型觸發(fā)器類型 行級觸發(fā)器（行級觸發(fā)器（FOR EACH ROW） 語句級觸發(fā)器（語句級觸發(fā)器（FOR EACH STATEMENT） An Introduction to Database System 定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù)) v 例如例如,假設在例假設在例11的的TEACHER表上創(chuàng)建了一個表上創(chuàng)建了一個 AFTER UPDATE觸發(fā)器。如果表觸發(fā)器。如果表TEACHER有有1000行，行， 執(zhí)行如下語句：執(zhí)行如下語句： UPDATE TEACHER SET Deptno=5; 如果該觸發(fā)器為如果該觸發(fā)器為語句級觸發(fā)器語句級觸發(fā)器，那么執(zhí)行完該語句后，

57、觸發(fā)，那么執(zhí)行完該語句后，觸發(fā) 動作只發(fā)生動作只發(fā)生一次一次 如果是如果是行級觸發(fā)器行級觸發(fā)器，觸發(fā)動作將執(zhí)行，觸發(fā)動作將執(zhí)行1000次次 An Introduction to Database System 定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù)) v 6. 觸發(fā)條件觸發(fā)條件 觸發(fā)條件為真觸發(fā)條件為真 省略省略WHEN觸發(fā)條件觸發(fā)條件 v 7. 觸發(fā)動作體觸發(fā)動作體 觸發(fā)動作體可以是一個匿名觸發(fā)動作體可以是一個匿名PL/SQL過程塊過程塊 也可以是對已創(chuàng)建存儲過程的調(diào)用也可以是對已創(chuàng)建存儲過程的調(diào)用 An Introduction to Database System 定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù)) v

58、如果是如果是行級行級觸發(fā)器，在兩種情況下，用戶都可以在過程體觸發(fā)器，在兩種情況下，用戶都可以在過程體 中使用中使用NEW和和OLD引用引用UPDATE/INSERT事件之后的事件之后的新新 值值和和UPDATE/INSERT事件之前的事件之前的舊值舊值 v 如果是如果是語句級語句級觸發(fā)器，則不能在觸發(fā)動作體中使用觸發(fā)器，則不能在觸發(fā)動作體中使用NEW 和和OLD進行引用進行引用 v 如觸發(fā)動作體執(zhí)行失敗，激活觸發(fā)器的事件會終止如觸發(fā)動作體執(zhí)行失敗，激活觸發(fā)器的事件會終止 An Introduction to Database System 定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù)) 例例18 定義一個定義一

59、個BEFORE行級行級觸發(fā)器，為教師表觸發(fā)器，為教師表Teacher定義完整定義完整 性規(guī)則性規(guī)則“教授的工資不得低于教授的工資不得低于4000元，如果低于元，如果低于4000元，自動改為元，自動改為 4000元元”。 CREATE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON Teacher /*觸發(fā)事件是插入或更新操作觸發(fā)事件是插入或更新操作*/ FOR EACH ROW /*行級觸發(fā)器行級觸發(fā)器 */ AS BEGIN /*定義觸發(fā)動作體，是定義觸發(fā)動作體，是PL/SQL過程塊過程塊*/ IF (new.Job=教授教授)

60、 AND (new.Sal 4000) THEN new.Sal :=4000; END IF; END; 觸發(fā)器名觸發(fā)器名 觸發(fā)事件觸發(fā)事件 觸發(fā)器的目標表觸發(fā)器的目標表 觸發(fā)器類型觸發(fā)器類型 觸發(fā)動作體觸發(fā)動作體 An Introduction to Database System 定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù)) 例例19定義定義AFTER行級行級觸發(fā)器，觸發(fā)器，當教師表當教師表Teacher的工資的工資 發(fā)生變化后就自動在工資變化表發(fā)生變化后就自動在工資變化表Sal_log中增加一條相應中增加一條相應 記錄記錄 首先建立工資變化表首先建立工資變化表Sal_log CREATE TABLE