數(shù)據(jù)庫原理及應(yīng)用(13)第13章 數(shù)據(jù)庫完整性

1、第第13章數(shù)據(jù)庫完整性章數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性就是確保數(shù)據(jù)庫中的數(shù)據(jù)的一致性和正確性。數(shù)據(jù)庫完整性就是確保數(shù)據(jù)庫中的數(shù)據(jù)的一致性和正確性。本章主要討論約束、默認(rèn)和規(guī)則等內(nèi)容本章主要討論約束、默認(rèn)和規(guī)則等內(nèi)容 13.1 約約 束束設(shè)計(jì)表時(shí)需要識(shí)別列的有效值并決定如何強(qiáng)制實(shí)現(xiàn)列中設(shè)計(jì)表時(shí)需要識(shí)別列的有效值并決定如何強(qiáng)制實(shí)現(xiàn)列中數(shù)據(jù)的完整性。數(shù)據(jù)的完整性。SQL Server 2005提供多種強(qiáng)制數(shù)據(jù)完整性的提供多種強(qiáng)制數(shù)據(jù)完整性的機(jī)制：機(jī)制：l PRIMARY KEY約束約束l FOREIGN KEY約束約束l UNIQUE約束約束l CHECK約束約束l NOT NULL（非空性）（非空性）1

2、3.1.1 PRIMARY KEY約束約束PRIMARY KEY約束標(biāo)識(shí)列或列集，這些列或列集的值唯約束標(biāo)識(shí)列或列集，這些列或列集的值唯一標(biāo)識(shí)表中的行。一個(gè)一標(biāo)識(shí)表中的行。一個(gè)PRIMARY KEY約束可以：約束可以：l 作為表定義的一部分在創(chuàng)建表時(shí)創(chuàng)建。作為表定義的一部分在創(chuàng)建表時(shí)創(chuàng)建。l 添加到還沒有添加到還沒有PRIMARY KEY約束的表中（一個(gè)表只能約束的表中（一個(gè)表只能有一個(gè)有一個(gè)PRIMARY KEY約束）。約束）。l 如果已有如果已有PRIMARY KEY約束，則可對(duì)其進(jìn)行修改或刪約束，則可對(duì)其進(jìn)行修改或刪除。例如，可以使表的除。例如，可以使表的PRIMARY KEY約束引用其

3、他列，約束引用其他列，更改列的順序、索引名、聚集選項(xiàng)或更改列的順序、索引名、聚集選項(xiàng)或PRIMARY KEY約約束的填充因子。定義了束的填充因子。定義了PRIMARY KEY約束的列的列寬約束的列的列寬不能更改。不能更改?！纠纠?3.1】 給出以下程序的功能。給出以下程序的功能。USE testGOCREATE TABLE department/*部門表部門表*/(dno int PRIMARY KEY, /*部門號(hào)部門號(hào),為主鍵為主鍵*/dname char(20),/*部門名部門名*/)GO解：解：本程序在本程序在test數(shù)據(jù)庫中創(chuàng)建一個(gè)名為數(shù)據(jù)庫中創(chuàng)建一個(gè)名為department的表，

4、的表，其中指定其中指定dno為主鍵。為主鍵。 注意：若要使用注意：若要使用T-SQL修改修改PRIMARY KEY，必須先，必須先刪除現(xiàn)有的刪除現(xiàn)有的PRIMARY KEY約束，然后再用新定義重新創(chuàng)約束，然后再用新定義重新創(chuàng)建。建。13.1.2 FOREIGN KEY約束約束FOREIGN KEY約束稱為外鍵約束，用于標(biāo)識(shí)表之間的約束稱為外鍵約束，用于標(biāo)識(shí)表之間的關(guān)系，以強(qiáng)制參照完整性，即為表中一列或者多列數(shù)據(jù)提供關(guān)系，以強(qiáng)制參照完整性，即為表中一列或者多列數(shù)據(jù)提供參照完整性。參照完整性。FOREIGN KEY約束也可以參照自身表中的其約束也可以參照自身表中的其他列，這種參照稱為自參照。他列，

