數(shù)據庫系統(tǒng)第05章 數(shù)據庫完整性

AnIntroductiontoDatabaseSystem1數(shù)據庫系統(tǒng)概論AnIntroductiontoDatabaseSystem第五章數(shù)據庫完整性AnIntroductiontoDatabaseSystem2第五章數(shù)據庫完整性什么是數(shù)據庫的完整性?數(shù)據的正確性和相容性防止不合語義的、不正確的數(shù)據進入數(shù)據庫。例:學生的年齡必須是整數(shù)，取值在6-50間；學生的性別只能是男或女；學生的學號一定是唯一的；學生所在的系必須是學校已有的系；完整性：保證數(shù)據庫能否真實地反映現(xiàn)實世界.區(qū)別數(shù)據完整性和安全性的兩個概念。AnIntroductiontoDatabaseSystem3

完整性約束條件是數(shù)據模型的組成部分，約束數(shù)據庫中數(shù)據的語義。

檢查用戶發(fā)出的操作請求是否違背了完整性約束條件。

操作違背了完整性約束條件，則采取一定的操作來保證數(shù)據的完整性。為保證完整性，DBMS必須做到：1.完整性約束的定義機制2.完整性的檢查機制3.違約反應第五章數(shù)據庫完整性AnIntroductiontoDatabaseSystem4第五章數(shù)據庫完整性5.1實體完整性5.2參照完整性5.3用戶定義完整性5.4完整性約束命名子句5.5觸發(fā)器AnIntroductiontoDatabaseSystem55.1實體完整性1.實體完整性定義在CREATETABLE語句中用PRIMARYKEY定義AnIntroductiontoDatabaseSystem65.1實體完整性[例5.1]在數(shù)據庫中，定義Student表的Sno屬性為主碼。定義在列級約束：

CREATETABLEStudent(SnoCHAR(8),SnameCHAR(8),SageINT);

定義在表級約束：

CREATETABLEStudent(SnoCHAR(8)，

SnameCHAR(8),SageINT，

);PRIMARYKEY,PRIMARYKEY(Sno));AnIntroductiontoDatabaseSystem75.1實體完整性[例5.2]在SC表中定義(Sno,Cno)為主碼。

CREATETABLESC(SnoCHAR(5),CnoCHAR(2),GradeSMALLINT,

PRIMARYKEY(Sno,Cno));AnIntroductiontoDatabaseSystem85.1實體完整性1.實體完整性定義在CREATETABLE語句中用PRIMARYKEY定義

碼由單字段構成：

定義可以為列級或表級約束；

碼由多字段構成：

定義只能為表級約束；AnIntroductiontoDatabaseSystem95.1實體完整性2.實體完整性檢查和違約處理

(1)完整性檢查

當用戶插入記錄或修改主碼時，DBMS自動檢查：每個主屬性值是否為空；主碼值是否惟一。

(2)違約處理拒絕操作。AnIntroductiontoDatabaseSystem105.1實體完整性2.實體完整性檢查和違約處理

(3)檢查主碼是否惟一的方法

全表掃描判斷新的主碼值在表中是否已存在。缺點：效率低利用主碼的B＋索引優(yōu)點：效率高AnIntroductiontoDatabaseSystem115.1實體完整性keyF1F2F33………25………20………15………93………79………65………84………71………51………7………30………41………54………65………69………68………25511230667837152025304151546568697176798493AnIntroductiontoDatabaseSystem12第五章數(shù)據庫完整性5.1實體完整性5.2參照完整性5.3用戶定義完整性5.4完整性約束命名子句5.5觸發(fā)器AnIntroductiontoDatabaseSystem135.2參照完整性1.參照完整性定義

在CREATETABLE語句中：

用FOREIGNKEY短語定義外碼列；用REFERENCES短語指明外碼參照哪個表的主碼。AnIntroductiontoDatabaseSystem145.2參照完整性[例5.3]在SC表中的參照完整性。

CREATETABLESC(SnoCHAR(8)NOTNULL,CnoCHAR(3)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),

FOREIGNKEY(Cno)REFERENCEScourse(Cno)

);REFERENCESstudent(Sno),AnIntroductiontoDatabaseSystem155.2參照完整性[例]在SC表中的參照完整性。

ALTERTABLESC

