第05章數(shù)據(jù)庫完整性new

1、第05章數(shù)據(jù)庫完整性new 1 第05章數(shù)據(jù)庫完整性new 2 第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 n數(shù)據(jù)的正確性和相容性數(shù)據(jù)的正確性和相容性 n防止不合語義的、不正確的數(shù)據(jù)進入數(shù)據(jù)庫。防止不合語義的、不正確的數(shù)據(jù)進入數(shù)據(jù)庫。 例例: :學生的年齡必須是整數(shù)，取值在學生的年齡必須是整數(shù)，取值在6-506-50間；間； 學生的性別只能是男或女；學生的性別只能是男或女； 學生的學號一定是唯一的；學生的學號一定是唯一的； 學生所在的系必須是學校已有的系；學生所在的系必須是學校已有的系； n完整性：保證數(shù)據(jù)庫能否真實地反映現(xiàn)實世界完整性：保證數(shù)據(jù)庫能否真實地反映現(xiàn)實世界. 第05章數(shù)據(jù)庫完整性ne

2、w 3 完整性約束條件是數(shù)據(jù)模型的組成部分，完整性約束條件是數(shù)據(jù)模型的組成部分， 約束數(shù)據(jù)庫中數(shù)據(jù)的語義。約束數(shù)據(jù)庫中數(shù)據(jù)的語義。 檢查用戶發(fā)出的操作請求是否違背了完整檢查用戶發(fā)出的操作請求是否違背了完整 性約束條件性約束條件 1.1.完整性約束條件定義機制完整性約束條件定義機制 2.2.完整性檢查機制完整性檢查機制 3.3.違約反應違約反應 第第五五章章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 第05章數(shù)據(jù)庫完整性new 4 第第五五章章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 5.1 5.1 實體完整性實體完整性 5.2 5.2 參照完整性參照完整性 5.3 5.3 用戶定義完整性用戶定義完整性 5.4 5.4 完整

3、性約束命名子句完整性約束命名子句 5.5 5.5 觸發(fā)器觸發(fā)器 第05章數(shù)據(jù)庫完整性new 5 5.1 5.1 實體完整性實體完整性 在在CREATE TABLECREATE TABLE語句中用語句中用PRIMARY KEYPRIMARY KEY定義定義 碼由單字段構(gòu)成碼由單字段構(gòu)成： 定義可以為列級或表級約束；定義可以為列級或表級約束； 碼由多字段構(gòu)成：碼由多字段構(gòu)成： 定義只能為表級約束；定義只能為表級約束； 第05章數(shù)據(jù)庫完整性new 6 5.1 5.1 實體完整性實體完整性 例例11在數(shù)據(jù)庫中，定義在數(shù)據(jù)庫中，定義StudentStudent表的表的SnoSno屬性為主碼。屬性為主碼。

4、 定義在列級約束定義在列級約束： CREATE TABLE Student CREATE TABLE Student (Sno CHAR(8) , (Sno CHAR(8) , Sname CHAR(8), Sname CHAR(8), Sage INT); Sage INT); 定義在表級約束：定義在表級約束： CREATE TABLE Student CREATE TABLE Student (Sno CHAR(8) (Sno CHAR(8)， Sname CHAR(8),Sname CHAR(8), Sage INT Sage INT， );); PRIMARY KEY, PRIMARY

5、 KEY(Sno);); 第05章數(shù)據(jù)庫完整性new 7 5.1 5.1 實體完整性實體完整性 例例22在在SCSC表中定義表中定義(Sno,Cno)(Sno,Cno)為主碼。為主碼。 CREATE TABLE SC CREATE TABLE SC ( Sno CHAR(5), ( Sno CHAR(5), Cno CHAR(2), Cno CHAR(2), Grade SMALLINT, Grade SMALLINT, PRIMARY KEY (Sno, Cno) PRIMARY KEY (Sno, Cno) ); ); 第05章數(shù)據(jù)庫完整性new 8 5.1 5.1 實體完整性實體完整性

6、(1)(1)完整性檢查完整性檢查 當用戶進行插入記錄或修改主碼時，當用戶進行插入記錄或修改主碼時，DBMSDBMS系統(tǒng)自系統(tǒng)自 動檢查：動檢查： 檢查主碼值是否惟一；檢查主碼值是否惟一； 檢查每個主屬性值是否為空。檢查每個主屬性值是否為空。 (2)(2)違約處理違約處理 拒絕插入或修改操作。拒絕插入或修改操作。 第05章數(shù)據(jù)庫完整性new 9 5.1 5.1 實體完整性實體完整性 (3)(3)檢查主碼是否惟一的方法檢查主碼是否惟一的方法 全表掃描全表掃描 依次判斷表中每一個記錄的主碼值與插入記依次判斷表中每一個記錄的主碼值與插入記 錄的主碼值是否一致。錄的主碼值是否一致。 缺點：效率低缺點：效

