上海商學(xué)院SQL

1、An Introduction to Database System上海商學(xué)院上海商學(xué)院SQLSQL數(shù)據(jù)庫系統(tǒng)概論數(shù)據(jù)庫系統(tǒng)概論An Introduction to Database System第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性An Introduction to Database System數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性v 數(shù)據(jù)庫的完整性 數(shù)據(jù)的正確性和相容性v 數(shù)據(jù)的完整性和安全性是兩個(gè)不同概念 數(shù)據(jù)的完整性 防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫中存在不正確的數(shù)據(jù) 防范對(duì)象：不合語義的、不正確的數(shù)據(jù) 數(shù)據(jù)的安全性 保護(hù)數(shù)據(jù)庫防止惡意的破壞和非法的存取 防范對(duì)象：非法用戶和非

2、法操作An Introduction to Database System數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性(續(xù)續(xù))為維護(hù)數(shù)據(jù)庫的完整性，DBMS必須：n1.提供定義完整性約束條件的機(jī)制n2.提供完整性檢查的方法n3.違約處理An Introduction to Database System第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性5.1 實(shí)體完整性實(shí)體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名字句完整性約束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 觸發(fā)器觸發(fā)器5.7 小結(jié)小結(jié)An Introduction to Database Sy

3、stem5.1 實(shí)體完整性實(shí)體完整性v5.1.1 實(shí)體完整性定義實(shí)體完整性定義v5.1.2 實(shí)體完整性檢查和違約處理實(shí)體完整性檢查和違約處理An Introduction to Database System5.1.1 實(shí)體完整性定義實(shí)體完整性定義v 關(guān)系模型的實(shí)體完整性 CREATE TABLE中用PRIMARY KEY定義v 單屬性構(gòu)成的碼有兩種說明方法 定義為列級(jí)約束條件 定義為表級(jí)約束條件v 對(duì)多個(gè)屬性構(gòu)成的碼只有一種說明方法 定義為表級(jí)約束條件 An Introduction to Database System實(shí)體完整性定義實(shí)體完整性定義(續(xù)續(xù))例1 將Student表中的Sno屬

4、性定義為碼 (1)在列級(jí)定義主碼在列級(jí)定義主碼 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20);An Introduction to Database System實(shí)體完整性定義實(shí)體完整性定義(續(xù)續(xù))(2)在表級(jí)定義主碼在表級(jí)定義主碼 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT，

5、 Sdept CHAR(20)， PRIMARY KEY (Sno) ); An Introduction to Database System實(shí)體完整性定義實(shí)體完整性定義(續(xù)續(xù))例2將SC表中的Sno，Cno屬性組定義為碼 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno，Cno) /*只能在表級(jí)定義主碼*/ ); An Introduction to Database System5.1 實(shí)體完整性實(shí)體完整性v5.1.1 實(shí)體完整性定義實(shí)體完整性定義v5

6、.1.2 實(shí)體完整性檢查和違約處理實(shí)體完整性檢查和違約處理An Introduction to Database System5.1.2 實(shí)體完整性檢查和違約處理實(shí)體完整性檢查和違約處理v 插入或?qū)χ鞔a列進(jìn)行更新操作時(shí)，RDBMS按照實(shí)體完整性規(guī)則自動(dòng)進(jìn)行檢查。包括： 1. 檢查主碼值是否唯一，如果不唯一則拒絕插入或修改 2. 檢查主碼的各個(gè)屬性是否為空，只要有一個(gè)為空就拒絕插入或修改An Introduction to Database System實(shí)體完整性檢查和違約處理實(shí)體完整性檢查和違約處理(續(xù)續(xù))v 檢查記錄中主碼值是否唯一的一種方法是進(jìn)行全表掃描An Introduction to

7、 Database System實(shí)體完整性檢查和違約處理實(shí)體完整性檢查和違約處理(續(xù)續(xù))v索引 An Introduction to Database System第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性5.1 實(shí)體完整性實(shí)體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名字句完整性約束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 觸發(fā)器觸發(fā)器5.7 小結(jié)小結(jié)An Introduction to Database System5.2 參照完整性參照完整性v5.2.1 參照完整性定義參照完整性定義v5.2.2 參照完整性檢查和違約處理參照