ADDFOREIGNKEY(SNO)REFERENCESSTUDENT(SNO)AnIntroductiontoDatabaseSystem162.參照完整性檢查和違約處理對被參照表和參照表進行增、刪、改操作時進行檢查有四種情況可能破壞參照完整性：參照表中插入新元組；修改外碼值；被參照表中刪除元組；修改主碼值。5.2參照完整性AnIntroductiontoDatabaseSystem17

當參照完整性不一致時，系統(tǒng)采取以下策略：拒絕執(zhí)行（默認策略）

級聯(lián)操作（CASCADE）當刪除或修改被參照表的一個元組造成與參照表不一致時，則刪除或修改參照表中所有不一致的元組。設置為空（SET-NULL）當刪除或修改被參照表的一個元組造成與參照表不一致時，則將參照表中所有造成不一致的元組對應屬性置空。5.2參照完整性

定義參照完整性時，除了定義外碼，還應定義外碼是否級聯(lián)、是否為空。AnIntroductiontoDatabaseSystem185.2參照完整性[例5.4]在SC表中的參照完整性。

CREATETABLESC(SnoCHAR(8)NOTNULL,CnoCHAR(3)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno),

FOREIGNKEY(Cno)REFERENCEScourse(Cno)

ONDELETECASCADEONUPDATECASCADE);REFERENCESstudent(Sno),AnIntroductiontoDatabaseSystem19例：要刪除Student關系中Sno=2012215121的元組，而SC關系中有5個元組的Sno都等于201215121。置空值刪除：

將SC關系中所有Sno=201215121的元組Sno置空?！獭痢碳壜?lián)刪除：

將SC關系中所有5個Sno=201215121的元組一起刪除。依此類推可以繼續(xù)級聯(lián)下去。

受限刪除：

系統(tǒng)將拒絕執(zhí)行此刪除操作。5.2參照完整性AnIntroductiontoDatabaseSystem20第五章數(shù)據庫完整性5.1實體完整性5.2參照完整性5.3用戶定義完整性5.4完整性約束命名子句5.5觸發(fā)器AnIntroductiontoDatabaseSystem215.3用戶定義的完整性1.屬性上約束條件的定義

使用CREATETABLE語句在定義屬性的同時，定義對屬性值的限制。

包括：列值非空(NOTNULL)

列值唯一(UNIQUE)

檢查列值是否滿足一個布爾表達式(CHECK子句)。AnIntroductiontoDatabaseSystem225.3用戶定義的完整性(1)不允許取空值[例5.5]在定義SC表時，說明Sno、Cno屬性不取空

CREATETABLESC(SnoCHAR(5)NOTNULL,CnoCHAR(2)NOTNULL,GradeSMALLINT,PRIMARYKEY(Sno,Cno));AnIntroductiontoDatabaseSystem235.3用戶定義的完整性(2)列值惟一[例5.6]建立部門表Dept，要求部門名稱Dname惟一，部門編號為主碼。

CREATETABLEDept(DnoNUMERIC(2)NOTNULL,DnameCHAR(20)UNIQUE,LocationCHAR(10),PRIMARYKEY(Dno));AnIntroductiontoDatabaseSystem245.3用戶定義的完整性(2)列值惟一[例]若部門表Dept已建立，添加部門名稱Dname惟一的約束。

ALTERTABLEDeptADDUNIQUE(Dname);AnIntroductiontoDatabaseSystem255.3用戶定義的完整性[例5.7]Student表中Ssex只允許取“男”和“女”。CREATETABLEStudent(SnoCHAR(5)PRIMARYKEY,SnameCHAR(8)NOTNULL,SsexCHAR(2)CHECK(SsexIN(‘男’,’女’)),SageSMALLINT,SdeptCHAR(20));(3)用CHECK短語指定列值應該滿足的條件AnIntroductiontoDatabaseSystem265.3用戶定義的完整性[例]向Student表添加Ssex只許取“男”和“女”的約束。ALTERTABLEStudentADDCHECK(SsexIN(‘男’,’女’));(3)用CHECK短語指定列值應該滿足的條件AnIntroductiontoDatabaseSystem275.3用戶定義的完整性[例5.8]在定義SC表時，說明Grade應該在0和100之間。

CREATETABLESC(SnoCHAR(5)NOTNULL,CnoCHAR(2)NOTNULL,GradeSMALLINTCHECK(Grade>=0ANDGrade<=100),PRIMARYKEY(Sno,Cno)

……

);

(3)用CHECK短語指定列值應該滿足的條件AnIntroductiontoDatabaseSystem285.3用戶定義的完整性2.屬性上約束條件檢查和違約處理

