1、第 8 章,第8章 SQL Server 2005數(shù)據(jù)庫控制與維護,數(shù)據(jù)庫應用教程,數(shù)據(jù)庫的完整性控制,數(shù)據(jù)庫的安全性管理,數(shù)據(jù)庫備份與還原,第8章 SQL Server 2005數(shù)據(jù)庫控制與維護,8.1.1 數(shù)據(jù)完整性概述,8.1.2 使用約束實施數(shù)據(jù)完整性控制,8.1.3 使用規(guī)則實施數(shù)據(jù)完整性控制,8.1 數(shù)據(jù)庫的完整性控制,8.1.4 使用默認值實施數(shù)據(jù)完整性控制,8.1.5 使用觸發(fā)器實施數(shù)據(jù)完整性控制,8.1.1 數(shù)據(jù)完整性概述,1完整性的概念,數(shù)據(jù)完整性是指數(shù)據(jù)的正確性、有效性、一致性和相容性。由于數(shù)據(jù)庫是一個動態(tài)的集合，數(shù)據(jù)不斷地被插入和被修改，因此由于主觀或客觀的原因，可能破

2、壞數(shù)據(jù)庫的數(shù)據(jù)完整性 。,2破壞數(shù)據(jù)庫的數(shù)據(jù)完整性的情況,（1）無效的數(shù)據(jù)被添加到數(shù)據(jù)庫中。如：某在教師管理系統(tǒng)中，輸入的教師號不存在 。,（2）對數(shù)據(jù)庫的修改不一致。如：在兩個不同的表中，同一教師的系號不同。,（3）將存在的數(shù)據(jù)修改為無效的數(shù)據(jù)。如：將某教師所在的系號修改為并不存在的系 。,8.1.1 數(shù)據(jù)完整性概述,3完整性機制,為了保證存放數(shù)據(jù)的完整性，DBMS應能對數(shù)據(jù)庫進行數(shù)據(jù)完整性控制。在SQL Server2000中，提供了約束、默認值、規(guī)則、觸發(fā)器等維護機制對數(shù)據(jù)完整性進行控制。,4完整性分類,在SQL Server中，數(shù)據(jù)完整性分成四類：實體完整性、域完整性、參照完整性和用戶

3、自定義完整性。,8.1.1 數(shù)據(jù)完整性概述,域完整性：也可稱為列完整性。 域完整性要求：向表中指定列輸入的數(shù)據(jù)必須具有正確的數(shù)據(jù)類型、格式以及有效的數(shù)據(jù)范圍。例如，在CollegeMIS數(shù)據(jù)庫的SelectCourse表中，向Score（成績）列輸入數(shù)據(jù)時，不能出現(xiàn)字符，也不能輸入小于0或大于100的數(shù)值。 域完整性的控制方法：主要有CHECK約束、外鍵約束、默認約束、默認值、非空定義、規(guī)則以及在創(chuàng)建表時設置的數(shù)據(jù)類型 。,實體完整性的控制方法：主要有主鍵約束、惟一索引、惟一約束和指定IDENTITY屬性。,5完整性控制方法,參照完整性的控制方法：主要有外鍵約束，有時利用觸發(fā)器也可實現(xiàn)參照完整

4、性控制。,8.1.2 使用約束實施數(shù)據(jù)完整性控制,在SQL Server中，可通過約束來實施數(shù)據(jù)庫的數(shù)據(jù)完整性控制，常用的約束有NOT NULL(非空)約束、CHECK(檢查)約束、UNIQUE(惟一)約束、PRIMARY KEY(主鍵)約束、FOREIGN KEY(外鍵)約束和DEFAULT(默認)約束 。,1約束的建立,【格式】ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED ( column ,.n ) ,（1）主鍵約束的創(chuàng)建,【功能】為table_name

5、作為表名指定的表增加一個由constraint_name指定名稱的主鍵約束。,【例8-1】把CollegeMIS數(shù)據(jù)庫中SelectCourse表中的主鍵pk_xuanke刪除，然后重建。,USE CollegeMIS GO ALTER TABLE SelectCourse DROP CONSTRAINT pk_xuanke GO ALTER TABLE SelectCourse ADD CONSTRAINT pk_xuanke PRIMARY KEY(StuNo,TeaNo,CourseNo) GO,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束的建立,【例8-2】為CollegeMIS數(shù)據(jù)

