數(shù)據庫系統(tǒng)概論：chp5 數(shù)據庫完整性

1、數(shù)據庫系統(tǒng)概論An Introduction to Database System第五章 數(shù)據庫完整性Database Integrity 數(shù)據庫完整性DBS能否真實地反映現(xiàn)實世界數(shù)據的正確性和相容性防止數(shù)據庫中存在不符合語義規(guī)定的數(shù)據防止因錯誤信息的輸入輸出造成無效操作或錯誤信息 在用Insert, Update, Delete語句修改數(shù)據庫內容時，數(shù)據的完整性可能會遭到破壞，例如： 1. 無效的數(shù)據被添加到數(shù)據庫中。如：某定單所指的產品不存在。 2. 對數(shù)據庫的修改不一致，如：為某產品增加了一份定單，但卻沒有調整產品的庫存信息。 3. 將存在的數(shù)據修改為無效的數(shù)據，如：將某學生的班號修改為

2、并不存在的班級數(shù)據的完整性 VS 安全性數(shù)據的完整性防止數(shù)據庫中存在不符合語義的數(shù)據，防范對象：不合語義的、不正確的數(shù)據例: 學生的年齡必須是整數(shù)，取值范圍為14-29； 學生的性別只能是男或女； 學生的學號一定是唯一的； 學生所在的系必須是學校開設的系； 數(shù)據的安全性保護數(shù)據庫防止惡意的破壞和非法的存取防范對象：非法用戶和非法操作DBS能否真實地反映現(xiàn)實世界“垃圾進垃圾出”只能造成的無效操作和錯誤結果。數(shù)據庫完整性(續(xù))由各種各樣的完整性約束來保證，因此可以說數(shù)據庫完整性設計就是數(shù)據庫完整性約束的設計。數(shù)據庫完整性約束，可以通過DBMS或應用程序來實現(xiàn)。完整性約束條件 加在數(shù)據上的語義約束條

3、件；為保證數(shù)據的完整性而規(guī)定的條件；完整性約束條件作為模式的一部分，存入數(shù)據庫中。數(shù)據庫完整性(續(xù))數(shù)據庫的完整性，DBMS必須：1. 完整性約束條件定義機制：提供定義數(shù)據完整性約束條件的方法，完整性約束條件作為模式的一部分存入數(shù)據庫中；2. 完整性檢查機制：進行完整性檢查，檢查數(shù)據是否滿足約束條件；3. 違約反應：若有違反，采取相應措施（拒絕、報告、改正）處理。完整性子系統(tǒng)：DBMS中執(zhí)行完整性控制的子系統(tǒng)。其功能為監(jiān)督事務的執(zhí)行，測試其是否違反完整性約束條件；若有違反，則分情況進行預定的處理。 完整性約束條件完整性約束條件 加在數(shù)據上的語義約束條件；為保證數(shù)據的完整性而規(guī)定的條件；完整性約

4、束條件作為模式的一部分，存入數(shù)據庫中。完整性檢查是圍繞完整性約束條件進行的，因此完整性約束條件，是完整性控制機制的核心。第五章 數(shù)據庫完整性5.1 實體完整性5.2 參照完整性5.3 用戶定義的完整性5.4 完整性約束命名字句*5.5 域中的完整性限制5.6 觸發(fā)器5.7 小結實體完整性規(guī)則（Entity Integrity） 若屬性A是基本關系R的主屬性，則屬性A不能取空值(1) 實體完整性規(guī)則是針對基本關系而言的。一個基本表通常對應現(xiàn)實世界的一個實體集。(2) 現(xiàn)實世界中的實體是可區(qū)分的，即它們具有某種唯一性標識。(3) 關系模型中以主碼作為唯一性標識。(4) 主碼中的屬性即主屬性不能取空

5、值。主屬性如果取空值，就說明存在某個不可標識的實體，即存在不可區(qū)分的實體，這與第（2）點相矛盾，因此這個規(guī)則稱為實體完整性5.1 實體完整性5.1.1 實體完整性定義方式單屬性多屬性5.1.2 實體完整性檢查和違約處理5.1.1 實體完整性定義關系模型的實體完整性CREATE TABLE中用PRIMARY KEY定義單屬性構成的碼有兩種說明方法 定義為列級約束條件定義為表級約束條件對多個屬性構成的碼只有一種說明方法定義為表級約束條件 實體完整性定義(續(xù))例1 將Student表中的Sno屬性定義為碼 (1)在列級定義主碼CREATE TABLE Student (Sno CHAR(9) PRI

6、MARY KEY， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20);(2)在表級定義主碼 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20)， PRIMARY KEY (Sno) ); 實體完整性定義(續(xù))例2將SC表中的Sno，Cno屬性組定義為碼 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4

