1、第第5章章 數據庫完整性數據庫完整性l5.1 實體完整性實體完整性l5.2 參照完整性參照完整性l5.3 用戶自定義完整性用戶自定義完整性l5.4 完整性約束命名子句完整性約束命名子句l5.6 觸發器觸發器l5.7 小結小結數據庫完整性（續）數據庫完整性（續）l什么是數據庫的完整性什么是數據庫的完整性 數據庫的完整性是指數據的正確性和相容性，數據庫的完整性是指數據的正確性和相容性，防止不合語義的數據進入數據庫。防止不合語義的數據進入數據庫。例例: 學生的年齡必須是整數，取值范圍為學生的年齡必須是整數，取值范圍為14-29； 學生的性別只能是男或女；學生的性別只能是男或女； 學生的學號一定是唯一

2、的；學生的學號一定是唯一的； 學生所在的系必須是學校開設的系；學生所在的系必須是學校開設的系； 數據庫是否具備完整性關系到數據庫系統能數據庫是否具備完整性關系到數據庫系統能否真實地反映現實世界，因此維護數據庫的否真實地反映現實世界，因此維護數據庫的完整性是非常重要的。完整性是非常重要的。數據庫完整性（續）數據庫完整性（續）l完整性控制機制完整性控制機制1.完整性約束條件定義機制完整性約束條件定義機制2.完整性檢查機制完整性檢查機制3.違約處理違約處理 數據庫完整性（續）數據庫完整性（續）l完整性控制機制完整性控制機制(續續)1.完整性約束條件定義機制完整性約束條件定義機制 完整性約束條件是數據

3、模型的一個重要組成完整性約束條件是數據模型的一個重要組成部分部分,它約束了數據庫中數據的語義。它約束了數據庫中數據的語義。 dbms應提供手段讓用戶根據現實世界的語應提供手段讓用戶根據現實世界的語義定義數據庫的完整性約束條件，并把它們義定義數據庫的完整性約束條件，并把它們作為模式的一部分存入數據庫中。作為模式的一部分存入數據庫中。 數據庫完整性（續）數據庫完整性（續）l完整性控制機制完整性控制機制(續續)2.完整性檢查機制完整性檢查機制 檢查用戶發出的操作請求是否違背了完整性檢查用戶發出的操作請求是否違背了完整性約束條件。約束條件。 一般在一般在insert、update、delete語句語句

4、執行后開始檢查，也可在事務提交時檢查。執行后開始檢查，也可在事務提交時檢查。 數據庫完整性（續）數據庫完整性（續）l完整性控制機制完整性控制機制(續續)3.違約處理違約處理 如果發現用戶的操作請求使數據違背了完整如果發現用戶的操作請求使數據違背了完整性約束條件，則采取一定的動作來保證數據性約束條件，則采取一定的動作來保證數據的完整性。的完整性。第第5章章 數據庫完整性數據庫完整性l5.1 實體完整性實體完整性l5.2 參照完整性參照完整性l5.3 用戶自定義完整性用戶自定義完整性l5.4 完整性約束命名子句完整性約束命名子句l5.6 觸發器觸發器l5.7 小結小結5.1 實體完整性實體完整性l

5、5.1.1 實體完整性定義實體完整性定義l5.1.2 實體完整性檢查和違約處理實體完整性檢查和違約處理5.1 實體完整性實體完整性l5.1.1 實體完整性定義實體完整性定義l5.1.2 實體完整性檢查和違約處理實體完整性檢查和違約處理5.1.1 實體完整性定義實體完整性定義l實體完整性在實體完整性在create table中用中用primary key定義定義l單屬性構成的碼有兩種說明方法：單屬性構成的碼有兩種說明方法： - 定義為列級約束條件定義為列級約束條件 - 定義為表級約束條件定義為表級約束條件l多個屬性構成的碼只有一種說明方法：多個屬性構成的碼只有一種說明方法： - 定義為表級約束條

