第5章 數據完整性

1、第第5章章 數據完整性數據完整性 2022年3月27日星期日1第五章第五章 數據完整性數據完整性 q 數據完整性的概念數據完整性的概念 q 約束管理約束管理 q 默認管理默認管理 q 規(guī)則管理規(guī)則管理 q 完整性技術比較研究完整性技術比較研究 q 習題習題第第5章章 數據完整性數據完整性 2022年3月27日星期日2一、數據完整性的概念一、數據完整性的概念 所謂完整性，就是指存儲在數據庫中數據的一致性和正確性。數據完整性是衡量數據庫中數據質量好壞的一種標志，是確保數據庫中數據一致、正確以及符合企業(yè)規(guī)則的一種思想，是使無序的數據條理化，確保正確的數據被存放在正確的位置的一種手段，實施數據庫完整性

2、的目的是確保數據的質量。所以，滿足完整性要求的數據具有以下3個特點：數據的值正確無誤。數據的存在必須確保同一表格數據之間的和諧關系。數據的存在必須能確保維護不同表格數據之間的和諧關系。在Microsoft SQL Server系統(tǒng)中，從維護數據完整性的意義上來看，數據完整性分為四類：實體完整性、域完整性、參考完整性、用戶定義的完整性。第第5章章 數據完整性數據完整性 2022年3月27日星期日31. 實體完整性實體完整性實體完整性，也可以稱為行完整性，規(guī)定表的每一行在表中是惟一的實體。實體就是數據庫所要表示的一個實際的物體或事件。實體完整性要求每個實體都保持惟一性，因此，要求表中的所有行有一個

3、惟一的標識符，這種標識符一般稱為主鍵值。實體完整性要求數據庫表中的每一條記錄都是惟一的，即表中沒有重復的記錄。因此，這就要求所有數據記錄中至少必須有一個字段的內容是不能重復的。第第5章章 數據完整性數據完整性 2022年3月27日星期日42域完整性（值域完整性）域完整性（值域完整性）域完整性，也可以稱為列完整性，是指給定列的輸入有效性，即數據庫表中的列必須滿足某種特定的數據類型或約束。指定一個數據集對某一個列是否有效和確定是否允許空值。域完整性通常是通過有效性檢查來實現的，并且還可以通過限制數據類型、格式或者可能的取值范圍來實現。因此，強制域有效性的方法有：限制類型（通過數據類型）、格式（通過

4、 CHECK 約束和規(guī)則）或可能值的范圍（通過 FOREIGN KEY 約束、CHECK 約束、DEFAULT 定義、NOT NULL 定義和規(guī)則）。第第5章章 數據完整性數據完整性 2022年3月27日星期日53. 參照完整性（引用完整性）參照完整性（引用完整性）參照完整性是在插入或刪除數據時，維護表格間數據一致性的手段。參照完整性確保在不同表之間的關鍵性數據保持一致（涉及兩個或兩個以上表的數據的一致性維護），防止了數據丟失或無意義的數據在數據庫中擴散。當添加、刪除或修改數據庫表中的記錄時，可以借助引用完整性來保證相關聯的表之間的數據一致性。在SQL Server中，參照完整性一般建立在主鍵

5、與外鍵之間的關系，或者外鍵與唯一索引之間關系的基礎上，它確保了有主關鍵字的表中對應其它表的外部關鍵字的行存在。第第5章章 數據完整性數據完整性 2022年3月27日星期日6第第5章章 數據完整性數據完整性 2022年3月27日星期日74. 用戶定義的完整性用戶定義的完整性這是由用戶定義的完整性。用戶定義的完整性即是針對某個特定關系數據庫的約束條件，它反映某一具體應用所涉及的數據必須滿足的語義要求。SQL Server 提供了定義和檢驗這類完整性的機制，以便用統(tǒng)一的系統(tǒng)方法來處理它們，而不是用應用程序來承擔這一功能。用戶定義完整性可以定義不屬于其他任何完整性分類的特定業(yè)務規(guī)則，其它的完整性類型都