8、完整性檢查和違約處理An Introduction to Database System5.2.1 參照完整性定義參照完整性定義v關(guān)系模型的參照完整性定義 在CREATE TABLE中用FOREIGN KEY短語定義哪些列為外碼 用REFERENCES短語指明這些外碼參照哪些表的主碼 An Introduction to Database System參照完整性定義參照完整性定義(續(xù)續(xù))例如，關(guān)系SC中一個(gè)元組表示一個(gè)學(xué)生選修的某門課程的成績，（Sno，Cno）是主碼。Sno，Cno分別參照引用Student表的主碼和Course表的主碼 例3 定義SC中的參照完整性 CREATE TABLE

9、 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í)定義參照完整性*/ );An Introduction to Database System5.2 參照完整性參照完整性v5.2.1 參照完整性定義參照完整性定義v5.2.2 參照完整

10、性檢查和違約處理參照完整性檢查和違約處理An Introduction to Database System參照完整性檢查和違約處理參照完整性檢查和違約處理可能破壞參照完整性的情況及違約處理被參照表（例如Student）參照表（例如SC）違約處理可能破壞參照完整性 插入元組拒絕可能破壞參照完整性 修改外碼值拒絕刪除元組 可能破壞參照完整性拒絕/級(jí)連刪除/設(shè)置為空值修改主碼值 可能破壞參照完整性拒絕/級(jí)連修改/設(shè)置為空值A(chǔ)n Introduction to Database System違約處理違約處理v 參照完整性違約處理 1. 拒絕(NO ACTION)執(zhí)行 默認(rèn)策略 2. 級(jí)聯(lián)(CASCA

11、DE)操作 3. 設(shè)置為空值（SET-NULL） 對(duì)于參照完整性，除了應(yīng)該定義外碼，還應(yīng)定義外碼列是否允許空值A(chǔ)n Introduction to Database System違約處理違約處理(續(xù)續(xù))例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 /*級(jí)聯(lián)刪除SC表中相應(yīng)的元組*/ ON

12、 UPDATE CASCADE， /*級(jí)聯(lián)更新SC表中相應(yīng)的元組*/ 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)的元組*/ )；An Introduction to Database System第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性5.1 實(shí)體完整性實(shí)體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名字句

13、完整性約束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 觸發(fā)器觸發(fā)器5.7 小結(jié)小結(jié)An Introduction to Database System5.3 用戶定義的完整性用戶定義的完整性v用戶定義的完整性就是針對(duì)某一具體應(yīng)用的數(shù)據(jù)必須滿足的語義要求 vRDBMS提供，而不必由應(yīng)用程序承擔(dān)An Introduction to Database System5.3 用戶定義的完整性用戶定義的完整性v5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義v5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 v5.3.3 元組上的約束條件的定義元組上的約束條件

14、的定義 v5.3.4元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理An Introduction to Database System5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義vCREATE TABLE時(shí)定義 列值非空（NOT NULL） 列值唯一（UNIQUE） 檢查列值是否滿足一個(gè)布爾表達(dá)式（CHECK）An Introduction to Database System屬性上的約束條件的定義屬性上的約束條件的定義(續(xù)續(xù))v 1.不允許取空值 例5 在定義SC表時(shí)，說明Sno、Cno、Grade屬性不允許取空值。 CREATE TABLE SC （Sno CHA

15、R(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT NOT NULL， PRIMARY KEY (Sno， Cno)， /* 如果在表級(jí)定義實(shí)體完整性，隱含了Sno，Cno不允許取空值，則在列級(jí)不允許取空值的定義就不必寫了 * / ）； An Introduction to Database System屬性上的約束條件的定義屬性上的約束條件的定義(續(xù)續(xù))v 2.列值唯一 例6 建立部門表DEPT，要求部門名稱Dname列取值唯一，部門編號(hào)Deptno列為主碼 CREATE TABLE DEPT (Deptno NUMERIC(2)， Dna

16、me CHAR(9) UNIQUE，/*要求Dname列值唯一*/ Location CHAR(10)， PRIMARY KEY (Deptno) )；An Introduction to Database System屬性上的約束條件的定義屬性上的約束條件的定義(續(xù)續(xù))v 3. 用CHECK短語指定列值應(yīng)該滿足的條件例7 Student表的Ssex只允許取“男”或“女”。 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(8) NOT NULL， Ssex CHAR(2) CHECK (Ssex IN (男，女) ) ， /*

17、性別屬性Ssex只允許取男或女 */ Sage SMALLINT， Sdept CHAR(20) );An Introduction to Database System5.3 用戶定義的完整性用戶定義的完整性v5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義v5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 v5.3.3 元組上的約束條件的定義元組上的約束條件的定義 v5.3.4元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理An Introduction to Database System5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條