6、件定義為表級約束條件例題例題例例1 將將student表中的表中的sno屬性定義為碼屬性定義為碼 create table student ( sno char(9) primary key, sname char(20) not null, ssex char(2), sage smallint, sdept char(20) ); 注：在列級定義主碼注：在列級定義主碼 例題例題或者：或者： create table student ( sno char(9), sname char(20) not null, ssex char(2), sage smallint, sdept char(

7、20), primary key (sno) ); 注：在表級定義主碼注：在表級定義主碼例題例題例例2 將將sc表中的表中的sno,cno屬性組定義為碼屬性組定義為碼 create table sc ( sno char(9) not null, cno char(4) not null, grade smallint, primary key (sno,cno) ); 注：只能在表級定義主碼注：只能在表級定義主碼5.1 實體完整性實體完整性l5.1.1 實體完整性定義實體完整性定義l5.1.2 實體完整性檢查和違約處理實體完整性檢查和違約處理5.1.2 實體完整性檢查和違約處理實體完整性檢查

8、和違約處理l用用primary key短語定義了關系的主碼后，短語定義了關系的主碼后，每當用戶程序對基本表進行插入或更新操作時，每當用戶程序對基本表進行插入或更新操作時，rdms將按照實體完整性規則自動進行檢查。將按照實體完整性規則自動進行檢查。 - 檢查主碼值是否唯一，如果不唯一則拒絕插入或修檢查主碼值是否唯一，如果不唯一則拒絕插入或修改改 - 檢查主碼的各個屬性是否為空，只要有一個為空就檢查主碼的各個屬性是否為空，只要有一個為空就拒絕插入或修改拒絕插入或修改實體完整性檢查和違約處理實體完整性檢查和違約處理(續續)l檢查記錄中主碼值是否唯一的一種方法是進行檢查記錄中主碼值是否唯一的一種方法是

9、進行全表掃描。依次判斷表中每一條記錄的主碼值全表掃描。依次判斷表中每一條記錄的主碼值與將插入記錄上的主碼值（或者修改的新主碼與將插入記錄上的主碼值（或者修改的新主碼值）是否相同。值）是否相同。l全表掃描是十分耗時的全表掃描是十分耗時的lrdbms核心一般都在主碼上自動建立一個索核心一般都在主碼上自動建立一個索引，通過索引查找基本表中是否已經存在新的引，通過索引查找基本表中是否已經存在新的主碼值，將大大提高效率。主碼值，將大大提高效率。第第5章章 數據庫完整性數據庫完整性l5.1 實體完整性實體完整性l5.2 參照完整性參照完整性l5.3 用戶自定義完整性用戶自定義完整性l5.4 完整性約束命名

10、子句完整性約束命名子句l5.6 觸發器觸發器l5.7 小結小結5.2 參照完整性參照完整性l5.2.1 參照完整性定義參照完整性定義l5.2.2 參照完整性檢查和違約處理參照完整性檢查和違約處理5.2 參照完整性參照完整性l5.2.1 參照完整性定義參照完整性定義l5.2.2 參照完整性檢查和違約處理參照完整性檢查和違約處理5.2.1 參照完整性定義參照完整性定義l參照完整性在參照完整性在cretat table中用中用foreign key短短語定義哪些列為外碼，用語定義哪些列為外碼，用references短語指明這短語指明這些外碼參照哪些表的主碼。些外碼參照哪些表的主碼。 例例3：定義：定

11、義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.2 參照完整性參照完整性l5.2.1 參照完整性定義參照完整性定義l5.2.2 參照完整性檢查和違約處理參照完整性檢查和違約處理5.2.2 參照完整性檢查和違約處理參照完整性檢查和違約處理l一個參

12、照完整性將兩個表中的相應元組聯系起一個參照完整性將兩個表中的相應元組聯系起來了，因此對被參照表和參照表進行增刪改操來了，因此對被參照表和參照表進行增刪改操作時有可能破壞參照完整性，必須進行檢查。作時有可能破壞參照完整性，必須進行檢查。l當不一致發生時，系統可以采用以下的策略進當不一致發生時，系統可以采用以下的策略進行處理行處理 拒絕執行，一般為默認策略拒絕執行，一般為默認策略 級連操作級連操作 設置為空值設置為空值參照完整性檢查和違約處理參照完整性檢查和違約處理(續續)l當對參照表和被參照表的操作違反了參照完整性，系當對參照表和被參照表的操作違反了參照完整性，系統采用默認策略。若想讓系統采用其