6、庫中的Department表的DepartName字段創(chuàng)建一個惟一約束，約束名為IX_DepartName。,（2）惟一約束的創(chuàng)建使用企業(yè)管理器創(chuàng)建惟一約束,在一張數(shù)據(jù)表中，有時除主鍵需要具有唯一性外，還有其他列也需要具有唯一性。例如，在“系部”表中，主鍵為“系部代碼”，但是另外一個字段“系部名稱”雖不是主鍵，也需保證它的唯一性，這時就需要創(chuàng)建表中的唯一約束。,使用對象資源管理器創(chuàng)建唯一約束 下面以“系部”表為例，為“系部名稱”字段創(chuàng)建唯一約束。操作步驟如下： （1）在“對象資源管理器”窗格中，右擊需要設置唯一約束的表（本例為“系部”表），在彈出的快捷菜單中選擇“修改”命令，打開“表設計器”窗

7、口。,（2）在“表設計器”窗口中，右擊需要設置為唯一約束的字段（本例為“系部名稱”字段），在彈出的快捷菜單中選擇“索引/鍵”命令，如圖6-7所示，也可以直接單擊工具欄中的“管理索引和鍵”按鈕，打開“索引/鍵”對話框，如圖6-8所示。,圖6-7 選擇“索引/鍵”命令,圖6-8 “索引/鍵”對話框,（3）在打開的“索引/鍵”對話框中，單擊“添加”按鈕，結果如圖6-9所示。,圖6-9 單擊“添加”按鈕創(chuàng)建唯一約束,（4）設置好相關選項后，單擊“關閉”按鈕，完成唯一約束的創(chuàng)建。這時，不只是該表的主鍵必須為唯一，并且被設置為唯一約束的字段同樣也必須為唯一。,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束

8、的建立,【格式】 ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE CLUSTERED | NONCLUSTERED ( column ,.n ) ,（2）惟一約束的創(chuàng)建使用Transact-SQL語句,【功能】為table_name作為表名指定的表增加一個由constraint_name指定名稱的惟一約束。 說明：惟一約束默認的索引類型為NONCLUSTERED。,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束的建立,【例8-3】通過Transact-SQL語句為CollegeMIS數(shù)據(jù)庫中的Department表的Te

9、lephone字段創(chuàng)建一個惟一約束，約束名為IX_Telephone。,（2）惟一約束的創(chuàng)建使用Transact-SQL語句,USE CollegeMIS GO ALTER TABLE Department ADD CONSTRAINT IX_Telphone UNIQUE (Telephone) GO,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束的建立,【例8-4】為CollegeMIS數(shù)據(jù)庫中的“課程表”的“學分” 字段創(chuàng)建一個檢查約束，約束名為CK_Grade。檢查條件只允許為17分，不允許小于1分的學分和大于7分的學分出現(xiàn)。 。,（3）檢查約束的創(chuàng)建使用對象資源管理器,檢查約束對輸入

10、的數(shù)據(jù)的值做檢查，可以限定數(shù)據(jù)輸入，從而維護數(shù)據(jù)的域完整性。,操作步驟如下： （1）在“對象資源管理器”窗格中，右擊需要設置唯一約束的表（本例為“課程表”），在彈出的快捷菜單中選擇“修改”命令，打開“表設計器”窗口。 （2）在“表設計器”窗口中右擊需要創(chuàng)建檢查約束的字段（本例為“學分”字段），在彈出的快捷菜單中選擇“CHECK約束”命令，如圖6-11所示，打開“CHECK約束”對話框。,圖6-11 選擇“CHECK約束”命令,（3）在“CHECK約束”對話框中，單擊“添加”按鈕，然后在“(名稱)”文本框中輸入檢查約束名稱，在約束“表達式”文本框中輸入約束條件，這里輸入“(學分=1 AND 學分

11、=7)”，如圖6-12所示。,圖6-12 設置“CHECK約束”條件,（4）單擊“關閉”按鈕關閉對話框，完成檢查約束的創(chuàng)建。 注意：如果表中原來就有數(shù)據(jù)，并且數(shù)據(jù)類型或范圍與所創(chuàng)建的約束相沖突，那么約束將不能成功創(chuàng)建。,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束的建立,【格式】 ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK ( logical_expression),（3）檢查約束的創(chuàng)建使用Transact-SQL語句,【功能】為table_name作為表名指定的表增加一個由constraint_name指定名稱的檢

