1、第5章 數(shù)據(jù)庫完整性本章重點：掌握數(shù)據(jù)庫完整性約束定義、檢驗方法及違約處理掌握觸發(fā)器定義及存放過程定義本章難點： 觸發(fā)器定義及存放過程定義了解內(nèi)容： 域中完整性控制第1頁第5章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性數(shù)據(jù)正確性和相容性數(shù)據(jù)完整性和安全性是兩個不一樣概念數(shù)據(jù)完整性預防數(shù)據(jù)庫中存在不符合語義數(shù)據(jù)，也就是預防數(shù)據(jù)庫中存在不正確數(shù)據(jù)防范對象：不合語義、不正確數(shù)據(jù)數(shù)據(jù)安全性保護數(shù)據(jù)庫預防惡意破壞和非法存取防范對象：非法用戶和非法操作第2頁第5章 數(shù)據(jù)庫完整性為維護數(shù)據(jù)庫完整性，DBMS必須：1.提供定義完整性約束條件機制2.提供完整性檢驗方法3.違約處理第3頁實體完整性參考完整性用戶定義完整性完整性約

2、束命名字句觸發(fā)器存放過程第5章 數(shù)據(jù)庫完整性第4頁5.1 實體完整性實體完整性定義實體完整性檢驗和違約處理第5頁5.1.1 實體完整性定義關(guān)系模型實體完整性CREATE TABLE中用PRIMARY KEY定義單屬性組成碼有兩種說明方法 定義為列級約束條件定義為表級約束條件對多個屬性組成碼只有一個說明方法定義為表級約束條件 第6頁5.1.1 實體完整性定義例1 將Student表中Sno屬性定義為碼 (1)在列級定義主碼 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ，

3、Sage SMALLINT， Sdept CHAR(20);第7頁(2)在表級定義主碼 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20)， PRIMARY KEY (Sno) ); 5.1.1 實體完整性定義第8頁5.1.1 實體完整性定義例2 將SC表中Sno，Cno屬性組定義為碼 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT，

4、PRIMARY KEY (Sno，Cno) /*只能在表級定義主碼*/ ); 第9頁5.1.2 實體完整性檢驗和違約處理插入或?qū)χ鞔a列進行更新操作時，RDBMS按照實體完整性規(guī)則自動進行檢驗。包含：1. 檢驗主碼值是否唯一，假如不唯一則拒絕插入或修改2. 檢驗主碼各個屬性是否為空，只要有一個為空就拒絕插入或修改第10頁檢驗統(tǒng)計中主碼值是否唯一一個方法是進行全表掃描5.1.2 實體完整性檢驗和違約處理第11頁5.1.2 實體完整性檢驗和違約處理索引 第12頁5.2 參考完整性參考完整性定義參考完整性檢驗和違約處理第13頁5.2.1 參考完整性定義關(guān)系模型參考完整性定義在CREATE TABLE中

5、用FOREIGN KEY短語定義哪些列為外碼用REFERENCES短語指明這些外碼參考哪些表主碼 第14頁例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)， /*在表級定義參考完整性*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表級定義參考完整性*/ );5

6、.2.1 參考完整性定義第15頁5.2.2 參考完整性檢驗和違約處理可能破壞參考完整性情況及違約處理被參考表（比如Student）參考表（比如SC）違約處理可能破壞參考完整性 插入元組拒絕可能破壞參考完整性 修改外碼值拒絕刪除元組 可能破壞參考完整性拒絕/級連刪除/設(shè)置為空值修改主碼值 可能破壞參考完整性拒絕/級連修改/設(shè)置為空值第16頁參考完整性違約處理1. 拒絕(NO ACTION)執(zhí)行默認策略2. 級聯(lián)(CASCADE)操作3. 設(shè)置為空值（SET-NULL）對于參考完整性，除了應該定義外碼，還應定義外碼列是否允許空值5.2.2 參考完整性檢驗和違約處理第17頁例3 顯式說明參考完整性違

7、約處理示例 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 (Cno) REFERENCES Course(Cno) ON DELETE NO ACTION /*當刪除course 表中元組造成了與S

8、C表不一致時拒絕刪除*/ ON UPDATE CASCADE /*當更新course表中cno時，級聯(lián)更新SC表中對應元組*/ )；5.2.2 參考完整性檢驗和違約處理第18頁5.3 用戶定義完整性用戶定義完整性就是針對某一詳細應用數(shù)據(jù)必須滿足語義要求 RDBMS提供，而無須由應用程序負擔第19頁5.3.1 屬性上約束條件定義CREATE TABLE時定義列值非空（NOT NULL）列值唯一（UNIQUE）檢驗列值是否滿足一個布爾表示式（CHECK）第20頁5.3.1 屬性上約束條件定義1.不允許取空值 例5 在定義SC表時，說明Sno、Cno、Grade屬性不允許取空值。 CREATE TA

9、BLE SC （Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT NOT NULL， PRIMARY KEY (Sno， Cno)， /* 假如在表級定義實體完整性，隱含了Sno，Cno不允許取空值，則在列級不允許取空值定義就無須寫了 * / ）； 第21頁2.列值唯一 例6 建立部門表DEPT，要求部門名稱Dname列取值唯一，部門編號Deptno列為主碼 CREATE TABLE DEPT (Deptno NUMERIC(2)， Dname CHAR(9) UNIQUE，/*要求Dname列值唯一*/ Location C

