第五章數(shù)據(jù)庫(kù)完整性

1、第五章 數(shù)據(jù)庫(kù)完整性5.1實(shí)體完整性15.1.1 實(shí)體完整性定義15.1.2 實(shí)體完整性檢查和違約處理25.2 參照完整性35.2.1 參照完整性定義35.2.2參照完整性檢查和違約處理45.3用戶定義的完整性65.3.1 屬性上的約束條件的定義65.3.2 屬性上的約束條件檢查和違約處理85.3.3元組上的約束條件的定義85.3.3元組上的約束條件檢查和違約處理85.4完整性約束命名子句9*5.5域中的完整性限制105.6 觸發(fā)器115.6.1 定義觸發(fā)器115.6.2激活觸發(fā)器135.6.3刪除觸發(fā)器14數(shù)據(jù)庫(kù)的完整性是指數(shù)據(jù)的正確性和兼容性。數(shù)據(jù)的完整性是為了防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義的

2、數(shù)據(jù)，也就是防止數(shù)據(jù)庫(kù)中存在不正確的數(shù)據(jù)。數(shù)據(jù)的安全性是保護(hù)數(shù)據(jù)庫(kù)防止惡意的破壞和非法的存取。為維護(hù)數(shù)據(jù)庫(kù)的完整性，DBMS必須能夠；1 提供定義完整性約束條件的機(jī)制2 提供完整性檢查的方法3 違約處理我們?cè)诘?章2.3已經(jīng)講解了關(guān)系數(shù)據(jù)庫(kù)三類完整性約束約束的基本概念，下面講解SQL語(yǔ)言中實(shí)現(xiàn)這些完整性控制功能的方法。5.1實(shí)體完整性5.1.1 實(shí)體完整性定義1. 將STUDENT表中的SNO屬性定義為碼。CREATE TABLE Student （Sno CHAR（9）PRIMARY KEY，/*在列級(jí)定義主碼*/ Sname CHAR（20） NOT NULL， Ssex CHAR（2），

3、 Sage SMALLINT， Sdept CHAR（20） ）；或者 CREATE TABLE Student （Sno CHAR（9）， Sname CHAR（20）NOT NULL， Ssex CHAR（20）， Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY (Sno) /*在表級(jí)定義主碼*/ );2. 將SC表中的Sno，Cno屬性組定義為碼 CREATE TABLE SC （Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT, PRIMARY KEY(Sno,Cno) /*只能在

4、表級(jí)定義主碼*/ ）；5.1.2 實(shí)體完整性檢查和違約處理包括：1. 檢查主碼值是否唯一，如果不唯一則拒絕插入或修改。2. 檢查主碼的各個(gè)屬性是否為空，只要有一個(gè)為空就拒絕插入或修改。從而保證了實(shí)體完整性。全表掃描是十分耗時(shí)的。為了避免對(duì)基本表進(jìn)行全表掃描，RDBMS核心一般都在主碼上自動(dòng)建立一個(gè)索引，如圖5.2的B+樹索引。通過(guò)索引查找基本表中是否已經(jīng)存在新的主碼值，將大大提高效率。255166 7812 303 715 20 2530 4151 54 6568 69 71 7679 84 93新記錄的主碼值 圖5.2 使用索引檢查主碼唯一性5.2 參照完整性5.2.1 參照完整性定義例如，

5、關(guān)系SC中一個(gè)元組表示一個(gè)學(xué)生選修的某門課程的成績(jī)，（Sno，Cno）是主碼。Sno，Cno分別參照引用Student表的主碼和Course表的主碼。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í)定義