12、查約束。 說明：“l(fā)ogical_expression”是檢查約束的檢查條件，通常是一個關系表達式或邏輯表達式。,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束的建立,【例8-5】使用Transact-SQL語句，為CollegeMIS數(shù)據(jù)庫中的Student表的Birthday字段創(chuàng)建一個檢查約束，約束名為CK_Birthday。檢查條件為Birthday字段的值必須小于當前日期。,（3）檢查約束的創(chuàng)建使用Transact-SQL語句,USE CollegeMIS GO ALTER TABLE Student ADD CONSTRAINT CK_Birthday CHECK (Birthda

13、y=GetDate() GO,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束的建立,【例8-6】為CollegeMIS數(shù)據(jù)庫中的Student表的Sex字段創(chuàng)建一個默認約束，默認值為“男”。,（4）默認約束的創(chuàng)建 使用對象資源管理器,操作步驟如下： （1）在“對象資源管理器”窗格中，右擊需要創(chuàng)建默認約束的表（這里為“學生”表），在彈出的快捷菜單中選擇“修改”命令，打開“表設計器”窗口。 （2）選擇需要創(chuàng)建默認約束的字段（這里為“性別”字段），然后在下方的“列屬性”選項卡中的“默認值或綁定”文本框中輸入默認值，本例為選擇“性別”字段，在默認值中輸入“男”，如圖6-13所示。,在用戶輸入某些數(shù)據(jù)時

14、，希望一些數(shù)據(jù)在沒有特例的情況下被自動輸入，例如，學生的注冊日期應該是數(shù)據(jù)錄入的當天日期；學生的修學年限是固定的值；學生性別默認是“男”等情況，這個時候需要對數(shù)據(jù)表創(chuàng)建默認約束。 下面分別用例子說明如何在對象資源管理器中和利用SQL語句創(chuàng)建默認約束,圖 6 | 13 輸入默認值,注意：單引號不需要輸入，在表保存后，在單引號外還會自動生成一對小括號。 （3）關閉“表設計器”窗口。,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束的建立,【格式】 ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT constant_expres

15、sion FOR column_name,（4）默認約束的創(chuàng)建 使用Transact-SQL語句,【功能】為table_name作為表名指定的表增加一個由constraint_name指定名稱的默認約束。 說明：“column_name”是要設置默認約束的列名，“constant_expression”是設置的默認值。,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束的建立,【例8-7】為CollegeMIS數(shù)據(jù)庫中的Student表的Grade字段創(chuàng)建一個默認約束，約束名為DF_Grade，默認值為1。,（4）默認約束的創(chuàng)建使用Transact-SQL語句,USE CollegeMIS GO

16、ALTER TABLE Student ADD CONSTRAINT DF_Grade DEFAULT 1 FOR Grade GO,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束的建立,【例8-8】為CollegeMIS數(shù)據(jù)庫中的“教師”表的創(chuàng)建一個外鍵約束，約束名為FK_ Teacher _Department。約束對應的主鍵表為Department （引用的主鍵字段為系部代碼），本表（ “教師”表）的外鍵字段為系部代碼。,（5）外鍵約束的創(chuàng)建 使用對象資源管理器,操作步驟如下： （1）在“對象資源管理器”窗格中，右擊需要創(chuàng)建外鍵約束的表（這里為“教師”表），在彈出的快捷菜單中選擇“修改”

17、命令，打開“表設計器”窗口。 （2）選擇需要創(chuàng)建外鍵約束的字段（這里為“系部代碼”字段），單擊工具欄中的“關系”按鈕，或右擊該字段，在彈出的快捷菜單中選擇“關系”命令，打開“外鍵關系”對話框，如圖6-14所示。,外鍵是用來維護表與表之間對應唯一關系的一種方法。可以利用對象資源管理器或SQL語句來創(chuàng)建外鍵約束。,圖6-14 “外鍵關系”對話框,（3）在“外鍵關系”對話框中，單擊“添加”按鈕，然后單擊“表和列規(guī)范”的按鈕，打開“表和列”對話框。在“主鍵表”下拉列表中選擇“系部”表，在“外鍵表”的下拉列表框中選擇“教師”表，分別在“主鍵表”和“外鍵表”的下面選擇“系部代碼”字段，如圖6-15所示。,

18、圖6-15 “表和列”對話框,（4）單擊“確定”按鈕，然后在“外鍵關系”對話框中進行相關設置后單擊“關閉”按鈕即可。,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束的建立,【格式】 ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name ,.n) REFERENCES rel_table (ref_column ,.n) ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION NOT FOR REPLICATION,（5）外鍵約束的創(chuàng)建使