7、率低 利用主碼的利用主碼的B B索引索引 優(yōu)點：效率高優(yōu)點：效率高 第05章數(shù)據(jù)庫完整性new 10 5.1 5.1 實體完整性實體完整性 keyF1F2F3 3 25 20 15 93 79 65 84 71 51 7 30 41 54 65 69 68 25 51 12 3066 78 3 715 20 25 30 41 51 54 6568 69 71 76 79 84 93 第05章數(shù)據(jù)庫完整性new 11 第第五五章章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 5.1 5.1 實體完整性實體完整性 5.2 5.2 參照完整性參照完整性 5.3 5.3 用戶定義完整性用戶定義完整性 5.4 5.4 完

8、整性約束命名子句完整性約束命名子句 5.5 5.5 觸發(fā)器觸發(fā)器 第05章數(shù)據(jù)庫完整性new 12 5.2 5.2 參照完整性參照完整性 在在CREATE TABLECREATE TABLE語句中：語句中： 用用 FOREIGN KEY FOREIGN KEY 短語定義哪些列為外碼；短語定義哪些列為外碼； 用用 REFERENCES REFERENCES 短語指明這些外碼參照哪個短語指明這些外碼參照哪個 表的主碼。表的主碼。 第05章數(shù)據(jù)庫完整性new 13 5.2 5.2 參照完整性參照完整性 例例33在在SCSC表中的參照完整性。表中的參照完整性。 第05章數(shù)據(jù)庫完整性new 14 5.2

9、 5.2 參照完整性參照完整性 例例33在在SCSC表中的參照完整性。表中的參照完整性。 第05章數(shù)據(jù)庫完整性new 15 對被對被參照表和參照表進行增、刪、改操作參照表和參照表進行增、刪、改操作 時進行檢查時進行檢查 有四種情況可能破壞參照完整性：有四種情況可能破壞參照完整性： 參照表中參照表中 l插入一個新元組；插入一個新元組； l修改外碼值；修改外碼值； 被參照表中被參照表中 l刪除一個元組；刪除一個元組； l修改主碼值。修改主碼值。 5.2 5.2 參照完整性參照完整性 第05章數(shù)據(jù)庫完整性new 16 當參照完整性不一致時，系統(tǒng)采取以下策略：當參照完整性不一致時，系統(tǒng)采取以下策略：

10、n拒絕執(zhí)行（默認策略）拒絕執(zhí)行（默認策略） n級聯(lián)操作（級聯(lián)操作（CASCADE） 當刪除或修改被參照表的一個元組造成與當刪除或修改被參照表的一個元組造成與 參照表不一致時，則刪除或修改參照表中所有參照表不一致時，則刪除或修改參照表中所有 不一致的元組。不一致的元組。 n設置為空（設置為空（SET-NULL） 當刪除或修改被參照表的一個元組造成與當刪除或修改被參照表的一個元組造成與 參照表不一致時，則將參照表中所有造成不一參照表不一致時，則將參照表中所有造成不一 致的元組對應屬性置空。致的元組對應屬性置空。 5.2 5.2 參照完整性參照完整性 定義參照完整性時，除了定義外碼，還應定義參照完整

11、性時，除了定義外碼，還應 定義外碼是否允許為空。定義外碼是否允許為空。 第05章數(shù)據(jù)庫完整性new 17 例：要刪除例：要刪除StudentStudent關(guān)系中關(guān)系中Sno=950001Sno=950001的元組，而的元組，而SCSC 關(guān)系中有關(guān)系中有4 4個元組的個元組的SnoSno都等于都等于950001950001。 n置空值刪除置空值刪除： 將將SCSC關(guān)系中所有關(guān)系中所有Sno=950001Sno=950001的元組的元組SnoSno置空。置空。 n級聯(lián)刪除級聯(lián)刪除： 將將SCSC關(guān)系中所有關(guān)系中所有4 4個個Sno=950001Sno=950001的元組一的元組一 起刪除。依此類推