7、) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno，Cno) /*只能在表級定義主碼*/ ); 5.1 實體完整性5.1.1 實體完整性定義5.1.2 實體完整性檢查和違約處理自動進行檢查違約則拒絕方式：全表掃描、使用索引5.1.2 實體完整性檢查和違約處理插入或對主碼列進行更新操作時，RDBMS按照實體完整性規(guī)則自動進行檢查。包括：1. 檢查主碼值是否唯一，如果不唯一則拒絕插入或修改2. 檢查主碼的各個屬性是否為空，只要有一個為空就拒絕插入或修改實體完整性檢查和違約處理(續(xù))檢查記錄中主碼值是否唯一的一種方法是進行全表掃描實體完整性檢查和違約處理(續(xù))為

8、了提高效率，使用索引 SQL2000中的實體完整性PRIMARY KEY 約束IDENTITY 屬性通過索引、UNIQUE 約束、第五章 數(shù)據庫完整性5.1 實體完整性5.2 參照完整性5.3 用戶定義的完整性5.4 完整性約束命名字句*5.5 域中的完整性限制5.6 觸發(fā)器5.7 小結參照/引用完整性規(guī)則若屬性（或屬性組）F是基本關系R的外碼，它與基本關系S的主碼Ks相對應（基本關系R和S不一定是不同的關系），則對于R中每個元組在F上的值必須為：或者取空值（F的每個屬性值均為空值）或者等于S中某個元組的主碼值學生（學號，姓名，性別，專業(yè)號，年齡）專業(yè)（專業(yè)號，專業(yè)名）5.2 參照完整性5.2

9、.1 參照完整性定義5.2.2 參照完整性檢查和違約處理引用完整性確保鍵值在所有表中一致；這樣的一致性要求不能引用不存在的值；如果鍵值更改了，那么在整個數(shù)據庫中，對該鍵值的所有引用要進行一致的更改。 5.2.1 參照完整性定義關系模型的參照完整性定義在CREATE TABLE中用FOREIGN KEY短語，定義哪些列為外碼用REFERENCES短語，指明這些外碼參照哪些表的主碼 參照完整性定義(續(xù))例如，關系SC中一個元組表示一個學生選修的某門課程的成績，（Sno，Cno）是主碼。Sno，Cno分別參照引用Student表的主碼和Course表的主碼 例3 定義SC中的參照完整性 CREATE

10、 TABLE SC (Sno CHAR(9) NOT NULL， 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.2參照完整性檢查和違約處理可能破壞參照完整性的情況及違約處理被參照表（例如Student）參照表（例如SC）違約處理可能破壞參照完整性 插入

11、元組拒絕可能破壞參照完整性 修改外碼值拒絕刪除元組 可能破壞參照完整性拒絕/級連刪除/設置為空值修改主碼值 可能破壞參照完整性拒絕/級連修改/設置為空值參照完整性違約處理1. 拒絕(NO ACTION)執(zhí)行默認策略2. 級聯(lián)(CASCADE)操作將參照關系(子表)中，所有外碼值與被參照關系(主表)中要刪除元組主碼值相同的元組，一起刪除。3. 設置為空值（SET-NULL）刪除被參照關系(主表)的元組, 并將參照關系(子表)中相應元組的外碼置空值。對于參照完整性，除了應該定義外碼，還應定義外碼列是否允許空值例4 顯式說明參照完整性的違約處理示例 CREATE TABLE SC (Sno CHAR

12、(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 CASCA

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

14、NULL）列值唯一（UNIQUE）檢查列值是否滿足一個布爾表達式（CHECK）1.不允許取空值 NOT NULL例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)， /* 如果在表級定義實體完整性，隱含了Sno，Cno不允許取空值，則在列級不允許取空值的定義就不必寫了 * / ）； 2.列值唯一 UNIQUE例6 建立部門表DEPT，要求部門名稱Dname列取

15、值唯一，部門編號Deptno列為主碼 CREATE TABLE DEPT (Deptno NUMERIC(2)， Dname CHAR(9) UNIQUE，/*要求Dname列值唯一*/ Location CHAR(10)， PRIMARY KEY (Deptno) )；3. 用CHECK短語 用CHECK短語指定列值應該滿足的條件例7 Student表的Ssex只允許取“男”或“女”。 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(8) NOT NULL， Ssex CHAR(2) CHECK (Ssex IN (男，女)

16、 ) ， /*性別屬性Ssex只允許取男或女 */ Sage SMALLINT， Sdept CHAR(20) );5.3 用戶定義的完整性5.3.1 屬性上的約束條件的定義5.3.2 屬性上的約束條件檢查和違約處理 插入元組或修改屬性的值時，RDBMS檢查屬性上的約束條件是否被滿足如果不滿足則操作被拒絕執(zhí)行 5.3.3 元組上的約束條件的定義 5.3.4元組上的約束條件檢查和違約處理5.3 用戶定義的完整性5.3.1 屬性上的約束條件的定義5.3.2 屬性上的約束條件檢查和違約處理 5.3.3 元組上的約束條件的定義 5.3.4元組上的約束條件檢查和違約處理5.3.3 元組上的約束條件的定義

