1、數據庫系統(tǒng)概論An Introduction to Database System第五章 數據庫完整性數據庫完整性數據庫的完整性數據的正確性是指數據是符合現(xiàn)實世界語義，反映了當前實際狀況的數據的相容性是指數據庫同一對象在不同關系表中的數據是符合邏輯的例如，學生的學號必須唯一性別只能是男或女本科學生年齡的取值范圍為1450的整數學生所選的課程必須是學校開設的課程，學生所在的院系必須是學校已成立的院系等數據庫完整性（續(xù)）數據的完整性和安全性是兩個不同概念數據的完整性防止數據庫中存在不符合語義的數據，也就是防止數據庫中存在不正確的數據防范對象：不合語義的、不正確的數據數據的安全性保護數據庫 防止惡意

2、的破壞和非法的存取防范對象：非法用戶和非法操作數據庫完整性（續(xù)）為維護數據庫的完整性，數據庫管理系統(tǒng)必須：1.提供定義完整性約束條件的機制完整性約束條件也稱為完整性規(guī)則，是數據庫中的數據必須滿足的語義約束條件SQL標準使用了一系列概念來描述完整性，包括關系模型的實體完整性、參照完整性和用戶定義完整性這些完整性一般由SQL的數據定義語言語句來實現(xiàn) 數據庫完整性（續(xù)）2.提供完整性檢查的方法數據庫管理系統(tǒng)中檢查數據是否滿足完整性約束條件的機制稱為完整性檢查。一般在INSERT、UPDATE、DELETE語句執(zhí)行后開始檢查，也可以在事務提交時檢查 數據庫完整性（續(xù)）3.違約處理 數據庫管理系統(tǒng)若發(fā)現(xiàn)

3、用戶的操作違背了完整性約束條件，就采取一定的動作拒絕（NO ACTION）執(zhí)行該操作級連（CASCADE）執(zhí)行其他操作第五章 數據庫完整性5.1 實體完整性5.2 參照完整性5.3 用戶定義的完整性5.4 完整性約束命名字句*5.5 域中的完整性限制5.6 斷言5.7 觸發(fā)器5.8 小結5.1 實體完整性5.1.1 實體完整性定義5.1.2 實體完整性檢查和違約處理5.1.1 實體完整性定義關系模型的實體完整性CREATE TABLE中用PRIMARY KEY定義單屬性構成的碼有兩種說明方法 定義為列級約束條件定義為表級約束條件對多個屬性構成的碼只有一種說明方法定義為表級約束條件 實體完整性定

4、義（續(xù)）例5.1 將Student表中的Sno屬性定義為碼 （1）在列級定義主碼 CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) );實體完整性定義（續(xù)）（2）在表級定義主碼 CREATE TABLE Student ( Sno CHAR(9), Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY (

5、Sno) ); 實體完整性定義（續(xù)）例5.2 將SC表中的Sno，Cno屬性組定義為碼 CREATE TABLE SC ( Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT, PRIMARY KEY (Sno,Cno) /*只能在表級定義主碼*/ ); 5.1 實體完整性5.1.1 實體完整性定義5.1.2 實體完整性檢查和違約處理5.1.2 實體完整性檢查和違約處理插入或對主碼列進行更新操作時，關系數據庫管理系統(tǒng)按照實體完整性規(guī)則自動進行檢查。包括：檢查主碼值是否唯一，如果不唯一則拒絕插入或修改檢查主碼的各個屬性是否為空，只

6、要有一個為空就拒絕插入或修改實體完整性檢查和違約處理（續(xù)）檢查記錄中主碼值是否唯一的一種方法是進行全表掃描依次判斷表中每一條記錄的主碼值與將插入記錄上的主碼值（或者修改的新主碼值）是否相同 實體完整性檢查和違約處理（續(xù)）表掃描缺點十分耗時為避免對基本表進行全表掃描，RDBMS核心一般都在主碼上自動建立一個索引 實體完整性檢查和違約處理（續(xù)）B+樹索引例如，新插入記錄的主碼值是25通過主碼索引，從B+樹的根結點開始查找讀取3個結點：根結點（51）、中間結點（12 30）、葉結點（15 20 25）該主碼值已經存在，不能插入這條記錄第五章 數據庫完整性5.1 實體完整性5.2 參照完整性5.3 用