5、這種參照稱為自參照。FOREIGN KEY約束可以在下面情況下使用：約束可以在下面情況下使用：l 作為表定義的一部分在創(chuàng)建表時(shí)創(chuàng)建。作為表定義的一部分在創(chuàng)建表時(shí)創(chuàng)建。l 如果如果FOREIGN KEY約束與另一個(gè)表（或同一表）已約束與另一個(gè)表（或同一表）已有的有的PRIMARY KEY約束或約束或UNIQUE約束相關(guān)聯(lián)，則約束相關(guān)聯(lián)，則可向現(xiàn)有表添加可向現(xiàn)有表添加FOREIGN KEY約束。一個(gè)表可以有約束。一個(gè)表可以有多個(gè)多個(gè)FOREIGN KEY約束。約束。l 對(duì)已有的對(duì)已有的FOREIGN KEY約束進(jìn)行修改或刪除。例如，約束進(jìn)行修改或刪除。例如，要使一個(gè)表的要使一個(gè)表的FOREIGN

6、KEY約束引用其他列。定義約束引用其他列。定義了了FOREIGN KEY約束列的列寬不能更改。約束列的列寬不能更改?！纠纠?3.2】 給出以下程序的功能。給出以下程序的功能。USE testGOCREATE TABLE worker/*職工表職工表*/(no int PRIMARY KEY, /*編號(hào)編號(hào),為主鍵為主鍵*/name char(8),/*姓名姓名*/sex char(2),/*性別性別*/dno int /*部門號(hào)部門號(hào)*/FOREIGN KEY REFERENCES department(dno)ON DELETE NO ACTION,address char(30)/*地址

7、地址*/)GO解：解：該程序使用該程序使用FOREIGN KEY子句在子句在worker表中建表中建立了一個(gè)刪除約束，即立了一個(gè)刪除約束，即worker表的表的dno列（是一個(gè)外鍵）與列（是一個(gè)外鍵）與department表的表的dno列關(guān)聯(lián)。列關(guān)聯(lián)。使用使用FOREIGN KEY約束，還應(yīng)注意以下幾個(gè)問題：約束，還應(yīng)注意以下幾個(gè)問題：l 一個(gè)表中最多可以有一個(gè)表中最多可以有253個(gè)可以參照的表，因此每個(gè)表最多個(gè)可以參照的表，因此每個(gè)表最多可以有可以有253個(gè)個(gè)FOREIGN KEY約束。約束。l 在在FOREIGN KEY約束中，只能參照同一個(gè)數(shù)據(jù)庫中的表，約束中，只能參照同一個(gè)數(shù)據(jù)庫中的表

8、，而不能參照其他數(shù)據(jù)庫中的表。而不能參照其他數(shù)據(jù)庫中的表。l FOREIGN KEY子句中的列數(shù)目和每個(gè)列指定的數(shù)據(jù)類型子句中的列數(shù)目和每個(gè)列指定的數(shù)據(jù)類型必須和必須和REFERENCE子句中的列相同。子句中的列相同。l FOREIGN KEY約束不能自動(dòng)創(chuàng)建索引。約束不能自動(dòng)創(chuàng)建索引。l 參照同一個(gè)表中的列時(shí)，必須只使用參照同一個(gè)表中的列時(shí)，必須只使用REFERENCE子句，子句，而不能使用而不能使用FOREIGN KEY子句。子句。l 在臨時(shí)表中，不能使用在臨時(shí)表中，不能使用FOREIGN KEY約束。約束。13.1.3 UNIQUE約束約束UNIQUE約束在列集內(nèi)強(qiáng)制執(zhí)行值的唯一性。對(duì)于

