1、1,數(shù)據(jù)庫系統(tǒng)原理,福州大學計算機系 程 燁 2010年4月14日,第一篇 基礎篇 第五章 數(shù)據(jù)庫完整性,2,數(shù)據(jù)庫完整性,數(shù)據(jù)庫的完整性是指數(shù)據(jù)庫中的數(shù)據(jù)必須始終滿足數(shù)據(jù)庫語義約束。保證數(shù)據(jù)庫中數(shù)據(jù)的正確性、有效性和相容性，防止錯誤的數(shù)據(jù)進入數(shù)據(jù)庫。,通過對數(shù)據(jù)庫中的數(shù)據(jù)進行語義定義來實現(xiàn)。,3,數(shù)據(jù)庫安全性與完整性,數(shù)據(jù)庫安全性與完整性是兩個不同的概念：,數(shù)據(jù)庫安全性的防范對象是非法用戶與非法操作；,數(shù)據(jù)庫完整性的防范對象是不合語義的數(shù)據(jù)。,4,數(shù)據(jù)庫安全性與完整性,兩者相似之處：,DBMS提供定義用戶對數(shù)據(jù)庫的操作權限的機制與定義完整性約束的機制；,將定義存儲在DBMS的數(shù)據(jù)字典中；,D

2、BMS完成對數(shù)據(jù)庫安全性，完整性的驗證功能。,5,數(shù)據(jù)庫完整性(續(xù)),數(shù)據(jù)庫完整性機制的任務是確保數(shù)據(jù)庫中存儲的數(shù)據(jù)符合現(xiàn)實世界語義。 為維護數(shù)據(jù)庫的完整性，DBMS必須： 1.提供定義完整性約束條件的機制 2.提供完整性檢查的方法 3.違約處理,6,第五章 數(shù)據(jù)庫完整性,5.1 實體完整性 5.2 參照完整性 5.3 用戶定義的完整性 5.4 完整性約束命名子句 *5.5 域中的完整性限制 5.6 觸發(fā)器 5.7 小結,7,5.1.1 實體完整性定義,關系模型的實體完整性 CREATE TABLE中用PRIMARY KEY定義 單屬性構成的碼有兩種說明方法 定義為列級約束條件 定義為表級約束

3、條件 對多個屬性構成的碼只有一種說明方法 定義為表級約束條件,8,5.1.2 實體完整性檢查和違約處理,插入或對主碼列進行更新操作時，RDBMS按照實體完整性規(guī)則自動進行檢查。包括： 1. 檢查主碼值是否唯一，如果不唯一則拒絕插入或修改 2. 檢查主碼的各個屬性是否為空，只要有一個為空就拒絕插入或修改,9,第五章 數(shù)據(jù)庫完整性,5.1 實體完整性 5.2 參照完整性 5.3 用戶定義的完整性 5.4 完整性約束命名字句 *5.5 域中的完整性限制 5.6 觸發(fā)器 5.7 小結,10,5.2.1 參照完整性定義,關系模型的參照完整性定義 在CREATE TABLE中用FOREIGN KEY短語定

