DB05數(shù)據(jù)庫(kù)教學(xué)市公開(kāi)課金獎(jiǎng)市賽課一等獎(jiǎng)?wù)n件

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

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

3、Sage SMALLINT， Sdept CHAR(20);第7頁(yè)(2)在表級(jí)定義主碼 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 實(shí)體完整性定義第8頁(yè)5.1.1 實(shí)體完整性定義例2 將SC表中Sno，Cno屬性組定義為碼 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT，

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

5、用FOREIGN KEY短語(yǔ)定義哪些列為外碼用REFERENCES短語(yǔ)指明這些外碼參考哪些表主碼 第14頁(yè)例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

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

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 /*級(jí)聯(lián)刪除SC表中對(duì)應(yīng)元組*/ ON UPDATE CASCADE， /*級(jí)聯(lián)更新SC表中對(duì)應(yīng)元組*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE NO ACTION /*當(dāng)刪除course 表中元組造成了與S

8、C表不一致時(shí)拒絕刪除*/ ON UPDATE CASCADE /*當(dāng)更新course表中cno時(shí)，級(jí)聯(lián)更新SC表中對(duì)應(yīng)元組*/ )；5.2.2 參考完整性檢驗(yàn)和違約處理第18頁(yè)5.3 用戶(hù)定義完整性用戶(hù)定義完整性就是針對(duì)某一詳細(xì)應(yīng)用數(shù)據(jù)必須滿(mǎn)足語(yǔ)義要求 RDBMS提供，而無(wú)須由應(yīng)用程序負(fù)擔(dān)第19頁(yè)5.3.1 屬性上約束條件定義CREATE TABLE時(shí)定義列值非空（NOT NULL）列值唯一（UNIQUE）檢驗(yàn)列值是否滿(mǎn)足一個(gè)布爾表示式（CHECK）第20頁(yè)5.3.1 屬性上約束條件定義1.不允許取空值 例5 在定義SC表時(shí)，說(shuō)明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)， /* 假如在表級(jí)定義實(shí)體完整性，隱含了Sno，Cno不允許取空值，則在列級(jí)不允許取空值定義就無(wú)須寫(xiě)了 * / ）； 第21頁(yè)2.列值唯一 例6 建立部門(mén)表DEPT，要求部門(mén)名稱(chēng)Dname列取值唯一，部門(mén)編號(hào)Deptno列為主碼 CREATE TABLE DEPT (Deptno NUMERIC(2)， Dname CHAR(9) UNIQUE，/*要求Dname列值唯一*/ Location C

10、HAR(10)， PRIMARY KEY (Deptno) )；5.3.1 屬性上約束條件定義第22頁(yè)3. 用CHECK短語(yǔ)指定列值應(yīng)該滿(mǎn)足條件例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頁(yè)5.3.1 屬性上約束條件定義例8

11、 SC表Grade值應(yīng)在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), /*在表級(jí)定義實(shí)體完整性*/ FOREIGN KEY (Sno) REFERENCES Student(Sno)， FOREIGN KEY (Cno) REFERENCES Course(Cno) );第24頁(yè)5.3.2 屬性上約束條件檢驗(yàn)和違約處理插入元組或修改屬性值時(shí)，RDBMS檢驗(yàn)屬性上約束條

12、件是否被滿(mǎn)足假如不滿(mǎn)足則操作被拒絕執(zhí)行 第25頁(yè)5.3.3 元組上約束條件定義例9 當(dāng)學(xué)生性別是男時(shí)，其名字不能以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兩個(gè)屬性值之間約束條件*/ )；性別是女性元組都能經(jīng)過(guò)該項(xiàng)檢驗(yàn)，因?yàn)镾sex=女成立；當(dāng)性別是男性時(shí)，要經(jīng)過(guò)檢驗(yàn)

13、則名字一定不能以Ms.打頭第26頁(yè)5.3.4 元組上約束條件檢驗(yàn)和違約處理插入元組或修改屬性值時(shí)，RDBMS檢驗(yàn)元組上約束條件是否被滿(mǎn)足假如不滿(mǎn)足則操作被拒絕執(zhí)行 第27頁(yè)5.4 完整性約束命名子句1.CONSTRAINT 約束子句CONSTRAINT PRIMARY KEY短語(yǔ) |FOREIGN KEY短語(yǔ) |CHECK短語(yǔ)第28頁(yè)5.4 完整性約束命名子句例10 建立學(xué)生記錄表Student，要求學(xué)號(hào)在9000099999之間，姓名不能取空值，年紀(jì)小于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個(gè)約束條件，包含主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個(gè)列級(jí)約束。第29頁(yè)2. 修改表中完整性限制使用ALTER TAB

15、LE語(yǔ)句修改表中完整性限制5.4 完整性約束命名子句例12 去掉例10 student表中對(duì)性別限制 ALTER TABLE Student DROP CONSTRAINT C4第30頁(yè)例13 修改表Student中約束條件，要求學(xué)號(hào)改為在900000999999之間，年紀(jì)由小于30改為小于40能夠先刪除原來(lái)約束條件，再增加新約束條件 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頁(yè)5.5 觸發(fā)器觸發(fā)器（Trigger）是用戶(hù)定義在關(guān)系表上一類(lèi)由事件驅(qū)動(dòng)特殊過(guò)程由服務(wù)器自動(dòng)激活能夠進(jìn)行更為復(fù)雜檢驗(yàn)和操作，含有更精細(xì)和更強(qiáng)大數(shù)據(jù)控制能力 第32頁(yè)SnoSnameSsex95001張勇男95002趙楠男95003宋佳女Student表CnoCnameCpno1計(jì)算機(jī)基礎(chǔ)2C語(yǔ)言13軟件工程4Course表SnoCnoScore950011 90950022 85SC表應(yīng)用實(shí)例5.5 觸

17、發(fā)器第33頁(yè)5.5.1 定義觸發(fā)器CREATE TRIGGER語(yǔ)法格式CREATE TRIGGER 觸發(fā)器名稱(chēng)ON 表名（觸發(fā)器定義在哪個(gè)表上）FOR DELETE, INSERT, UPDATE AS BEGIN觸發(fā)體END第34頁(yè)定義觸發(fā)器語(yǔ)法說(shuō)明:1. 觸發(fā)器名2. 表名：觸發(fā)器目標(biāo)表3. 觸發(fā)事件：INSERT、DELETE、UPDATE5.5.1 定義觸發(fā)器第35頁(yè)比如,假設(shè)在Student表上創(chuàng)建了一個(gè)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頁(yè)4. 觸發(fā)條件觸發(fā)條件為真省略WHERE觸發(fā)條件5. 觸發(fā)動(dòng)作體觸發(fā)動(dòng)作體能夠是一個(gè)匿名PL/SQL過(guò)程塊也能夠是對(duì)已創(chuàng)建存放過(guò)程調(diào)用5.5.1 定義觸發(fā)器第37頁(yè)插入視圖和刪除視圖(SQL SERVER)當(dāng)在定義了觸發(fā)器表上發(fā)生修改操作時(shí)，會(huì)派生出兩個(gè)視圖，一個(gè)是插入視圖，一個(gè)是刪除視圖。當(dāng)在表上發(fā)生插入操作時(shí)，新插入行將出現(xiàn)在插入視圖中；當(dāng)在表上發(fā)生刪除操作時(shí)，被刪除行將出現(xiàn)在刪除視圖中；當(dāng)在表上發(fā)生更新操作時(shí)，舊行出現(xiàn)在刪除視圖中，而新

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