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

2、：不合語(yǔ)義的、不正確的數(shù)據(jù) 數(shù)據(jù)的安全性 保護(hù)數(shù)據(jù)庫(kù) 防止惡意的破壞和非法的存取 防范對(duì)象：非法用戶(hù)和非法操作,數(shù)據(jù)庫(kù)完整性（續(xù)）,為維護(hù)數(shù)據(jù)庫(kù)的完整性，數(shù)據(jù)庫(kù)管理系統(tǒng)必須： 1.提供定義完整性約束條件的機(jī)制 完整性約束條件也稱(chēng)為完整性規(guī)則，是數(shù)據(jù)庫(kù)中的數(shù)據(jù)必須滿(mǎn)足的語(yǔ)義約束條件 SQL標(biāo)準(zhǔn)使用了一系列概念來(lái)描述完整性，包括關(guān)系模型的實(shí)體完整性、參照完整性和用戶(hù)定義完整性 這些完整性一般由SQL的數(shù)據(jù)定義語(yǔ)言語(yǔ)句來(lái)實(shí)現(xiàn),數(shù)據(jù)庫(kù)完整性（續(xù)）,2.提供完整性檢查的方法 數(shù)據(jù)庫(kù)管理系統(tǒng)中檢查數(shù)據(jù)是否滿(mǎn)足完整性約束條件的機(jī)制稱(chēng)為完整性檢查。 一般在INSERT、UPDATE、DELETE語(yǔ)句執(zhí)行后開(kāi)始

3、檢查，也可以在事務(wù)提交時(shí)檢查,數(shù)據(jù)庫(kù)完整性（續(xù)）,3.違約處理 數(shù)據(jù)庫(kù)管理系統(tǒng)若發(fā)現(xiàn)用戶(hù)的操作違背了完整性約束條件，就采取一定的動(dòng)作 拒絕（NO ACTION）執(zhí)行該操作 級(jí)連（CASCADE）執(zhí)行其他操作,第五章 數(shù)據(jù)庫(kù)完整性,5.1 實(shí)體完整性 5.2 參照完整性 5.3 用戶(hù)定義的完整性 5.4 完整性約束命名字句 *5.5 域中的完整性限制 5.6 斷言 5.7 觸發(fā)器 5.8 小結(jié),5.1 實(shí)體完整性,5.1.1 實(shí)體完整性定義 5.1.2 實(shí)體完整性檢查和違約處理,5.1.1 實(shí)體完整性定義,關(guān)系模型的實(shí)體完整性 CREATE TABLE中用PRIMARY KEY定義 單屬性構(gòu)成的

4、碼有兩種說(shuō)明方法 定義為列級(jí)約束條件 定義為表級(jí)約束條件 對(duì)多個(gè)屬性構(gòu)成的碼只有一種說(shuō)明方法 定義為表級(jí)約束條件,實(shí)體完整性定義（續(xù)）,例5.1 將Student表中的Sno屬性定義為碼 （1）在列級(jí)定義主碼 CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) );,實(shí)體完整性定義（續(xù)）,（2）在表級(jí)定義主碼 CREATE TABLE Student ( Sno CHAR(9), Sname CHAR(20) NO

5、T NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY (Sno) );,實(shí)體完整性定義（續(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) /*只能在表級(jí)定義主碼*/ );,5.1 實(shí)體完整性,5.1.1 實(shí)體完整性定義 5.1.2 實(shí)體完整性檢查和違約處理,5.1.2 實(shí)體完整性檢查和違約處理,插入或?qū)χ鞔a列進(jìn)行更

6、新操作時(shí)，關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)按照實(shí)體完整性規(guī)則自動(dòng)進(jìn)行檢查。包括： 檢查主碼值是否唯一，如果不唯一則拒絕插入或修改 檢查主碼的各個(gè)屬性是否為空，只要有一個(gè)為空就拒絕插入或修改,實(shí)體完整性檢查和違約處理（續(xù)）,檢查記錄中主碼值是否唯一的一種方法是進(jìn)行全表掃描 依次判斷表中每一條記錄的主碼值與將插入記錄上的主碼值（或者修改的新主碼值）是否相同,實(shí)體完整性檢查和違約處理（續(xù)）,表掃描缺點(diǎn) 十分耗時(shí) 為避免對(duì)基本表進(jìn)行全表掃描，RDBMS核心一般都在主碼上自動(dòng)建立一個(gè)索引,實(shí)體完整性檢查和違約處理（續(xù)）,B+樹(shù)索引 例如， 新插入記錄的主碼值是25 通過(guò)主碼索引，從B+樹(shù)的根結(jié)點(diǎn)開(kāi)始查找 讀取3個(gè)結(jié)點(diǎn)