6、參照完整性*/）；5.2.2參照完整性檢查和違約處理一個(gè)參照完整性將兩個(gè)表中的相應(yīng)元組聯(lián)系起來(lái)了。因此，對(duì)被參照表和參照表進(jìn)行增刪操作時(shí)有可能破壞參照完整性，必須進(jìn)行檢查。例如，對(duì)表SC和Student有四種可能破壞參照完整性的情況，如表5.1所示。1. SC表中增加一個(gè)元組，該元組的Sno屬性的值在表Student中找不到一個(gè)元組，其Sno屬性的值與之相等。2. 修改SC表中的一個(gè)元組，修改后該元組的Sno屬性的值在表Student中找不到一個(gè)元組，其Sno屬性的值與之相等。3. 從Student表中刪除一個(gè)元組，造成SC表中某些元組的Sno屬性的值在表Student中找不到一個(gè)元組，其Sn

7、o屬性的值與之相等。4. 修改Student表中的一個(gè)元組的Sno屬性，造成SC表中某些元組的Sno屬性的值在表Student中找不到一個(gè)元組，其Sno屬性的值與之相等。 表5.1可能破壞參照完整性的情況及違約處理被參照表（例如Student）參照表（例如SC）違約處理可能破壞參照完整性插入元組拒絕可能破壞參照完整性修改外碼值拒絕刪除元組可能破壞參照完整性拒絕/級(jí)連刪除/設(shè)置為空值修改主碼值可能破壞參照完整性拒絕/級(jí)連修改/設(shè)置為空值當(dāng)上述的不一致發(fā)生時(shí)，系統(tǒng)就可以采用以下的策略加以處理。1 拒絕（NO ACTION）執(zhí)行2 級(jí)連（CASCADE）操作3 設(shè)置為空值（SET-NULL）當(dāng)刪除或

8、修改被參照表的一個(gè)元組時(shí)造成了不一致，則將參照表中的所有造成不一致的元組的對(duì)應(yīng)屬性設(shè)置為空值。例如，有下面2個(gè)關(guān)系 學(xué)生（學(xué)號(hào)，姓名，性別，專業(yè)號(hào)，年齡） 專業(yè)（專業(yè)號(hào)，專業(yè)名）學(xué)生關(guān)系的“專業(yè)號(hào)”是外碼，因?yàn)閷I(yè)號(hào)是專業(yè)關(guān)系的主碼。假設(shè)專業(yè)表中某個(gè)元組被刪除，專業(yè)號(hào)為12，按照設(shè)置為了空值策略，就要把學(xué)生表中專業(yè)號(hào)=12的所有元組的專業(yè)號(hào)設(shè)置為空值。這對(duì)應(yīng)了這樣的語(yǔ)義：某個(gè)專業(yè)刪除了，該專業(yè)的所有學(xué)生專業(yè)未定，等待重新分配專業(yè)。因此對(duì)于參照完整性時(shí)，除了應(yīng)該定義外碼，還應(yīng)定義外碼列是否允許空值。4 顯示說(shuō)明參照完整性的違約處理事例。 CREATE TABLE SC （Sno CHAR (9)

9、 NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT, PRIMARY KEY(Sno,Cno), /*在表級(jí)定義實(shí)體完整性*/ FOREIGN KEY(Sno)REFERENCES Student(Sno) /*在表級(jí)定義參照完整性*/ ON DELETE CASCADE /*當(dāng)刪除Student表中的元組時(shí)，級(jí)連刪除SC表中相應(yīng)的元組*/ ON UPDATE CASCADE, /*當(dāng)更新Student表中的Sno時(shí)，級(jí)連更新SC表中相應(yīng)的元組*/ FOREIGN KEY(Cno)REFERENCES Course(Cno)/*在表級(jí)定義參照完整性*

10、/ ON DELETE NO ACTION /*當(dāng)刪除course表中的元組造成了與SC表不一致時(shí)拒絕刪除*/ ON UPDATE CASCADE /*當(dāng)更新course表中的cno時(shí)，級(jí)連更新SC表中相應(yīng)的元組*/ );從上面的討論看到RSBMS在實(shí)現(xiàn)參照完整性時(shí)，除了要提供定義主碼、外碼的機(jī)制外，還需要提供不同的策略供用戶選擇。選擇哪種策略，要根據(jù)應(yīng)用環(huán)境的要求確定。 5.3用戶定義的完整性用戶定義的完整性就是針對(duì)某個(gè)具體應(yīng)用的數(shù)據(jù)必須滿足的語(yǔ)義要求。目前的RDBMS都提供了定義和檢驗(yàn)這類完整性的機(jī)制，使用了和實(shí)體完整性、參照完整性相同的技術(shù)和方法來(lái)處理他們，而不必由應(yīng)用程序來(lái)承擔(dān)這一功能

