數(shù)據(jù)庫系統(tǒng)概論高等教育出版社chp5a

1、數(shù)據(jù)庫系統(tǒng)概論數(shù)據(jù)庫系統(tǒng)概論An Introduction to Database System第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 主要內(nèi)容：本章主要介紹RDBMS完整性的實(shí)現(xiàn)機(jī)制，包括完整性約束定義機(jī)制，完整性檢查機(jī)制和違背完整性約束條件時(shí)RDBMS應(yīng)采取的動作。 重點(diǎn)和難點(diǎn)：理解實(shí)體完整性和參照完整性的概念，以及DBMS是如何保證實(shí)體及參照完整性的。 數(shù)據(jù)庫的完整性：數(shù)據(jù)的正確性正確性和相容性相容性。 數(shù)據(jù)的完整性和安全性是兩個(gè)不同概念。 數(shù)據(jù)的完整性數(shù)據(jù)的完整性防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫中存在不正確的數(shù)據(jù)防范對象：不合語義的、不正確的數(shù)據(jù) 數(shù)據(jù)的安全性數(shù)據(jù)的安

2、全性保護(hù)數(shù)據(jù)庫防止惡意的破壞和非法的存取防范對象：非法用戶和非法操作為維護(hù)數(shù)據(jù)庫的完整性，DBMS必須：1.提供定義完整性約束條件的機(jī)制2.提供完整性檢查的方法3.違約處理5.1 實(shí)體完整性實(shí)體完整性5.2 參照完整性參照完整性5.3 用戶定義的完整性用戶定義的完整性5.4 完整性約束命名字句完整性約束命名字句5.6 觸發(fā)器觸發(fā)器5.7 小結(jié)小結(jié)5.1.1 實(shí)體完整性定義實(shí)體完整性定義5.1.2 實(shí)體完整性檢查和違約處理實(shí)體完整性檢查和違約處理關(guān)系模型的實(shí)體完整性CREATE TABLE中用PRIMARY KEY定義單屬性構(gòu)成的碼有兩種說明方法 定義為列級約束條件定義為表級約束條件對多個(gè)屬性構(gòu)

3、成的碼只有一種說明方法定義為表級約束條件 例1 將Student表中的Sno屬性定義為碼 (1)在列級定義主碼在列級定義主碼 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20);(2)在表級定義主碼在表級定義主碼 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(20) NOT NULL， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(2

4、0)， 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) /*只能在表級定義主碼*/ ); T增加主鍵約束的基本語法如下：ALTER TABLE table_nameWITH CHECK|WITH NOCHECK ADD CONSTRAINT constraint_namePRIMARY KEY CLUSTERED|NONCLUSTERED(column,n)W

5、ITH FILLFACTOR=filefactorONfilegroup|DEFAULT例：修改學(xué)生基本信息表STUDENT，為其添加主鍵約束。ALTER TABLE STUDENTADD CONSTRAINT PK_STPRIMARY KEY(SNO)GO5.1.1 實(shí)體完整性定義實(shí)體完整性定義5.1.2 實(shí)體完整性檢查和違約處理實(shí)體完整性檢查和違約處理 插入或?qū)χ鞔a列進(jìn)行更新操作時(shí)，RDBMS按照實(shí)體完整性規(guī)則自動進(jìn)行檢查。包括：1. 檢查主碼值是否唯一，如果不唯一則拒絕插入或修改2. 檢查主碼的各個(gè)屬性是否為空，只要有一個(gè)為空就拒絕插入或修改 檢查記錄中主碼值是否唯一的方法： 全表掃描

6、索引 5.2.1 參照完整性定義參照完整性定義5.2.2 參照完整性檢查和違約處理參照完整性檢查和違約處理關(guān)系模型的參照完整性定義 在CREATE TABLE中用FOREIGN KEY短語定義哪些列為外碼 用REFERENCES短語指明這些外碼參照哪些表的主碼 例如，關(guān)系SC中一個(gè)元組表示一個(gè)學(xué)生選修的某門課程的成績，（Sno，Cno）是主碼。Sno，Cno分別參照引用Student表的主碼和Course表的主碼 例3 定義SC中的參照完整性 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT，