7、戶定義的完整性5.4 完整性約束命名字句*5.5 域中的完整性限制5.6 斷言5.7 觸發(fā)器5.8 小結5.2 參照完整性5.2.1 參照完整性定義5.2.2 參照完整性檢查和違約處理5.2.1 參照完整性定義關系模型的參照完整性定義在CREATE TABLE中用FOREIGN KEY短語定義哪些列為外碼用REFERENCES短語指明這些外碼參照哪些表的主碼 參照完整性定義（續(xù)）例如，關系SC中（Sno，Cno）是主碼。Sno，Cno分別參照Student表 的主碼和Course表的主碼 例5.3定義SC中的參照完整性 CREATE TABLE SC ( Sno CHAR(9) NOT NUL

8、L, Cno CHAR(4) NOT NULL, Grade SMALLINT, PRIMARY KEY (Sno, Cno), /*在表級定義實體完整性*/ FOREIGN KEY (Sno) REFERENCES Student(Sno), /*在表級定義參照完整性*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表級定義參照完整性*/ );5.2 參照完整性5.2.1 參照完整性定義5.2.2 參照完整性檢查和違約處理參照完整性檢查和違約處理一個參照完整性將兩個表中的相應元組聯(lián)系起來對被參照表和參照表進行增刪改操作時有可能破壞參照完整性，必須進

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

10、成SC表中某些元組的Sno屬性的值在表Student中找不到一個元組，其Sno屬性的值與之相等 。參照完整性檢查和違約處理（續(xù)）表5.1 可能破壞參照完整性的情況及違約處理被參照表（例如Student）參照表（例如SC）違約處理可能破壞參照完整性 插入元組拒絕可能破壞參照完整性 修改外碼值拒絕刪除元組 可能破壞參照完整性拒絕/級連刪除/設置為空值修改主碼值 可能破壞參照完整性拒絕/級連修改/設置為空值參照完整性檢查和違約處理（續(xù)）參照完整性違約處理（1） 拒絕（NO ACTION）執(zhí)行不允許該操作執(zhí)行。該策略一般設置為默認策略（2） 級聯(lián)（CASCADE）操作當刪除或修改被參照表（Studen

11、t）的一個元組造成了與參照表（SC）的不一致，則刪除或修改參照表中的所有造成不一致的元組（3）設置為空值（SET-NULL）當刪除或修改被參照表的一個元組時造成了不一致，則將參照表中的所有造成不一致的元組的對應屬性設置為空值。參照完整性檢查和違約處理（續(xù)）例如，有下面2個關系 學生（學號，姓名，性別，專業(yè)號，年齡） 專業(yè)（專業(yè)號，專業(yè)名）假設專業(yè)表中某個元組被刪除，專業(yè)號為12按照設置為空值的策略，就要把學生表中專業(yè)號=12的所有元組的專業(yè)號設置為空值對應語義：某個專業(yè)刪除了，該專業(yè)的所有學生專業(yè)未定，等待重新分配專業(yè) 外碼參照完整性檢查和違約處理（續(xù)）對于參照完整性，除了應該定義外碼，還應定