12、可以繼續(xù)級聯(lián)下去。起刪除。依此類推可以繼續(xù)級聯(lián)下去。 n受限刪除受限刪除： 系統(tǒng)將拒絕執(zhí)行此刪除操作。系統(tǒng)將拒絕執(zhí)行此刪除操作。 5.2 5.2 參照完整性參照完整性 第05章數(shù)據(jù)庫完整性new 18 第第五五章章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 5.1 5.1 實體完整性實體完整性 5.2 5.2 參照完整性參照完整性 5.3 5.3 用戶定義完整性用戶定義完整性 5.4 5.4 完整性約束命名子句完整性約束命名子句 5.5 5.5 觸發(fā)器觸發(fā)器 第05章數(shù)據(jù)庫完整性new 19 5.3 5.3 用戶定義的完整性用戶定義的完整性 使用使用CREATE TABLECREATE TABLE語句在定義屬

13、性的同時，定義語句在定義屬性的同時，定義 對屬性值的限制。對屬性值的限制。 包括：包括： 列值非空列值非空(NOT NULL)(NOT NULL) 列值唯一列值唯一(UNIQUE)(UNIQUE) 檢查列值是否滿足一個布爾表達式檢查列值是否滿足一個布爾表達式(CHECK(CHECK子子 句句) )。 第05章數(shù)據(jù)庫完整性new 20 5.3 5.3 用戶定義的完整性用戶定義的完整性 第05章數(shù)據(jù)庫完整性new 21 5.3 5.3 用戶定義的完整性用戶定義的完整性 第05章數(shù)據(jù)庫完整性new 22 5.3 5.3 用戶定義的完整性用戶定義的完整性 第05章數(shù)據(jù)庫完整性new 23 5.3 5.

14、3 用戶定義的完整性用戶定義的完整性 第05章數(shù)據(jù)庫完整性new 24 5.3 5.3 用戶定義的完整性用戶定義的完整性 第05章數(shù)據(jù)庫完整性new 25 5.3 5.3 用戶定義的完整性用戶定義的完整性 第05章數(shù)據(jù)庫完整性new 26 5.3 5.3 用戶定義的完整性用戶定義的完整性 當往表中插入元組或修改屬性的值時，當往表中插入元組或修改屬性的值時， RDBMSRDBMS就檢查屬性上的約束條件是否被滿足，就檢查屬性上的約束條件是否被滿足， 如果不滿足操作被拒絕執(zhí)行。如果不滿足操作被拒絕執(zhí)行。 第05章數(shù)據(jù)庫完整性new 27 5.3 5.3 用戶定義的完整性用戶定義的完整性 在在CREA

15、TE TABLECREATE TABLE語句可以用語句可以用CHECKCHECK短語定義元組短語定義元組 上的約束條件，即元組級限制上的約束條件，即元組級限制 元組級限制可以設置不同屬性之間的取值的相互元組級限制可以設置不同屬性之間的取值的相互 約束條件。約束條件。 第05章數(shù)據(jù)庫完整性new 28 5.3 5.3 用戶定義的完整性用戶定義的完整性 第05章數(shù)據(jù)庫完整性new 29 5.3 5.3 用戶定義的完整性用戶定義的完整性 第05章數(shù)據(jù)庫完整性new 30 5.3 5.3 用戶定義的完整性用戶定義的完整性 在在CREATE TABLECREATE TABLE語句可以用語句可以用CHEC

16、KCHECK短語定義元組短語定義元組 上的約束條件，即元組級限制上的約束條件，即元組級限制 元組級限制可以設置不同屬性之間的取值的相互元組級限制可以設置不同屬性之間的取值的相互 約束條件。約束條件。 第05章數(shù)據(jù)庫完整性new 31 第第五五章章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 5.1 5.1 實體完整性實體完整性 5.2 5.2 參照完整性參照完整性 5.3 5.3 用戶定義完整性用戶定義完整性 5.4 5.4 完整性約束命名子句完整性約束命名子句 5.5 5.5 觸發(fā)器觸發(fā)器 第05章數(shù)據(jù)庫完整性new 32 5.4 5.4 完整性約束命名子句完整性約束命名子句 SQLSQL在提供了完整性約束命

17、名子句在提供了完整性約束命名子句 CONSTRAINTCONSTRAINT， 用來對完整性約束條件進行命名。用來對完整性約束條件進行命名。 格式：格式： CONSTRAINT CONSTRAINT PRIMARY KEY PRIMARY KEY短語短語| | FOREIGN KEY FOREIGN KEY短語短語| | CHECK CHECK短語短語 該短語可以放置在任何一個約束短語的前面。該短語可以放置在任何一個約束短語的前面。 第05章數(shù)據(jù)庫完整性new 33 第05章數(shù)據(jù)庫完整性new 34 第05章數(shù)據(jù)庫完整性new 35 5.4 5.4 完整性約束命名子句完整性約束命名子句 格式：格

