1、11:08:57數據庫原理與應用寶雞文理學院寶雞文理學院數據庫系統概論數據庫系統概論An Introduction to Database System第五章第五章 數據庫完整性數據庫完整性11:08:57數據庫原理與應用數據庫完整性數據庫完整性n數據庫的完整性n數據的正確性和相容性數據的正確性和相容性n數據的完整性和安全性是兩個不同概念n數據的完整性防止數據庫中存在不符合語義的數據，也就是防止數據庫中存在不正確的數據防范對象：不合語義的、不正確的數據n數據的安全性保護數據庫防止惡意的破壞和非法的存取防范對象：非法用戶和非法操作11:08:57數據庫原理與應用數據庫完整性數據庫完整性(續續)為

2、維護數據庫的完整性，DBMS必須：n1.提供定義完整性約束條件的機制n2.提供完整性檢查的方法n3.違約處理11:08:57數據庫原理與應用第五章第五章 數據庫完整性數據庫完整性5.1 實體完整性實體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名字句完整性約束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 觸發器觸發器5.7 小結小結11:08:57數據庫原理與應用5.1 實體完整性實體完整性n5.1.1 實體完整性定義實體完整性定義n5.1.2 實體完整性檢查和違約處理實體完整性檢查和違約處理11:08:57數據庫原理與應用5.1.

3、1 實體完整性定義實體完整性定義n關系模型的實體完整性nCREATE TABLE中用PRIMARY KEY定義n單屬性構成的碼有兩種說明方法 n定義為列級約束條件n定義為表級約束條件n對多個屬性構成的碼只有一種說明方法n定義為表級約束條件 11:08:57數據庫原理與應用實體完整性定義實體完整性定義(續續)例1 將Student表中的Sno屬性定義為碼 (1)在列級定義主碼在列級定義主碼 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sd

4、ept CHAR(20);11:08:57數據庫原理與應用實體完整性定義實體完整性定義(續續)(2)在表級定義主碼在表級定義主碼 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20)， PRIMARY KEY (Sno) ); 11:08:57數據庫原理與應用實體完整性定義實體完整性定義(續續)例2將SC表中的Sno，Cno屬性組定義為碼 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4)

5、NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno，Cno) /*只能在表級定義主碼*/ ); 11:08:57數據庫原理與應用5.1 實體完整性實體完整性n5.1.1 實體完整性定義實體完整性定義n5.1.2 實體完整性檢查和違約處理實體完整性檢查和違約處理11:08:57數據庫原理與應用5.1.2 實體完整性檢查和違約處理實體完整性檢查和違約處理n插入或對主碼列進行更新操作時，RDBMS按照實體完整性規則自動進行檢查。包括：n1. 檢查主碼值是否唯一，如果不唯一則拒絕插入或修改n2. 檢查主碼的各個屬性是否為空，只要有一個為空就拒絕插入或修改11:08:5

6、7數據庫原理與應用實體完整性檢查和違約處理實體完整性檢查和違約處理(續續)n檢查記錄中主碼值是否唯一的一種方法是進行全表掃描11:08:57數據庫原理與應用實體完整性檢查和違約處理實體完整性檢查和違約處理(續續)n索引 11:08:57數據庫原理與應用第五章第五章 數據庫完整性數據庫完整性5.1 實體完整性實體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名字句完整性約束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 觸發器觸發器5.7 小結小結11:08:57數據庫原理與應用5.2 參照完整性參照完整性n5.2.1 參照完整性定義參照

7、完整性定義n5.2.2 參照完整性檢查和違約處理參照完整性檢查和違約處理11:08:57數據庫原理與應用5.2.1 參照完整性定義參照完整性定義n關系模型的參照完整性定義n在CREATE TABLE中用FOREIGN KEY短語定義哪些列為外碼n用REFERENCES短語指明這些外碼參照哪些表的主碼 11:08:57數據庫原理與應用參照完整性定義參照完整性定義(續續)例如，關系SC中一個元組表示一個學生選修的某門課程的成績，（Sno，Cno）是主碼。Sno，Cno分別參照引用Student表的主碼和Course表的主碼 例3 定義SC中的參照完整性 CREATE TABLE SC (Sno C

8、HAR(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) /*在表級定義參照完整性*/ );11:08:57數據庫原理與應用5.2 參照完整性參照完整性n5.2.1 參照完整性定義參照完整性定義n5.2.2 參照完整性檢查和違約處理參照完整性檢查和違約處理11:08:57