4、義哪些列為外碼 用REFERENCES短語指明這些外碼參照哪些表的主碼,11,參照完整性定義(續(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 (Sno) REFERENCES Student(Sno)

5、， /*在表級定義參照完整性*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表級定義參照完整性*/ );,12,5.2.2參照完整性檢查和違約處理,可能破壞參照完整性的情況及違約處理,13,違約處理,參照完整性違約處理 1. 拒絕(NO ACTION)執(zhí)行 默認策略 2. 級聯(lián)(CASCADE)操作 需要進行一系列相關操作,保證數(shù)據(jù)的一致性 3. 設置為空值（SET-NULL） 對于參照完整性，除了應該定義外碼，還應定義外碼列是否允許空值,14,違約處理(續(xù)),例4 顯式說明參照完整性的違約處理示例 CREATE TABLE SC (Sno CHA

6、R(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 (Cno) REFERENCES Course(Cno) ON DELETE NO ACTION /*當刪除course 表中的元組造成了與SC表不一致時拒絕刪除*/ ON UPDATE CASC

7、ADE /*當更新course表中的cno時，級聯(lián)更新SC表中相應的元組*/ )；,15,第五章 數(shù)據(jù)庫完整性,5.1 實體完整性 5.2 參照完整性 5.3 用戶定義的完整性 5.4 完整性約束命名字句 *5.5 域中的完整性限制 5.6 觸發(fā)器 5.7 小結,16,5.3 用戶定義的完整性,用戶定義的完整性就是針對某一具體應用的數(shù)據(jù)必須滿足的語義要求 RDBMS提供，而不必由應用程序承擔,17,5.3 用戶定義的完整性,5.3.1 屬性上的約束條件的定義 5.3.2 屬性上的約束條件檢查和違約處理 5.3.3 元組上的約束條件的定義 5.3.4元組上的約束條件檢查和違約處理,18,5.3.

8、1 屬性上的約束條件的定義,CREATE TABLE時定義 列值非空（NOT NULL） 列值唯一（UNIQUE） 檢查列值是否滿足一個布爾表達式（CHECK）,19,屬性上的約束條件的定義(續(xù)),3. 用CHECK短語指定列值應該滿足的條件 例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， Sdep

9、t CHAR(20) );,20,5.3.2 屬性上的約束條件檢查和違約處理,插入元組或修改屬性的值時，RDBMS檢查屬性上的約束條件是否被滿足 如果不滿足則操作被拒絕執(zhí)行,21,5.3.3 元組上的約束條件的定義,在CREATE TABLE時可以用CHECK短語定義元組上的約束條件，即元組級的限制 同屬性值限制相比，元組級的限制可以設置不同屬性之間的取值的相互約束條件,22,元組上的約束條件的定義(續(xù)),例9 當學生的性別是男時，其名字不能以Ms.打頭。 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(8) NOT NULL， Ssex CHAR(

10、2)， Sage SMALLINT， Sdept CHAR(20)， PRIMARY KEY (Sno)， CHECK (Ssex=女 OR Sname NOT LIKE Ms.%) /*定義了元組中Sname和 Ssex兩個屬性值之間的約束條件*/ )； 性別是女性的元組都能通過該項檢查，因為Ssex=女成立； 當性別是男性時，要通過檢查則名字一定不能以Ms.打頭,23,5.3.4 元組上的約束條件檢查和違約處理,插入元組或修改屬性的值時，RDBMS檢查元組上的約束條件是否被滿足 如果不滿足則操作被拒絕執(zhí)行,24,第五章 數(shù)據(jù)庫完整性,5.1 實體完整性 5.2 參照完整性 5.3 用戶定義

11、的完整性 5.4 完整性約束命名子句 *5.5 域中的完整性限制 5.6 觸發(fā)器 5.7 小結,25,5.4 完整性約束命名子句,CONSTRAINT對完整性 約束條件命名 CONSTRAINT PRIMARY KEY短語 |FOREIGN KEY短語 |CHECK短語 作用可靈活地增加、刪除完整性 約束條件,26,完整性約束命名子句(續(xù)),例10 建立學生登記表Student，要求學號在9000099999之間，姓名不能取空值，年齡小于30，性別只能是“男”或“女”。 CREATE TABLE Student (Sno NUMERIC(6) CONSTRAINT C1 CHECK (Sno

12、BETWEEN 90000 AND 99999)， Sname CHAR(20) CONSTRAINT C2 NOT NULL， Sage NUMERIC(3) CONSTRAINT C3 CHECK (Sage 30)， Ssex CHAR(2) CONSTRAINT C4 CHECK (Ssex IN ( 男，女)， CONSTRAINT StudentKey PRIMARY KEY(Sno) )； 在Student表上建立了5個約束條件，包括主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個列級約束。,27,完整性約束命名子句(續(xù)),修改表中的完整性限制 使用ALTER

13、 TABLE語句修改表中的完整性限制 ALTER TABLE 表名 DROP CONSTRAINT 已定義的約束; ALTER TABLE 表名 ADD CONSTRAINT定義一個新的約束條件;,28,完整性約束命名子句(續(xù)),例13 修改表Student中的約束條件，要求學號改為在900000999999之間，年齡由小于30改為小于40 可以先刪除原來的約束條件，再增加新的約束條件 ALTER TABLE Student DROP CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 A

14、ND 999999); ALTER TABLE Student DROP CONSTRAINT C3; ALTER TABLE Student ADD CONSTRAINT C3 CHECK (Sage 40)；,29,第五章 數(shù)據(jù)庫完整性,5.1 實體完整性 5.2 參照完整性 5.3 用戶定義的完整性 5.4 完整性約束命名字句 *5.5 域中的完整性限制 5.6 觸發(fā)器 5.7 小結,30,5.5 域中的完整性限制,SQL支持域的概念，并可以用CREATE DOMAIN語句建立一個域以及該域應該滿足的完整性約束條件。 例14建立一個性別域，并聲明性別域的取值范圍 CREATE DOMAI