6、支持用戶定義的完整性。用戶定義的完整性主要是體現實際運用的業(yè)務規(guī)則，可以通過前面三種完整性的實施而得到維護。例如：在titles表中，規(guī)定商業(yè)類用書的title_id的前兩個字符必須是BU等。 第第5章章 數據完整性數據完整性 2022年3月27日星期日8實現數據完整性實現數據完整性在Microsoft SQL Server系統(tǒng)中，可以使用兩種方式實現數據完整性，即聲明數據完整性和過程數據完整性。聲明數據完整性（說明性引用數據完整性）聲明數據完整性（說明性引用數據完整性）就是通過在對象定義中定義的數據標準來實現數據完整性，在插入、修改和刪除數據時由系統(tǒng)本身自動強制來實現的。聲明數據完整性的方式

7、包括使用各種約束、缺省和規(guī)則。過程數據完整性（引用的行動完整性）過程數據完整性（引用的行動完整性）是通過在腳本語言中定義的數據完整性標準來實現的。在執(zhí)行這些腳本的過程中，由腳本中定義的強制完整性的實現。過程數據完整性的方式包括使用視圖、觸發(fā)器和存儲過程等，其中視圖和存儲過程不能自動執(zhí)行。第第5章章 數據完整性數據完整性 2022年3月27日星期日9實現數據完整性的途徑實現數據完整性的途徑數據完整性類型實施途徑數據完整性類型實施途徑實體完整體性主鍵約束 PRIMARY KEY域完整性默認值約束 Default惟一性約束 Unique Key檢查約束 Check索引 Unique Index外鍵約

8、束 Foreign Key標識列 Identity Column數據類型 Data Type參照完整性外鍵約束 Foreign Key規(guī)則 Rule檢查約束 Check用戶定義完整性Rule、 Triggers 、 Procedure觸發(fā)器 TriggersCreate Table中的全部列級和表級約束系統(tǒng)存儲過程 Stored Procedure第第5章章 數據完整性數據完整性 2022年3月27日星期日10二、約束管理二、約束管理 約束是通過限制列中、行中和表之間數據輸入值的范圍來保證數據完整性的非常有效的方法。約束可以分為兩種： 列級約束：是行定義的一部分，只能夠應用在一列上。 表級約束

9、：其定義獨立于列的定義，可以應用在一個表中的多個列上。約束的類型 定義約束 默認管理 檢查約束管理 主鍵約束管理 惟一性約束管理 外鍵約束管理 約束類型約束類型綜合例題第第5章章 數據完整性數據完整性 2022年3月27日星期日11約束的類型約束的類型完整性類型完整性類型約約 束束 類類 型型描描 述述域完整性域完整性DEFAULT(缺省)在使用INSERT語句插入數據時，如果某個列的值沒有明確提供，則將定義的缺省值插入到該列中CHECK(檢查)指定某一個列中的可保存值的范圍實體完整性實體完整性PRIMARY KEY(主鍵)每一行的惟一標識符，確保用戶不能輸入冗余值和確保創(chuàng)建索引，提高性能，不

10、允許空值UNIQUE(惟一性)防止出現冗余值，并且確保創(chuàng)建索引，提高性能。允許空值參考完整性參考完整性FOREIGN KEY(外鍵)定義一列或幾列，其值與本表或者另外一個表的主鍵值匹配約束是保證數據完整性的有效方法。約束可以確保把有效的數據輸入到列中和維護表和表之間的特定關系。每一種數據完整性類型，例如實體完整性、域完整性、參考完整性，都由不同的約束類型來保障。下表描述了不同類型的約束和完整性之間的關系。第第5章章 數據完整性數據完整性 2022年3月27日星期日12定義約束定義約束定義約束從無到有的創(chuàng)建約束，這種操作可以使用CREATE TABLE語句或ALTER TABLE語句完成。使用C