當往表中插入元組或修改屬性的值時，RDBMS就檢查屬性上的約束條件是否被滿足，如果不滿足操作被拒絕執(zhí)行。AnIntroductiontoDatabaseSystem295.3用戶定義的完整性3.元組上的約束條件的定義

在CREATETABLE語句可以用CHECK短語定義元組上的約束條件，即元組級限制元組級限制可以設置不同屬性之間的取值的相互約束條件。AnIntroductiontoDatabaseSystem305.3用戶定義的完整性3.元組上的約束條件的定義

在CREATETABLE語句可以用CHECK短語定義元組上的約束條件，即元組級限制元組級限制可以設置不同屬性之間的取值的相互約束條件。4.元組上的約束條件檢查和違約處理

例：當學生的性別是男時，姓名中不能包含“花”

AnIntroductiontoDatabaseSystem31第五章數(shù)據庫完整性5.1實體完整性5.2參照完整性5.3用戶定義完整性5.4完整性約束命名子句5.5觸發(fā)器AnIntroductiontoDatabaseSystem325.4完整性約束命名子句

SQL在提供了完整性約束命名子句CONSTRAINT，用來對完整性約束條件進行命名。1.完整性約束命名子句格式：

CONSTRAINT<約束命名>[PRIMARYKEY短語|FOREIGNKEY短語|CHECK短語]

放置在任何一個約束短語的前面。AnIntroductiontoDatabaseSystem33[例5.10]建立學生表student，姓名不能取空值，年齡小于30，性別只能取“男”或“女”。

CREATETABLEStudent

(SnoNUMERIC(6)

SnameCHAR(8)

NOTNULL,

SsexCHAR(2)CHECK(SsexIN(‘男’,’女’)),

SageINTCHECK(Sage<30),PRIMARYKEY(Sno));

CONSTRAINTC1

CONSTRAINT

C2

CONSTRAINTC3

CONSTRAINT

StuKey

AnIntroductiontoDatabaseSystem345.4完整性約束命名子句

2.修改表中的完整性限制

(1)刪除完整性限制格式：ALTERTABLEStudent

DROP[CONSTRAINT]<約束命名>[例5.12]去掉[例10]student表中對性別的限制。

ALTERTABLEStudentDROP[CONSTRAINT]C3;(2)修改完整性限制

先刪除，再添加。AnIntroductiontoDatabaseSystem35第五章數(shù)據庫完整性5.1實體完整性5.2參照完整性5.3用戶定義完整性5.4完整性約束命名子句5.5觸發(fā)器AnIntroductiontoDatabaseSystem365.5觸發(fā)器1.觸發(fā)器

(1)什么是觸發(fā)器？

它是定義在表上的一類由事件驅動的特殊過程作用：

用來實現(xiàn)當使用update、insert、delete對表進行操作時，自動觸發(fā)的處理方法；實現(xiàn)復雜的業(yè)務要求，類似于約束保證數(shù)據的一致性。AnIntroductiontoDatabaseSystem375.5觸發(fā)器1.觸發(fā)器

(2)觸發(fā)器的分類

After觸發(fā)器：（默認類型）

觸發(fā)Insert、Update、Delete動作之后執(zhí)行。（表中數(shù)據已經變動之后）

Insteadof觸發(fā)器：

當執(zhí)行Insert、Update、Delete操作時用觸發(fā)器中的操作來代替該操作。

(表中數(shù)據改動之前)AnIntroductiontoDatabaseSystem385.5觸發(fā)器2.定義觸發(fā)器格式：(P169)

CreateTrigger<觸發(fā)器名>on<對象名>{for|after|insteadof}{[delete],[insert],[update]}

as<觸發(fā)器的條件和操作>觸發(fā)器的類型觸發(fā)事件AnIntroductiontoDatabaseSystem395.5觸發(fā)器[例1]創(chuàng)建一個向學生表添加新記錄時顯示歡迎信息的觸發(fā)器。

CreateTriggerT_InOnstudentafterinsertasprint‘歡迎新同學的到來！'

AnIntroductiontoDatabaseSystem405.5觸發(fā)器3.建立觸發(fā)器的條件