19、用Transact-SQL語句,【功能】為由table_name作為表名指定的表創(chuàng)建一個由constraint_name作為約束名的外鍵約束。,8.1.2 使用約束實施數(shù)據(jù)完整性控制,1約束的建立,【例8-9】為CollegeMIS數(shù)據(jù)庫中的SelectCourse表的創(chuàng)建一個外鍵約束，約束名為FK_SelectCourse_Student。約束對應的主鍵表為Student（引用的主鍵字段為StuNo），本表（SelectCourse表）的外鍵字段為StuNo。并設置級聯(lián)刪除和級聯(lián)更新 。,（6）默認約束的創(chuàng)建使用Transact-SQL語句,USE CollegeMIS GO ALTER T

20、ABLE SelectCourse ADD CONSTRAINT FK_SelectCourse_Student FOREIGN KEY (StuNo) REFERENCES Student(StuNo) ON DELETE CASCADE ON UPDATE CASCADE GO,8.1.2 使用約束實施數(shù)據(jù)完整性控制,2約束定義的查看,對于創(chuàng)建好的約束，根據(jù)實際需要可以查看其定義信息。SQL Server 2005提供了多種查看約束信息的方法，經(jīng)常使用的有利用對象資源管理器和系統(tǒng)存儲過程。 （1）利用對象資源管理器查看約束信息 使用對象資源管理器查看約束信息的操作步驟如下： （1）在“對象

21、資源管理器”窗格中，右擊要查看約束的表，在彈出的快捷菜單中選擇“修改”命令，打開“表設計器”窗口。 （2）右擊該表，在彈出的快捷菜單中分別選擇“關系”、“索引/鍵”、“CHECK約束”等命令查看約束信息，如圖6-16所示。,圖6-16 查看約束信息菜單,（2）利用系統(tǒng)存儲過程查看約束的定義,存儲過程sp_helptext是用來查看約束的一個系統(tǒng)提供的存儲過程，可以通過查詢分析器來查看約束的名稱、創(chuàng)建者、類型和創(chuàng)建時間。 其語法格式為： EXEC sp_help 約束名稱 如果該約束有具體的定義和文本，那么可以用sp_helptext來查看。 其語法格式為：EXEC sp_helptext 約束

22、名稱 【例6.9】使用系統(tǒng)存儲過程查看student數(shù)據(jù)庫中定義的入學時間（名稱為ck_rxsj）的約束信息和文本信息。代碼如下，結果如圖6-17所示。,USE student GO EXEC sp_help ck_rxsj GO USE student GO EXEC sp_helptext ck_rxsj GO,8.1.2 使用約束實施數(shù)據(jù)完整性控制,3刪除約束,使用對象資源管理器刪除約束非常方便，正如在建立約束時一樣，只需要在“表設計器”窗口中，將如圖6-2所示的“設置主鍵”前的復選框取消即可刪除主鍵約束，或刪除默認值以刪除默認約束；如圖6-8所示，單擊“刪除”按鈕刪除唯一約束；如圖6-

23、12所示，單擊“刪除”按鈕刪除檢查約束；如圖6-14所示，單擊“刪除”按鈕刪除外鍵約束。,（1）使用對象資源管理器來刪除表約束,（2）使用Transact-SQL語句刪除約束,【格式】 ALTER TABLE table_name DROP CONSTRAINT constraint_name ，n,【功能】刪除由“table_name”作為表名指定的表中由“constraint_name”作為約束名的約束?？梢酝瑫r刪除多個約束，約束名之間用“,”隔開。,8.1.2 使用約束實施數(shù)據(jù)完整性控制,3刪除約束,【例8-10】刪除CollegeMIS數(shù)據(jù)庫中的SelectCourse表的外鍵約束fk

24、_scStuNo（該約束在第5章中建立）。,USE CollegeMIS GO ALTER TABLE SelectCourse DROP CONSTRAINT fk_scStuNo GO,8.1.3 使用規(guī)則實施數(shù)據(jù)完整性控制,規(guī)則概述:,規(guī)則：是一種數(shù)據(jù)庫對象，與CHECK約束的作用基本相同，也是用來限制輸入值的取值范圍，從而實施域完整性控制。 使用規(guī)則：首先要創(chuàng)建規(guī)則，然后把規(guī)則綁定到列上，或從列上解綁和刪除。它與CHECK約束有以下幾點不同：,（1）CHECK約束可以在建表時由CREATE TABLE語句將其作為表的一部分進行指定，而規(guī)則需要單獨創(chuàng)建并綁定到列上；,（2）在一個列上只能