11、REATE TABLE語句表示在創(chuàng)建表的時候定義約束，使用ALTER TABLE語句表示在已有的表中添加約束。如果表中已經有數據，那么也可以增加約束。第第5章章 數據完整性數據完整性 2022年3月27日星期日13CREATE TABLE語句語句CREATE TABLE table_name( column_name data_type CONSTRAINT constraint_name PRIMARY KEY CLUSTERED |NONCLUSTERED | UNIQUE CLUSTERED |NONCLUSTERED | FOREIGN KEY REFERENCES ref_table

12、( ref_colunm )| DEFAULT constant_expression| CHECK logical_expression 使用ALTER TABLE語句定義約束的語法與使用CREATE TABLE語句定義約束的語法類似。第第5章章 數據完整性數據完整性 2022年3月27日星期日14【示例】定義一個主鍵約束CREATE TABLE students(student_id int NOT NULL CONSTRAINT PK_student_id PRIMARY KEY,student_name varchar(10) NOT NULL ,student_gender char

13、(1) NOT NULL ,student_birthday datetime NOT NULL )第第5章章 數據完整性數據完整性 2022年3月27日星期日15當定義約束或修改約束的定義時，應該考慮下列因素：l不必刪除表，就可以直接創(chuàng)建、修改和刪除約束的定義。l應該在應用程序中增加錯誤檢查機制，測試數據是否與約束相沖突。l當在表上增加索引時，SQL SERVER系統(tǒng)檢查表中的數據是否與約束沖突。當創(chuàng)建約束時，應該指定約束的名稱。否則，系統(tǒng)將要提供復雜的、系統(tǒng)自動生成的名稱。對于一個數據庫來說，約束名稱必須是惟一的。一般來說，約束的名稱格式應該是：約束類型簡稱約束類型簡稱_表名表名_列號列號

14、_代號代號第第5章章 數據完整性數據完整性 2022年3月27日星期日16約束信息的查看約束信息的查看查看約束信息的方法為：（1）執(zhí)行系統(tǒng)存儲過程，如：sp_help 或 sp_helpconstraint。（2）查詢信息模式視圖，如：check_constraints、referential_constraints或table_constraints。（3）查看一些系統(tǒng)表，例如約束的定義存儲在下列系統(tǒng)表中：sysconstraints、sysreferences和syscomments。第第5章章 數據完整性數據完整性 2022年3月27日星期日17（1）使用sp_helpconstrain

15、t可得到數據庫中某一個表中的全部約束信息?！纠匡@示pubs數據庫中authors表中的全部約束信息USE pubsEXEC sp_helpconstraint authors第第5章章 數據完整性數據完整性 2022年3月27日星期日18（2）syscomments系統(tǒng)表包含了每一個視圖、規(guī)則、缺省、觸發(fā)器、檢查約束、缺省約束和存儲過程等信息。在該系統(tǒng)表中的文本列上記錄了這些對象的原始定義語句。使用該系統(tǒng)表，可以查看有關約束的定義信息。【例】顯示pubs數據庫中syscomments系統(tǒng)表的內容USE pubsSELECT id,text,texttype,languageFROM sysc

16、omments第第5章章 數據完整性數據完整性 2022年3月27日星期日19（3）sysreferences系統(tǒng)表記錄了與外鍵約束有關的信息?！纠匡@示pubs數據庫中sysreferences系統(tǒng)表的內容USE pubsSELECT *FROM sysreferences第第5章章 數據完整性數據完整性 2022年3月27日星期日20默認（默認（ DEFAULT ）約束管理）約束管理當使用INSERT語句插入數據時，如果某一個列沒有指定數據，那么默認約束就在該列中輸入一個值。 因此，默認約束保證了域完整性。DEFAULT約束用于指定一個字段的默認值。它的作用是：當向表中插入數據時，如果用戶

17、沒有給某一字段輸入數據，則系統(tǒng)自動將默認值作為該字段的數據內容。向表中添加數據時，如果沒有輸入字段值，則此字段的值可能是下面幾種情況： 此字段定義了默認值，則此字段的內容為默認值； 此字段未定義默認值，而且允許為NULL值，則NULL值將成為該字段的內容； 此字段未定義默認值，也不允許為NULL值，保存時將會出現錯誤信息，而且添加數據操作失敗。第第5章章 數據完整性數據完整性 2022年3月27日星期日21使用使用CREATE TABLE語句創(chuàng)建語句創(chuàng)建DEFAULT約束約束在CREATE TABLE語句中添加DEFAULT子句，DEFAULT子句格式如下：CONSTRAINT constra

18、int_name DEFAULT constant_expression 其中： constraint_name 為約束名； constant_expression為常量表達式?！臼纠?將入學日期字段的默認值設置為2003-9-1CREATE TABLE stud_new (學號 char(8) NOT NULL,姓名 char(8) NOT NULL,入學日期 datetime CONSTRAINT dt DEFAULT 2003-9-1)第第5章章 數據完整性數據完整性 2022年3月27日星期日22使用企業(yè)管理器創(chuàng)建使用企業(yè)管理器創(chuàng)建DEFAULT約束約束第第5章章 數據完整性數據完整

19、性 2022年3月27日星期日23當使用默認約束時，需要考慮下列一些因素：l默認約束只能應用于INSERT語句。l每一個列只能定義一個默認約束。l默認約束不能放在有IDENTITY屬性的列上或者數據類型為timestamp的列上。l默認約束允許指定一些由系統(tǒng)函數提供的值。這些系統(tǒng)函數包括USER 、CURRENT_USER、SESSION_USER、SYSTEM_USER、CURRENT_TIMESTAMP等。第第5章章 數據完整性數據完整性 2022年3月27日星期日24檢查（檢查（CHECK）約束管理）約束管理檢查約束用來限制用戶輸入某一個列的數據，即在該列中只能輸入指定范圍的數據。檢查約

20、束非常類似于WHERE子句，其共同之處就是指定可接受數據的條件。 檢查約束與外鍵約束的相同之處，在于都是通過檢查數據的值的合理性來實現數據完整性的維護。但是，外鍵約束是從另一張表上獲得合理的數據，而檢查約束則是通過對一個邏輯表達式的結果進行判斷來對數據進行檢查。檢查約束可以用來限制字段上可以接受的數據值。檢查約束使用邏輯表達式來限制字段上可以接受的數據值。檢查約束通過檢查輸入表中字段的的數據值來維護域完整性。第第5章章 數據完整性數據完整性 2022年3月27日星期日25使用檢查約束的方式有： 在創(chuàng)建表格時，定義檢查約束，檢查約束是表格定義的一部分。 在已經建立的表上添加檢查約束。 修改或刪除

21、表上已經定義的檢查約束。第第5章章 數據完整性數據完整性 2022年3月27日星期日26使用使用CREATE TABLE語句創(chuàng)建檢查約束語句創(chuàng)建檢查約束使用CREATE TABLE語句創(chuàng)建表時，可以通過在該語句中添加一個CHECK子句創(chuàng)建檢查約束。該子句的語法格式如下： CONSTRAINT check_name CHECK (check_criterial)其中，check_name為約束名，check_criterial為約束條件。 【示例】 將“學號”和“課程編號”兩個字段的組合設置為主鍵，再將“成績”字段的取值限制在0到100之間USE lxCREATE TABLE 學生成績(學號 c

22、har(8) NOT NULL, 課程編號 char(3) NOT NULL,成績 real NOT NULL CONSTRAINT CK_CJ CHECK ( 成績成績=0 AND 成績成績=0 AND vale= $1000 and range =18 AND employee_age=35)GOsp_bindrule rule_employee_age , employee.age第第5章章 數據完整性數據完整性 2022年3月27日星期日82 2、將規(guī)則綁定到用戶定義的數據類型【示例】假設存在名為 rule_ssn 的規(guī)則和名為 ssn 的用戶定義數據類型，此示例將 rule_ssn

23、綁定到 ssn。在 CREATE TABLE語句中，類型 ssn 的列繼承 rule_ssn 規(guī)則。類型 ssn 的現有列也繼承 rule_ssn 規(guī)則，除非為 futureonly_flag 指定了 futureonly 或者在 ssn 上直接綁定了規(guī)則。綁定到列的規(guī)則始終優(yōu)先于綁定到數據類型的規(guī)則。 USE master EXEC sp_bindrule rule_ssn, ssn GO第第5章章 數據完整性數據完整性 2022年3月27日星期日83 3、使用 futureonly_flag【示例】將 rule_ssn 規(guī)則綁定到用戶定義數據類型 ssn。因為已指定 futureonly，

24、所以不影響類型 ssn 的現有列。 USE master EXEC sp_bindrule rule_ssn, ssn, futureonly GO 4、使用分隔標識符 【示例】顯示在 object_name 中分隔標識符的使用 USE master CREATE TABLE t.2 (c1 int) EXEC sp_bindrule rule1, t.2.c1 GO第第5章章 數據完整性數據完整性 2022年3月27日星期日84解除規(guī)則綁定解除規(guī)則綁定使用sp_unbindrule在當前數據庫中為列或用戶定義數據類型解除規(guī)則綁定。語法格式為： sp_unbindrule objname =

25、object_name , futureonly = futureonly_flag其中：object_name為要解除規(guī)則綁定的列或用戶自定義數據類型的名稱。futureonly = futureonly_flag為一個可選參數，僅在解除一個捆綁在用戶自定義數據類型上的規(guī)則時使用。futureonly_flag默認值是NULL，如果設置為future_only，則在解除綁定前，使用這個數據類型的表列將繼續(xù)受到規(guī)則的約束。其目的是為了防止具有指定用戶定義數據類型的列丟失掉捆綁在其上的規(guī)則。第第5章章 數據完整性數據完整性 2022年3月27日星期日85【示例】為表 employees 的 st

26、artdate 列解除規(guī)則綁定。 exec sp_unbindrule employees.startdate【示例】為用戶定義數據類型 ssn 解除規(guī)則綁定。這將為該數據類型的現有列和將來的列解除規(guī)則綁定。 exec sp_unbindrule ssn【示例】為用戶定義數據類型ssn解除規(guī)則綁定，現有的 ssn 列不受影響。 exec sp_unbindrule ssn, futureonly第第5章章 數據完整性數據完整性 2022年3月27日星期日86刪除規(guī)則刪除規(guī)則如果規(guī)則未綁定到任何列或用戶定義的數據類型，那么該規(guī)則將被立即刪除。如果規(guī)則被綁定于表列或用戶定義的數據類型，那么在刪除該

27、規(guī)則之前，必須從所有的表列或用戶定義的數據類型上解除該規(guī)則對象。當要刪除的規(guī)則仍捆綁在列或用戶自定義數據類型上時，執(zhí)行DROP RULE語句會顯示錯誤信息，同時DROP RULE操作將被撤銷。使用DROP RULE從當前數據庫中刪除一個或多個用戶定義的規(guī)則。 語法格式如下： DROP RULE 規(guī)則名規(guī)則名 ,.n 【示例】刪除規(guī)則USE company DROP RULE rule_employee_ageGO第第5章章 數據完整性數據完整性 2022年3月27日星期日87規(guī)則的優(yōu)先級規(guī)則的優(yōu)先級用于規(guī)則綁定的優(yōu)先級規(guī)則如下：綁定于列的規(guī)則優(yōu)先于綁定于用戶定義的數據類型的規(guī)則。如果一個表列和

28、定義該列的用戶定義的數據類型都綁定了規(guī)則，那么綁定于表列的規(guī)則有效。如果在表列上綁定了一個新規(guī)則，那么該規(guī)則代替綁定于定義該列的用戶定義的數據類型的規(guī)則。如果把規(guī)則綁定到某一列或者用戶定義的數據類型上，則新規(guī)則取代原來的規(guī)則。用戶只能使用綁定到表列或用戶定義的數據類型之上的規(guī)則。如果把規(guī)則綁定到用戶定義的數據類型上，那么它不會取代綁定于使用這種類型定義的列上。第第5章章 數據完整性數據完整性 2022年3月27日星期日88規(guī)則對已經輸入到表中的數據無效。因此，表中已有的數據可以不滿足規(guī)則指定的要求。如果希望一個規(guī)則限制已經輸入到表中的數據，那么就要在數據輸入到表中之前就把定義好的規(guī)則綁定到用戶定義的數據類型或表列上。規(guī)則的優(yōu)先級新規(guī)則綁定到綁定于用戶定義的數據類型的舊規(guī)則綁定于列的舊規(guī)則用戶定義的數據類型替換舊規(guī)則沒有變化表列替換舊規(guī)則替換舊規(guī)則第第5章章 數據完整性數據完整性 2022年3月27日星期日89A .使用企業(yè)管理器創(chuàng)