18、件檢查和違約處理v插入元組或修改屬性的值時(shí)，RDBMS檢查屬性上的約束條件是否被滿足v如果不滿足則操作被拒絕執(zhí)行 An Introduction to Database System5.3 用戶定義的完整性用戶定義的完整性v5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義v5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 v5.3.3 元組上的約束條件的定義元組上的約束條件的定義 v5.3.4元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理An Introduction to Database System5.3.3 元組上的約束條件的定義元組上的約

19、束條件的定義v 在CREATE TABLE時(shí)可以用CHECK短語定義元組上的約束條件，即元組級(jí)的限制v 同屬性值限制相比，元組級(jí)的限制可以設(shè)置不同屬性之間的取值的相互約束條件 An Introduction to Database System元組上的約束條件的定義元組上的約束條件的定義(續(xù)續(xù))例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)， CHE

20、CK (Ssex=女女 OR Sname NOT LIKE Ms.%) /*定義了元組中Sname和 Ssex兩個(gè)屬性值之間的約束條件*/ )； 性別是女性的元組都能通過該項(xiàng)檢查，因?yàn)镾sex=女成立； 當(dāng)性別是男性時(shí)，要通過檢查則名字一定不能以Ms.打頭An Introduction to Database System5.3 用戶定義的完整性用戶定義的完整性v5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義v5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理 v5.3.3 元組上的約束條件的定義元組上的約束條件的定義 v5.3.4 元組上的約束條件檢查和違約處

21、理元組上的約束條件檢查和違約處理An Introduction to Database System5.3.4 元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理v 插入元組或修改屬性的值時(shí)，RDBMS檢查元組上的約束條件是否被滿足v 如果不滿足則操作被拒絕執(zhí)行 An Introduction to Database System第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性5.1 實(shí)體完整性實(shí)體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名子句完整性約束命名子句*5.5 域中的完整性限制域中的完整性限制5.6 觸發(fā)器觸發(fā)器5.7 小結(jié)小結(jié)A

22、n Introduction to Database System5.4 完整性約束命名子句完整性約束命名子句vCONSTRAINT 約束CONSTRAINT PRIMARY KEY短語 |FOREIGN KEY短語 |CHECK短語An Introduction to Database System完整性約束命名子句完整性約束命名子句(續(xù)續(xù))例10 建立學(xué)生登記表Student，要求學(xué)號(hào)在9000099999之間，姓名不能取空值，年齡小于30，性別只能是“男”或“女”。 CREATE TABLE Student (Sno NUMERIC(6) CONSTRAINT C1 CHECK (Sno

23、 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í)約束。An Introduction to Database System

24、完整性約束命名子句完整性約束命名子句(續(xù)續(xù))v2. 修改表中的完整性限制修改表中的完整性限制 使用ALTER TABLE語句修改表中的完整性限制An Introduction to Database System完整性約束命名子句完整性約束命名子句(續(xù)續(xù))例13 修改表Student中的約束條件，要求學(xué)號(hào)改為在900000999999之間，年齡由小于30改為小于40n可以先刪除原來的約束條件，再增加新的約束條件 ALTER TABLE Student DROP CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BET

25、WEEN 900000 AND 999999)， ALTER TABLE Student DROP CONSTRAINT C3; ALTER TABLE Student ADD CONSTRAINT C3 CHECK (Sage 40)；An Introduction to Database System第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性5.1 實(shí)體完整性實(shí)體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名字句完整性約束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 觸發(fā)器觸發(fā)器5.7 小結(jié)小結(jié)An Introduction to

26、Database System5.5 域中的完整性限制域中的完整性限制v SQL支持域的概念，并可以用CREATE DOMAIN語句建立一個(gè)域以及該域應(yīng)該滿足的完整性約束條件。例14建立一個(gè)性別域，并聲明性別域的取值范圍 CREATE DOMAIN GenderDomain CHAR(2) CHECK (VALUE IN (男，女) ); 這樣例10中對(duì)Ssex的說明可以改寫為 Ssex GenderDomain例15建立一個(gè)性別域GenderDomain，并對(duì)其中的限制命名 CREATE DOMAIN GenderDomain CHAR(2) CONSTRAINT GD CHECK ( VA

27、LUE IN (男，女) );An Introduction to Database System域中的完整性限制域中的完整性限制(續(xù)續(xù))例16刪除域GenderDomain的限制條件GD。 ALTER DOMAIN GenderDomain DROP CONSTRAINT GD;例17在域GenderDomain上增加限制條件GDD。 ALTER DOMAIN GenderDomain ADD CONSTRAINT GDD CHECK (VALUE IN ( 1，0) ); 通過例16和例17，就把性別的取值范圍由(男，女)改為 ( 1，0) An Introduction to Datab