17、在CREATE TABLE時，可以用CHECK短語，定義元組上的約束條件，即元組級的限制同屬性值限制相比，元組級的限制可以設置不同屬性之間的取值的相互約束條件 例9 當學生的性別是男時，其名字不能以Ms.打頭。 CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY ， Sname CHAR(8) NOT NULL， Ssex CHAR(2)， CHECK (Ssex=女 OR Sname NOT LIKE Ms.%) /*定義了元組中Sname和 Ssex兩個屬性值之間的約束條件*/ )性別是女性的元組都能通過該項檢查，因為Ssex=女成立；當性別是男性

18、時，要通過檢查則名字一定不能以Ms.打頭5.3 用戶定義的完整性5.3.1 屬性上的約束條件的定義5.3.2 屬性上的約束條件檢查和違約處理 5.3.3 元組上的約束條件的定義 5.3.4 元組上的約束條件檢查和違約處理插入元組或修改屬性的值時，RDBMS檢查元組上的約束條件是否被滿足如果不滿足則操作被拒絕執(zhí)行 第五章 數(shù)據庫完整性5.1 實體完整性5.2 參照完整性5.3 用戶定義的完整性5.4 完整性約束命名子句*5.5 域中的完整性限制5.6 觸發(fā)器5.7 小結5.4 完整性約束命名子句1、CONSTRAINT 約束CONSTRAINT PRIMARY KEY短語 | FOREIGN K

19、EY短語 | CHECK短語 例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 30)， Ssex CHAR(2) CONSTRAINT C4 CHECK (Ssex IN ( 男

20、，女)， CONSTRAINT StudentKey PRIMARY KEY(Sno) )；在Student表上建立了5個約束條件，包括主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個列級約束。5.4 完整性約束命名子句2. 修改表中的完整性限制使用ALTER TABLE語句，修改表中的完整性限制可以先刪除原來的約束條件，再增加新的約束條件 ALTER TABLE Student DROP CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK ( Sno BETWEEN 900000 AND 999999 );

21、例13 修改表Student中的約束條件，要求學號改為在900000999999之間，年齡由小于30改為小于40可以先刪除原來的約束條件，再增加新的約束條件 ALTER TABLE Student DROP CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999)， ALTER TABLE Student DROP CONSTRAINT C3; ALTER TABLE Student ADD CONSTRAINT C3 CHECK (Sage 40)；第五章 數(shù)據庫完整性

22、5.1 實體完整性5.2 參照完整性5.3 用戶定義的完整性5.4 完整性約束命名字句*5.5 域中的完整性限制5.6 觸發(fā)器5.7 小結5.5 域中的完整性限制SQL支持域的概念，并可以用CREATE DOMAIN語句建立一個域，以及該域應該滿足的完整性約束條件。例14建立一個域，并聲明域的取值范圍例15建立一個域，并對其中的限制命名例16刪除域的限制條件GD。例17在域上增加限制條件GDD。 5.5 域中的完整性限制例14建立一個性別域，并聲明性別域的取值范圍 CREATE DOMAIN GenderDomain CHAR(2) CHECK (VALUE IN (男，女) );例10中對S

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

