第五章 完整性 - use

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

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

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

4、現(xiàn)中完整性的實(shí)現(xiàn)實(shí)體完整性實(shí)體完整性參照完整性參照完整性用戶定義完整性用戶定義完整性5.3.1實(shí)體完整性實(shí)體完整性用用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實(shí)體完整性實(shí)體完整性標(biāo)識(shí)列(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 實(shí)體完整性實(shí)體完整性實(shí)體完整性檢查和違約處理實(shí)體完整性檢查和違約處理1.檢查主碼值是否唯一，如果不唯一則拒絕插入檢查主碼值是否唯一，如果不唯一則拒絕插入或修改。或修改。2.檢查主碼的各個(gè)屬性是否為空，只要有一個(gè)為檢查主碼的各個(gè)屬性是否為空，只要有一個(gè)為空就拒絕插入或修改?？站途芙^插入或修改。5.3.2 參照完整性參照完整性用用foreign key短語(yǔ)定義哪些列為外碼，用短語(yǔ)定義哪些列為外碼，用references短語(yǔ)指明這些外碼參照哪些表的主碼短語(yǔ)指明這些外碼參照哪些

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 參照完整性參照完整性對(duì)被參照表和參照表進(jìn)行增刪改操作時(shí)有可能破對(duì)被參照表和參照表進(jìn)行增刪改操作時(shí)有可能破壞參照完整性，必須進(jìn)行檢查。壞參照完整性，必須進(jìn)行檢查。1.SC表中增加一個(gè)元組。表中增加一個(gè)元組。2.修改修改SC表中的一個(gè)元組。表中的一個(gè)元組。3.從從Student表中刪除一個(gè)元組。表中刪除一個(gè)元組。4.修改修改Student表中一個(gè)元組的表中一個(gè)元組的Sno屬性。屬性。5.3.2 參照完整性參照完整性當(dāng)上述的不一致發(fā)生時(shí)，系統(tǒng)可以采用以下的策略加以當(dāng)上述的不一致發(fā)生時(shí)，系統(tǒng)可以采用以下

8、的策略加以處理。處理。1.拒絕執(zhí)行拒絕執(zhí)行不允許該操作執(zhí)行。該策略一般設(shè)置為默認(rèn)策略。不允許該操作執(zhí)行。該策略一般設(shè)置為默認(rèn)策略。2.級(jí)聯(lián)操作級(jí)聯(lián)操作當(dāng)刪除或修改被參照表的一個(gè)元組造成了與參照表的不當(dāng)刪除或修改被參照表的一個(gè)元組造成了與參照表的不一致，則刪除或修改參照表中的所有造成不一致的元組。一致，則刪除或修改參照表中的所有造成不一致的元組。5.4 用戶定義的完整性用戶定義的完整性1.不允許為空不允許為空2.列值唯一列值唯一3.使用使用CHECK短語(yǔ)短語(yǔ)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中有這么一個(gè)功能，輸入一條成績(jī)之后，如果成績(jī)合格，系統(tǒng)中有這么一個(gè)功能，輸入一條成績(jī)之后，如果成績(jī)合格，

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

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

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

14、或Update語(yǔ)句而要從表中刪除的所有行。Inserted表存放由于執(zhí)行Insert或Update語(yǔ)句而要向表中插入的所有行。deleted、inserted表在執(zhí)行觸發(fā)器時(shí)記錄的發(fā)生情況。Transact-SQL語(yǔ)句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ā)器對(duì)選課記錄進(jìn)行修改，如果成績(jī)不在對(duì)選課記錄進(jìn)行修改，如果成績(jī)不在0,100之間，之間，則不修改，輸出成績(jī)錯(cuò)誤，否則，修改。則不修改，輸出成績(jī)錯(cuò)誤，否則，修改。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