CREATETRIGGER語句必須是批處理語句中的第一條；只有表的擁有者可以創(chuàng)建觸發(fā)器；一個表只能創(chuàng)建一定數(shù)量的觸發(fā)器；在同一模式下，觸發(fā)器名必須惟一；當觸發(fā)器被激活時，只有觸發(fā)條件為真時觸發(fā)體才被執(zhí)行。AnIntroductiontoDatabaseSystem415.5觸發(fā)器4.觸發(fā)器工作原理

(1)觸發(fā)器工作時建立兩個表：

inserted表：

存儲insert和update語句所影響的數(shù)據行副本

inserted中的行是觸發(fā)器表中新行的副本。

deleted表：

存儲delete和update語句所影響的數(shù)據行副本

deleted中的行是從觸發(fā)器表中刪除的行。

觸發(fā)器作用的表和deleted表沒有相同的行AnIntroductiontoDatabaseSystem425.5觸發(fā)器4.觸發(fā)器工作原理

(2)這兩個表的特點：

系統(tǒng)自動維護對用戶而言，是只讀的表結構與觸發(fā)器作用的表結構相同與觸發(fā)器同時存在（觸發(fā)器運行結束，系統(tǒng)自動刪除這兩表）AnIntroductiontoDatabaseSystem435.5觸發(fā)器4.觸發(fā)器工作原理

(3)各種操作時觸發(fā)器的工作：

insert操作

insert觸發(fā)器被觸發(fā)激活

新記錄會添加到基本表和inserted表。

delete操作

delete觸發(fā)器被觸發(fā)激活

被刪除的記錄會存放到deleted表中。

update操作

相當于刪除一條舊記錄,插入一條新紀錄。AnIntroductiontoDatabaseSystem445.5觸發(fā)器[例2]創(chuàng)建一個限制年齡不能小于10的觸發(fā)器

CREATETRIGGERt_ageonStudentFORInsert,UpdateASIF()<10BEGINPRINT'年齡不能小于10，操作失敗'

ROLLBACK

END;selectsagefrominsertedAnIntroductiontoDatabaseSystem455.5觸發(fā)器[例3]為student表建立一個觸發(fā)器t_del，禁止刪除學號為201215122的學生createtriggert_delonstudentforupdate,deleteasif((selectsnofromdeleted)=‘201215122')

beginprint'不允許刪除該學生，操作失??！'rollbackendAnIntroductiontoDatabaseSystem465.5觸發(fā)器[例4]為student表建立一個觸發(fā)器t_sno，要求在更新操作中不能修改學號。

CREATETRIGGERt3ONStudentFORUpdateASIFUpdate(sno)BEGINPRINT'學號不允許修改！'ROLLBACKEND

AnIntroductiontoDatabaseSystem475.5觸發(fā)器[例5]建立觸發(fā)器t_sc，要求修改SC表的成績時，同時將記錄插入的old_sc。createtableold_sc(snovarchar(12),cnochar(4),gradeint,unamevarchar(10),udatedatetimedefaultgetdate())AnIntroductiontoDatabaseSystem48CREATETRIGGERupdate_scONscFORUPDATEAS

ifupdate(grade)

begin

insertintoold_sc(sno,cno,grade)

select*fromdeleted

updateold_sc

setuname=user

wheresno=(selectsnofromdeleted)

andcno=(selectcnofromdeleted)

endAnIntroductiontoDatabaseSystem495.5觸發(fā)器[例6]創(chuàng)建V1視圖，包括如下四個屬性，解決不能利用該視圖插入數(shù)據的問題。

createviewv1

as

selectStudent.sno,sname,cno,grade

fromstudent,sc

wherestudent.sno=sc.snoAnIntroductiontoDatabaseSystem50createtriggert_ins_v1onv1insteadofINSERTasbeginINSERTINTOStudent(sno,sname)

SELECTsno,snamefrominserted

INSERTINTOSC

SELECTsno,cno,gradefrominserted

endAnIntroductiontoDatabaseSystem515.5觸發(fā)器5.激活觸發(fā)器的過程

(1)執(zhí)行insteadof觸發(fā)器

(2)執(zhí)行SQL語句

(3)執(zhí)行after觸發(fā)器6.刪除管理

droptrigger<觸發(fā)器名>on<表名>

AnIntroductiontoDatabaseSystem525.5觸發(fā)器7.觸發(fā)器和約束的比較

相同：觸發(fā)器和約束都可用于處理數(shù)據的輸入

不同：

(1)約束：表中所有的數(shù)據均滿足

(2)觸發(fā)器

創(chuàng)建觸發(fā)器前表中的數(shù)據可不滿足實施瞬時約束，即數(shù)據改變時約束AnIntroductiontoDatabaseSystem53分析該觸發(fā)器的作用createtriggert5onscforinsert,updateasifupdate(grade)updatestudentsetssum=(selectsum(ccredit)oin(ofromsc,insertedwheresc.sno=inserted.snoandsc.grade>=60))wheresno=(selectsnofrominserted)

某工廠的倉庫管理數(shù)據庫的部分關系模式如下：倉庫(倉庫號,面積,負責人,電話)原材料(編號,名稱,數(shù)量,儲備量,倉庫號)

訂購計劃(原材料編號,訂購數(shù)量)要求觸發(fā)器實現(xiàn)：當倉庫中的任一原材料的數(shù)量小于其儲備量時，向訂購計劃表中插入該原材料的訂購計劃，其訂購量為儲備量的3倍練習倉庫(倉庫號,面積,負責人,電話)原材料(編號,名稱,數(shù)量,儲備量,倉庫號)

訂購計劃(原材料編號,訂購數(shù)量)

CREATETRIGGERt_訂購

ON原材料FOR

。

AS

IF

<

)Insertinto訂購計劃VALUES(

，

)

