第5章 完整性約束定義

1、1,5.4 數(shù)據(jù)完整性的概念與實施方法,5.4.1 數(shù)據(jù)完整性概念數(shù)據(jù)完整性是指數(shù)據(jù)的正確性、完備性和一致性，是衡量數(shù)據(jù)庫質(zhì)量好壞的重要標準。在用INSERT、DELETE、UPDATE語句修改數(shù)據(jù)庫內(nèi)容時，數(shù)據(jù)的完整性可能會遭到破壞。可能會存在下列情況：無效的數(shù)據(jù)被添加到數(shù)據(jù)庫的表中。如：將學生考試成績輸入成負數(shù)； SQL Server提供了對數(shù)據(jù)庫中表、列實施數(shù)據(jù)完整性的方法。對表進行設(shè)計數(shù)據(jù)完整性有兩個重要內(nèi)容：標識列的有

2、效值和確定如何強制列中的數(shù)據(jù)完整性。,2,1. 域完整性域完整性是指一個列的輸入有效性，是否允許空值。強制域完整性的方法有：限制類型（通過設(shè)定列的數(shù)據(jù)類型）、格式（通過CHECK約束和規(guī)則）或可能值的范圍（通過 FOREIGN KEY 約束、CHECK 約束、DEFAULT定義、NOT NULL定義和規(guī)則）。2. 實體完整性實體完整性是指保證表中所有的行唯一。實體完整性要求表中的所有行都有一個唯一標識符。這個唯一標識符可能是一列，

3、也可能是幾列的組合，稱之為主鍵。也就是說，表中主鍵在所有行上必須取值唯一。強制實體完整性的方法有：索引、UNIQUE約束、PRIMARY KEY約束或 IDENTITY屬性 。3. 參照完整性參照完整性也叫引用完整性。參照完整性總是保證主關(guān)鍵字（被引用表）和外部關(guān)鍵字（引用表）之間的參照關(guān)系。它涉及兩個或兩個以上表數(shù)據(jù)的一致性維護。,3,5.4.2 數(shù)據(jù)完整性實施方法1. 聲明型數(shù)據(jù)完整性聲明型數(shù)據(jù)完整性一般在對象創(chuàng)建時定義，

4、由SQL Server強制實施，通常使用約束、缺省值和規(guī)則來實現(xiàn)。實現(xiàn)基本數(shù)據(jù)完整性的首選方法是使用聲明型數(shù)據(jù)完整性。聲明型數(shù)據(jù)完整性作為數(shù)據(jù)庫對象說明的一部分在語法中實現(xiàn)，在CREATE TABLE和ALTER TABLE定義中使用CONSTRAINT、DEFAULT等語句限制表中的值。使用這種方法實現(xiàn)數(shù)據(jù)完整性簡單且不易出錯，系統(tǒng)直接將實現(xiàn)數(shù)據(jù)完整性的要求定義在表和列上。2. 過程型數(shù)據(jù)完整性過程型數(shù)據(jù)完整性是指由某個過程引發(fā)

5、而實施的數(shù)據(jù)完整性。一般先寫出實施數(shù)據(jù)完整性的條件，再寫出強制該條件所執(zhí)行的用于保證數(shù)據(jù)完整性的腳本。通常由觸發(fā)器和存儲過程實現(xiàn)。過程型數(shù)據(jù)完整性也可以在客戶機和服務器上使用其它編程語言和工具實現(xiàn)。,4,5.4.3 約束1.約束的定義和類型約束是SQL Server提供的自動保持數(shù)據(jù)庫完整性的一種方法 。列級約束：列級約束是行定義的一部分，只能夠應用在一列上。表級約束：表級約束的定義獨立于列的定義，可以應用在一個表中的多列上。

6、約束有六種類型：非空約束、缺省約束、檢查約束、主鍵約束、唯一約束、外鍵約束（參照約束）。非空約束（NOT NULL）：表中的某些列必須存在有效值，不允許有空值出現(xiàn)。這是最簡單的數(shù)據(jù)完整性約束，可在建表時將該列聲明為NOT NULL即可。缺省約束（DEFALUT CONSTRAINTS）：當向數(shù)據(jù)庫中的表插入數(shù)據(jù)時，如果用戶沒有明確給出某列的值，SQL Server自動為該列輸入指定值。,5,檢查約束（CHECK CONSTRAINT