28、ase System第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性5.1 實(shí)體完整性實(shí)體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名字句完整性約束命名字句*5.5 域中的完整性限制域中的完整性限制5.6 觸發(fā)器觸發(fā)器5.7 小結(jié)小結(jié)An Introduction to Database System觸發(fā)器觸發(fā)器v觸發(fā)器（Trigger）是用戶定義在關(guān)系表上的一類由事件驅(qū)動(dòng)的特殊過程 由服務(wù)器自動(dòng)激活 可以進(jìn)行更為復(fù)雜的檢查和操作，具有更精細(xì)和更強(qiáng)大的數(shù)據(jù)控制能力 An Introduction to Database System5.6 觸發(fā)器觸發(fā)器

29、v5.6.1 定義觸發(fā)器定義觸發(fā)器 v5.6.2 激活觸發(fā)器激活觸發(fā)器 v5.6.3 刪除觸發(fā)器刪除觸發(fā)器 An Introduction to Database System5.6.1 定義觸發(fā)器定義觸發(fā)器vCREATE TRIGGER語法格式 CREATE TRIGGER BEFORE | AFTER ON FOR EACH ROW | STATEMENT WHEN An Introduction to Database System定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù))v定義觸發(fā)器的語法說明: 1. 創(chuàng)建者：表的擁有者 2. 觸發(fā)器名 3. 表名：觸發(fā)器的目標(biāo)表 4. 觸發(fā)事件：INSERT、DE

30、LETE、UPDATE 5. 觸發(fā)器類型 行級(jí)觸發(fā)器（FOR EACH ROW） 語句級(jí)觸發(fā)器（FOR EACH STATEMENT）An Introduction to Database System定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù))v 例如,假設(shè)在例11的TEACHER表上創(chuàng)建了一個(gè)AFTER UPDATE觸發(fā)器。如果表TEACHER有1000行，執(zhí)行如下語句： UPDATE TEACHER SET Deptno=5; 如果該觸發(fā)器為語句級(jí)觸發(fā)器，那么執(zhí)行完該語句后，觸發(fā)動(dòng)作只發(fā)生一次 如果是行級(jí)觸發(fā)器，觸發(fā)動(dòng)作將執(zhí)行1000次 An Introduction to Database Syste

31、m定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù))v 6. 觸發(fā)條件 觸發(fā)條件為真 省略WHEN觸發(fā)條件v 7. 觸發(fā)動(dòng)作體 觸發(fā)動(dòng)作體可以是一個(gè)匿名PL/SQL過程塊 也可以是對(duì)已創(chuàng)建存儲(chǔ)過程的調(diào)用An Introduction to Database System定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù))例18 定義一個(gè)BEFORE行級(jí)觸發(fā)器，為教師表Teacher定義完整性規(guī)則“教授的工資不得低于4000元，如果低于4000元，自動(dòng)改為4000元”。 CREATE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON Teacher /*觸發(fā)事件是插入或更新操作*

32、/ FOR EACH ROW /*行級(jí)觸發(fā)器*/ AS BEGIN /*定義觸發(fā)動(dòng)作體，是PL/SQL過程塊*/ IF (new.Job=教授) AND (new.Sal 4000) THEN new.Sal :=4000; END IF; END; An Introduction to Database System定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù))例19定義AFTER行級(jí)觸發(fā)器，當(dāng)教師表Teacher的工資發(fā)生變化后就自動(dòng)在工資變化表Sal_log中增加一條相應(yīng)記錄 首先建立工資變化表Sal_log CREATE TABLE Sal_log (Eno NUMERIC(4) references

33、teacher(eno)， Sal NUMERIC(7，2)， Username char(10)， Date TIMESTAMP )；An Introduction to Database System定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù))例19（續(xù)）CREATE TRIGGER Insert_Sal AFTER INSERT ON Teacher /*觸發(fā)事件是INSERT*/ FOR EACH ROW AS BEGIN INSERT INTO Sal_log VALUES( new.Eno，new.Sal，CURRENT_USER，CURRENT_TIMESTAMP); END;An Introduction to Database System定義觸發(fā)器定義觸發(fā)器(續(xù)續(xù))例19（續(xù)）CREATE TRIGGER Update_Sal AFTER UPDATE ON Teacher /*觸發(fā)事件是UPDATE */ FOR EACH ROW AS BEGIN IF (new.Sal old.Sal) THEN INSERT INTO Sal_log VALUES( new.Eno，new.Sal，CURRENT_US