12、義外碼列是否允許空值 參見愛課程網數據庫系統(tǒng)概論5.2節(jié)動畫參照完整性參照完整性檢查和違約處理（續(xù)）例5.4 顯式說明參照完整性的違約處理示例 CREATE TABLE SC ( Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT, PRIMARY KEY(Sno,Cno)， FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADE /*級聯(lián)刪除SC表中相應的元組*/ ON UPDATE CASCADE, /*級聯(lián)更新SC表中相應的元組*/ FOREIGN KEY (

13、Cno) REFERENCES Course(Cno) ON DELETE NO ACTION /*當刪除course 表中的元組造成了與SC表不一致時拒絕刪除*/ ON UPDATE CASCADE /*當更新course表中的cno時，級聯(lián)更新SC表中相應的元組*/ );第五章 數據庫完整性5.1 實體完整性5.2 參照完整性5.3 用戶定義的完整性5.4 完整性約束命名字句*5.5 域中的完整性限制5.6 斷言5.7 觸發(fā)器5.8 小結5.3 用戶定義的完整性用戶定義的完整性是：針對某一具體應用的數據必須滿足的語義要求 關系數據庫管理系統(tǒng)提供了定義和檢驗用戶定義完整性的機制，不必由應用程

14、序承擔5.3 用戶定義的完整性5.3.1 屬性上的約束條件5.3.2 元組上的約束條件 1. 屬性上約束條件的定義CREATE TABLE時定義屬性上的約束條件列值非空（NOT NULL）列值唯一（UNIQUE）檢查列值是否滿足一個條件表達式（CHECK）屬性上約束條件的定義（續(xù)）（1）不允許取空值 例5.5 在定義SC表時，說明Sno、Cno、Grade屬性不允許取空值。 CREATE TABLE SC ( Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT NOT NULL, PRIMARY KEY (Sno, Cno),

15、/* 如果在表級定義實體完整性，隱含了Sno，Cno不允許取空值，則在 列級不允許取空值的定義 可以不寫 * / ); 屬性上約束條件的定義（續(xù)）（2）列值唯一 例5.6建立部門表DEPT，要求部門名稱Dname列取值唯一，部門編號Deptno列為主碼 CREATE TABLE DEPT ( Deptno NUMERIC(2), Dname CHAR(9) UNIQUE NOT NULL， /*要求Dname列值唯一, 并且不能取空值*/ Location CHAR(10), PRIMARY KEY (Deptno) );屬性上約束條件的定義（續(xù)）（3）用CHECK短語指定列值應該滿足的條件

16、例5.7 Student表的Ssex只允許取“男”或“女”。 CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(8) NOT NULL, Ssex CHAR(2) CHECK （Ssex IN （男,女）， /*性別屬性Ssex只允許取男或女 */ Sage SMALLINT, Sdept CHAR(20) );屬性上約束條件的定義（續(xù)）例5.8 SC表的Grade的值應該在0和100之間。 CREATE TABLE SC ( Sno CHAR(9) , Cno CHAR(4),Grade SMALLINT CHECK (Gr

17、ade=0 AND Grade =100)， /*Grade取值范圍是0到100*/ PRIMARY KEY (Sno,Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) );屬性上的約束條件檢查和違約處理插入元組或修改屬性的值時，關系數據庫管理系統(tǒng)檢查屬性上的約束條件是否被滿足如果不滿足則操作被拒絕執(zhí)行 2. 屬性上的約束條件檢查和違約處理5.3 用戶定義的完整性5.3.1 屬性上的約束條件5.3.2 元組上的約束條件1. 元組上約束條件的定義在CREATE TAB

18、LE時可以用CHECK短語定義元組上的約束條件，即元組級的限制同屬性值限制相比，元組級的限制可以設置不同屬性之間的取值的相互約束條件 元組上約束條件的定義（續(xù)）例5.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兩個屬性值之間的約束條件

19、*/ );性別是女性的元組都能通過該項檢查，因為Ssex=女成立;當性別是男性時，要通過檢查則名字一定不能以Ms.打頭元組上的約束條件檢查和違約處理插入元組或修改屬性的值時，關系數據庫管理系統(tǒng)檢查元組上的約束條件是否被滿足如果不滿足則操作被拒絕執(zhí)行 2. 元組上約束條件檢查和違約處理第五章 數據庫完整性5.1 實體完整性5.2 參照完整性5.3 用戶定義的完整性5.4 完整性約束命名子句*5.5 域中的完整性限制5.6 斷言5.7 觸發(fā)器5.8 小結5.4 完整性約束命名子句1.完整性約束命名子句CONSTRAINT 包括NOT NULL、UNIQUE、PRIMARY KEY短語、FOREIG