18、式：DROP CONSTRAINT DROP CONSTRAINT 定義約束時未命名，系統(tǒng)會自動命名。定義約束時未命名，系統(tǒng)會自動命名。 可以省略可以省略 第05章數(shù)據(jù)庫完整性new 36 5.4 5.4 完整性約束命名子句完整性約束命名子句 格式：格式： ADD CONSTRAINT ADD CONSTRAINT 先刪除，先刪除， 再添加。再添加。 第05章數(shù)據(jù)庫完整性new 37 例例 建立訂購單建立訂購單 CREATE TABLE CREATE TABLE 訂購單訂購單 ( (訂購單號訂購單號 CHAR(5) PRIMARY KEY,CHAR(5) PRIMARY KEY, 職工號職工號

19、 CHAR(5) NOT NULL FOREIGN KEY REFERENCES CHAR(5) NOT NULL FOREIGN KEY REFERENCES 職工職工, , 供應商號供應商號 CHAR(5) NULL FOREIGN KEY REFERENCES CHAR(5) NULL FOREIGN KEY REFERENCES 供應商供應商, , 訂購日期訂購日期 DATETIME DATETIME getdate(), getdate(), 訂購數(shù)量訂購數(shù)量 INT CHECK(INT CHECK(訂購數(shù)量訂購數(shù)量 =10 AND =10 AND 訂購數(shù)量訂購數(shù)量=50);=50)

20、; 5.4 5.4 完整性約束命名子句完整性約束命名子句 第05章數(shù)據(jù)庫完整性new 38 第第五五章章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 5.1 5.1 實體完整性實體完整性 5.2 5.2 參照完整性參照完整性 5.3 5.3 用戶定義完整性用戶定義完整性 5.4 5.4 完整性約束命名子句完整性約束命名子句 5.5 5.5 觸發(fā)器觸發(fā)器 第05章數(shù)據(jù)庫完整性new 39 5.5 5.5 觸發(fā)器觸發(fā)器 觸發(fā)器是一種特殊的存儲過程觸發(fā)器是一種特殊的存儲過程 作用：作用： 用來實現(xiàn)當使用用來實現(xiàn)當使用update update 、insertinsert、deletedelete 語句對表進行操作時，

21、自動觸發(fā)的處理方法；語句對表進行操作時，自動觸發(fā)的處理方法； 實現(xiàn)復雜的業(yè)務要求，類似于約束實現(xiàn)復雜的業(yè)務要求，類似于約束 保證數(shù)據(jù)的一致性。保證數(shù)據(jù)的一致性。 第05章數(shù)據(jù)庫完整性new 40 5.5 5.5 觸發(fā)器觸發(fā)器 After觸發(fā)器：（默認類型）觸發(fā)器：（默認類型） 觸發(fā)觸發(fā)Insert、Update、Delete動作之后執(zhí)行。動作之后執(zhí)行。 （表中數(shù)據(jù)已經(jīng)變動之后）（表中數(shù)據(jù)已經(jīng)變動之后） Instead of觸發(fā)器：觸發(fā)器： 當執(zhí)行當執(zhí)行Insert、Update、Delete操作時用其操作時用其 它操作來代替。它操作來代替。 (操作不做，是完成觸發(fā)器定義的操作操作不做，是完成觸

22、發(fā)器定義的操作) 第05章數(shù)據(jù)庫完整性new 41 5.5 5.5 觸發(fā)器觸發(fā)器 無論在無論在“企業(yè)管理器企業(yè)管理器”和和“查詢分析器查詢分析器”中，都中，都 用用CREATE TRIGGERCREATE TRIGGER語句創(chuàng)建觸發(fā)器。語句創(chuàng)建觸發(fā)器。 格式：格式： Create Trigger on for | after | instead of delete , insert , update as 觸發(fā)器的類型觸發(fā)器的類型 觸發(fā)事件觸發(fā)事件 第05章數(shù)據(jù)庫完整性new 42 5.5 5.5 觸發(fā)器觸發(fā)器 Create Trigger T_In On student after inse

23、rt as print 歡迎新同學的到來！歡迎新同學的到來！ 第05章數(shù)據(jù)庫完整性new 43 5.5 5.5 觸發(fā)器觸發(fā)器 (1)(1)觸發(fā)器工作時建立兩個表：觸發(fā)器工作時建立兩個表： inserted表：表： 存儲存儲insert和和update語句所影響的數(shù)據(jù)行副本語句所影響的數(shù)據(jù)行副本 insertedinserted中的行是觸發(fā)器表中新行的副本。中的行是觸發(fā)器表中新行的副本。 deleted表：表： 存儲存儲delete和和update語句所影響的數(shù)據(jù)行副本語句所影響的數(shù)據(jù)行副本 deleted中的行是從觸發(fā)器表中刪除的行。中的行是從觸發(fā)器表中刪除的行。 觸發(fā)器表和觸發(fā)器表和dele

24、ted表沒有相同的行表沒有相同的行 第05章數(shù)據(jù)庫完整性new 44 5.5 5.5 觸發(fā)器觸發(fā)器 (2)(2)這兩個表的特點：這兩個表的特點： 系統(tǒng)自動維護系統(tǒng)自動維護 用戶可以使用，但不可以修改用戶可以使用，但不可以修改 兩個表的結(jié)構(gòu)與觸發(fā)器作用的表結(jié)構(gòu)相同兩個表的結(jié)構(gòu)與觸發(fā)器作用的表結(jié)構(gòu)相同 觸發(fā)器工作完成后，系統(tǒng)自動刪除觸發(fā)器工作完成后，系統(tǒng)自動刪除 第05章數(shù)據(jù)庫完整性new 45 5.5 5.5 觸發(fā)器觸發(fā)器 (3)(3)各種操作時觸發(fā)器的工作：各種操作時觸發(fā)器的工作： 當插入數(shù)據(jù)時，當插入數(shù)據(jù)時，insertinsert觸發(fā)器將觸發(fā)執(zhí)行，觸發(fā)器將觸發(fā)執(zhí)行， 新的記錄會增加到觸發(fā)器

25、表和新的記錄會增加到觸發(fā)器表和insertedinserted表。表。 當刪除數(shù)據(jù)時，當刪除數(shù)據(jù)時，deletedelete觸發(fā)器將觸發(fā)執(zhí)行，觸發(fā)器將觸發(fā)執(zhí)行， 被刪除的記錄會存放到被刪除的記錄會存放到deleteddeleted表中。表中。 相當于插入一條新紀錄，刪除一條舊記錄。相當于插入一條新紀錄，刪除一條舊記錄。 第05章數(shù)據(jù)庫完整性new 46 5.5 5.5 觸發(fā)器觸發(fā)器 CREATE TRIGGER t1 on Student FOR Insert,Update AS IF( )10 BEGIN PRINT 年齡不能小于年齡不能小于10，操作失敗，操作失敗 ROLLBACK END

26、; 第05章數(shù)據(jù)庫完整性new 47 5.5 5.5 觸發(fā)器觸發(fā)器 CREATE TRIGGER語句必須是批處理語句中的語句必須是批處理語句中的 第一條；第一條； 只有表的擁有者可以創(chuàng)建觸發(fā)器；只有表的擁有者可以創(chuàng)建觸發(fā)器； 一個表只能創(chuàng)建一定數(shù)量的觸發(fā)器；一個表只能創(chuàng)建一定數(shù)量的觸發(fā)器； 在同一模式下，觸發(fā)器名必須惟一；在同一模式下，觸發(fā)器名必須惟一； 當觸發(fā)器被激活時，只有觸發(fā)條件為真時觸發(fā)動當觸發(fā)器被激活時，只有觸發(fā)條件為真時觸發(fā)動 作體才執(zhí)行。作體才執(zhí)行。 第05章數(shù)據(jù)庫完整性new 48 5.5 5.5 觸發(fā)器觸發(fā)器 create trigger t2 on student for

27、update,delete as if (select sno from deleted)=0215122) begin print 不允許刪除該學生，操作失??！不允許刪除該學生，操作失敗！ rollback end 第05章數(shù)據(jù)庫完整性new 49 5.5 5.5 觸發(fā)器觸發(fā)器 CREATE TRIGGER t3 ON Student FOR Update AS IF Update(sno) BEGIN PRINT 學號不允許修改！學號不允許修改！ ROLLBACK END 第05章數(shù)據(jù)庫完整性new 50 5.5 5.5 觸發(fā)器觸發(fā)器 create trigger t4 on student for update,insert as if begin print rollback end 第05章數(shù)據(jù)庫完整性new 51 5.5 5.5 觸發(fā)器觸發(fā)器 第05章數(shù)據(jù)庫完整性new 52 5.5 5.5 觸發(fā)器觸發(fā)器 (1) 執(zhí)行執(zhí)行instead of觸發(fā)器觸發(fā)器 (2) 執(zhí)行執(zhí)行SQL語句語句 (3) 執(zhí)行執(zhí)行after觸發(fā)器觸發(fā)器 (1) 刪除