第五章 完整性 - use

1、第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性q什么是數(shù)據(jù)庫的完整性什么是數(shù)據(jù)庫的完整性q完整性約束條件完整性約束條件 q完整性控制完整性控制5.1 完整性完整性l什么是數(shù)據(jù)庫的完整性什么是數(shù)據(jù)庫的完整性數(shù)據(jù)庫的完整性是指數(shù)據(jù)的正確性和相容性，防止不合語義的數(shù)據(jù)進入數(shù)據(jù)庫。例: 學生的年齡必須是整數(shù)，取值范圍為14-29； 學生的性別只能是男或女； 學生的學號一定是唯一的； 學生所在的系必須是學校開設的系；數(shù)據(jù)庫是否具備完整性關系到數(shù)據(jù)庫系統(tǒng)能否真實地反映現(xiàn)實世界，因此維護數(shù)據(jù)庫的完整性是非常重要的。5.2 完整性控制完整性控制lDBMS的完整性控制機制的完整性控制機制l關系系統(tǒng)三類完整性的實現(xiàn)關系系

2、統(tǒng)三類完整性的實現(xiàn)5.2.1 DBMS的完整性控制機制的完整性控制機制lDBMS的完整性控制機制的主要功能的完整性控制機制的主要功能 定義功能允許用戶定義各類完整性約束條件 檢查功能l檢查用戶提供的請求是否違背了完整性約束條件 違約反應l如果發(fā)現(xiàn)用戶的操作違背了完整性約束條件, 則采取一定的動作來保證數(shù)據(jù)的完整性(一般是取消該事務已產(chǎn)生的影響-恢復)5.2.2 DBMS的完整性控制機制的完整性控制機制l違約反應違約反應 拒絕該操作 其他處理方法5.2.2關系系統(tǒng)三類完整性的實現(xiàn)關系系統(tǒng)三類完整性的實現(xiàn)l目前許多關系數(shù)據(jù)庫系統(tǒng)都提供了定義和檢查實體完目前許多關系數(shù)據(jù)庫系統(tǒng)都提供了定義和檢查實體完

3、整性、參照完整性和用戶定義的完整性的功能。整性、參照完整性和用戶定義的完整性的功能。l對于對于違反實體完整性違反實體完整性規(guī)則和用戶定義的完整性規(guī)則的規(guī)則和用戶定義的完整性規(guī)則的操作操作一般都是采用拒絕執(zhí)行一般都是采用拒絕執(zhí)行的方式進行處理。的方式進行處理。l而對于而對于違反參照完整性違反參照完整性的操作，并不都是簡單地拒絕的操作，并不都是簡單地拒絕執(zhí)行，有時還需要采取另一種方法，即接受這個操作，執(zhí)行，有時還需要采取另一種方法，即接受這個操作，同時執(zhí)行一些附加的操作，以保證數(shù)據(jù)庫的狀態(tài)仍然同時執(zhí)行一些附加的操作，以保證數(shù)據(jù)庫的狀態(tài)仍然是正確的。是正確的。5.3 Sql Server中完整性的實