7、S）：限制插入列中的值的范圍。主鍵約束（PRIMARY KEY CONSTRAINTS）：要求主鍵的列上沒有兩行具有相同值，也沒有空值。唯一約束（UNIQUE CONSTRAINTS）：要求表中所有行在指定的列上沒有完全相同的列值。外鍵約束（FROEIGN KEY CONSTRAINTS）：要求正被插入或更新的列（外鍵）的新值，必須在被參照表（主表）的相應列（主鍵）中已經(jīng)存在。,6,不同的約束強制不同類型的數(shù)據(jù)完整性。表中 給出了

8、兩者的對應關(guān)系。,7,2.使用T-SQL語言創(chuàng)建、管理約束(1)使用CREATE TABLE語句創(chuàng)建約束使用CREATE TABLE語句創(chuàng)建約束的一般語法如下：CREATE TABLE table_name（column_name data_type[[CONSTRAINT constraint_name]{PRIMARY KEY [CLUSTERED | NONCLUSTERED]| UNIQUE [CLUSTERED

9、| NONCLUSTERED]| [FOREIGN KEY] REFERENCES ref_table [(ref_column) ]| DEFAULT constant_expression| CHECK(logical_expression)}] [,...n]),8,其中：table_name：創(chuàng)建約束所在的表的名稱。column_name：列名。 data_type：數(shù)據(jù)類型。constraint_name：約

10、束名。在創(chuàng)建、修改、實現(xiàn)約束時注意以下幾點：① 可以在已有的表上創(chuàng)建、修改、刪除約束，而不必刪除并重建表。② 可以在應用程序中創(chuàng)建錯誤檢查邏輯，測試是否違反約束。③ 在給表添加約束時，SQL將驗證表中已有數(shù)據(jù)是否滿足正在添加的約束。,9,⑵ 缺省約束缺省約束是指當向數(shù)據(jù)庫中的表插入數(shù)據(jù)時，如果用戶沒有明確給出某列的值時，SQL Server自動為該列輸入的值，缺省約束用于實現(xiàn)域的完整性。創(chuàng)建DEFAULT約束時應考慮以下因素

11、：DEFAULT約束只能用于INSERT語句不能用于具有IDENTITY屬性的列每列只能有一個DEFAULT約束【例1】 為數(shù)據(jù)庫JWGL中表teacher的SEX（性別）字段創(chuàng)建一個缺省約束，缺省值為1（男）USE JWGLGOALTER TABLE teacherADD CONSTRAINT default_sex DEFAULT 1 FOR SEX,10,⑶ 檢查約束檢查約束用來指定某列可取值的清

12、單、可取值的集合或可取值的范圍。檢查約束主要用于實現(xiàn)域完整性。創(chuàng)建CHECK約束時應考慮以下因素：當向數(shù)據(jù)庫中的表執(zhí)行插入或更新操作時，檢查插入的新列值是否滿足CHECK約束條件不能在具有IDENTITY屬性，或具有timestamp或uniqueidentifier數(shù)據(jù)類型的列上放置CHECK約束CHECK條件不能含有子查詢【例2】 為數(shù)據(jù)庫JWGL中表student_course 的GRADE（成績）字段創(chuàng)建一個檢查約束，

13、使得GRADE的值在0-100之間。USE JWGLGOALTER TABLE student_courseADD CONSTRAINT check_grade CHECK (grade>=0 and grade<=100),11,⑷ 主鍵約束主鍵約束保證某一列或一組列值的組合相對于表中的每一行都是唯一的，主鍵約束創(chuàng)建在表的主鍵列上，它對實現(xiàn)實體完整性更加有用。主鍵約束的作用就是為表創(chuàng)建主鍵。創(chuàng)建P

14、RIMARY KEY約束時應考慮以下因素：每個表只能有一個主鍵，并且必須有一個主鍵 ;不允許有空值;參照約束使用它作為維護參照完整性的參考點;創(chuàng)建主鍵時，在創(chuàng)建主鍵的列上創(chuàng)建了一個唯一索引，可以是聚簇索引，也可以是非聚簇索引，默認是聚簇索引?！纠?】 將數(shù)據(jù)庫JWGL中teacher表的teacher_id（教師號）字段設(shè)為主鍵。 USE JWGLGOALTER TABLE teacher ADD

15、CONSTRAINT PK_teacher_id PRIMARY KEY clustered (teacher_id),12,⑸ 唯一約束唯一約束限制表中指定列上所有的非空值必須唯一，即表中任意兩行在指定列上都不允許有相同的值。創(chuàng)建UNIQUE約束時應考慮以下因素：一個表可以放置多個UNIQUE約束允許有空值創(chuàng)建唯一索引時強制UNIQUE約束【例4】 將數(shù)據(jù)庫JWGL中teacher表的teacher_id（教師號