11、。5.3.1 屬性上的約束條件的定義在CREATE TABLE中定義屬性的同時(shí)可以根據(jù)應(yīng)用要求，定義屬性上的約束條件，即屬性值限制，包括：l 列值非空（NOT NULL短語(yǔ)）l 列值唯一（UNIQUE短語(yǔ)）l 檢查列值是否滿足一個(gè)布爾表達(dá)式（CHECK短語(yǔ)）1. 不允許取空值在定義SC表時(shí)，說(shuō)明Sno、Cno、Grade屬性不允許取空值。CREATE TABLE SC (Sno CHAR (9) NOT NULL, /*Sno屬性不允許取空值*/ Cno CHAR(4)NOT NULL, /*Cno屬性不允許取空值*/ Grade SMALLINT NOT NULL,/*Grade屬性不允許取

12、空值*/ PRIMARY KEY（Sno，Cno），/*如果在表級(jí)定義實(shí)體完整性，隱含了Sno，Cno不允許取空值則在列級(jí)不允許取空值的定義就不必寫了*/ .）；2. 列值唯一建立部門表DEPT，要求部門名稱Dname列取值唯一，部門編號(hào)Deptno列為主碼。 CREATE TABLE DEPT （Deptno NUMERIC(2), Dname CHAR(9) UNIQUE, /*要求Dname列值唯一*/ Location CHAR(10), PRIMARY KEY (Deptno) );3. 用CHECK短語(yǔ)指定列值應(yīng)該滿足的條件 Student表的Ssex只允許取“男”或“女”CREA

13、TE TABLE Student(Sno CHAR(9) PRIMARY KEY, /*在列級(jí)定義主碼*/Sname CHAR(8) NOT NULL, /*Sname屬性不允許取空值*/Ssex CHAR(2) CHECK(Ssex in (男或女),/*性別屬性Ssex只允許取男或女*/Sage SMALLINT,Sdept CHAR(20);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

14、), PRIMARY KEY (Sno,Cno),FOREIGN (Sno) PEFERENCES Student(Sno),FOREIGN (Cno)REFERENCES Course(Cno);5.3.2 屬性上的約束條件檢查和違約處理當(dāng)往表中插入元組或修改屬性的值時(shí)，RDSMS就檢查屬性上的約束條件是否被滿足，如果不滿足則操作被拒絕執(zhí)行。5.3.3元組上的約束條件的定義與屬性上約束條件的定義類似，在CREATE TABLE語(yǔ)句中可以用CHECK短語(yǔ)定義元組上的約束條件，即元組級(jí)的限制。同屬性值限制相比，元組級(jí)的限制可以設(shè)置不同屬性之間的取值的相互約束條件。例9 當(dāng)學(xué)生的性別是男孩時(shí)，其名

15、字不能以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 LIKEMs.% );/*定義了元組中Sname和Ssex兩個(gè)屬性值之間的約束條件*/5.3.3元組上的約束條件檢查和違約處理當(dāng)往表中插入元組或修改屬性的值時(shí)，RDBMS就檢查元組上的約束條件是否被滿足，如果不滿足則操作被拒絕執(zhí)行。5.4完整性約束命名子句1. 完整性的約束命