13、他的策略必須在統采用默認策略。若想讓系統采用其他的策略必須在創建表的時候顯式地加以說明。創建表的時候顯式地加以說明。 例例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 on update cascade, foreign key(cno) references course(cno) on delete no action

14、 on update cascade);第第5章章 數據庫完整性數據庫完整性l5.1 實體完整性實體完整性l5.2 參照完整性參照完整性l5.3 用戶自定義完整性用戶自定義完整性l5.4 完整性約束命名子句完整性約束命名子句l5.6 觸發器觸發器l5.7 小結小結5.3 用戶定義的完整性用戶定義的完整性l5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義l5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理l5.3.3 元組上的約束條件的定義元組上的約束條件的定義l5.3.4 元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理5.3.1 屬性上的約束條件的定

15、義屬性上的約束條件的定義l在在create table 中定義屬性的同時可以根中定義屬性的同時可以根據應用要求，定義屬性上的約束條件，即屬性據應用要求，定義屬性上的約束條件，即屬性值限制，包括：值限制，包括： - 列值非空（列值非空（not null短語）短語） - 列值唯一（列值唯一（unique短語）短語） - 檢查列值是否滿足一個布爾表達式（檢查列值是否滿足一個布爾表達式（check短語）短語）例題例題例例5 在定義在定義sc表時，說明表時，說明sno,cno,grade屬性不屬性不允許取空值允許取空值 create table sc ( sno char(9) not null, cn

16、o char(4) not null, grade smallint not null, primary key(sno,cno);例題例題例例6 建立部門表建立部門表dept，要求部門名稱，要求部門名稱dname列列取值唯一，部門編號取值唯一，部門編號deptno列為主碼列為主碼 create table dept ( deptno numeric(2), dname char(9) unique, location char(10), primary key (deptno);例題例題例例7 student表的表的ssex只允許取只允許取“男男”或或“女女” create table st

17、udent ( sno char(9) primary key, sname char(8) not null, ssex char(2) check(ssex in (男男,女女), sage smallint, sdept char(20) ); 例題例題例例8 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), fore

18、ign key(sno) references student(sno), foreign key(cno) references course(cno);5.3 用戶定義的完整性用戶定義的完整性l5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義l5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理l5.3.3 元組上的約束條件的定義元組上的約束條件的定義l5.3.4 元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理5.3.2 屬性上的約束條件檢查和違屬性上的約束條件檢查和違約處理約處理l當往表中插入元組或修改屬性的值時，當往表中插入元組或修改屬性的值時

19、，rdbms就檢查屬性上的約束條件是否被就檢查屬性上的約束條件是否被滿足，若不滿足則操作被拒絕執行。滿足，若不滿足則操作被拒絕執行。5.3 用戶定義的完整性用戶定義的完整性l5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義l5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理l5.3.3 元組上的約束條件的定義元組上的約束條件的定義l5.3.4 元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理5.3.3 元組上的約束條件的定義元組上的約束條件的定義l在在create table語句中可以用語句中可以用check短語定義元組上的約束條件，即短語定義元組上的

20、約束條件，即元組級的限制。元組級的限制。l元組級的限制可以設置不同屬性之間的元組級的限制可以設置不同屬性之間的取值的相互約束條件取值的相互約束條件例題例題 例例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.%);5.3 用戶定義的完整性用戶定義

21、的完整性l5.3.1 屬性上的約束條件的定義屬性上的約束條件的定義l5.3.2 屬性上的約束條件檢查和違約處理屬性上的約束條件檢查和違約處理l5.3.3 元組上的約束條件的定義元組上的約束條件的定義l5.3.4 元組上的約束條件檢查和違約處理元組上的約束條件檢查和違約處理5.3.4 元組上的約束條件檢查和違元組上的約束條件檢查和違約處理約處理l當往表中插入元組或修改屬性的值時，當往表中插入元組或修改屬性的值時，rdbms就檢查元組上的約束條件是否被就檢查元組上的約束條件是否被滿足，如果不滿足則操作被拒絕執行。滿足，如果不滿足則操作被拒絕執行。第第5章章 數據庫完整性數據庫完整性l5.1 實體完

22、整性實體完整性l5.2 參照完整性參照完整性l5.3 用戶自定義完整性用戶自定義完整性l5.4 完整性約束命名子句完整性約束命名子句l5.6 觸發器觸發器l5.7 小結小結5.4 完整性約束命名子句完整性約束命名子句lsql還在還在create table語句中提供了語句中提供了完整性約束命名子句完整性約束命名子句constraint，用，用來對完整性約束條件命名。從而可以靈來對完整性約束條件命名。從而可以靈活地增加、刪除一個完整性約束條件活地增加、刪除一個完整性約束條件l完整性約束命名子句完整性約束命名子句 constraint primary key短語短語foreign key 短語短語

23、check短語短語例題例題例例10 建立學生登記表建立學生登記表student，要求學號在，要求學號在90000-99999之間，姓名之間，姓名不能取空值，年齡小于不能取空值，年齡小于30，性別只能是，性別只能是“男男”或或“女女”。 create table student ( sno numeric(6) constraint c1 check(sno between 900000 and 99999), sname char(20) constraint c2 not null, sage numeric(3) constraint c3 check (sage=3000);修改表中的完

24、整性限制修改表中的完整性限制l可以使用可以使用alter table 語句修改表中語句修改表中的完整性限制的完整性限制 例例12 去掉去掉student表中對性別的限制表中對性別的限制 alter table student drop constraint c4; 例題例題例例13 修改表修改表student中的約束條件，要求學號改為在中的約束條件，要求學號改為在900000-999999之間，年齡由小于之間，年齡由小于30改為小于改為小于40 可以先刪除再增加可以先刪除再增加 alter table student drop constraint c1; alter table studen

25、t add constraint c1 check (sno between 900000 and 999999); alter table student drop constraint c3; alter table student add constraint c3 check (sage40);第第5章章 數據庫完整性數據庫完整性l5.1 實體完整性實體完整性l5.2 參照完整性參照完整性l5.3 用戶自定義完整性用戶自定義完整性l5.4 完整性約束命名子句完整性約束命名子句l5.6 觸發器觸發器l5.7 小結小結5.6 觸發器觸發器l觸發器是用戶定義在關系表上的一類由事件驅觸發器是用

26、戶定義在關系表上的一類由事件驅動的特殊過程動的特殊過程l一旦定義，任何用戶對表的增、刪、改操作均一旦定義，任何用戶對表的增、刪、改操作均由服務器自動激活相應的觸發器，在由服務器自動激活相應的觸發器，在dbms核核心層進行集中的完整性控制。心層進行集中的完整性控制。l類似于約束，但比約束更加靈活，可以實施比類似于約束，但比約束更加靈活，可以實施比foreign key約束、約束、check約束更為復雜約束更為復雜的檢查和操作，具有更精細和更強大的數據控的檢查和操作，具有更精細和更強大的數據控制能力。制能力。5.6 觸發器觸發器l5.6.1 定義觸發器定義觸發器l5.6.2 激活觸發器激活觸發器l

27、5.6.3 刪除觸發器刪除觸發器5.6.1 定義觸發器定義觸發器lsql使用使用create trigger命令建立觸發器，命令建立觸發器，一般格式為：一般格式為： create trigger beforeafter on for each rowstatement when 定義觸發器（續）定義觸發器（續）l表的擁有者即創建表的用戶才可以在表上創建觸發器，表的擁有者即創建表的用戶才可以在表上創建觸發器，并且一個表上只能創建一定數量的觸發器。并且一個表上只能創建一定數量的觸發器。l觸發器名可以包含模式名，也可以不包含模式名。同觸發器名可以包含模式名，也可以不包含模式名。同一模式下，觸發器名必

28、須是唯一的；且觸發器名和表一模式下，觸發器名必須是唯一的；且觸發器名和表名必須在同一模式下。名必須在同一模式下。l當表的數據發生變化時，將激活定義在表上相應觸發當表的數據發生變化時，將激活定義在表上相應觸發事件的觸發器，因此，表也被稱為觸發器的目標表事件的觸發器，因此，表也被稱為觸發器的目標表l觸發事件可以是觸發事件可以是insert、delete或或update，也，也可以是這幾個事件的組合?？梢允沁@幾個事件的組合。update后面還可以有后面還可以有of，用于進一步指明修改哪些列時觸發，用于進一步指明修改哪些列時觸發器激活。器激活。定義觸發器（續）定義觸發器（續）l按照所觸發動作的間隔尺寸

29、可以分為行級觸發器按照所觸發動作的間隔尺寸可以分為行級觸發器(for each row)和語句級觸發器和語句級觸發器(for each statement)l語句級觸發器觸發動作只執行一次，行級觸發器的觸語句級觸發器觸發動作只執行一次，行級觸發器的觸發動作執行的次數與涉及到的元組數相關發動作執行的次數與涉及到的元組數相關l觸發器被激活時，只有當觸發條件為真時觸發動作體觸發器被激活時，只有當觸發條件為真時觸發動作體才執行；否則觸發動作體不執行，若省略才執行；否則觸發動作體不執行，若省略when觸發觸發條件，則觸發動作體在觸發器激活后立即執行條件，則觸發動作體在觸發器激活后立即執行l觸發動作體既可

30、以是一個匿名觸發動作體既可以是一個匿名pl/sql過程塊，也可以過程塊，也可以是對已創建存儲過程的調用。是對已創建存儲過程的調用。定義觸發器（續）定義觸發器（續）l在行級觸發器中，不管是匿名在行級觸發器中，不管是匿名pl/sql過程塊還是對已過程塊還是對已創建存儲過程的調用，用戶都可以在過程體中使用創建存儲過程的調用，用戶都可以在過程體中使用new和和old引用引用update/insert事件之后的新值和事件之后的新值和update/delete事件之前的舊值。若是語句級觸發事件之前的舊值。若是語句級觸發器，則不能在觸發動作體中使用器，則不能在觸發動作體中使用new或或old進行引用。進行引

31、用。l若觸發動作體執行失敗，激活觸發器的事件就會終止若觸發動作體執行失敗，激活觸發器的事件就會終止執行，觸發器的目標表或觸發器可能影響的其他對象執行，觸發器的目標表或觸發器可能影響的其他對象不發生任何變化。不發生任何變化。例題例題例例18 定義一個定義一個before行級觸發器，為教師表行級觸發器，為教師表teacher定義完整性規則定義完整性規則“教授的工資不得低于教授的工資不得低于4000元，若低元，若低于于4000元，自動改為元，自動改為4000元元” create trigger insert_or_update_sal before insert or update on teach

32、er for each row as begin if (new.job=教授教授) and (new.sal4000) then new.sal:=4000; end if; end;例題（續）例題（續）例例19 定義定義after行級觸發器，當教師表行級觸發器，當教師表teacher的工資的工資發生變化后就自動在工資變化表發生變化后就自動在工資變化表sal_log中增加一條相中增加一條相應記錄應記錄 先建立工資變化表先建立工資變化表sal_log： create table sal_lot (eno numeric(4) references teacher(eno), sal numer

33、ic(7,2), username char(10), date timestamp);例題（續）例題（續）接著分別為接著分別為insert和和update操作創建觸發器：操作創建觸發器： create trigger insert_sal after insert on teacher for each row as begin insert into sal_log values (new.eno,new.sal,current_user, current_timestamp); end;例題（續）例題（續） create trigger update_sal after update on teacher for each row as begin if (new.salold.sal) then insert into sal_log values (new.eno,new.sal,current_user, current_timestamp); end if; end;5.6 觸發器觸發器