7、 PRIMARY KEY (Sno， Cno)， /*在表級定義實(shí)體完整性*/ FOREIGN KEY (Sno) REFERENCES Student(Sno)， /*在表級定義參照完整性*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表級定義參照完整性*/ );T修改表結(jié)構(gòu)ALTER TABLE table_nameWITH CHECK|WITH NOCHECK ADD CONSTRAINT constraint_nameFROEIGN KEY (column,n)REFERENCES ref_table_name(ref_column,n)O

8、N DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTIONNOT FOR REPLICATIONT例：修改選課表SC，為其添加外鍵約束GOALTER TABLE SCADD CONSTRAINT FK_STFOREIGN KEY(SNO)REFERENCES STUDENT(SNO)GO5.2.1 參照完整性定義參照完整性定義5.2.2 參照完整性檢查和違約處理參照完整性檢查和違約處理可能破壞參照完整性的情況及違約處理被參照表（例如Student）參照表（例如SC）違約處理可能破壞參照完整性 插入元組拒絕可能破壞參照完整性 修改外碼值拒絕刪除元

9、組 可能破壞參照完整性拒絕/級連刪除/設(shè)置為空值修改主碼值 可能破壞參照完整性拒絕/級連修改/設(shè)置為空值參照完整性違約處理1. 拒絕(NO ACTION)執(zhí)行默認(rèn)策略2. 級聯(lián)(CASCADE)操作3. 設(shè)置為空值（SET-NULL）對于參照完整性，除了應(yīng)該定義外碼，還應(yīng)定義外碼列是否允許空值例4 顯式說明參照完整性的違約處理示例 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY（Sno，Cno）， FOREIGN KEY (Sno) REFERENCES Stude

10、nt(Sno) ON DELETE CASCADE /*級聯(lián)刪除SC表中相應(yīng)的元組*/ ON UPDATE CASCADE， /*級聯(lián)更新SC表中相應(yīng)的元組*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE NO ACTION /*當(dāng)刪除course 表中的元組造成了與SC表不一致時(shí)拒絕刪除*/ ON UPDATE CASCADE /*當(dāng)更新course表中的cno時(shí)，級聯(lián)更新SC表中相應(yīng)的元組*/ )；用戶定義的完整性就是針對某一具體應(yīng)用的數(shù)據(jù)必須滿足的語義要求 RDBMS提供，而不必由應(yīng)用程序承擔(dān)CREATE TABLE時(shí)定義 列值非

11、空（NOT NULL） 列值唯一（UNIQUE） 檢查列值是否滿足一個(gè)布爾表達(dá)式（CHECK） 1.不允許取空值 例5 在定義SC表時(shí)，說明Sno、Cno、Grade屬性不允許取空值。 CREATE TABLE SC （Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT NOT NULL， PRIMARY KEY (Sno， Cno)， /* 如果在表級定義實(shí)體完整性，隱含了Sno，Cno不允許取空值，則在列級不允許取空值的定義就不必寫了 * / ）； 2.列值唯一 例6 建立部門表DEPT，要求部門名稱Dname列取值唯一，部

12、門編號Deptno列為主碼 CREATE TABLE DEPT (Deptno NUMERIC(2)， Dname CHAR(9) UNIQUE，/*要求Dname列值唯一*/ Location CHAR(10)， PRIMARY KEY (Deptno) )；3. 用CHECK短語指定列值應(yīng)該滿足的條件例7 Student表的Ssex只允許取“男”或“女”。 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(8) NOT NULL， Ssex CHAR(2) CHECK (Ssex IN (男，女) ) ， /*性別屬性Sse

13、x只允許取男或女 */ Sage SMALLINT， Sdept CHAR(20) ); 插入元組或修改屬性的值時(shí)，RDBMS檢查屬性上的約束條件是否被滿足 如果不滿足則操作被拒絕執(zhí)行 在CREATE TABLE時(shí)可以用CHECK短語定義元組上的約束條件，即元組級的限制 同屬性值限制相比，元組級的限制可以設(shè)置不同屬性之間的取值的相互約束條件 例9 當(dāng)學(xué)生的性別是男時(shí)，其名字不能以Ms.打頭。 CREATE TABLE Student (Sno CHAR(9)， Sname CHAR(8) NOT NULL， Ssex CHAR(2)， Sage SMALLINT， Sdept CHAR(20)

14、， PRIMARY KEY (Sno)， CHECK (Ssex=女女 OR Sname NOT LIKE Ms.%) /*定義了元組中Sname和 Ssex兩個(gè)屬性值之間的約束條件*/ )； 性別是女性的元組都能通過該項(xiàng)檢查，因?yàn)镾sex=女成立； 當(dāng)性別是男性時(shí)，要通過檢查則名字一定不能以Ms.打頭 插入元組或修改屬性的值時(shí)，RDBMS檢查元組上的約束條件是否被滿足 如果不滿足則操作被拒絕執(zhí)行 CONSTRAINT 約束CONSTRAINT PRIMARY KEY短語 |FOREIGN KEY短語 |CHECK短語例10 建立學(xué)生登記表Student，要求學(xué)號在9000099999之間，姓