4、現(xiàn)中完整性的實現(xiàn)實體完整性實體完整性參照完整性參照完整性用戶定義完整性用戶定義完整性5.3.1實體完整性實體完整性用用primary key定義定義lcreate table studentl(lsno char(10) primary key,lsname char(10) not null,lssex char(2),lsage smallint,lsdept char(10)l)linsert into student(sno,sname)lvalues(1,wang)5.3.1實體完整性實體完整性標識列(IDENTITY)CREATE TABLE TC(a int identity(1

5、0,2),b char(20)insert into tc(b)values(a)select * from tc5.3.1 實體完整性實體完整性實體完整性檢查和違約處理實體完整性檢查和違約處理1.檢查主碼值是否唯一，如果不唯一則拒絕插入檢查主碼值是否唯一，如果不唯一則拒絕插入或修改?；蛐薷?。2.檢查主碼的各個屬性是否為空，只要有一個為檢查主碼的各個屬性是否為空，只要有一個為空就拒絕插入或修改?？站途芙^插入或修改。5.3.2 參照完整性參照完整性用用foreign key短語定義哪些列為外碼，用短語定義哪些列為外碼，用references短語指明這些外碼參照哪些表的主碼短語指明這些外碼參照哪些

6、表的主碼lcreate table scl(lsno char(10),lcno char(10),lgrade int,lforeign key (sno) references student(sno)l)5.3.2參照完整性參照完整性lcreate table scl(lsno char(10),lcno char(10),lgrade int,lforeign key (sno) references student(sno)lon delete cascadelon update cascadel)5.3.2參照完整性參照完整性ldelete lfrom studentlwhere

7、sno=1lupdate studentlset sno=001lwhere sno=15.3.2 參照完整性參照完整性對被參照表和參照表進行增刪改操作時有可能破對被參照表和參照表進行增刪改操作時有可能破壞參照完整性，必須進行檢查。壞參照完整性，必須進行檢查。1.SC表中增加一個元組。表中增加一個元組。2.修改修改SC表中的一個元組。表中的一個元組。3.從從Student表中刪除一個元組。表中刪除一個元組。4.修改修改Student表中一個元組的表中一個元組的Sno屬性。屬性。5.3.2 參照完整性參照完整性當上述的不一致發(fā)生時，系統(tǒng)可以采用以下的策略加以當上述的不一致發(fā)生時，系統(tǒng)可以采用以下

8、的策略加以處理。處理。1.拒絕執(zhí)行拒絕執(zhí)行不允許該操作執(zhí)行。該策略一般設置為默認策略。不允許該操作執(zhí)行。該策略一般設置為默認策略。2.級聯(lián)操作級聯(lián)操作當刪除或修改被參照表的一個元組造成了與參照表的不當刪除或修改被參照表的一個元組造成了與參照表的不一致，則刪除或修改參照表中的所有造成不一致的元組。一致，則刪除或修改參照表中的所有造成不一致的元組。5.4 用戶定義的完整性用戶定義的完整性1.不允許為空不允許為空2.列值唯一列值唯一3.使用使用CHECK短語短語4.元組上的約束條件元組上的約束條件5.完整性約束命名子句完整性約束命名子句6.觸發(fā)器觸發(fā)器5.4.1用戶定義的完整性用戶定義的完整性lCR

9、EATE TABLE T1l(lS CHAR(10) CHECK(S IN (男男,女女)l)linsert into t1lValues(mei)5.4.1用戶定義的完整性用戶定義的完整性元組上的約束條件定義元組上的約束條件定義lcreate table t2l(la int,lb char(10),lcheck(a10 and b not like 王王%)l)linsert into t2(a,b)lvalues(11,張一張一)lselect * from t25.4.1用戶定義的完整性用戶定義的完整性完整性約束命名子句完整性約束命名子句lcreate table t3l(lsno i

10、ntlconstraint c1 check(sno between 100 and 999)l)linsert into t3lvalues(1097)5.4.1用戶定義的完整性用戶定義的完整性修改表中的完整性限制修改表中的完整性限制lalter table t3ldrop constraint c1lalter table t3ladd constraint c1 check(sno between 100 and 999)5.4.2 觸發(fā)器觸發(fā)器Student CourseSC在在DB中有這么一個功能，輸入一條成績之后，如果成績合格，系統(tǒng)中有這么一個功能，輸入一條成績之后，如果成績合格，

11、系統(tǒng)自動根據(jù)課程號得到課程學分，再把課程的學分加到該學生的總學自動根據(jù)課程號得到課程學分，再把課程的學分加到該學生的總學分上。分上。 5.4.2 觸發(fā)器觸發(fā)器snosnamescredit1wang25cnocnameccredit1DB22C3StudentCoursesnocnogradeSCcnocnameccredit1DB22C3snocnograde1170在course表中查找1號課程學分snosnamescredit1wang2525+2snosnamescredit1wang275.4.2 觸發(fā)器觸發(fā)器觸發(fā)器（trigger）是一種特殊類型的存儲過程;它不能由用戶直接調(diào)用，而

12、是在對表進行操作（insert,update,delete）時,被自動地激活。5.4.2 觸發(fā)器觸發(fā)器使用CREATE TRIGGER語句創(chuàng)建觸發(fā)器語法格式如下：5.4.2 觸發(fā)器觸發(fā)器5.4.2 觸發(fā)器觸發(fā)器（4）INSTEAD OF：執(zhí)行本觸發(fā)器而不是觸發(fā)操：執(zhí)行本觸發(fā)器而不是觸發(fā)操作，即觸發(fā)器替代觸發(fā)語句的操作。每個更新操作作，即觸發(fā)器替代觸發(fā)語句的操作。每個更新操作（DELETE,INSERT,UPDATE）最多只能定義一個最多只能定義一個INSTEAD OF觸發(fā)器。觸發(fā)器。（5）DELETE , INSERT , UPDATE：引發(fā)：引發(fā)觸發(fā)器執(zhí)行的操作觸發(fā)器執(zhí)行的操作( (觸發(fā)操作

13、觸發(fā)操作) )，至少要指定一個選，至少要指定一個選項，若選項多于一個，需用逗號分隔這些選項。項，若選項多于一個，需用逗號分隔這些選項。（6）sql_statement：定義觸發(fā)器將執(zhí)行的：定義觸發(fā)器將執(zhí)行的SQLSQL語句。語句。5.4.2 觸發(fā)器觸發(fā)器在觸發(fā)器語句中可以使用兩個特殊的臨時工作表：INSERTED表和DELETED表。這兩個表是在用戶執(zhí)行數(shù)據(jù)的更改操作時，SQL Server自動創(chuàng)建和管理的。這兩個表駐留在內(nèi)存中，其結構同觸發(fā)器所作用的基本表的結構，并且只可以被觸發(fā)器使用，當觸發(fā)器結束時系統(tǒng)自動釋放這兩個表的空間。5.4.2 觸發(fā)器觸發(fā)器Deleted表存放由于執(zhí)行Delete

14、或Update語句而要從表中刪除的所有行。Inserted表存放由于執(zhí)行Insert或Update語句而要向表中插入的所有行。deleted、inserted表在執(zhí)行觸發(fā)器時記錄的發(fā)生情況。Transact-SQL語句deleted表inserted表INSERT空新增加的記錄UPDATE舊記錄新記錄DELETE刪除的記錄空5.4.2 觸發(fā)器觸發(fā)器create trigger InsertScore on SC after insertasbegindeclare Icno int;declare Isno int;declare Igrade int;declare Icredit int;

15、5.4.2 觸發(fā)器觸發(fā)器select Icno=cno from inserted;select Isno=sno from inserted;select Igrade=grade from inserted;select Icredit=ccredit from course where cno=Icno;if Igrade=60 update student set scredit=scredit+Icreditwhere sno=Isno;end;5.4.2 觸發(fā)器觸發(fā)器對選課記錄進行修改，如果成績不在對選課記錄進行修改，如果成績不在0,100之間，之間，則不修改，輸出成績錯誤，否則，修改。則不修改，輸出成績錯誤，否則，修改。5.4.2 觸發(fā)器觸發(fā)器create trigger UpdateGrade on scinstead of updateasdeclare Ngrade int;declare Nsno int;declare Ncno int;select Ngrade=grade from inserted;select Nsno=sno from inserted;select Ncno=cno from inserted;5.4.2 觸發(fā)器觸發(fā)器if Ngrade=0 and Ngrade=100update scset grade=Ngra