20、N KEY短語、CHECK短語等完整性約束命名子句（續(xù)）例5.10建立學生登記表Student，要求學號在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ù)）2. 修改表中的完整性限制

21、使用ALTER TABLE語句修改表中的完整性限制例5.12去掉例5.10 Student表中對性別的限制。 ALTER TABLE Student DROP CONSTRAINT C4;完整性約束命名子句（續(xù)） 例5.13 修改表Student中的約束條件，要求學號改為在900000999999之間，年齡由小于30改為小于40可以先刪除原來的約束條件，再增加新的約束條件 ALTER TABLE Student DROP CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 9999

22、99), ALTER TABLE Student DROP CONSTRAINT C3; ALTER TABLE Student ADD CONSTRAINT C3 CHECK(Sage 40);第五章 數據庫完整性5.1 實體完整性5.2 參照完整性5.3 用戶定義的完整性5.4 完整性約束命名字句*5.5 域中的完整性限制5.6 斷言5.7 觸發(fā)器5.8 小結斷言SQL中，可以使用 CREATE ASSERTION語句，通過聲明性斷言來指定更具一般性的約束。可以定義涉及多個表的或聚集操作的比較復雜的完整性約束。斷言創(chuàng)建以后，任何對斷言中所涉及的關系的操作都會觸發(fā)關系數據庫管理系統(tǒng)對斷言的檢

23、查，任何使斷言不為真值的操作都會被拒絕執(zhí)行斷言（續(xù)）1. 創(chuàng)建斷言的語句格式CREATE ASSERTION每個斷言都被賦予一個名字，中的約束條件與WHERE子句的條件表達式類似。例5.18 限制數據庫課程最多60名學生選修CREATE ASSERTION ASSE_SC_DB_NUMCHECK (60 = (select count(*) /*此斷言的謂詞涉及聚集操作count的SQL語句*/ From Course,SC Where SC.Cno=Course.Cno and Course.Cname =數據庫) );例5.19限制每一門課程最多60名學生選修CREATE ASSERTIO

24、N ASSE_SC_CNUM1CHECK(60 = ALL (SELECT count(*) FROM SC GROUP by cno) ); /*此斷言的謂詞，涉及聚集操作count 和分組函數group by的SQL語句*/斷言（續(xù)）例5.20限制每個學期每一門課程最多60名學生選修 首先需要修改SC表的模式，增加一個“學期（TERM）”屬性 ALTER TABLE SC ADD TERM DATE; 然后，定義斷言： CREATE ASSERTION ASSE_SC_CNUM2 CHECK(60 = ALL (SELECT count(*) FROM SC GROUP by cno,TE

25、RM) );斷言（續(xù)）2. 刪除斷言的語句格式為DROP ASSERTION ;如果斷言很復雜，則系統(tǒng)在檢測和維護斷言的開銷較高，這是在使用斷言時應該注意的斷言（續(xù)）第五章 數據庫完整性5.1 實體完整性5.2 參照完整性5.3 用戶定義的完整性5.4 完整性約束命名字句*5.5 域中的完整性限制5.6 斷言5.7 觸發(fā)器5.8 小結觸發(fā)器觸發(fā)器（Trigger）是用戶定義在關系表上的一類由事件驅動的特殊過程 觸發(fā)器保存在數據庫服務器中任何用戶對表的增、刪、改操作均由服務器自動激活相應的觸發(fā)器觸發(fā)器可以實施更為復雜的檢查和操作，具有更精細和更強大的數據控制能力 5.7 觸發(fā)器5.7.1 定義觸