Insert,Update(select編號frominserted)(select數(shù)量

frominserted)(select儲備量

frominserted)(select儲備量*

3

frominserted)CREATETRIGGERt_訂購ONscFORInsert,UpdateASIF(select儲備量frominserted)<(select數(shù)量frominserted)begin

ifexists(select原材料編號from訂購計劃

where原材料編號=(select編號frominserted))print‘已存在該材料的訂購計劃！'elseInsertinto訂購計劃

VALUES((select編號frominserted),(select儲備量*3frominserted))end避免重復添加訂購計劃的觸發(fā)器AnIntroductiontoDatabaseSystem57補充—規(guī)則規(guī)則（Rule）是數(shù)據庫對存儲在表中的列或用戶自定義數(shù)據類型的取值規(guī)定和限制。特點①規(guī)則與其作用的對象是相互獨立的，即對象的刪除、修改不會對與之相連的規(guī)則產生影響；②規(guī)則與CHECK類似，與約束可同時使用；③一列可以有一個規(guī)則及多個約束；④規(guī)則可作用于自定義數(shù)據類型,但約束不能。AnIntroductiontoDatabaseSystem58補充—規(guī)則1.創(chuàng)建規(guī)則CREATERULE規(guī)則名AS約束條件

[例1]

創(chuàng)建學生年齡在18~60歲的規(guī)則。

CREATERULEage_ruleAS@age>=18and@age<=60局部變量Where中的任何表達式AnIntroductiontoDatabaseSystem59補充—規(guī)則2.規(guī)則的綁定與松綁若要規(guī)則發(fā)生作用，需將其與數(shù)據庫表或用戶自定義對象聯(lián)系起來。綁定：指定規(guī)則作用于哪個表的哪一列或哪個用戶自定義數(shù)據類型。松綁：解除規(guī)則與對象的綁定。AnIntroductiontoDatabaseSystem60補充2.規(guī)則的綁定與松綁(1)用存儲過程sp_bindrule綁定規(guī)則

sp_bindrule[@rulename=]規(guī)則名,[@objname=]對象名[,‘futureonly’]

[例2]

綁定規(guī)則age_rule到Student表Sage屬性EXECsp_bindruleage_rule,'Student.Sage'只在自定義數(shù)據類型時用AnIntroductiontoDatabaseSystem61補充[例3]

創(chuàng)建一個規(guī)則，并綁定到C表的CNo列，用于限定CNo的輸入范圍。CREATERULECno_ruleAS@rangelike'[1-5][0-9][0-9]'EXECsp_bindrule'Cno_rule',C.CNoAnIntroductiontoDatabaseSystem62(2)用存儲過程sp_unbindrule解除規(guī)則的綁定

sp_unbindrule[@objname=]'object_name' [,'futureonly'][例4]

解除已綁定到S表Age屬性的規(guī)則。

EXECsp_unbindrule'S.Age'補充2.規(guī)則的綁定與松綁AnIntroductiontoDatabaseSystem63提醒：在刪除一個規(guī)則前必須先將與其綁定的對象松綁。3.刪除規(guī)則

使用DROPRULE命令刪除規(guī)則

DROPRULE{rule_name}[,...n]

[例5]

刪除ag