9、數據庫原理與應用參照完整性檢查和違約處理參照完整性檢查和違約處理可能破壞參照完整性的情況及違約處理被參照表（例如Student）參照表（例如SC）違約處理可能破壞參照完整性 插入元組拒絕可能破壞參照完整性 修改外碼值拒絕刪除元組 可能破壞參照完整性拒絕/級連刪除/設置為空值修改主碼值 可能破壞參照完整性拒絕/級連修改/設置為空值11:08:58數據庫原理與應用違約處理違約處理n參照完整性違約處理n1. 拒絕(NO ACTION)執行n默認策略n2. 級聯(CASCADE)操作n3. 設置為空值（SET-NULL）n對于參照完整性，除了應該定義外碼，還應定義外碼列是否允許空值11:08:58數據

10、庫原理與應用違約處理違約處理(續續)例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 /*級聯刪除SC表中相應的元組*/ ON UPDATE CASCADE， /*級聯更新SC表中相應的元組*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELE

11、TE NO ACTION /*當刪除course 表中的元組造成了與SC表不一致時拒絕刪除*/ ON UPDATE CASCADE /*當更新course表中的cno時，級聯更新SC表中相應的元組*/ )；11:08:58數據庫原理與應用第五章第五章 數據庫完整性數據庫完整性5.1 實體完整性實體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名字句完整性約束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 觸發器觸發器5.7 小結小結11:08:58數據庫原理與應用5.3 用戶定義的完整性用戶定義的完整性n用戶定義的完整性就是針對某一具體

12、應用的數據必須滿足的語義要求 nRDBMS提供，而不必由應用程序承擔11:08:58數據庫原理與應用5.3 用戶定義的完整性用戶定義的完整性n5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義n5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 n5.3.3 元組上的約束條件的定義元組上的約束條件的定義 n5.3.4元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理11:08:58數據庫原理與應用5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義nCREATE TABLE時定義n列值非空（NOT NULL）n列值唯一（UNIQUE）n檢查列值是否滿足

13、一個布爾表達式（CHECK）11:08:58數據庫原理與應用屬性上的約束條件的定義屬性上的約束條件的定義(續續)n1.不允許取空值 例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不允許取空值，則在列級不允許取空值的定義就不必寫了 * / ）； 11:08:58數據庫原理與應用屬性上的約束條件的定義屬性

14、上的約束條件的定義(續續)n2.列值唯一 例6 建立部門表DEPT，要求部門名稱Dname列取值唯一，部門編號Deptno列為主碼 CREATE TABLE DEPT (Deptno NUMERIC(2)， Dname CHAR(9) UNIQUE，/*要求Dname列值唯一*/ Location CHAR(10)， PRIMARY KEY (Deptno) )；11:08:58數據庫原理與應用屬性上的約束條件的定義屬性上的約束條件的定義(續續)n3. 用CHECK短語指定列值應該滿足的條件例7 Student表的Ssex只允許取“男”或“女”。 CREATE TABLE Student (S

15、no CHAR(9) PRIMARY KEY， Sname CHAR(8) NOT NULL， Ssex CHAR(2) CHECK (Ssex IN (男，女) ) ， /*性別屬性Ssex只允許取男或女 */ Sage SMALLINT， Sdept CHAR(20) );11:08:58數據庫原理與應用5.3 用戶定義的完整性用戶定義的完整性n5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義n5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 n5.3.3 元組上的約束條件的定義元組上的約束條件的定義 n5.3.4元組上的約束條件檢查和違約處理元組上的約束條