26、發(fā)器 5.7.2 激活觸發(fā)器 5.7.3 刪除觸發(fā)器 5.7.1 定義觸發(fā)器CREATE TRIGGER語法格式 CREATE TRIGGER BEFORE | AFTER ON REFERENCING NEW|OLD ROW AS FOR EACH ROW | STATEMENT WHEN 觸發(fā)器又叫做事件-條件-動作（event-condition-action）規(guī)則。當特定的系統(tǒng)事件發(fā)生時，對規(guī)則的條件進行檢查，如果條件成立則執(zhí)行規(guī)則中的動作，否則不執(zhí)行該動作。規(guī)則中的動作體可以很復雜，通常是一段SQL存儲過程。定義觸發(fā)器（續(xù)）定義觸發(fā)器的語法說明（1）表的擁有者才可以在表上創(chuàng)建觸發(fā)器（

27、2）觸發(fā)器名觸發(fā)器名可以包含模式名，也可以不包含模式名同一模式下，觸發(fā)器名必須是唯一的觸發(fā)器名和表名必須在同一模式下（3）表名觸發(fā)器只能定義在基本表上，不能定義在視圖上當基本表的數據發(fā)生變化時，將激活定義在該表上相應觸 發(fā)事件的觸發(fā)器定義觸發(fā)器（續(xù)）（4）觸發(fā)事件觸發(fā)事件可以是INSERT、DELETE或UPDATE 也可以是這幾個事件的組合還可以UPDATE OF，即進一步指明修改哪些列時激活觸發(fā)器AFTER/BEFORE是觸發(fā)的時機AFTER表示在觸發(fā)事件的操作執(zhí)行之后激活觸發(fā)器BEFORE表示在觸發(fā)事件的操作執(zhí)行之前激活觸發(fā)器定義觸發(fā)器（續(xù)）（5）觸發(fā)器類型行級觸發(fā)器（FOR EACH

28、ROW）語句級觸發(fā)器（FOR EACH STATEMENT） 例如,在例5.11的TEACHER表上創(chuàng)建一個AFTER UPDATE觸發(fā)器，觸發(fā)事件是UPDATE語句： UPDATE TEACHER SET Deptno=5; 假設表TEACHER有1000行 如果是語句級觸發(fā)器，那么執(zhí)行完該語句后，觸發(fā)動作只發(fā)生一次如果是行級觸發(fā)器，觸發(fā)動作將執(zhí)行1000次定義觸發(fā)器（續(xù)）（6）觸發(fā)條件觸發(fā)器被激活時，只有當觸發(fā)條件為真時觸發(fā)動作體才執(zhí)行;否則觸發(fā)動作體不執(zhí)行。如果省略WHEN觸發(fā)條件，則觸發(fā)動作體在觸發(fā)器激活后立即執(zhí)行定義觸發(fā)器（續(xù)）（7）觸發(fā)動作體觸發(fā)動作體可以是一個匿名PL/SQL過程

29、塊 也可以是對已創(chuàng)建存儲過程的調用如果是行級觸發(fā)器，用戶都可以在過程體中使用NEW和OLD引用事件之后的新值和事件之前的舊值如果是語句級觸發(fā)器，則不能在觸發(fā)動作體中使用NEW或OLD進行引用如果觸發(fā)動作體執(zhí)行失敗，激活觸發(fā)器的事件就會終止執(zhí)行，觸發(fā)器的目標表或觸發(fā)器可能影響的其他對象不發(fā)生任何變化 注意：不同的RDBMS產品觸發(fā)器語法各部相同定義觸發(fā)器（續(xù)）例5.21當對表SC的Grade屬性進行修改時，若分數增加了10%則將此次操作記錄到下面表中： SC_U（Sno,Cno,Oldgrade,Newgrade） 其中Oldgrade是修改前的分數，Newgrade是修改后的分數。CREATE

30、 TRIGGER SC_TAFTER UPDATE OF Grade ON SC REFERENCING OLD row AS OldTuple, NEW row AS NewTupleFOR EACH ROW WHEN (NewTuple.Grade = 1.1*OldTuple.Grade) INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade) VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)定義觸發(fā)器（續(xù)）例5.22 將每次對表Student的插入操作所增加的學生個數記錄到表S