25、應用一個規(guī)則，但是卻可以應用多個CHECK約束。,（3）一個規(guī)則只需定義一次就可以被多次應用，可以應用于多個表或多個列，還可以應用到用戶定義的數(shù)據(jù)類型上。,（4）可以在查詢分析器中用SQL語句完成。,8.1.3 使用規(guī)則實施數(shù)據(jù)完整性控制,1使用對象資源管理器管理規(guī)則,（1）規(guī)則的創(chuàng)建,【例8-11】為CollegeMIS數(shù)據(jù)庫創(chuàng)建一個名為xb_rule規(guī)則，將它綁定到“學生”表的“性別”字段，保證輸入數(shù)據(jù)只能為“男”或“女”,規(guī)則作為一種數(shù)據(jù)庫對象，在使用前必須被創(chuàng)建。 創(chuàng)建規(guī)則的SQL命令是CREATE RULE。其語法格式如下： CREATE RULE rule_name AS cond

26、ition_expression 其中： rule_name是規(guī)則的名稱，命名必須符合SQL Server 2005的命名規(guī)則。 condition_expression是條件表達式。,CREATE RULE xb_rule AS xb in(男,女),8.1.3 使用規(guī)則實施數(shù)據(jù)完整性控制,（2）規(guī)則的綁定,要使創(chuàng)建好的規(guī)則作用到指定的列或表等，還必須將規(guī)則綁定到列或用戶定義的數(shù)據(jù)類型上才能夠起作用。 在查詢分析器中，可以利用系統(tǒng)存儲過程將規(guī)則綁定到字段或用戶定義的數(shù)據(jù)類型上。其語法格式如下： EXECUTE sp_bindrule 規(guī)則名稱,表名.字段名|自定義數(shù)據(jù)類型名,EXEC sp_

27、bindrule xb_rule,學生.性別,將規(guī)則“xb_rule”綁定到“學生”表的“性別”列上,如果字段已經(jīng)不再需要規(guī)則限制輸入了，那么必須把已經(jīng)綁定了的規(guī)則去掉，這就是解綁規(guī)則。在查詢分析器中，同樣用存儲過程來完成解綁操作。其語法格式如下： EXECUTE sp_unbindrule 表名.字段名|自定義數(shù)據(jù)類型名,圖6-18 刪除xb_rule規(guī)則,（3）解綁規(guī)則,如果規(guī)則已經(jīng)沒有用了，那么可以將其刪除。在刪除前應先對規(guī)則進行解綁，當規(guī)則已經(jīng)不再作用于任何表或字段等時，則可以用DROP RULE刪除一個或多個規(guī)則。 其語法格式如下：DROP RULE 規(guī)則名稱,n,（4）刪除規(guī)則,【

28、例6.12】從student數(shù)據(jù)庫中刪除xb_rule規(guī)則。代碼和結果如圖6-18所示。,8.1.3 使用規(guī)則實施數(shù)據(jù)完整性控制,1使用Transact-SQL語句和系統(tǒng)存儲過程管理規(guī)則,（1）創(chuàng)建規(guī)則,【格式】CREATE RULE rule_nameAS condition_expression,【功能】創(chuàng)建一個由“rule_name”指定名稱的規(guī)則，規(guī)則的條件表達式由“condition_expression”指定。,（2）綁定規(guī)則,【格式】EXEC sp_bindrule rule_name tablename_columnname|UDTname,【功能】把rule_name規(guī)則綁定

29、到tablename_columnname指定的列上或UDTname指定的用戶自定義類型上 。,【例8-13】為CollegeMIS數(shù)據(jù)庫創(chuàng)建一個名為Sex_Rule的規(guī)則，規(guī)則的條件表達式為“Sex=男 OR Sex=女”。然后把該規(guī)則分別綁定到Student表的Sex字段和Teacher表的Sex字段。,USE CollegeMIS GO CREATE RULE Sex_Rule As Sex=男 OR Sex=女 GO EXEC sp_bindrule Sex_Rule ,Student.Sex EXEC sp_bindrule Sex_Rule,Teacher.Sex GO,8.1.3

30、 使用規(guī)則實施數(shù)據(jù)完整性控制,1使用Transact-SQL語句和系統(tǒng)存儲過程管理規(guī)則,（3）解綁規(guī)則,【格式】EXEC sp_unbindrule tablename_columnname|UDTname,【功能】把tablename_columnname指定的列上或UDTname指定的用戶自定義數(shù)據(jù)類型上綁定的規(guī)則去掉。,（4）刪除規(guī)則,【格式】DROP RULE rule_name ,.n ,【功能】刪除由rule_name作為規(guī)則名指定的規(guī)則。可刪除多個規(guī)則，規(guī)則之間用“,”分開。,【例如】EXEC sp_unbindrule Teacher.Sex,【例如】DROP RULE Sex