9、約束在列集內(nèi)強(qiáng)制執(zhí)行值的唯一性。對(duì)于UNIQUE約束中的列，表中不允許有兩行包含相同的非空值。約束中的列，表中不允許有兩行包含相同的非空值。主鍵也強(qiáng)制執(zhí)行唯一性，但主鍵不允許空值，而且每個(gè)表中主主鍵也強(qiáng)制執(zhí)行唯一性，但主鍵不允許空值，而且每個(gè)表中主鍵只能有一個(gè)，但是鍵只能有一個(gè)，但是UNIQUE列卻可以有多個(gè)。列卻可以有多個(gè)。UNIQUE約束優(yōu)先于唯一索引。約束優(yōu)先于唯一索引。【例【例13.3】 給出一個(gè)示例說明給出一個(gè)示例說明UNIQUE約束的使用方法。約束的使用方法。解：解：以下程序在以下程序在test數(shù)據(jù)庫中創(chuàng)建了一個(gè)數(shù)據(jù)庫中創(chuàng)建了一個(gè)table5表，其中指表，其中指定了定了c1列不能包

10、含重復(fù)的值：列不能包含重復(fù)的值：USE testGOCREATE TABLE table5(cl int UNIQUE,c2 int)GOINSERT table5 VALUES(1,100)GO如果再插入一行：如果再插入一行：INSERT table5 VALUES(1,200)則會(huì)出現(xiàn)如圖則會(huì)出現(xiàn)如圖13.1所示的錯(cuò)誤消息。所示的錯(cuò)誤消息。13.1.4 CHECK約束約束CHECK約束通過限制用戶輸入的值來加強(qiáng)域完整性。它約束通過限制用戶輸入的值來加強(qiáng)域完整性。它指定應(yīng)用于列中輸入的所有值的布爾（取值為指定應(yīng)用于列中輸入的所有值的布爾（取值為TRUE或或FALSE）搜索條件，拒絕所有不取值