16、）字段設(shè)為唯一約束。 USE JWGLGOALTER TABLE department ADD CONSTRAINT unique_department_name UNIQUE nonclustered (department_name),13,唯一約束和主鍵約束的區(qū)別：唯一約束與主鍵約束都為指定的列建立唯一索引，即不允許唯一索引的列上有相同的值。主鍵約束限制更嚴格，不但不允許有重復值，而且也不允許有空值。 唯

17、一約束與主鍵約束產(chǎn)生的索引可以是聚簇索引也可以是非聚簇索引，但在缺省情況下唯一約束產(chǎn)生非聚簇索引，主鍵約束產(chǎn)生聚簇索引。⑹ 外鍵約束通過外鍵約束強制參照完整性。SQL Server提供了外鍵/主鍵值約束。即滿足以下兩點：① 存在外鍵時，被參照表中這一行不能刪除。② 向子表插入記錄或更新子表中外鍵值的前提是，必須保證這個外鍵值與主表中主鍵的某個值相等或者該外鍵值為空，否則不允許插入或修改外鍵值。,14,創(chuàng)建FOREIGN KEY約

18、束時應考慮以下因素：它提供一列或多列數(shù)據(jù)的參照完整性。FOREIGN KEY約束不自動創(chuàng)建索引。但如果考慮數(shù)據(jù)庫的多表連接，建議為外鍵創(chuàng)建一個索引，以提高連接性能主鍵與外鍵的數(shù)據(jù)類型和長度必須一致，或系統(tǒng)可轉(zhuǎn)換【例5】為表teacher創(chuàng)建外鍵department_id，外鍵department_id參考表department中的主鍵department_id。USE JWGLGOALTER TABLE teacher

19、ADD CONSTRAINT FK_department_id FOREIGN KEY (department_id)REFERENCES department(department_id)GO,15,3. 使用企業(yè)管理器管理約束創(chuàng)建缺省約束 創(chuàng)建缺省約束的具體步驟如下：① 啟動企業(yè)管理器。② 分別點擊“數(shù)據(jù)庫”、“JWGL”、“表”，顯示數(shù)據(jù)庫JWGL中所有的表。③ 右擊要設(shè)置缺省的表（假設(shè)要設(shè)置缺省的表為te

20、acher），在系統(tǒng)彈出的快捷菜單上點擊“設(shè)計表”，④ 點擊設(shè)置缺省的列，在下面列屬性設(shè)置欄的默認值輸入框中，輸入對應缺省值即可。 創(chuàng)建檢查約束創(chuàng)建主鍵約束創(chuàng)建唯一性約束 創(chuàng)建外鍵約束,16,5.4.4 使用默認值1. 默認值的定義默認值是數(shù)據(jù)庫對象之一，它指定在向數(shù)據(jù)庫中的表插入數(shù)據(jù)時，如果用戶沒有明確給出某列的值，SQL Server自動為該列使用此默認值。它是實現(xiàn)數(shù)據(jù)完整性的方法之一。在關(guān)系數(shù)據(jù)庫中，每個列都必須包

21、含有值，即使這個值是個空值。對于不接受空值的列，就必須為該列輸入某個非空值，要么由用戶明確輸入，要么由SQL Server輸入默認值。默認值可用于表中的列和用戶定義數(shù)據(jù)類型。請注意，缺省約束中使用的缺省值可以由用戶創(chuàng)建時輸入，也可以通過綁定已創(chuàng)建的默認值來指定。在創(chuàng)建默認值時，請考慮以下幾點：默認值需和它要綁定的列或用戶定義數(shù)據(jù)類型具有相同的數(shù)據(jù)類型。默認值需符合該列的所有規(guī)則。默認值缺省還需符合所有CHECK約束。創(chuàng)建、管

22、理默認值可以用T-SQL語言也可以用企業(yè)管理器,17,2. 使用T-SQL語言創(chuàng)建默認值：⑴ 創(chuàng)建默認值的語法 CREATE DEFAULT default_name AS constant_expression這里：default_name：新建默認值的名稱，它必須遵循SQL Server標識符的命名規(guī)則。constant_expression：默認值default_name的值，是一個常數(shù)表達式，在這個表達式中不能含有任

23、何列名或其他數(shù)據(jù)庫對象名，但可使用不涉及數(shù)據(jù)庫對象的SQL Server內(nèi)部函數(shù)。默認值創(chuàng)建后，可以使用系統(tǒng)存儲過程sp_helptext可以查看用于定義一個默認值的SQL腳本。其語法是： sp_helptext default_name,18,⑵ 默認值的綁定默認值創(chuàng)建之后，只有將其綁定到某個列或用戶自定義數(shù)據(jù)類型才能有效。使用系統(tǒng)存儲過程sp_bindefault可實現(xiàn)與表中的列及用戶自定義數(shù)據(jù)類型的綁定。一個