7、：根結(jié)點(diǎn)（51）、中間結(jié)點(diǎn)（12 30）、葉結(jié)點(diǎn)（15 20 25） 該主碼值已經(jīng)存在，不能插入這條記錄,第五章 數(shù)據(jù)庫(kù)完整性,5.1 實(shí)體完整性 5.2 參照完整性 5.3 用戶(hù)定義的完整性 5.4 完整性約束命名字句 *5.5 域中的完整性限制 5.6 斷言 5.7 觸發(fā)器 5.8 小結(jié),5.2 參照完整性,5.2.1 參照完整性定義 5.2.2 參照完整性檢查和違約處理,5.2.1 參照完整性定義,關(guān)系模型的參照完整性定義 在CREATE TABLE中用FOREIGN KEY短語(yǔ)定義哪些列為外碼 用REFERENCES短語(yǔ)指明這些外碼參照哪些表的主碼,參照完整性定義（續(xù)）,例如，關(guān)系SC

8、中（Sno，Cno）是主碼。Sno，Cno分別參照Student表 的主碼和Course表的主碼 例5.3定義SC中的參照完整性 CREATE TABLE SC ( Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT, PRIMARY KEY (Sno, Cno), /*在表級(jí)定義實(shí)體完整性*/ FOREIGN KEY (Sno) REFERENCES Student(Sno), /*在表級(jí)定義參照完整性*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表級(jí)定義參照完整性*/ );,5

9、.2 參照完整性,5.2.1 參照完整性定義 5.2.2 參照完整性檢查和違約處理,參照完整性檢查和違約處理,一個(gè)參照完整性將兩個(gè)表中的相應(yīng)元組聯(lián)系起來(lái) 對(duì)被參照表和參照表進(jìn)行增刪改操作時(shí)有可能破壞參照完整性，必須進(jìn)行檢查,參照完整性檢查和違約處理（續(xù)）,例如，對(duì)表SC和Student有四種可能破壞參照完整性的情況 : SC表中增加一個(gè)元組，該元組的Sno屬性的值在表Student中找不到一個(gè)元組，其Sno屬性的值與之相等。 修改SC表中的一個(gè)元組，修改后該元組的Sno屬性的值在表Student中找不到一個(gè)元組，其Sno屬性的值與之相等。,參照完整性檢查和違約處理（續(xù)）,例如，對(duì)表SC和Stu

10、dent有四種可能破壞參照完整性的情況 （續(xù)）: 從Student表中刪除一個(gè)元組，造成SC表中某些元組的Sno屬性的值在表Student中找不到一個(gè)元組，其Sno屬性的值與之相等。 修改Student表中一個(gè)元組的Sno屬性，造成SC表中某些元組的Sno屬性的值在表Student中找不到一個(gè)元組，其Sno屬性的值與之相等 。,參照完整性檢查和違約處理（續(xù)）,表5.1 可能破壞參照完整性的情況及違約處理,參照完整性檢查和違約處理（續(xù)）,參照完整性違約處理 （1） 拒絕（NO ACTION）執(zhí)行 不允許該操作執(zhí)行。該策略一般設(shè)置為默認(rèn)策略 （2） 級(jí)聯(lián)（CASCADE）操作 當(dāng)刪除或修改被參照表

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

12、完整性，除了應(yīng)該定義外碼，還應(yīng)定義外碼列是否允許空值 參見(jiàn)愛(ài)課程網(wǎng)數(shù)據(jù)庫(kù)系統(tǒng)概論5.2節(jié)動(dòng)畫(huà)參照完整性,參照完整性檢查和違約處理（續(xù)）,例5.4 顯式說(shuō)明參照完整性的違約處理示例 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 /*級(jí)聯(lián)刪除SC表中相應(yīng)的元組*/ ON UPDATE CASCADE, /*級(jí)聯(lián)更新SC表中相應(yīng)的

13、元組*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE NO ACTION /*當(dāng)刪除course 表中的元組造成了與SC表不一致時(shí)拒絕刪除*/ ON UPDATE CASCADE /*當(dāng)更新course表中的cno時(shí)，級(jí)聯(lián)更新SC表中相應(yīng)的元組*/ );,第五章 數(shù)據(jù)庫(kù)完整性,5.1 實(shí)體完整性 5.2 參照完整性 5.3 用戶(hù)定義的完整性 5.4 完整性約束命名字句 *5.5 域中的完整性限制 5.6 斷言 5.7 觸發(fā)器 5.8 小結(jié),5.3 用戶(hù)定義的完整性,用戶(hù)定義的完整性是：針對(duì)某一具體應(yīng)用的數(shù)據(jù)必須滿(mǎn)足的語(yǔ)義要求 關(guān)系數(shù)據(jù)庫(kù)