11、為搜索條件，拒絕所有不取值為TRUE的值?？梢詾槊苛兄付ǘ嗟闹??？梢詾槊苛兄付ǘ鄠€(gè)個(gè)CHECK約束。約束?！纠纠?3.4】 給出一個(gè)示例說明給出一個(gè)示例說明CHECK約束的使用方法。約束的使用方法。解：解：以下程序在以下程序在test數(shù)據(jù)庫中創(chuàng)建一個(gè)數(shù)據(jù)庫中創(chuàng)建一個(gè)table6表，其中使用表，其中使用CHECK約束來限定約束來限定f2列只能為列只能為0100分：分：USE testGOCREATE TABLE table6(f1 int,f2 int NOT NULL CHECK(f2=0 AND f2=100)GO當(dāng)執(zhí)行如下語句：當(dāng)執(zhí)行如下語句：INSERT table6 VALUES(1

12、,120)則會(huì)出現(xiàn)如圖則會(huì)出現(xiàn)如圖13.2所示的錯(cuò)誤消息。所示的錯(cuò)誤消息。13.1.5 列約束和表約束列約束和表約束約束可以是列約束或表約束：約束可以是列約束或表約束：l 列約束被指定為列定義的一部分，并且僅適用于那個(gè)列列約束被指定為列定義的一部分，并且僅適用于那個(gè)列（前面的（前面的score表中的約束就是列約束）。表中的約束就是列約束）。l 表約束的聲明與列的定義無關(guān)，可以適用于表中一個(gè)以表約束的聲明與列的定義無關(guān)，可以適用于表中一個(gè)以上的列。上的列。l 當(dāng)一個(gè)約束中必須包含一個(gè)以上的列時(shí)，必須使用表約當(dāng)一個(gè)約束中必須包含一個(gè)以上的列時(shí)，必須使用表約束。例如，如果一個(gè)表的主鍵內(nèi)有兩個(gè)或兩個(gè)以

13、上的列，束。例如，如果一個(gè)表的主鍵內(nèi)有兩個(gè)或兩個(gè)以上的列，則必須使用表約束將這兩列加入主鍵內(nèi)。則必須使用表約束將這兩列加入主鍵內(nèi)?！纠纠?3.5】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE testGOCREATE TABLE table7(c1 int,c2 int,c3 char(5),c4 char(10),CONSTRAINT c1 PRIMARY KEY(c1,c2)GOUSE testGOINSERT table7 VALUES(1,2,ABC1,XYZ1)INSERT table7 VALUES(1,2,ABC2,XYZ2)GOSELECT * FROM tabl

14、e7GO解：解：該程序在該程序在test數(shù)據(jù)庫中創(chuàng)建數(shù)據(jù)庫中創(chuàng)建table7表，它的主鍵為表，它的主鍵為c1和和c2。然后將其中插入兩個(gè)記錄（它們的。然后將其中插入兩個(gè)記錄（它們的c1和和c2列值相同），最列值相同），最后輸出這些記錄。執(zhí)行時(shí)錯(cuò)誤消息如圖后輸出這些記錄。執(zhí)行時(shí)錯(cuò)誤消息如圖13.3所示。所示。圖圖13.3 錯(cuò)誤消息錯(cuò)誤消息 在圖在圖13.3中單擊中單擊“結(jié)果結(jié)果”選項(xiàng)卡，看到如圖選項(xiàng)卡，看到如圖13.4所示的執(zhí)所示的執(zhí)行結(jié)果，從中看到，第行結(jié)果，從中看到，第2個(gè)個(gè)INSERT語句由于主鍵約束而沒有語句由于主鍵約束而沒有成功執(zhí)行。成功執(zhí)行。圖圖13.4 程序執(zhí)的結(jié)果程序執(zhí)的結(jié)果 1

15、3.2 默默 認(rèn)認(rèn) 值值如果在插入行時(shí)沒有指定列的值，則默認(rèn)值指定列中所如果在插入行時(shí)沒有指定列的值，則默認(rèn)值指定列中所使用的值。默認(rèn)值可以是任何取值為常量的對(duì)象。使用的值。默認(rèn)值可以是任何取值為常量的對(duì)象。在在SQL Server中，有兩種使用默認(rèn)值的方法：中，有兩種使用默認(rèn)值的方法：l 在創(chuàng)建表時(shí)，指定默認(rèn)值。如果使用在創(chuàng)建表時(shí)，指定默認(rèn)值。如果使用SQL Server管管理控制器，則可以在設(shè)計(jì)表時(shí)指定默認(rèn)值。如果使理控制器，則可以在設(shè)計(jì)表時(shí)指定默認(rèn)值。如果使用用T-SQL語言，則在語言，則在CREATE TABLE語句中使用語句中使用DEFAULT子句。這是首選的方法，也是定義默認(rèn)子句。

16、這是首選的方法，也是定義默認(rèn)值比較簡潔的方法。值比較簡潔的方法。l 使用使用CREATE DEFAULT語句創(chuàng)建默認(rèn)對(duì)象，然后語句創(chuàng)建默認(rèn)對(duì)象，然后使用存儲(chǔ)過程使用存儲(chǔ)過程sp_bindefault將該默認(rèn)對(duì)象綁定到列將該默認(rèn)對(duì)象綁定到列上。這是向前兼容的方法。上。這是向前兼容的方法。13.2.1 在創(chuàng)建表時(shí)指定默認(rèn)值在創(chuàng)建表時(shí)指定默認(rèn)值在使用在使用SQL Server管理控制器創(chuàng)建表時(shí)，可以為列指定默管理控制器創(chuàng)建表時(shí)，可以為列指定默認(rèn)值，默認(rèn)值可以是計(jì)算結(jié)果為常量的任何值，例如常量、內(nèi)認(rèn)值，默認(rèn)值可以是計(jì)算結(jié)果為常量的任何值，例如常量、內(nèi)置函數(shù)或數(shù)學(xué)表達(dá)式。置函數(shù)或數(shù)學(xué)表達(dá)式。在創(chuàng)建表時(shí)，

17、輸入列名稱后，設(shè)定該列的默認(rèn)值，如圖在創(chuàng)建表時(shí)，輸入列名稱后，設(shè)定該列的默認(rèn)值，如圖13.5所示，將所示，將student表性別列的默認(rèn)值設(shè)置為表性別列的默認(rèn)值設(shè)置為“男男”。【例【例13.6】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE testGOCREATE TABLE table8(c1 int,c2 int DEFAULT 2*5,c3 datetime DEFAULT getdate()GO-如下語句插入一行數(shù)據(jù)并顯示記錄。如下語句插入一行數(shù)據(jù)并顯示記錄。USE testGOINSERT table8(c1) VALUES(1)SELECT * FROM table8G

18、O13.2.2 使用默認(rèn)對(duì)象使用默認(rèn)對(duì)象默認(rèn)對(duì)象是單獨(dú)存儲(chǔ)的，刪除表的時(shí)候，默認(rèn)對(duì)象是單獨(dú)存儲(chǔ)的，刪除表的時(shí)候，DEFAULT約束約束會(huì)自動(dòng)刪除，但是默認(rèn)對(duì)象不會(huì)被刪除。另外，創(chuàng)建默認(rèn)對(duì)會(huì)自動(dòng)刪除，但是默認(rèn)對(duì)象不會(huì)被刪除。另外，創(chuàng)建默認(rèn)對(duì)象后，需要將其綁定到某列或者用戶自定義的數(shù)據(jù)類型上。象后，需要將其綁定到某列或者用戶自定義的數(shù)據(jù)類型上。1創(chuàng)建默認(rèn)對(duì)象創(chuàng)建默認(rèn)對(duì)象可以使用可以使用CREATE DEFAULT語句創(chuàng)建默認(rèn)對(duì)象。其語法語句創(chuàng)建默認(rèn)對(duì)象。其語法格式如下：格式如下：CREATE DEFAULT default AS constant_exprion例如，使用下面的例如，使用下面的SQ

19、L語句創(chuàng)建語句創(chuàng)建con3默認(rèn)對(duì)象：默認(rèn)對(duì)象：USE testGOCREATE DEFAULT con3 AS 10 /*默認(rèn)值設(shè)為默認(rèn)值設(shè)為10*/GO2綁定默認(rèn)對(duì)象綁定默認(rèn)對(duì)象默認(rèn)對(duì)象創(chuàng)建后不能使用，必須首先將其綁定到某列或默認(rèn)對(duì)象創(chuàng)建后不能使用，必須首先將其綁定到某列或者用戶自定義的數(shù)據(jù)類型上。綁定過程可以使用者用戶自定義的數(shù)據(jù)類型上。綁定過程可以使用sp_bindefault存儲(chǔ)過程來完成。其使用語法格式如下：存儲(chǔ)過程來完成。其使用語法格式如下：sp_bindefault defname = default, objname = object_name , futureonly = f

20、utureonly_flag例如，上面將例如，上面將con3默認(rèn)對(duì)象綁定到默認(rèn)對(duì)象綁定到test數(shù)據(jù)庫的數(shù)據(jù)庫的table8表的表的c1列上的操作過程可以使用下面的列上的操作過程可以使用下面的T-SQL語句來完成：語句來完成：USE test GO EXEC sp_bindefault con3,table8.c1 GO3重命名默認(rèn)對(duì)象重命名默認(rèn)對(duì)象和其他的數(shù)據(jù)庫對(duì)象一樣，也可以重命名默認(rèn)對(duì)象。重和其他的數(shù)據(jù)庫對(duì)象一樣，也可以重命名默認(rèn)對(duì)象。重命名默認(rèn)對(duì)象也是使用命名默認(rèn)對(duì)象也是使用sp_rename存儲(chǔ)過程來完成的。例如，存儲(chǔ)過程來完成的。例如，以下以下T-SQL語句將默認(rèn)對(duì)象語句將默認(rèn)對(duì)象

21、con3的名稱改為的名稱改為con4：USE test GO EXEC sp_rename con3,con4 GO4解除默認(rèn)對(duì)象的綁定解除默認(rèn)對(duì)象的綁定可以使用可以使用sp_unbindefault存儲(chǔ)過程來解除綁定，其語法格存儲(chǔ)過程來解除綁定，其語法格式如下：式如下：sp_unbindefault objname = object_name ,futureonly = futureonly_flag例如，下面的例如，下面的SQL語句解除語句解除test數(shù)據(jù)庫中數(shù)據(jù)庫中table8表表c1列上的列上的默認(rèn)值綁定：默認(rèn)值綁定：USE test GO EXEC sp_unbindefault t

22、able8.c1 GO對(duì)應(yīng)的消息如下：對(duì)應(yīng)的消息如下：已解除了表列與其默認(rèn)值之間的綁定。已解除了表列與其默認(rèn)值之間的綁定。5刪除默認(rèn)對(duì)象刪除默認(rèn)對(duì)象在刪除默認(rèn)對(duì)象之前，首先要確認(rèn)默認(rèn)對(duì)象已經(jīng)解除綁在刪除默認(rèn)對(duì)象之前，首先要確認(rèn)默認(rèn)對(duì)象已經(jīng)解除綁定。刪除默認(rèn)對(duì)象使用定。刪除默認(rèn)對(duì)象使用DROP DEFAULT語句，其語法格式語句，其語法格式如下：如下：DROP DEFAULT default ,n其中，其中，“default”是現(xiàn)有默認(rèn)值的名稱。若要查看現(xiàn)有默認(rèn)是現(xiàn)有默認(rèn)值的名稱。若要查看現(xiàn)有默認(rèn)值的列表，可以執(zhí)行值的列表，可以執(zhí)行sp_help存儲(chǔ)過程。例如，以下存儲(chǔ)過程。例如，以下T-SQL

23、語語句用于刪除默認(rèn)對(duì)象句用于刪除默認(rèn)對(duì)象con4：USE test GO DROP DEFAULT con4 GO13.3 規(guī)規(guī) 則則規(guī)則限制了可以存儲(chǔ)在表中或者用戶定義數(shù)據(jù)類型的值，規(guī)則限制了可以存儲(chǔ)在表中或者用戶定義數(shù)據(jù)類型的值，它可以使用多種方式來完成對(duì)數(shù)據(jù)值的檢驗(yàn)，可以使用函數(shù)它可以使用多種方式來完成對(duì)數(shù)據(jù)值的檢驗(yàn)，可以使用函數(shù)返回驗(yàn)證信息，也可以使用關(guān)鍵字返回驗(yàn)證信息，也可以使用關(guān)鍵字BETWEEN、LIKE和和IN完成對(duì)輸入數(shù)據(jù)的檢查。完成對(duì)輸入數(shù)據(jù)的檢查。當(dāng)將規(guī)則綁定到列或者用戶定義數(shù)據(jù)類型時(shí)，規(guī)則將指當(dāng)將規(guī)則綁定到列或者用戶定義數(shù)據(jù)類型時(shí)，規(guī)則將指定可以插入到列中的可接受的值。

24、規(guī)則是作為一個(gè)獨(dú)立的數(shù)定可以插入到列中的可接受的值。規(guī)則是作為一個(gè)獨(dú)立的數(shù)據(jù)庫對(duì)象存在，表中每列或者每個(gè)用戶定義數(shù)據(jù)類型只能和據(jù)庫對(duì)象存在，表中每列或者每個(gè)用戶定義數(shù)據(jù)類型只能和一個(gè)規(guī)則綁定。一個(gè)規(guī)則綁定。13.3.1 創(chuàng)建規(guī)則創(chuàng)建規(guī)則創(chuàng)建規(guī)則使用創(chuàng)建規(guī)則使用CREATE RULE語句，其語法格式如下：語句，其語法格式如下：CREATE RULE 規(guī)則名規(guī)則名 AS condition_exprion【例【例13.8】 給出以下程序的功能。給出以下程序的功能。USE test GO CREATE RULE rule1 AS c1 BETWEEN 0 and 10 GO解：解：該程序創(chuàng)建一個(gè)名為該程序創(chuàng)建一個(gè)名為rule1的規(guī)則，限定輸入的值必的規(guī)則，限定輸入的值必須在須在010之間。之間。13.3.2 綁定規(guī)則綁定規(guī)則要使用規(guī)則，必須首先將其和列或者用戶定義數(shù)據(jù)類型綁要使用規(guī)則，必須首先將其和列或者用戶定義數(shù)據(jù)類型綁定?？梢允褂枚?。可以使用sp_bindrule存儲(chǔ)過程，也可以使用存儲(chǔ)過程，也可以使用SQL Server管管理控制器。理控制器。使用使用SQL Server管理控制器綁定規(guī)則的操作步驟和綁定默管理控制器綁定規(guī)則的操作步驟和綁定默認(rèn)對(duì)