24、默認值可以綁定到多個列或用戶自定義數(shù)據(jù)類型。綁定默認值的語法是： sp_bindefault default_name, ‘object_name’其中：default_name ：由CREATE DEFAULT 語句創(chuàng)建的默認值名字，它將與指定的列或用戶定義數(shù)據(jù)類型相綁定。object_name ：要與該默認值相綁定的列名或用戶自定義數(shù)據(jù)類型名。如果指定的是表中的列，其格式為‘table_name.column’；否則

25、被認為是用戶自定義數(shù)據(jù)類型名。,19,默認值綁定的幾點考慮：① 綁定的默認值只適用于受INSERT語句影響的行。② 不能將默認值綁定到系統(tǒng)數(shù)據(jù)類型或timestamp列。③ 若綁定了一個默認值到一用戶定義數(shù)據(jù)類型，又給使用該數(shù)據(jù)類型的列綁定了一個不同的默認值或規(guī)則，則綁定到列的默認值和規(guī)則有效。⑶ 默認值綁定的解除一個默認值被綁定到表中的列或用戶自定義的數(shù)據(jù)類型之后，可使用系統(tǒng)存儲過程sp_unbinddefault解除其綁定

26、。綁定解除后，作為數(shù)據(jù)庫對象的默認值仍然存在于數(shù)據(jù)庫中。解除默認值綁定的語法是： sp_unbindefault ‘object_name’ object_name ：要解除和默認值綁定的表的列名或用戶自定義數(shù)據(jù)類型名。如果指定的是表中的列，其格式為“table_name.column”，否則被認為是用戶定義數(shù)據(jù)類型名。,20,⑷ 默認值綁定的刪除如果一個默認值不再使用了，可以用DROP語句將其刪除。刪除默

27、認值的語法是： DROP DEFAULT default_name default_name ：要刪除默認值的名稱。在刪除默認值時，應注意以下幾點：① DROP將默認值從數(shù)據(jù)庫中刪除。② 如果要刪除一個默認值，必須解除這個默認值的所有綁定，否則該默認值就不能被刪除掉。③ 在一個批中不能既定義默認值又同時使用默認值。④ 只有默認值的創(chuàng)建者可以刪除默認值。,21,【例6】 為數(shù)據(jù)庫JWGL創(chuàng)建一個名為profession_

28、default ，值為“講師”的默認值，并綁定到表teacher的profession列，然后解除這個綁定，綁定解除后將此默認值刪除。USE JWGLGOCREATE DEFAULT profession_default AS '講師'GOsp_bindefault profession_default , 'teacher.profession'GOsp_unbindefault

29、'teacher.profession'DROP DEFAULT profession_defaultGO,22,5.4.5 規(guī)則1. 規(guī)則的定義規(guī)則是數(shù)據(jù)庫對象之一。它指定向表的某列（或使用與該規(guī)則綁定的用戶定義數(shù)據(jù)類型的所有列）插入或更新數(shù)據(jù)時，限制輸入新值的取值范圍。一個規(guī)則可以是：值的清單或值的集合;值的范圍;必須滿足的單值條件;用like子句定義的編輯掩碼;規(guī)則是實現(xiàn)域完整性的方法之一。規(guī)

30、則用來驗證一個數(shù)據(jù)庫中的數(shù)據(jù)是否處于一個指定的值域范圍內(nèi)，是否與特定的格式相匹配。當數(shù)據(jù)庫中數(shù)據(jù)值被更新或被插入時，就要檢查新值是否遵循規(guī)則，如果不符合規(guī)則就拒絕執(zhí)行此更新或插入的操作。,23,規(guī)則可用于表中列或用戶定義數(shù)據(jù)類型。規(guī)則在實現(xiàn)功能上等同于CHECK約束。用CREATE RULE語句創(chuàng)建規(guī)則，然后用sp_bindrule把它綁定至一列或用戶定義的數(shù)據(jù)類型。使用系統(tǒng)存儲過程sp_helptext查看用于定義一個規(guī)則或缺省的S

31、QL腳本。其語法是： sp_helptext rule_name在創(chuàng)建規(guī)則時，應考慮以下幾點：① 缺省情況下，規(guī)則將檢查創(chuàng)建和綁定規(guī)則之前輸入的數(shù)據(jù)。② 規(guī)則可以綁定到一列、多列或數(shù)據(jù)庫中的用戶定義數(shù)據(jù)類型。③ 規(guī)則要求一個值在一定范圍內(nèi)，并與特定格式相匹配。④ 在一個列上至多有一個規(guī)則起作用，如果有多個規(guī)則與一列相綁定，那么只有最后綁定到該列的規(guī)則才是有效的。,24,2. 使用T-SQL語言創(chuàng)建、管理規(guī)則