24、VALUE IN ( 1，0) ); 通過例16和例17，就把性別的取值范圍由(男，女)改為 ( 1，0) 第五章 數(shù)據庫完整性5.1 實體完整性5.2 參照完整性5.3 用戶定義的完整性5.4 完整性約束命名字句*5.5 域中的完整性限制5.6 觸發(fā)器5.7 小結觸發(fā)器Trigger前面提到的一些約束機制屬于被動約束機制，在檢查出違背約束后，只能做些比較簡單的動作，比如拒絕操作。比較復雜的操作還需要程序員去安排。如果希望在某個操作后，系統(tǒng)能自動根據條件轉去執(zhí)行各種操作，甚至執(zhí)行與原操作無關的一些操作，那么需要新機制來實現(xiàn)。觸發(fā)器Trigger用戶定義在關系表上的、由事件驅動的特殊過程由服務器

25、自動激活可以進行更為復雜的檢查和操作，具有更精細和更強大的數(shù)據控制能力 5.6 觸發(fā)器觸發(fā)器是用戶定義在關系表上的、由事件驅動的特殊過程，能由系統(tǒng)自動執(zhí)行對數(shù)據庫修改的語句。觸發(fā)器有時也稱為主動規(guī)則、或事件-條件-動作規(guī)則(簡稱為ECA規(guī)則)。5.6.1 定義觸發(fā)器 5.6.2 激活觸發(fā)器 5.6.3 刪除觸發(fā)器 觸發(fā)器：事件-條件-動作規(guī)則觸發(fā)事件：是指對數(shù)據庫的插入Insert 、刪除delete 、修改update等操作。這些事件發(fā)生時，觸發(fā)器將開始工作。約束條件：觸發(fā)器將測試約束條件是否成立。如果成立就執(zhí)行相應的動作，否則什么也不做。違約反映動作：指明觸發(fā)器執(zhí)行的動作是什么。如果觸發(fā)器

26、測試滿足預定的條件，那么就由DBMS執(zhí)行這些動作（即對數(shù)據庫的操作）。這些動作能使觸發(fā)事件不發(fā)生，也可以是一系列對數(shù)據庫的操作，甚至可以是與觸發(fā)事件本身無關的其他操作。5.6.1 定義觸發(fā)器 CREATE TRIGGER BEFORE | AFTER ON FOR EACH ROW | STATEMENT WHEN 例18 定義一個BEFORE行級觸發(fā)器為教師表Teacher定義完整性規(guī)則：“教授的工資不得低于4000元，如果低于4000元，自動改為4000元”。 CREATE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON Te

27、acher /*觸發(fā)事件是插入或更新操作*/ FOR EACH ROW /*行級觸發(fā)器*/ AS BEGIN /*定義觸發(fā)動作體，是PL/SQL過程塊*/ IF (new.Job=教授) AND (new.Sal 4000) THEN new.Sal :=4000; END IF; END; 定義觸發(fā)器的語法說明:1. 創(chuàng)建者：表的擁有者2. 觸發(fā)器名3. 表名：觸發(fā)器的目標表4. 觸發(fā)事件：INSERT、DELETE、UPDATE5. 觸發(fā)器類型行級觸發(fā)器（FOR EACH ROW）語句級觸發(fā)器（FOR EACH STATEMENT）6. 觸發(fā)條件觸發(fā)條件為真省略WHEN觸發(fā)條件7. 觸發(fā)動

28、作體觸發(fā)動作體可以是一個匿名PL/SQL過程塊也可以是對已創(chuàng)建存儲過程的調用觸發(fā)器類型行級觸發(fā)器（FOR EACH ROW）語句級觸發(fā)器（FOR EACH STATEMENT）例如,假設在例11的TEACHER表上創(chuàng)建了一個AFTER UPDATE觸發(fā)器。如果表TEACHER有1000行，執(zhí)行如下語句： UPDATE TEACHER SET Deptno=5; 如果該觸發(fā)器為語句級觸發(fā)器，那么執(zhí)行完該語句后，觸發(fā)動作只發(fā)生一次如果是行級觸發(fā)器，觸發(fā)動作將執(zhí)行1000次 （所有涉及的行）例19定義AFTER行級觸發(fā)器 當教師表Teacher的工資發(fā)生變化后，就自動在工資變化表Sal_log中增加

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