16、名子句CONSTRAINT完整性約束條件名PRIMARY KEY短語(yǔ)|FOREIGN KEY短語(yǔ)|CHECK短語(yǔ)例 10 建立學(xué)生登記表Student，要求學(xué)號(hào)在90000-99999之間，姓名不能取空值，年齡小于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 CHECK (Sage=3000) )

17、;2. 修改表中的完整性限制我們可以使用ALTER TABLE 語(yǔ)句修改表中的完整性限制。（1） 去掉例10student表中對(duì)性別的限制。 ALTER TABLE Student DROP CONSTRAINT C4；（2）修改表Student中的約束條件，要求學(xué)號(hào)改為在-之間，年齡由小于30改為小于40?？梢韵葎h除原來(lái)的約束條件，再增加新的約束條件。 ALTER TABLE Student DROP CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN AND ), ALTER TABLE Stude

18、nt DROP CONSTRAINT C3; ALTER TABLE StudentADD CONSTRAINT C3 CHECK (Sage 40) *5.5域中的完整性限制 SQL支持域的概念，并可以用CREATE DOMAIN語(yǔ)句建立一個(gè)域應(yīng)該滿足的完整性約束條件 1.建立一個(gè)性別域，并聲明性別域的取值范圍 CREATE DOMAIN GenderDomain CHAR(2) CHECK(VALUE IN(男,女);2.對(duì)Ssex的說(shuō)明可以改寫為 Ssex GenderDomain3.建立一個(gè)性別域GenderDomain，并對(duì)其中的限制命名。 CREATE DOMAIN GenderD

19、omain CHAR(2) CONSTRAINT GD CHECK(VALUE IN(男,女);4.刪除域GenderDomain的限制條件GD。 ALTER DOMAIN Genderdomain DROP CONSTRAINT GD;5.在域GenderDomain上增加限制條件GDDALTER DOMAIN GenderdomainADD CONSTRAINT GDD CHECK (VALUE IN(1,0);這樣，通過(guò)例16和例17，就把性別的取值范圍由(男,女) 改為(1,0)。 5.6 觸發(fā)器觸發(fā)器（Trigger）是用戶定義在關(guān)系表上的一類由事件驅(qū)動(dòng)的特殊過(guò)程。觸發(fā)器類似于約束；

20、一旦定義，任何用戶對(duì)表的增、刪、改操作均由服務(wù)器自動(dòng)激活相應(yīng)的觸發(fā)器，在DBMS核心層進(jìn)行集中的完整性控制。不同的RDBMS實(shí)現(xiàn)的觸發(fā)器語(yǔ)法也會(huì)有所不同。請(qǐng)讀者在上機(jī)實(shí)驗(yàn)時(shí)注意閱讀實(shí)驗(yàn)系統(tǒng)的使用說(shuō)明。5.6.1 定義觸發(fā)器建立觸發(fā)器命令其一般格式為CREATE TRIGGER BEFORE|AFTER ONFOR EACH ROW|STATEMENTWHEN 觸發(fā)動(dòng)作體各部分語(yǔ)法進(jìn)行詳細(xì)說(shuō)明：1. 表的擁有者即創(chuàng)建表的用戶才可以在表上創(chuàng)建觸發(fā)器，并且一個(gè)表上只能創(chuàng)建一定數(shù)量的觸發(fā)器。2. 觸發(fā)器名：觸發(fā)器名可以包含模式名，也可以不包含模式名。同一模式下，觸發(fā)器名必須是唯一的；并且觸發(fā)器名和必須

21、在同一模式下。3. 表名：當(dāng)這個(gè)表的數(shù)據(jù)發(fā)生變化時(shí)，將激活定義在該表上相應(yīng)的觸發(fā)器，因此，該表也稱為觸發(fā)器的目標(biāo)表。4. 觸發(fā)事件：觸發(fā)事件可以是INSERT、DELETE或UPDATE，也可以是這幾個(gè)事件的組合，如INSERT OR DELETE等。UPDATE后面還可以有OF觸發(fā)列.，即進(jìn)一步修改那些列時(shí)觸發(fā)器激活。5. 觸發(fā)器類型：觸發(fā)器按照所觸發(fā)動(dòng)作的間隔尺寸可以分為行級(jí)觸發(fā)器（FOR EACH ROW）和語(yǔ)句級(jí)觸發(fā)器（FOR EACH STATEMENT）。例如，假設(shè)在例11的TEACHER表上創(chuàng)建了一個(gè)AFTER UPDATE觸發(fā)器。如果表TEACHER有1000行，執(zhí)行如下語(yǔ)句：

22、UPDATE TEACHER SET Deptone =5;如果該觸發(fā)器為語(yǔ)句級(jí)觸發(fā)器，那么執(zhí)行完語(yǔ)句后，觸發(fā)動(dòng)作只發(fā)生一次。如果是行動(dòng)級(jí)觸發(fā)器，觸發(fā)動(dòng)作將執(zhí)行1000次。6. 觸發(fā)條件：觸發(fā)器被激活時(shí)，只有當(dāng)觸發(fā)條件為真時(shí)觸發(fā)動(dòng)作體才執(zhí)行；否則觸發(fā)動(dòng)作體不執(zhí)行。如果省略WHEN觸發(fā)條件，則觸發(fā)動(dòng)作體在觸發(fā)器激活后立即執(zhí)行。7. 觸發(fā)動(dòng)作體：觸發(fā)動(dòng)作體既可以是一個(gè)匿名PL/SQL過(guò)程塊，也可以是對(duì)已創(chuàng)建存儲(chǔ)過(guò)程的調(diào)用。如果是行級(jí)觸發(fā)器，在兩種情況下，用戶都可以在過(guò)程體中使用NEW和OLD引用UPDATE/INSERT事件之后的新值和UPDATE/INSERT事件之前的舊值。如果是語(yǔ)句級(jí)觸發(fā)器，

23、則不能在觸發(fā)動(dòng)作體中使用NEW和OLD進(jìn)行引用。如果觸發(fā)動(dòng)作體執(zhí)行失敗，激活觸發(fā)器的時(shí)間就會(huì)中止執(zhí)行，觸發(fā)器的目標(biāo)表或觸發(fā)器可能影響的其他對(duì)象不發(fā)生任何變化。例18定義一個(gè)BEFORE行級(jí)觸發(fā)器，為教師表TEACHER定義完整性規(guī)則“接受的工資不得低于4000元，如果低于4000元，自動(dòng)改為4000元”。CREATE TRIGGER Insert_Or_Sal /*在教師表Teacher上定義觸發(fā)器*/ BEFORE INSERT OR UPDATE ON Teacher /*觸發(fā)事件是插入或更新操作*/ FOR EACH ROW /*這是行級(jí)觸發(fā)器*/ AS BEGIN /*定義觸發(fā)動(dòng)作體，

24、這是一個(gè)PL/SQL過(guò)程塊*/ If (new.job=教授) AND (new.Sal4000) THEN New.Sal=4000; /*因?yàn)槭切屑?jí)觸發(fā)器，可在過(guò)程體中使用插入或更新操作后的新值*/ END IF; END; /*觸發(fā)動(dòng)作體結(jié)束*/例19定義AFTER行級(jí)觸發(fā)器，當(dāng)教師表Teacher的工資發(fā)生變化以后就自動(dòng)在工資變化表Sal_log中增加一條相應(yīng)的記錄。 首先建立工資變化表Sal_log. CREATE TABLE Sal_log (Eno NUMERIC(4) referencr teacher(eno), Sal NUMERIC(7,2), Username char(10), Date TIMESTAMP); CREATE TRIGGER Insert_Sal /*建立了一個(gè)觸發(fā)器*/ AFTER INSERT ON Teacher /*觸發(fā)事件是INSERT*/ FOR EACH ROW AS BEGIN INSERT INTO Sal_log VALUES( New.Eno,new.Sa