31、_Rule,8.1.4 使用默認值實施數(shù)據(jù)完整性控制,1使用Transact-SQL語句和系統(tǒng)存儲過程管理默認值,（1）創(chuàng)建默認值,【格式】CREATE DEFAULT default_nameAS default_constant,【功能】創(chuàng)建一個由“default_name”指定名稱的默認值，默認值的值由“default_constant”確定。default_constant是常量表達式，可以包含常量、內(nèi)置函數(shù)或數(shù)學表達式。,（2）綁定默認值,【格式】EXEC sp_bindefault default_name tablename_columnname|UDTname,【功能】把def

32、ault_name默認值綁定到tablename_columnname指定的列上或UDTname指定的用戶自定義數(shù)據(jù)類型上。,【例8-14】為CollegeMIS數(shù)據(jù)庫創(chuàng)建一個名為Sex_Default的默認值，值為“男”。然后把該默認值綁定到Teacher表的Sex字段上。,USE CollegeMIS GO CREATE Default Sex_Default As 男 GO EXEC sp_bindefault Sex_Default,Teacher.Sex GO,8.1.4 使用默認值實施數(shù)據(jù)完整性控制,1使用Transact-SQL語句和系統(tǒng)存儲過程管理默認值,（3）解綁默認值,【格

33、式】EXEC sp_unbindefault tablename_columnname|UDTname,【功能】把tablename_columnname指定的列上或UDTname指定的用戶自定義數(shù)據(jù)類型上綁定的默認值去掉。,（4）刪除默認值,【格式】DROP DEFAULT default_name ,.n ,【功能】刪除由default_name作為默認值名指定的默認值?？蓜h除多個默認值，默認值之間用“,”分開。,【例如】EXEC sp_unbindefault Teacher.Sex,【例如】DROP DEFAULT Sex_Default,數(shù)據(jù)完整性強制選擇方法,SQL Server

34、2005提供了許多實現(xiàn)數(shù)據(jù)完整性的方法。除了本章介紹的約束、默認和規(guī)則外，還有前面章節(jié)介紹的數(shù)據(jù)類型和后面需要學習的觸發(fā)器等。對于某一問題可能存在多種解決辦法，應該根據(jù)系統(tǒng)的實際要求，從數(shù)據(jù)完整性方法實現(xiàn)的功能和開銷綜合考慮。 下面來簡單討論一下各種實現(xiàn)數(shù)據(jù)完整性的方法的功能和性能開銷。 觸發(fā)器功能強大，既可以維護基礎的數(shù)據(jù)完整性邏輯，又可以維護復雜的完整性邏輯，如多表的級聯(lián)操作，但是開銷較高； 約束的功能比觸發(fā)器弱，但開銷低； 默認和規(guī)則功能更弱，開銷也更低； 數(shù)據(jù)類型提供最低級別的數(shù)據(jù)完整性功能，開銷也是最低的。 在選擇完整性方案時，應該遵循在完成同樣任務的條件下，選擇開銷低的方案解決。也

35、就是說，能用約束完成的功能，就不用觸發(fā)器完成；能用數(shù)據(jù)類型完成的功能，就不用規(guī)則來完成。,應 用 舉 例,1使用約束 （1）用SQL語句創(chuàng)建cust_sample表，在其中創(chuàng)建四個字段，將cust_id創(chuàng)建為主鍵，并用檢查約束限制cust_id。代碼如下： USE student GO CREATE TABLE cust_sample (cust_id int PRIMARY KEY, cust_name char(16), cust_address char(30), cust_credit_limit money, CONSTRAINT chk_id CHECK (cust_id BETW

36、EEN 0 and 10000) GO,（2）用SQL語句將“教師”表中的“學歷”字段的默認值改為“本科”。代碼如下： IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME=df_xl AND TYPE=D) BEGIN ALTER TABLE 教師 DROP CONSTRAINT df_xl END GO ALTER TABLE 教師 ADD CONSTRAINT df_xl DEFAULT 本科 FOR 學歷 GO,2使用規(guī)則 用SQL語句創(chuàng)建一個xbdm_rule規(guī)則，將其綁定到“系部”表的“系部代碼”字段上，用來保證輸入的“系部代碼”只能是

37、數(shù)字字符，最后顯示規(guī)則的文本信息。代碼如下： USE student GO IF EXISTS (SELECT name FROM sysobjects WHERE name=xbdm_rule AND TYPE=R) BEGIN EXEC sp_unbindrule 系部.系部代碼 DROP RULE xbdm_rule END GO CREATE RULE xbdm_rule AS ch like0-90-9 GO EXEC sp_bindrule xbdm_rule,系部.系部代碼 GO EXEC sp_helptext xbdm_rule GO,3使用默認 用SQL語句創(chuàng)建一個df_b