15、名不能取空值，年齡小于30，性別只能是“男”或“女”。 CREATE TABLE Student (Sno NUMERIC(6) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999)， Sname CHAR(20) CONSTRAINT C2 NOT NULL， Sage NUMERIC(3) CONSTRAINT C3 CHECK (Sage 30)， Ssex CHAR(2) CONSTRAINT C4 CHECK (Ssex IN ( 男，女)， CONSTRAINT StudentKey PRIMARY KEY(Sno) )；2. 修改表中

16、的完整性限制修改表中的完整性限制 使用ALTER TABLE語句修改表中的完整性限制例13 修改表Student中的約束條件，要求學(xué)號改為在900000999999之間，年齡由小于30改為小于40n可以先刪除原來的約束條件，再增加新的約束條件 ALTER TABLE Student DROP CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999)， ALTER TABLE Student DROP CONSTRAINT C3; ALTER TABLE Student A

17、DD CONSTRAINT C3 CHECK (Sage 40)；觸發(fā)器（Trigger）是一種由事件驅(qū)動的特殊的存儲過程，當(dāng)它被定義在表上時(shí)，可看作表的一部分，一旦定義，任何用戶當(dāng)試圖對表的增加、刪除或修改操作，都由服務(wù)器自動激活相應(yīng)的觸發(fā)器，即觸發(fā)器被請求（被觸發(fā)）。在DBMS核心層進(jìn)行集中的完整性控制。觸發(fā)器的作用類似約束，但比約束更加靈活，可以實(shí)施比外鍵約束（FORGIGN KEY）、檢查約束（CHECK）更為復(fù)雜的操作，具有更為精細(xì)和強(qiáng)大的控制能力。觸發(fā)器并不是SQL92標(biāo)準(zhǔn)中SQL規(guī)范的內(nèi)容，但很多關(guān)系數(shù)據(jù)庫管理系統(tǒng)早就支持觸發(fā)器，而不同的關(guān)系數(shù)據(jù)庫管理系統(tǒng)具體實(shí)現(xiàn)的語法各不相同。

18、觸發(fā)器基于表而建立時(shí)，它是一個(gè)高級形式的規(guī)則，常用于執(zhí)行更為復(fù)雜的數(shù)據(jù)約束。以防止對數(shù)據(jù)進(jìn)行不正確的、沒有授權(quán)或不一致的修改。當(dāng)對某一表進(jìn)行諸如UPDATE、 INSERT、DELETE 這些操作時(shí)，SQL Server 就會自動執(zhí)行觸發(fā)器所定義的SQL 語句，從而確保對數(shù)據(jù)的處理必須符合由這些SQL 語句所定義的規(guī)則。觸發(fā)器的主要作用就是：能夠?qū)崿F(xiàn)由主鍵、外鍵以及各種常規(guī)數(shù)據(jù)約束所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性。所以，觸發(fā)器是一種確保數(shù)據(jù)和業(yè)務(wù)完整性的較好方法。觸發(fā)器不同于存儲過程，觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行，而存儲過程則是通過存儲過程名而被直接調(diào)用。即觸發(fā)器不能直接調(diào)用