32、⑴ 創(chuàng)建規(guī)則的語法是：CREATE RULE rule_name AS condition_expression rule_name：創(chuàng)建規(guī)則的名稱，應遵循SQL Server標識符的命名規(guī)范。 condition_expression：指明定義規(guī)則的條件，在這個條件表達式中不能包含列名或其他數(shù)據(jù)庫對象名，但它帶有一個@為前綴的參數(shù)（即參數(shù)的名字必須以@為第一個字符），也稱空間標志符( spaceholder)。意即這個規(guī)則被附

33、加到這個空間標志符，它只在規(guī)則定義中引用，為數(shù)據(jù)項值在內(nèi)存中保留空間，以便與規(guī)則作比較。⑵ 規(guī)則的綁定規(guī)則創(chuàng)建之后，只有將其綁定到某個列或用戶自定義數(shù)據(jù)類型才能有效。使用系統(tǒng)存儲過程sp_binderule可實現(xiàn)與表中的列及用戶自定義數(shù)據(jù)類型的綁定。綁定規(guī)則的語法是： sp_bindrule rule_name, object_name,25,其中：rule_name ：由CREATE RULE語句創(chuàng)建的規(guī)則名字，它將與指定的列

34、或用戶定義數(shù)據(jù)類型相捆綁。object_name ：指定要與該規(guī)則相綁定的列名或用戶定義數(shù)據(jù)類型名。如果指定的是表中的列，其格式為“table.column”，否則被認為是用戶定義數(shù)據(jù)類型名。綁定時應注意如下幾點：① 綁定的缺省只適用于受INSERT語句影響的行。② 綁定的規(guī)則只適用于受INSERT和UPDATE語句影響的行。③ 不能將缺省或規(guī)則綁定到系統(tǒng)數(shù)據(jù)類型或timestamp列。,26,⑶ 解除規(guī)則使用系統(tǒng)存儲過程s

35、p_unbindrule可以解除由sp_unbindrule綁定到列或用戶定義數(shù)據(jù)類型的規(guī)則。但被解除的規(guī)則仍然存在于數(shù)據(jù)庫中。 解除規(guī)則的語法如下： sp_unbindrule rule_name , object_name其中：rule_name：由CREATE RULE語句創(chuàng)建的規(guī)則名字，它將與指定的列或用戶定義數(shù)據(jù)類型相捆綁。object_name：指定要解除與該規(guī)則相綁定的列名或用戶定義數(shù)據(jù)類型名。如果

36、指定的是表中的列，其格式為table.column；否則被認為是用戶定義數(shù)據(jù)類型名。,27,⑷ 刪除規(guī)則使用DROP RULE可刪除由CREATE RULE命令創(chuàng)建的規(guī)則。刪除規(guī)則的語法如下： DROP RULE [owner.] rule_name其中：rule_name：要解除的規(guī)則。在刪除規(guī)則時，應注意以下幾點：① 使用DROP可將規(guī)則從數(shù)據(jù)庫中刪除。② 在刪除規(guī)則之前需先從列中或用戶定義數(shù)據(jù)類型中解除

37、綁定。③ 只有規(guī)則定義者可以刪除規(guī)則。,28,【例7】 為數(shù)據(jù)庫JWGL創(chuàng)建一個規(guī)則grade_rule ，grade_rule的值大于等于0，小于等于100，并綁定到表student_course的grade列，然后解除這個綁定，綁定解除后將此規(guī)則刪除。USE JWGLGOcreate rule grade_rule as @grade>=0 and @grade<=100GOsp_bindrule gra

38、de_rule , 'student_course.grade'GOsp_unbindrule 'student_course.grade'GOdrop rule grade_rule,29,5.4.6 使用標識列1. 標識列的定義 IDENTITY屬性使得某一列的取值是基于上一行的列值和為該列定義的步長自動生成的。IDENTITY列的值可以唯一地標識表中的一行，可以用于實現(xiàn)實體完整性。

39、有關(guān)DENTITY列屬性的特點及定義DENTITY列時的注意事項，本章第一節(jié)已經(jīng)介紹。2. 用T-SQL語言定義標識列⑴ 定義標識列的語法： ALETR TABLE table_name ADD column_name data_type IDENTITY(seed , increment),30,table_name：要添加標識列的表名。 column_name：要添加的標識列的名稱。 data_type：要添加