38、z默認對象，將其綁定到“班級”表的“備注”字段上，使默認值為“教學班”。最后查看默認對象定義的文本信息。代碼如下： USE student GO IF EXISTS (SELECT name FROM sysobjects WHERE name=df_bz AND TYPE=D) BEGIN EXEC sp_unbindefault 班級.備注 DROP DEFAULT df_bz END GO CREATE DEFAULT df_bz AS 教學班 GO EXEC sp_bindefault df_bz,班級.備注 GO EXEC sp_helptext df_bz GO,8.1.5 使用觸

39、發(fā)器實施數(shù)據(jù)完整性控制,1觸發(fā)器概述,（1）觸發(fā)器的概念,觸發(fā)器：也是實施數(shù)據(jù)完整性控制的一種手段，它是一種特殊類型的存儲過程，由Transact-SQL語句組成，不允許帶參數(shù)，與表緊密相連，可以看作表定義的一部分。 使用：它是在用戶對表中的數(shù)據(jù)進行修改、插入或刪除時，由系統(tǒng)自動調用，而不允許由用戶或程序通過名稱調用。,功能：觸發(fā)器是基于一個表創(chuàng)建的，但是可以針對多個表進行操作，因此利用觸發(fā)器不但能夠對數(shù)據(jù)庫中的相關表實施級聯(lián)操作，而且可以引用其他表中的列來完成檢查工作以實現(xiàn)比CHECK約束更為復雜的數(shù)據(jù)完整性約束。利用觸發(fā)器還可以評估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對策。 種類：在一個

40、表中可以存在三種不同操作（INSERT、UPDATE、DELETE）的觸發(fā)器，以對相應的修改操作響應并進行處理。,8.1.5 使用觸發(fā)器實施數(shù)據(jù)完整性控制,1觸發(fā)器概述,（2）觸發(fā)器的種類,SOL Server 2000按觸發(fā)器被激活的時機可以分為兩種類型：AFTER觸發(fā)器和INSTEAD OF觸發(fā)器。,AFTER觸發(fā)器：又稱為后觸發(fā)器，該類觸發(fā)器是在引起觸發(fā)器執(zhí)行的修改語句成功執(zhí)行后被觸發(fā)執(zhí)行，如果修改語句執(zhí)行失敗，觸發(fā)器將不會執(zhí)行。此類觸發(fā)器只能定義在表上，而且可以為每個觸發(fā)操作(INSERT,UPDATE或DELETE)創(chuàng)建多個AFTER觸發(fā)器。注意，該類觸發(fā)器不能創(chuàng)建在視圖上。,INS

41、TEAD OF觸發(fā)器：又稱為替代觸發(fā)器，表示不執(zhí)行引起觸發(fā)器執(zhí)行的修改語句，而只執(zhí)行觸發(fā)器本身。該類觸發(fā)器既可在表上定義，也可在視圖上定義。對于每個觸發(fā)操作(INSERT,UPDATE和DELETE)，只能定義一個INSTEAD OF觸發(fā)器。,8.1.5 使用觸發(fā)器實施數(shù)據(jù)完整性控制,1觸發(fā)器概述,（3）Deleted表與Inserted表,在觸發(fā)器的執(zhí)行過程中，SQL Server建立并管理兩個臨時表：Deleted表和Inserted表。這兩個表包含了在激發(fā)觸發(fā)器的操作中插入、修改或刪除的記錄。可以利用這一特性來檢查某些數(shù)據(jù)修改情況，并根據(jù)修改情況決定觸發(fā)器應執(zhí)行的操作。這兩個特殊表用戶只

42、能查詢，而不能直接修改。 。,在執(zhí)行INSERT語句（插入操作）時被添加的記錄將存儲在Inserted表中。 在執(zhí)行DELETE語句（刪除操作）時，表中被刪除的記錄會發(fā)送到Deleted表。在執(zhí)行UPDATE語句（修改操作）時，SQL Server先將要進行修改的記錄存儲到Deleted表中，然后再將修改后的記錄復制到Inserted表中。,8.1.5 使用觸發(fā)器實施數(shù)據(jù)完整性控制,2創(chuàng)建觸發(fā)器,（1）使用對象資源管理器創(chuàng)建觸發(fā)器,【例8-15】為CollegeMIS數(shù)據(jù)庫的Teacher表創(chuàng)建一個名為Insert_DepartNo的INSERT觸發(fā)器。當在Teacher表插入數(shù)據(jù)時，如果在D