16、件檢查和違約處理11:08:58數據庫原理與應用5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理n插入元組或修改屬性的值時，RDBMS檢查屬性上的約束條件是否被滿足n如果不滿足則操作被拒絕執行 11:08:58數據庫原理與應用5.3 用戶定義的完整性用戶定義的完整性n5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義n5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 n5.3.3 元組上的約束條件的定義元組上的約束條件的定義 n5.3.4元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理11:08:58數據庫原理與應用5.3.3

17、元組上的約束條件的定義元組上的約束條件的定義n在CREATE TABLE時可以用CHECK短語定義元組上的約束條件，即元組級的限制n同屬性值限制相比，元組級的限制可以設置不同屬性之間的取值的相互約束條件 11:08:58數據庫原理與應用元組上的約束條件的定義元組上的約束條件的定義(續續)例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 (

18、Ssex=女女 OR Sname NOT LIKE Ms.%) /*定義了元組中Sname和 Ssex兩個屬性值之間的約束條件*/ )；性別是女性的元組都能通過該項檢查，因為Ssex=女成立；當性別是男性時，要通過檢查則名字一定不能以Ms.打頭11:08:58數據庫原理與應用5.3 用戶定義的完整性用戶定義的完整性n5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義n5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 n5.3.3 元組上的約束條件的定義元組上的約束條件的定義 n5.3.4 元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理11:08:58

19、數據庫原理與應用5.3.4 元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理n插入元組或修改屬性的值時，RDBMS檢查元組上的約束條件是否被滿足n如果不滿足則操作被拒絕執行 11:08:58數據庫原理與應用第五章第五章 數據庫完整性數據庫完整性5.1 實體完整性實體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名子句完整性約束命名子句*5.5 域中的完整性限制域中的完整性限制5.6 觸發器觸發器5.7 小結小結11:08:58數據庫原理與應用5.4 完整性約束命名子句完整性約束命名子句nCONSTRAINT 約束CONSTRAINT

20、PRIMARY KEY短語 |FOREIGN KEY短語 |CHECK短語11:08:58數據庫原理與應用完整性約束命名子句完整性約束命名子句(續續)例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 CH

21、ECK (Sage 30)， Ssex CHAR(2) CONSTRAINT C4 CHECK (Ssex IN ( 男，女)， CONSTRAINT StudentKey PRIMARY KEY(Sno) )；在Student表上建立了5個約束條件，包括主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個列級約束。11:08:58數據庫原理與應用完整性約束命名子句完整性約束命名子句(續續)n2. 修改表中的完整性限制修改表中的完整性限制n使用ALTER TABLE語句修改表中的完整性限制11:08:58數據庫原理與應用完整性約束命名子句完整性約束命名子句(續續) 例13 修改

22、表Student中的約束條件，要求學號改為在900000999999之間，年齡由小于30改為小于40n可以先刪除原來的約束條件，再增加新的約束條件 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)；11:08:58數據庫原理與應

23、用第五章第五章 數據庫完整性數據庫完整性5.1 實體完整性實體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名字句完整性約束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 觸發器觸發器5.7 小結小結11:08:58數據庫原理與應用5.5 域中的完整性限制域中的完整性限制nSQL支持域的概念，并可以用CREATE DOMAIN語句建立一個域以及該域應該滿足的完整性約束條件。例14建立一個性別域，并聲明性別域的取值范圍 CREATE DOMAIN GenderDomain CHAR(2) CHECK (VALUE IN (男，女) );

24、這樣例10中對Ssex的說明可以改寫為 Ssex GenderDomain例15建立一個性別域GenderDomain，并對其中的限制命名 CREATE DOMAIN GenderDomain CHAR(2) CONSTRAINT GD CHECK ( VALUE IN (男，女) );11:08:58數據庫原理與應用域中的完整性限制域中的完整性限制(續續)例16刪除域GenderDomain的限制條件GD。 ALTER DOMAIN GenderDomain DROP CONSTRAINT GD;例17在域GenderDomain上增加限制條件GDD。 ALTER DOMAIN Gender