10、HAR(10)， PRIMARY KEY (Deptno) )；5.3.1 屬性上約束條件定義第22頁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， Sdept CHAR(20) );5.3.1 屬性上約束條件定義第23頁5.3.1 屬性上約束條件定義例8

11、 SC表Grade值應在0到100之間 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT Check(Grade=0 and Grade=100), PRIMARY KEY (Sno， Cno), /*在表級定義實體完整性*/ FOREIGN KEY (Sno) REFERENCES Student(Sno)， FOREIGN KEY (Cno) REFERENCES Course(Cno) );第24頁5.3.2 屬性上約束條件檢驗和違約處理插入元組或修改屬性值時，RDBMS檢驗屬性上約束條

12、件是否被滿足假如不滿足則操作被拒絕執(zhí)行 第25頁5.3.3 元組上約束條件定義例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兩個屬性值之間約束條件*/ )；性別是女性元組都能經(jīng)過該項檢驗，因為Ssex=女成立；當性別是男性時，要經(jīng)過檢驗

13、則名字一定不能以Ms.打頭第26頁5.3.4 元組上約束條件檢驗和違約處理插入元組或修改屬性值時，RDBMS檢驗元組上約束條件是否被滿足假如不滿足則操作被拒絕執(zhí)行 第27頁5.4 完整性約束命名子句1.CONSTRAINT 約束子句CONSTRAINT PRIMARY KEY短語 |FOREIGN KEY短語 |CHECK短語第28頁5.4 完整性約束命名子句例10 建立學生記錄表Student，要求學號在9000099999之間，姓名不能取空值，年紀小于30，性別只能是“男”或“女”。 CREATE TABLE Student (Sno NUMERIC(6) CONSTRAINT C1 CH

14、ECK (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 ( 男，女)， CONSTRAINT StudentKey PRIMARY KEY(Sno) )；在Student表上建立了5個約束條件，包含主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個列級約束。第29頁2. 修改表中完整性限制使用ALTER TAB

15、LE語句修改表中完整性限制5.4 完整性約束命名子句例12 去掉例10 student表中對性別限制 ALTER TABLE Student DROP CONSTRAINT C4第30頁例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 St

16、udent DROP CONSTRAINT C3; ALTER TABLE Student ADD CONSTRAINT C3 CHECK (Sage 40)；5.4 完整性約束命名子句第31頁5.5 觸發(fā)器觸發(fā)器（Trigger）是用戶定義在關(guān)系表上一類由事件驅(qū)動特殊過程由服務器自動激活能夠進行更為復雜檢驗和操作，含有更精細和更強大數(shù)據(jù)控制能力 第32頁SnoSnameSsex95001張勇男95002趙楠男95003宋佳女Student表CnoCnameCpno1計算機基礎(chǔ)2C語言13軟件工程4Course表SnoCnoScore950011 90950022 85SC表應用實例5.5 觸

17、發(fā)器第33頁5.5.1 定義觸發(fā)器CREATE TRIGGER語法格式CREATE TRIGGER 觸發(fā)器名稱ON 表名（觸發(fā)器定義在哪個表上）FOR DELETE, INSERT, UPDATE AS BEGIN觸發(fā)體END第34頁定義觸發(fā)器語法說明:1. 觸發(fā)器名2. 表名：觸發(fā)器目標表3. 觸發(fā)事件：INSERT、DELETE、UPDATE5.5.1 定義觸發(fā)器第35頁比如,假設(shè)在Student表上創(chuàng)建了一個UPDATE觸發(fā)器。CREATE TRIGGER update_sno ON Student FOR updateAS if update(sno) begin update sc

18、set sno=c.sno from deleted b,inserted c where sc.sno=b.sno end5.5.1 定義觸發(fā)器第36頁4. 觸發(fā)條件觸發(fā)條件為真省略WHERE觸發(fā)條件5. 觸發(fā)動作體觸發(fā)動作體能夠是一個匿名PL/SQL過程塊也能夠是對已創(chuàng)建存放過程調(diào)用5.5.1 定義觸發(fā)器第37頁插入視圖和刪除視圖(SQL SERVER)當在定義了觸發(fā)器表上發(fā)生修改操作時，會派生出兩個視圖，一個是插入視圖，一個是刪除視圖。當在表上發(fā)生插入操作時，新插入行將出現(xiàn)在插入視圖中；當在表上發(fā)生刪除操作時，被刪除行將出現(xiàn)在刪除視圖中；當在表上發(fā)生更新操作時，舊行出現(xiàn)在刪除視圖中，而新

19、行出現(xiàn)在插入視圖中。（更新實現(xiàn)過程是先刪除舊行，然后插入新行）這里插入視圖和刪除視圖只在觸發(fā)器內(nèi)可用，一旦觸發(fā)器完成任務，這兩個視圖將不再存在。這兩個視圖名稱分別是insterted和deleted，他們和原表含有完全相同結(jié)構(gòu)。5.5.1 定義觸發(fā)器第38頁假如從student表中刪除學生基本信息，則從SC中刪除全部該學生成績信息。CREATE TRIGGER delete_sno ON Student FOR deleteAS begin delete sc from sc,deleted a where sc.sno=b.sno end第39頁假如從student表中插入學生基本信息，則從student1中插入該學生信息。CREATE TR