43、epartment（系）表中沒有的DepartNo（系號）的值，則提示用戶不能插入記錄，否則提示記錄插入成功。,在創(chuàng)建觸發(fā)器時，必須指明在哪一個表上定義觸發(fā)器以及觸發(fā)器的名稱、激發(fā)時機、激活觸發(fā)器的修改語句（INSERT、UPDATE或DELETE）。,操作步驟如下： 在“對象資源管理器”窗格中，展開“數(shù)據(jù)庫”結點。 展開相應的數(shù)據(jù)庫（選擇CollegeMIS數(shù)據(jù)庫）和“表”結點。 單擊相應的表（選擇“Teacher”表），右擊“觸發(fā)器”結點，在彈出的快捷菜單中選擇“新建觸發(fā)器”命令，打開新建觸發(fā)器初始界面，如圖9-6所示。, 單擊“分析”按鈕，然后單擊“執(zhí)行”按鈕，完成觸發(fā)器的創(chuàng)建。,CRE

44、ATE TRIGGER Insert_DepartNo ON dbo.Teacher FOR INSERT AS DECLARE DepartNo char(2) SELECT DepartNo=Department.DepartNo FROM Department,Inserted WHERE Department.DepartNo=Inserted.DepartNo IF DepartNo PRINT(教師信息插入成功) ELSE BEGIN PRINT(系表中沒有該系的信息，插入失敗) ROLLBACK TRANSACTION /*撤消事務*/ END, 輸入觸發(fā)器文本，本例中輸入的代碼

45、如下：,8.1.5 使用觸發(fā)器實施數(shù)據(jù)完整性控制,2創(chuàng)建觸發(fā)器,（2）使用Transact-SQL語句創(chuàng)建觸發(fā)器,【格式】CREATE TRIGGER trigger_name ON table|view WITH ENCRYPTION FOR|AFTER|INSTEAD OFINSERT,UPDATE ,DELETE NOT FOR REPLICATION AS IF UPDATE(column) AND|OR UPDATE(column),.n |IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask) comparison_operator

46、column_bitmask,.n sql_statement ,.n ,【功能】為table指定的表或view指定的視圖創(chuàng)建一個由trigger_name指定名稱的觸發(fā)器。,【例8-16】為CollegeMIS數(shù)據(jù)庫中的表Teacher表建立一個名為delete_Teacher的DELETE觸發(fā)器。當用戶刪除Teacher表中的記錄時，如果SelectCourse表中引用了該記錄的TeaNo值，則提示用戶不能刪除記錄，否則提示記錄已刪除。,USE CollegeMIS GO CREATE TRIGGER Delete_Teacher ON Teacher FOR DELETE AS IF(

47、SELECT Count(*) FROM SelectCourse INNER JOIN Deleted ON SelectCourse.TeaNo=Deleted.TeaNo)0 BEGIN PRINT 該教師在選課表中已經(jīng)有授課信息，不能刪除！ ROLLBACK TRANSACTION END ELSE PRINT 記錄刪除成功 GO,【例8-17】為CollegeMIS數(shù)據(jù)庫中的表Teacher表建立一個名為Update_Teacher的Update觸發(fā)器。當用戶修改Teacher表的TeaID的值時，提示用戶不能修改身份證號。,USE CollegeMIS GO CREATE TRIG

48、GER Update_Teacher ON Teacher FOR UPDATE AS IF UPDATE(TeaID) BEGIN PRINT 不能修改教師的身份證號 ROLLBACK TRANSACTION END GO,8.1.5 使用觸發(fā)器實施數(shù)據(jù)完整性控制,3查看觸發(fā)器信息,（1）使用對象資源管理器查看觸發(fā)器信息,（2）使用系統(tǒng)存儲過程查看觸發(fā)器信息,可以使用系統(tǒng)存儲過程sp_help、sp_helptext和sp_helptrigger來查看觸發(fā)器信息。sp_help和sp_helptext的使用方法，在前面的各章已經(jīng)多次講解。調用sp_helptrigger的語句格式如下： EXEC sp_helptrigger table_name INSERT,UPDATE ,DELETE,例如： USE CollegeMIS GO EXEC sp_helptrigger Teacher GO,8.1.5 使用觸發(fā)器實施數(shù)據(jù)完整性控制,4修改觸發(fā)器,（1）使用對象資源管理器修改觸發(fā)器,（2）使用Transact-SQL語句修改觸發(fā)器,修改觸發(fā)器的定義，可以使用Transact-SQL語句： ALTER TRIGGER,