15、N GenderDomain CHAR(2) CHECK (VALUE IN (男，女) ); 這樣例10中對Ssex的說明可以改寫為 Ssex GenderDomain 例15建立一個性別域GenderDomain，并對其中的限制命名 CREATE DOMAIN GenderDomain CHAR(2) CONSTRAINT GD CHECK ( VALUE IN (男，女) );,31,域中的完整性限制(續(xù)),例16刪除域GenderDomain的限制條件GD。 ALTER DOMAIN GenderDomain DROP CONSTRAINT GD; 例17在域GenderDomain上

16、增加限制條件GDD。 ALTER DOMAIN GenderDomain ADD CONSTRAINT GDD CHECK (VALUE IN ( 1，0) ); 通過例16和例17，就把性別的取值范圍由(男，女)改為 ( 1，0),32,域中的完整性限制(續(xù)),例建立一個顏色域COLOR，并對其中的限制命名 CREATE DOMAIN COLOR CHAR(6) DEFAULT ? CONSTRAINT VALID_COLORS CHECK ( VALUE IN (Red，Yellow，Blue，Green，?) );,33,斷言,如果完整性約束牽涉面較廣，與多個關系有關，或者與聚合操作有關

17、，可以使用“斷言”（Assertions）機制。 句法： CREATE ASSERTION CHECK (條件); DROP ASSERTION 例每位教師開設的課程不能超過10門 CREATE ASSERTION ASSE1 CHECK ( 10 = ALL( SELECT COUNT(C#) FROM C GROUP BY TEACHER) );,34,第五章 數(shù)據(jù)庫完整性,5.1 實體完整性 5.2 參照完整性 5.3 用戶定義的完整性 5.4 完整性約束命名字句 *5.5 域中的完整性限制 5.6 觸發(fā)器 5.7 小結,35,存儲過程的概念,客戶/服務器數(shù)據(jù)庫與傳統(tǒng)的數(shù)據(jù)庫結構的一個很

18、重要的區(qū)別是，在傳統(tǒng)的數(shù)據(jù)庫中只存放數(shù)據(jù)，所有的應用程序都在用戶端，都與用戶實際運行的應用程序捆綁在一起；而在客戶/服務器結構的數(shù)據(jù)庫中，在數(shù)據(jù)庫中還可以存放程序，即存儲過程。,36,存儲過程是事先編好的、存儲在數(shù)據(jù)庫中的程序，這些程序用來完成對數(shù)據(jù)庫的指定操作。,利用存儲過程，可以避免在網絡上傳輸大量無用的信息或原始數(shù)據(jù)，只需要傳輸調用存儲過程的指令和數(shù)據(jù)庫服務器返回的處理結果。且可以反復調用，從而減輕程序編寫的工作量。,存儲過程的概念(續(xù)),37,不使用存儲過程時，所有的數(shù)據(jù)處理都在客戶端完成；而使用存儲過程時，可以使數(shù)據(jù)處理在服務器端完成。,38,系統(tǒng)本身提供了一些存儲過程，用于管理數(shù)據(jù)

19、庫服務器和顯示有關數(shù)據(jù)庫和用戶的信息，我們稱之為系統(tǒng)存儲過程,系統(tǒng)存儲過程,用戶存儲過程,用戶也可以編寫自己的存儲過程，并把它存放在數(shù)據(jù)庫中。這樣安排的主要目的就是要充分發(fā)揮數(shù)據(jù)庫服務器的功能，盡量減少網絡上的堵塞。,存儲過程的概念(續(xù)),39,此外，還可以利用存儲過程間接的實現(xiàn)一些安全控制功能。 例如：不允許直接接觸某些數(shù)據(jù)對象，可以授權用戶執(zhí)行某個查詢功能的存儲過程來完成信息查詢，達到隔離用戶的目的。,存儲過程的概念(續(xù)),40,存儲過程是客戶/服務器機制的一個重要組成部分，如果使用客戶/服務器機制的數(shù)據(jù)庫管理系統(tǒng)，但是不理解存儲過程或沒有充分利用存儲過程，那將使客戶/服務器機制的功能大打

20、折扣，使系統(tǒng)的整體性能可能降低很多。,存儲過程的概念(續(xù)),41,觸發(fā)器,觸發(fā)器（Trigger）是用戶定義在關系表上的一類由事件驅動的特殊過程, 它在滿足某個特定條件時自動觸發(fā)執(zhí)行。 由服務器自動激活 可以進行更為復雜的檢查和操作，具有更精細和更強大的數(shù)據(jù)控制能力,42,觸發(fā)器的用途,1. 觸發(fā)器可以通過級聯(lián)的方式對相關的表進行修改。比如，對父表的修改，可以引起對子孫表的一系列修改，從而保證數(shù)據(jù)的一致性和完整性。 2. 觸發(fā)器可以禁止或撤消違反參照完整性的修改。 3. 觸發(fā)器可以強制比用CHECK約束定義更加復雜的限制。,43,觸發(fā)器是依附于表的數(shù)據(jù)庫對象,一個觸發(fā)器和三部分內容有關： 定義

21、觸發(fā)器的表 激活觸發(fā)器的數(shù)據(jù)操作語句 觸發(fā)器要采取的動作,44,5.6 觸發(fā)器,5.6.1 定義觸發(fā)器 5.6.2 激活觸發(fā)器 5.6.3 刪除觸發(fā)器,45,5.6.1 定義觸發(fā)器,CREATE TRIGGER語法格式 CREATE TRIGGER BEFORE | AFTER ON FOR EACH ROW | STATEMENT WHEN ,46,定義觸發(fā)器(續(xù)),定義觸發(fā)器的語法說明: 1. 創(chuàng)建者：表的擁有者 2. 觸發(fā)器名 3. 表名：觸發(fā)器的目標表 4. 觸發(fā)事件：INSERT、DELETE、UPDATE 5. 觸發(fā)器類型 行級觸發(fā)器（FOR EACH ROW） 語句級觸發(fā)器（FO

22、R EACH STATEMENT）,47,定義觸發(fā)器(續(xù)),例如,假設在例11的TEACHER表上創(chuàng)建了一個AFTER UPDATE觸發(fā)器。如果表TEACHER有1000行，執(zhí)行如下語句： UPDATE TEACHER SET Deptno=5; 如果該觸發(fā)器為語句級觸發(fā)器，那么執(zhí)行完該語句后，觸發(fā)動作只發(fā)生一次 如果是行級觸發(fā)器，觸發(fā)動作將執(zhí)行1000次,48,定義觸發(fā)器(續(xù)),6. 觸發(fā)條件 觸發(fā)條件為真 省略WHEN觸發(fā)條件 7. 觸發(fā)動作體 觸發(fā)動作體可以是一個匿名PL/SQL過程塊 也可以是對已創(chuàng)建存儲過程的調用,49,定義觸發(fā)器(續(xù)),例18 定義一個BEFORE行級觸發(fā)器，為教師

23、表Teacher定義完整性規(guī)則“教授的工資不得低于4000元，如果低于4000元，自動改為4000元”。 CREATE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON Teacher FOR EACH ROW /*行級觸發(fā)器*/ AS BEGIN /*定義觸發(fā)動作體，是PL/SQL過程塊*/ IF (new.Job=教授) AND (new.Sal 4000) THEN new.Sal :=4000; END IF; END;,50,定義觸發(fā)器(續(xù)),例19定義AFTER行級觸發(fā)器，當教師表Teacher的工資發(fā)生變化后就自動在工

24、資變化表Sal_log中增加一條相應記錄 首先建立工資變化表Sal_log CREATE TABLE Sal_log (Eno NUMERIC(4) references teacher(eno)， Sal NUMERIC(7，2)， Username char(10)， Date TIMESTAMP )；,51,定義觸發(fā)器(續(xù)),例19（續(xù)） CREATE TRIGGER Insert_Sal AFTER INSERT ON Teacher /*觸發(fā)事件是INSERT*/ FOR EACH ROW AS BEGIN INSERT INTO Sal_log VALUES( new.Eno，ne

25、w.Sal，CURRENT_USER，CURRENT_TIMESTAMP); END;,52,定義觸發(fā)器(續(xù)),例19（續(xù)） CREATE TRIGGER Update_Sal AFTER UPDATE ON Teacher /*觸發(fā)事件是UPDATE */ FOR EACH ROW AS BEGIN IF (new.Sal old.Sal) THEN INSERT INTO Sal_log VALUES( new.Eno，new.Sal，CURRENT_USER，CURRENT_TIMESTAMP); END IF; END;,53,5.6 觸發(fā)器,5.6.1 定義觸發(fā)器 5.6.2 激活觸

26、發(fā)器 5.6.3 刪除觸發(fā)器,54,5.6.2 激活觸發(fā)器,觸發(fā)器的執(zhí)行，是由觸發(fā)事件激活的，并由數(shù)據(jù)庫服務器自動執(zhí)行 一個數(shù)據(jù)表上可能定義了多個觸發(fā)器 同一個表上的多個觸發(fā)器激活時遵循如下的執(zhí)行順序： （1） 執(zhí)行該表上的BEFORE觸發(fā)器； （2） 激活觸發(fā)器的SQL語句； （3） 執(zhí)行該表上的AFTER觸發(fā)器。,55,激活觸發(fā)器(續(xù)),例20執(zhí)行修改某個教師工資的SQL語句，激活上述定義的觸發(fā)器。 UPDATE Teacher SET Sal=800 WHERE Ename=陳平; 執(zhí)行順序是： 執(zhí)行觸發(fā)器Insert_Or_Update_Sal 執(zhí)行SQL語句“UPDATE Teach

27、er SET Sal=800 WHERE Ename=陳平;” 執(zhí)行觸發(fā)器Insert_Sal； 執(zhí)行觸發(fā)器Update_Sal,56,5.6 觸發(fā)器,5.6.1 定義觸發(fā)器 5.6.2 激活觸發(fā)器 5.6.3 刪除觸發(fā)器,57,5.6.3 刪除觸發(fā)器,刪除觸發(fā)器的SQL語法： DROP TRIGGER ON ; 觸發(fā)器必須是一個已經創(chuàng)建的觸發(fā)器，并且只能由具有相應權限的用戶刪除。 例21 刪除教師表Teacher上的觸發(fā)器Insert_Sal DROP TRIGGER Insert_Sal ON Teacher;,58,第五章 數(shù)據(jù)庫完整性,5.1 實體完整性 5.2 參照完整性 5.3 用戶定義的完整性 5.4 完整性約束命名字句 *5.5 域中的完整性限制 5.6 觸發(fā)器 5.7 小結,59,5.7 小結,數(shù)據(jù)庫的完整性是為了保證數(shù)據(jù)庫中存儲的數(shù)據(jù)是正確的 RDBMS完整性實現(xiàn)的機制 完整性約束定義機制 完整性檢查機制 違背完整性約束條件時RDBMS應采取的動作,例一個電影資料庫假定有3個基本表：電影表Movie、演員表Actor和電影主演表Acts。 其中Movie表的屬性包括電影名(Tit