14、管理系統(tǒng)提供了定義和檢驗(yàn)用戶(hù)定義完整性的機(jī)制，不必由應(yīng)用程序承擔(dān),5.3 用戶(hù)定義的完整性,5.3.1 屬性上的約束條件 5.3.2 元組上的約束條件,1. 屬性上約束條件的定義,CREATE TABLE時(shí)定義屬性上的約束條件 列值非空（NOT NULL） 列值唯一（UNIQUE） 檢查列值是否滿(mǎn)足一個(gè)條件表達(dá)式（CHECK）,屬性上約束條件的定義（續(xù)）,（1）不允許取空值 例5.5 在定義SC表時(shí)，說(shuō)明Sno、Cno、Grade屬性不允許取空值。 CREATE TABLE SC ( Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALL

15、INT NOT NULL, PRIMARY KEY (Sno, Cno), /* 如果在表級(jí)定義實(shí)體完整性，隱含了Sno，Cno不允許取空值，則在 列級(jí)不允許取空值的定義 可以不寫(xiě) * / );,屬性上約束條件的定義（續(xù)）,（2）列值唯一 例5.6建立部門(mén)表DEPT，要求部門(mén)名稱(chēng)Dname列取值唯一，部門(mén)編號(hào)Deptno列為主碼 CREATE TABLE DEPT ( Deptno NUMERIC(2), Dname CHAR(9) UNIQUE NOT NULL， /*要求Dname列值唯一, 并且不能取空值*/ Location CHAR(10), PRIMARY KEY (Deptno)

16、 );,屬性上約束條件的定義（續(xù)）,（3）用CHECK短語(yǔ)指定列值應(yīng)該滿(mǎn)足的條件 例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的值應(yīng)該在0和100之間。 CREATE TABLE SC ( Sno CHA

17、R(9) , Cno CHAR(4), Grade SMALLINT CHECK (Grade=0 AND Grade = ALL (SELECT count(*) FROM SC GROUP by cno) ); /*此斷言的謂詞，涉及聚集操作count 和分組函數(shù)group by 的SQL語(yǔ)句*/,斷言（續(xù)）,例5.20限制每個(gè)學(xué)期每一門(mén)課程最多60名學(xué)生選修 首先需要修改SC表的模式，增加一個(gè)“學(xué)期（TERM）”屬性 ALTER TABLE SC ADD TERM DATE; 然后，定義斷言： CREATE ASSERTION ASSE_SC_CNUM2 CHECK(60 = ALL (

18、SELECT count(*) FROM SC GROUP by cno,TERM) );,斷言（續(xù)）,2. 刪除斷言的語(yǔ)句格式為 DROP ASSERTION ; 如果斷言很復(fù)雜，則系統(tǒng)在檢測(cè)和維護(hù)斷言的開(kāi)銷(xiāo)較高，這是在使用斷言時(shí)應(yīng)該注意的,斷言（續(xù)）,第五章 數(shù)據(jù)庫(kù)完整性,5.1 實(shí)體完整性 5.2 參照完整性 5.3 用戶(hù)定義的完整性 5.4 完整性約束命名字句 *5.5 域中的完整性限制 5.6 斷言 5.7 觸發(fā)器 5.8 小結(jié),觸發(fā)器,觸發(fā)器（Trigger）是用戶(hù)定義在關(guān)系表上的一類(lèi)由事件驅(qū)動(dòng)的特殊過(guò)程 觸發(fā)器保存在數(shù)據(jù)庫(kù)服務(wù)器中 任何用戶(hù)對(duì)表的增、刪、改操作均由服務(wù)器自動(dòng)激活相

19、應(yīng)的觸發(fā)器 觸發(fā)器可以實(shí)施更為復(fù)雜的檢查和操作，具有更精細(xì)和更強(qiáng)大的數(shù)據(jù)控制能力,5.7 觸發(fā)器,5.7.1 定義觸發(fā)器 5.7.2 激活觸發(fā)器 5.7.3 刪除觸發(fā)器,5.7.1 定義觸發(fā)器,CREATE TRIGGER語(yǔ)法格式 CREATE TRIGGER BEFORE | AFTER ON REFERENCING NEW|OLD ROW AS FOR EACH ROW | STATEMENT WHEN 觸發(fā)器又叫做事件-條件-動(dòng)作（event-condition-action）規(guī)則。 當(dāng)特定的系統(tǒng)事件發(fā)生時(shí)，對(duì)規(guī)則的條件進(jìn)行檢查，如果條件成立則執(zhí) 行規(guī)則中的動(dòng)作，否則不執(zhí)行該動(dòng)作。規(guī)則中

20、的動(dòng)作體可以很復(fù)雜，通 常是一段SQL存儲(chǔ)過(guò)程。,定義觸發(fā)器（續(xù)）,定義觸發(fā)器的語(yǔ)法說(shuō)明 （1）表的擁有者才可以在表上創(chuàng)建觸發(fā)器 （2）觸發(fā)器名 觸發(fā)器名可以包含模式名，也可以不包含模式名 同一模式下，觸發(fā)器名必須是唯一的 觸發(fā)器名和表名必須在同一模式下 （3）表名 觸發(fā)器只能定義在基本表上，不能定義在視圖上 當(dāng)基本表的數(shù)據(jù)發(fā)生變化時(shí)，將激活定義在該表上相應(yīng)觸 發(fā)事件的觸發(fā)器,定義觸發(fā)器（續(xù)）,（4）觸發(fā)事件 觸發(fā)事件可以是INSERT、DELETE或UPDATE 也可以是這幾個(gè)事件的組合 還可以UPDATE OF，即進(jìn)一步指明修改哪些列時(shí)激活觸發(fā)器 AFTER/BEFORE是觸發(fā)的時(shí)機(jī) AF

21、TER表示在觸發(fā)事件的操作執(zhí)行之后激活觸發(fā)器 BEFORE表示在觸發(fā)事件的操作執(zhí)行之前激活觸發(fā)器,定義觸發(fā)器（續(xù)）,（5）觸發(fā)器類(lèi)型 行級(jí)觸發(fā)器（FOR EACH ROW） 語(yǔ)句級(jí)觸發(fā)器（FOR EACH STATEMENT） 例如,在例5.11的TEACHER表上創(chuàng)建一個(gè)AFTER UPDATE觸發(fā)器，觸發(fā)事件是UPDATE語(yǔ)句： UPDATE TEACHER SET Deptno=5; 假設(shè)表TEACHER有1000行 如果是語(yǔ)句級(jí)觸發(fā)器，那么執(zhí)行完該語(yǔ)句后，觸發(fā)動(dòng)作只發(fā)生一次 如果是行級(jí)觸發(fā)器，觸發(fā)動(dòng)作將執(zhí)行1000次,定義觸發(fā)器（續(xù)）,（6）觸發(fā)條件 觸發(fā)器被激活時(shí)，只有當(dāng)觸發(fā)條件為真

22、時(shí)觸發(fā)動(dòng)作體才執(zhí)行;否則觸發(fā)動(dòng)作體不執(zhí)行。 如果省略WHEN觸發(fā)條件，則觸發(fā)動(dòng)作體在觸發(fā)器激活后立即執(zhí)行,定義觸發(fā)器（續(xù)）,（7）觸發(fā)動(dòng)作體 觸發(fā)動(dòng)作體可以是一個(gè)匿名PL/SQL過(guò)程塊 也可以是對(duì)已創(chuàng)建存儲(chǔ)過(guò)程的調(diào)用 如果是行級(jí)觸發(fā)器，用戶(hù)都可以在過(guò)程體中使用NEW和OLD引用事件之后的新值和事件之前的舊值 如果是語(yǔ)句級(jí)觸發(fā)器，則不能在觸發(fā)動(dòng)作體中使用NEW或OLD進(jìn)行引用 如果觸發(fā)動(dòng)作體執(zhí)行失敗，激活觸發(fā)器的事件就會(huì)終止執(zhí)行，觸發(fā)器的目標(biāo)表或觸發(fā)器可能影響的其他對(duì)象不發(fā)生任何變化 注意：不同的RDBMS產(chǎn)品觸發(fā)器語(yǔ)法各部相同,定義觸發(fā)器（續(xù)）,例5.21當(dāng)對(duì)表SC的Grade屬性進(jìn)行修改時(shí)，

23、若分?jǐn)?shù)增加了10%則將此次操作記錄到下面表中： SC_U（Sno,Cno,Oldgrade,Newgrade） 其中Oldgrade是修改前的分?jǐn)?shù)，Newgrade是修改后的分?jǐn)?shù)。 CREATE TRIGGER SC_T AFTER UPDATE OF Grade ON SC REFERENCING OLD row AS OldTuple, NEW row AS NewTuple FOR EACH ROW WHEN (NewTuple.Grade = 1.1*OldTuple.Grade) INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade) VALUES(O

24、ldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade),定義觸發(fā)器（續(xù)）,例5.22 將每次對(duì)表Student的插入操作所增加的學(xué)生個(gè)數(shù)記錄到表StudentInsertLog中。 CREATE TRIGGER Student_Count AFTER INSERT ON Student /*指明觸發(fā)器激活的時(shí)間是在執(zhí)行INSERT后*/ REFERENCING NEW TABLE AS DELTA FOR EACH STATEMENT /*語(yǔ)句級(jí)觸發(fā)器, 即執(zhí)行完INSERT語(yǔ)句后下面的觸發(fā)動(dòng)作體才執(zhí)行一次*/ INSERT INTO StudentInsertLog (Numbers) SEL