25、Domain ADD CONSTRAINT GDD CHECK (VALUE IN ( 1，0) ); 通過例16和例17，就把性別的取值范圍由(男，女)改為 ( 1，0) 11:08:58數據庫原理與應用第五章第五章 數據庫完整性數據庫完整性5.1 實體完整性實體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名字句完整性約束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 觸發器觸發器5.7 小結小結11:08:58數據庫原理與應用觸發器觸發器n觸發器（Trigger）是用戶定義在關系表上的一類由事件驅動的特殊過程n由服務器自動激活n可

26、以進行更為復雜的檢查和操作，具有更精細和更強大的數據控制能力 11:08:58數據庫原理與應用5.6 觸發器觸發器n5.6.1 定義觸發器定義觸發器 n5.6.2 激活觸發器激活觸發器 n5.6.3 刪除觸發器刪除觸發器 11:08:58數據庫原理與應用5.6.1 定義觸發器定義觸發器nCREATE TRIGGER語法格式 CREATE TRIGGER BEFORE | AFTER ON FOR EACH ROW | STATEMENT WHEN 11:08:58數據庫原理與應用定義觸發器定義觸發器(續續)n定義觸發器的語法說明:n1. 創建者：表的擁有者n2. 觸發器名n3. 表名：觸發器的

27、目標表n4. 觸發事件：INSERT、DELETE、UPDATEn5. 觸發器類型行級觸發器（FOR EACH ROW）語句級觸發器（FOR EACH STATEMENT）11:08:58數據庫原理與應用定義觸發器定義觸發器(續續)n例如,假設在例11的TEACHER表上創建了一個AFTER UPDATE觸發器。如果表TEACHER有1000行，執行如下語句： UPDATE TEACHER SET Deptno=5; n如果該觸發器為語句級觸發器，那么執行完該語句后，觸發動作只發生一次n如果是行級觸發器，觸發動作將執行1000次 11:08:58數據庫原理與應用定義觸發器定義觸發器(續續)n6

28、. 觸發條件n觸發條件為真n省略WHEN觸發條件n7. 觸發動作體n觸發動作體可以是一個匿名PL/SQL過程塊n也可以是對已創建存儲過程的調用11:08:58數據庫原理與應用定義觸發器定義觸發器(續續)例18 定義一個BEFORE行級觸發器，為教師表Teacher定義完整性規則“教授的工資不得低于4000元，如果低于4000元，自動改為4000元”。 CREATE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON Teacher /*觸發事件是插入或更新操作*/ FOR EACH ROW /*行級觸發器*/ AS BEGIN /*定

29、義觸發動作體，是PL/SQL過程塊*/ IF (new.Job=教授) AND (new.Sal 4000) THEN new.Sal :=4000; END IF; END; 11:08:58數據庫原理與應用定義觸發器定義觸發器(續續)例19定義AFTER行級觸發器，當教師表Teacher的工資發生變化后就自動在工資變化表Sal_log中增加一條相應記錄 首先建立工資變化表Sal_log CREATE TABLE Sal_log (Eno NUMERIC(4) references teacher(eno)， Sal NUMERIC(7，2)， Username char(10)， Date

30、 TIMESTAMP )；11:08:58數據庫原理與應用定義觸發器定義觸發器(續續) 例19（續）CREATE TRIGGER Insert_Sal AFTER INSERT ON Teacher /*觸發事件是INSERT*/ FOR EACH ROW AS BEGIN INSERT INTO Sal_log VALUES( new.Eno，new.Sal，CURRENT_USER，CURRENT_TIMESTAMP); END;11:08:58數據庫原理與應用定義觸發器定義觸發器(續續) 例19（續）CREATE TRIGGER Update_Sal AFTER UPDATE ON Teacher /*觸發事件是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;11:08:58數據庫原理與應用5.6 觸發器觸發器n5.6.1 定義觸發器定義觸發器 n5.6.2 激活觸發器激活觸發器 n5.6.3 刪除觸發器刪除觸發器 11:08:58數據庫原理與應用5.6.2 激活觸發器激活觸發器n觸發