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

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

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

操作違背了完整性約束條件，則采取一定的操作來保證數據的完整性。為保證完整性，DBMS必須做到：1.完整性約束的定義機制2.完整性的檢查機制3.違約反應第五章數據庫完整性AnIntroductiontoDatabaseSystem4第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶定義完整性5.4完整性約束命名子句5.5觸發器AnIntroductiontoDatabaseSystem55.1實體完整性1.實體完整性定義在CREATETABLE語句中用PRIMARYKEY定義AnIntroductiontoDatabaseSystem65.1實體完整性[例5.1]在數據庫中，定義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＋索引優點：效率高AnIntroductiontoDatabaseSystem115.1實體完整性keyF1F2F33………25………20………15………93………79………65………84………71………51………7………30………41………54………65………69………68………25511230667837152025304151546568697176798493AnIntroductiontoDatabaseSystem12第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶定義完整性5.4完整性約束命名子句5.5觸發器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

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

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

定義參照完整性時，除了定義外碼，還應定義外碼是否級聯、是否為空。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置空?！獭痢碳壜搫h除：

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

受限刪除：

系統將拒絕執行此刪除操作。5.2參照完整性AnIntroductiontoDatabaseSystem20第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶定義完整性5.4完整性約束命名子句5.5觸發器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就檢查屬性上的約束條件是否被滿足，如果不滿足操作被拒絕執行。AnIntroductiontoDatabaseSystem295.3用戶定義的完整性3.元組上的約束條件的定義

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

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

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

AnIntroductiontoDatabaseSystem31第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶定義完整性5.4完整性約束命名子句5.5觸發器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第五章數據庫完整性5.1實體完整性5.2參照完整性5.3用戶定義完整性5.4完整性約束命名子句5.5觸發器AnIntroductiontoDatabaseSystem365.5觸發器1.觸發器

(1)什么是觸發器？

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

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

(2)觸發器的分類

After觸發器：（默認類型）

觸發Insert、Update、Delete動作之后執行。（表中數據已經變動之后）

Insteadof觸發器：

當執行Insert、Update、Delete操作時用觸發器中的操作來代替該操作。

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

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

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

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

AnIntroductiontoDatabaseSystem405.5觸發器3.建立觸發器的條件

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

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

inserted表：

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

inserted中的行是觸發器表中新行的副本。

deleted表：

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

deleted中的行是從觸發器表中刪除的行。

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

(2)這兩個表的特點：

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

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

insert操作

insert觸發器被觸發激活

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

delete操作

delete觸發器被觸發激活

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

update操作

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

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

ROLLBACK

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

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

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

AnIntroductiontoDatabaseSystem475.5觸發器[例5]建立觸發器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觸發器[例6]創建V1視圖，包括如下四個屬性，解決不能利用該視圖插入數據的問題。

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觸發器5.激活觸發器的過程

(1)執行insteadof觸發器

(2)執行SQL語句

(3)執行after觸發器6.刪除管理

droptrigger<觸發器名>on<表名>

AnIntroductiontoDatabaseSystem525.5觸發器7.觸發器和約束的比較

相同：觸發器和約束都可用于處理數據的輸入

不同：

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

(2)觸發器

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

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

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

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

CREATETRIGGERt_訂購

ON原材料FOR

。

AS

IF

<

)Insertinto訂購計劃VALUES(

，

)

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

frominserted)(select儲備量

frominserted)(select儲備量*

3

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

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

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

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

[例1]

創建學生年齡在18~60歲的規則。

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

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

[例2]

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

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

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

解除已綁定到S表Age屬性的規則。

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

使用DROPRULE命令刪除規則

DROPRULE{rule_name}[,...n]

[例5]

刪除ag