19、，也不能有參數(shù)。 SQL Server 2005提供兩大類觸發(fā)器：DML觸發(fā)器和DDL觸發(fā)器。其中DML觸發(fā)器包括兩種類型：AFTER觸發(fā)器和INSTEAD OF觸發(fā)器。DML觸發(fā)器是指當(dāng)數(shù)據(jù)庫服務(wù)器發(fā)生數(shù)據(jù)操作語言（DML）事件時(shí)所要執(zhí)行的操作。其用于在數(shù)據(jù)被修改時(shí)，強(qiáng)制執(zhí)行業(yè)務(wù)規(guī)則，以及擴(kuò)展SQL Server 2005數(shù)據(jù)庫約束。DDL觸發(fā)器是SQL Server 2005新增的功能，用于在數(shù)據(jù)庫中執(zhí)行管理任務(wù)。它是一種特殊的觸發(fā)器，在響應(yīng)數(shù)據(jù)定義語言（DDL）語句時(shí)被觸發(fā)。它主要應(yīng)用于數(shù)據(jù)審計(jì)等工作，不屬于數(shù)據(jù)庫基礎(chǔ)使用范圍。TAFTER觸發(fā)器 這種類型的觸發(fā)器是指觸發(fā)器只有在觸發(fā)SQ

20、L語句中指定的所有操作都已成功執(zhí)行后才激發(fā)。所有的引用級聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器。換言之，AFTER觸發(fā)器的行為是在數(shù)據(jù)修改觸發(fā)器的行為是在數(shù)據(jù)修改發(fā)生之后執(zhí)行。發(fā)生之后執(zhí)行。 該類型觸發(fā)器要求只有執(zhí)行某一操作如：INSERT、UPDATE、DELETE之后觸發(fā)器才被觸發(fā)，且只能在表上定義。可以為針對表的同一操作定義多個(gè)觸發(fā)器可以為針對表的同一操作定義多個(gè)觸發(fā)器。TINSTEAD OF觸發(fā)器 從SQL Server 2000就引入了INSTEAD OF觸發(fā)器。這種觸發(fā)器擴(kuò)展了SQL Server的觸發(fā)器功能，這類觸發(fā)器并不是執(zhí)行激發(fā)其動作的SQL語句所定義的操作，如I

21、NSERT、UPDATE、DELETE。而是執(zhí)行觸發(fā)器本身的代碼以替代其觸發(fā)操作。換言之，INSTEAD OF觸發(fā)器可以越過觸發(fā)操作語句而優(yōu)先執(zhí)行觸發(fā)器觸發(fā)器可以越過觸發(fā)操作語句而優(yōu)先執(zhí)行觸發(fā)器。 使用者既可在表上定義INSTEAD OF 觸發(fā)器，也可以在視圖上定義INSTEAD OF 觸發(fā)器。但對同一操作只但對同一操作只能定義一個(gè)能定義一個(gè)INSTEAD OF 觸發(fā)器觸發(fā)器。 使用SQL Server Management Studio創(chuàng)建觸發(fā)器 使用T-SQL語句創(chuàng)建觸發(fā)器1.使用SQL Server Management Studio創(chuàng)建觸發(fā)器 在SQL Server Managemen

22、t Studio的對象資源管理器中，展開指定的服務(wù)器、數(shù)據(jù)庫和要在其上創(chuàng)建觸發(fā)器的那個(gè)表，右擊該表所屬的觸發(fā)器文件夾，從彈出的快捷菜單中選擇“新建觸發(fā)器”選項(xiàng)，則在右側(cè)查詢分析器窗口會出現(xiàn)觸發(fā)器定義文本框架。 在該框架中合適位置填入觸發(fā)器名稱、創(chuàng)建觸發(fā)器的SQL語句即完成了創(chuàng)建工作。2.使用T-SQL語句創(chuàng)建觸發(fā)器 CREATE TRIGGER語句必須是批處理語句中的第一條語句CREATE TRIGGER schema_name.trigger_nameON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDAT

23、E , DELETE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n sql_statement .n 注意：在觸發(fā)器中不能使用的注意：在觸發(fā)器中不能使用的SQL命令有：命令有： ALTER DATABASE CREATE DATABASE DROP DATABASE LOAD DATABASE/LOAD LOG RECONFIGURE RESTORE DATABASE/RESTORE LOG IF UPDATE (column)可測試在指定的列上進(jìn)行的INSERT或UPDATE操作，可以指定多列。因?yàn)樵贠N子句中指定了表名，所以在IF UPDATE子句中的列名前不要包含表名。u針對插入操作事件的觸發(fā)器應(yīng)用u針對更新操作的觸發(fā)器應(yīng)用uinstead of型觸發(fā)器的應(yīng)用例：向teacher表插入記錄，若職稱為講師，工資低于3500的將工資調(diào)整為3500例：如修改了sc表的成績字段時(shí)，給予提示。 列級更新操作的觸發(fā)器 在有些