數(shù)據(jù)庫技術(shù)及應(yīng)用：第6章 索引與數(shù)據(jù)完整性

第6章索引與數(shù)據(jù)完整性6.1索引6.3.1數(shù)據(jù)完整性的分類1．域完整性域完整性又稱為列完整性，指給定列輸入的有效性。【例6.15】定義表KC的同時定義學(xué)分的約束條件。USEXSCJCREATETABLEKC( 課程號char(6)NOTNULL,

課程名char(8)NOTNULL,

學(xué)分tinyintCHECK(學(xué)分>=0AND學(xué)分<=10)NUL/*通過CHECK子句定義約束條件*/

備注textNULL)GO2．實(shí)體完整性實(shí)體完整性又稱為行的完整性，要求表中有一個主鍵，其值不能為空且能唯一地標(biāo)識對應(yīng)的記錄。3．參照完整性參照完整性又稱為引用完整性。6.3.1數(shù)據(jù)完整性的分類XS和XS_KC表的對應(yīng)關(guān)系如表6.2、6.3所示。(1)從表不能引用不存在的鍵值。如果主表中的鍵值更改了，那么在整個數(shù)據(jù)庫中，對從表中鍵值的所有引用要進(jìn)行一致的更改。如果主表中沒有關(guān)聯(lián)的記錄，則不能將記錄添加到從表。6.3.2域完整性的實(shí)現(xiàn)1．CHECK約束的定義與刪除對于TimeStamp和Identity兩種類型字段不能定義CHECK約束。

(1)通過企業(yè)管理器創(chuàng)建與刪除CHECK約束第1步在XS_KC表設(shè)計器界面右擊鼠標(biāo)，出現(xiàn)如圖6.8所示的快捷菜單；6.3.2域完整性的實(shí)現(xiàn)第2步選擇CHECK約束菜單項(xiàng)進(jìn)入如圖6.9所示的CHECK選項(xiàng)卡屬性窗口；第3步選擇“新建”，進(jìn)入CHECK約束的輸入窗口，輸入約束表達(dá)式“成績>=0and成績<=100”，如圖6.10所示，選擇“關(guān)閉”。6.3.3實(shí)體完整性的實(shí)現(xiàn)PRIMARYKEY約束與UNIQUE約束的主要區(qū)別如下：(1)一個數(shù)據(jù)表只能創(chuàng)建一個PRIMARYKEY約束，但一個表中可根據(jù)需要對不同的列創(chuàng)建若干個UNIQUE約束；(2)PRIMARYKEY字段的值不允許為NULL，而UNIQUE字段的值可取NULL；(3)一般創(chuàng)建PRIMARYKEY約束時，系統(tǒng)會自動產(chǎn)生索引，索引的缺省類型為簇索引。PRIMARYKEY約束與UNIQUE約束的相同點(diǎn)在于：二者均不允許表中對應(yīng)字段存在重復(fù)值。利用企業(yè)管理器創(chuàng)建和刪除PRIMARYKEY約束（1）利用企業(yè)管理器創(chuàng)建PRIMARYKEY約束如果要對XS表按學(xué)號建立PRIMARYKEY約束，應(yīng)如何操作呢？可以按下列步驟進(jìn)行：6.3.3實(shí)體完整性的實(shí)現(xiàn)第1步選擇XS表圖標(biāo)，右擊，打開XS表的表設(shè)計器，進(jìn)入如圖6.16所示的表設(shè)計器界面。第2步選中“學(xué)號”對應(yīng)的這一行，選擇主鍵圖標(biāo)，這樣在“學(xué)號”對應(yīng)的這一行前面，將出現(xiàn)一主鍵圖標(biāo)。6.3.3實(shí)體完整性的實(shí)現(xiàn)（2）利用企業(yè)管理器刪除PRIMARYKEY約束如果要刪除對表XS中對學(xué)號字段建立的PRIMARYKEY約束，按如下步驟進(jìn)行：第1步選擇XS表圖標(biāo)，右擊，打開XS表設(shè)計器，進(jìn)入如圖6.17所示的表設(shè)計器界面。第2步選中XS表設(shè)計器中主鍵對應(yīng)的行，選擇工具欄的“設(shè)置主鍵圖標(biāo)”，則取消了原來定義的主鍵。2．利用企業(yè)管理器創(chuàng)建和刪除UNIQUE約束（1）利用企業(yè)管理器創(chuàng)建UNIQUE約束如果要對XS表中的“身份證號碼”列創(chuàng)建UNIQUE約束，以保證該列取值的唯一性，可按以下步驟進(jìn)行：第1步選擇XS表圖標(biāo)，右擊，打開XS表設(shè)計器，在表設(shè)計器中右擊，出現(xiàn)如圖6.18所示的快捷菜單。第2步選擇“屬性”菜單項(xiàng)，出現(xiàn)屬性界面，在此界面中選擇“索引/鍵”選項(xiàng)卡，如圖6.19所示。6.3.3實(shí)體完整性的實(shí)現(xiàn)第3步在圖6.19的屬性界面中選擇“新建”，輸入新建索引的名字或使用系統(tǒng)缺省名，在“列名”下拉表中選擇“身份證號碼”，并設(shè)置索引順序，將“創(chuàng)建UNIQUE約束”單選按鈕設(shè)置為選中狀態(tài)，如圖6.20所示。6.3.3實(shí)體完整性的實(shí)現(xiàn)第3步選擇“關(guān)閉”按鈕。（2）利用企業(yè)管理器刪除UNIQUE約束進(jìn)入如圖6.20所示的屬性窗口，在“選定的索引”下拉框中選擇要刪除的UNIQUE約束的索引名，再點(diǎn)擊“刪除”按鈕即刪除了指定的UNIQUE約束。6.3.3實(shí)體完整性的實(shí)現(xiàn)3．利用SQL命令創(chuàng)建及刪除PRIMARYKEY約束或UNIQUE約束（1）創(chuàng)建表的同時創(chuàng)建PRIMARYKEY約束或UNIQUE約束語法格式：CREATETABLEtable_name /*指定表名*/(column_namedatatype /*定義字段*/[CONSTRAINTconstraint_name] /*約束名*/

NOTNULLPRIMARYKEY｜UNIQUE /*定義約束類型*/[CLUSTERED|NONCLUSTERED] /*定義約束的索引類型*/ [,

…n]

) /*n表示可定義多個字段*/(2)通過修改表創(chuàng)建PRIMARYKEY約束或UNIQUE約束創(chuàng)建PRIMARYKEY約束語法格式：ALTERTABLEtable_nameADD

[CONSTRAINTconstraint_name]

PRIMARYKEYCLUSTERED|NONCLUSTERED(column[,...n])6.3.3實(shí)體完整性的實(shí)現(xiàn)【例6.24】對XSCJ數(shù)據(jù)庫中XS表的學(xué)號字段創(chuàng)建PRIMARYKEY約束，對身份證號碼字段定義UNIQUE約束。USEXSCJCREATETABLEXS(學(xué)號char(6)NOTNULLCONSTRAINTXH_PKPRIMARYKEY,

姓名char(8)NOTNULL,

身份證號碼char(20)CONSTRAINTSH_UKUNIQUE,

專業(yè)名char(10)NULL,

性別bitNOTNULL,

出生時間smalldatetimeNOTNULL,

總學(xué)分tinyintNULL,

備注textNULL,

入學(xué)日期datetimeCONSTRAINTdatedfltdefaultgetdate() /*定義默認(rèn)值約束*/)6.3.3實(shí)體完整性的實(shí)現(xiàn)【例6.25】先在XSCJ數(shù)據(jù)庫中創(chuàng)建表XS，然后通過修改表，對學(xué)號字段創(chuàng)建PRIMARYKEY約束，對身份證號碼字段定義UNIQUE約束。

USEXSCJCREATETABLEXS( 學(xué)號char(6)NOTNULL,

姓名char(8)NOTNULL,

身份證號碼char(20),

專業(yè)名char(10)NULL,

性別bitNOTNULL,

出生時間smalldatetimeNOTNULL,

總學(xué)分tinyintNULL,

備注textNULL,

入學(xué)日期datetimeCONSTRAINTdatedfltdefaultgetdate() /*定義默認(rèn)值約束*/)GOALTERTABLEXSADD

CONSTRAINTXS_PKPRIMARYKEYCLUSTERED(學(xué)號)GOALTERTABLEXSADD

CONSTRAINTXS_UK

UNIQUENONCLUSTEREDGO6.3.3實(shí)體完整性的實(shí)現(xiàn)（3）刪除PRIMARYKEY約束或UNIQUE約束語法格式：ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name[,…n]【例6.26】刪除前面例中創(chuàng)建的PRIMARYKEY約束和UNIQUE約束。ALTERTABLEXSDROP

CONSTRAINTXS_PK,XS_UKGO6.3.4參照完整性的實(shí)現(xiàn)1．利用企業(yè)管理器定義表間的參照關(guān)系例如：要建立XS表與XS_KC表之間的參照完整性，操作步驟如下：第1步按照前面所介紹的方法定義主表的主碼（或唯一碼）。在此，定義XS表中的學(xué)號字段為主碼。第2步選擇企業(yè)管理器目錄樹中XSCJ數(shù)據(jù)庫目錄下的“關(guān)系圖”圖標(biāo)右擊，出現(xiàn)如圖6.21所示的快捷菜單。第3步選擇菜單項(xiàng)“新建數(shù)據(jù)庫關(guān)系圖”，進(jìn)入如圖6.22所示的歡迎界面。6.3.4參照完整性的實(shí)現(xiàn)第4步選擇歡迎界面中的“下一步”按鈕，進(jìn)入如圖6.23所示“創(chuàng)建關(guān)系圖向?qū)А钡慕缑?，從可用表中選擇要添加到關(guān)系圖中的表，本例中選擇了XS表和XS_KC表。第5步點(diǎn)擊“下一步”按鈕，并按提示進(jìn)行后繼操作，進(jìn)入如圖6.24所示的關(guān)系圖界面。6.3.4參照完整性的實(shí)現(xiàn)第6步在關(guān)系圖上，將鼠標(biāo)指向主表的主鍵并拖動到從表。對于本例：將XS表中的學(xué)號字段拖動到從表XS_KC，出現(xiàn)如圖6.25所示的關(guān)系設(shè)置界面。第7步退出圖6.26的關(guān)系圖界面，并根據(jù)提示，將關(guān)系圖的有關(guān)信息存盤，即創(chuàng)建了主表與從表之間的參照關(guān)系。6.3.4參照完整性的實(shí)現(xiàn)2．利用企業(yè)管理器刪除表間的參照關(guān)系第1步進(jìn)入XS表設(shè)計器右擊，出現(xiàn)一快捷菜單如圖6.27所示課程類別。第2步選擇菜單項(xiàng)“關(guān)系”，出現(xiàn)如圖6.28所示的屬性界面。第3步在圖6.28的屬性界面的關(guān)系下拉表中選擇要刪除的關(guān)系，然后點(diǎn)擊“刪除鍵”，選擇“關(guān)閉”按鈕。6.3.4參照完整性的實(shí)現(xiàn)3．利用SQL命令定義表間的參照關(guān)系前面已介紹了創(chuàng)建主碼（PRMARYKEY約束）及唯一碼（UNIQUE約束）的方法，在此將介紹通過SQL命令創(chuàng)建外碼的方法。（1）創(chuàng)建表的同時定義外碼約束語法格式：

CREATETABLEtable_name /*指定表名*/(column_namedatatype[FOREIGNKEY]REFERENCESref_table(ref_column)[,

…n]

) /*n表示可定義多個字段*/（2）通過修改表定義外碼約束語法格式：ALTERTABLEtable_nameADD

[

CONSTRAINTconstraint_name]FOREIGNKEY

(column[,...n])REFERENCESref_table(ref_column[,...n])

6.3.4參照完整性的實(shí)現(xiàn)【例6.27】在XSCJ數(shù)據(jù)庫中創(chuàng)建主表XS，XS.學(xué)號為主鍵，然后定義從表XS_KC，XS_KC.學(xué)號為外碼。USEXSCJCREATETABLEXS( 學(xué)號char(6)NOTNULL CONSTRAINTXH_PKPRIMARYKEY,

姓名char(8)NOTNULL,

專業(yè)名char(10)NULL,

性別bitNOTNULL,

出生時間smalldatetimeNOTNULL,

總學(xué)分tinyintNULL,

備注textNULL)GOCREATETABLEXS_KC( 學(xué)號char(6)NOTNULLFOREIGNKEY

REFERENCESXS(學(xué)號),

課程號char(3)NOTNULL,

成績smallint,

學(xué)分smallint)GO6.3.4參照完整性的實(shí)現(xiàn)【例6.28】假設(shè)XSCJ數(shù)據(jù)庫中KC表為主表，KC.課程號字段已定義為主鍵。XS_KC表為從表，如下示例用于將XS_KC.課程號字段定義為外碼。USEXSCJALTERTABLEXS_KC ADD

CONSTRAINTkc_foreign

FOREIGNKEY

(課程號)

REFERENCESKC(課程號)

GO4．利用SQL命令刪除表間的參照關(guān)系刪除表間的參照關(guān)系，實(shí)際上刪除從表的外碼約束即可。語法格式與前面其它約束刪除的格式同?！纠?.29】刪除上例對XS_KC.課程號字段定義的外碼約束。USEXSCJ ALTERTABLEXS_KC DROPCONSTRAINTkc_foreignGO7.2.1利用SQL命令創(chuàng)建觸發(fā)器舉例【例7.11】對于XSCJ數(shù)據(jù)庫，如果在XS表中添加或更改數(shù)據(jù)，則將向客戶端顯示一條信息。/*使用帶有提示消息的觸發(fā)器*/USEXSCJIFEXISTS(SELECTnameFROMsysobjects WHEREname='reminder'ANDtype='TR')DROPTRIGGERreminderGOCREATETRIGGERreminderONXS FORINSERT,UPDATEASRAISERROR(4008,16,10)GO7.2.1利用SQL命令創(chuàng)建觸發(fā)器使用觸發(fā)器的限制使用觸發(fā)器有下列限制：(1)CREATETRIGGER必須是批處理中的第一條語句，并且只能應(yīng)用到一個表中。(2)觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫中創(chuàng)建，但觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫的外部對象。(3)如果指定觸發(fā)器所有者名限定觸發(fā)器，要以相同的方式限定表名。(4)在同一CREATETRIGGER語句中，可以為多種操作（如INSERT和UPDATE）定義相同的觸發(fā)器操作。(5)一個表的外鍵在DELETE、UPDATE操作上定義了級聯(lián)，不能在該表上定義INSTEADOFDELETE、INSTEADOFUPDATE觸發(fā)器。7.2.1利用SQL命令創(chuàng)建觸發(fā)器(7)在觸發(fā)器內(nèi)可以指定任意的SET語句，所選擇的SET選項(xiàng)在觸發(fā)器執(zhí)行期間有效，并在觸發(fā)器執(zhí)行完后恢復(fù)到以前的設(shè)置。

(8)觸發(fā)器中不允許包含以下T-SQL語句：CREATEDATABASE、ALTERDATABASE、LOADDATABASE、RESTOREDATABASE、DROPDATABASE、LOADLOG、RESTORELOG、DISKINIT、DISKRESIZE和RECONFIGURE(9)觸發(fā)器不能返回任何結(jié)果，為了阻止從觸發(fā)器返回結(jié)果，不要在觸發(fā)器定義中包含SELECT語句或變量賦值。4．權(quán)限CREATETRIGGER權(quán)限默認(rèn)授予定義觸發(fā)器的表所有者、sysadmin固定服務(wù)器角色成員、db_owner和db_ddladmin固定數(shù)據(jù)庫角色成員，并且不可轉(zhuǎn)讓。7.2.1利用SQL命令創(chuàng)建觸發(fā)器【例7.12】在數(shù)據(jù)庫XSCJ中創(chuàng)建一觸發(fā)器，當(dāng)向XS_KC表插入一記錄時，檢查該記錄的學(xué)號在XS表是否存在，檢查課程號在KC表中是否存在，若有一項(xiàng)為否，則不允許插入。USEXSCJIFEXISTS(SELECTnameFROMsysobjectsWHEREname='check_trig'ANDtype='TR')DROPTRIGGERcheck_trigGOCREATETRIGGERcheck_trig ONXS_KC FORINSERTASSELECT* FROMinserteda WHEREa.學(xué)號NOTIN(SELECTb.學(xué)號FROMXSb)ORa.課程號NOTIN(SELECTc.課程號FROMKCc)BEGIN RAISERROR('違背數(shù)據(jù)的一致性.',16,1) ROLLBACKTRANSACTIONEND7.2.2利用企業(yè)管理器創(chuàng)建觸發(fā)器

步驟如下：第1步展開服務(wù)器組，然后展開服務(wù)器。第2步展開“數(shù)據(jù)庫”文件夾，展開將定義觸發(fā)器的表所屬的數(shù)據(jù)庫，然后單擊“表”文件夾。第3步選擇將在其上創(chuàng)建觸發(fā)器的表右擊，出現(xiàn)快捷菜單，選擇“所有任務(wù)”菜單項(xiàng)下的“管理觸發(fā)器”子菜單項(xiàng)，如圖7.1所示。執(zhí)行該命令后，進(jìn)入如圖7.2所示的界面。第4步在“名稱”中，單擊“新建”，在“文本”框中輸入觸發(fā)器文本。若要檢查語法，單擊“檢查語法”命令。7.2.3觸發(fā)器的修改1．利用SQL命令修改觸發(fā)器語法格式：ALTERTRIGGERtrigger_nameON(table|view)[WITHENCRYPTION]{

{(FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}

[NOTFORREPLICATION]

AS

sql_statement[

...n]

}

|{(FOR|AFTER|INSTEADOF){[INSERT][,][UPDATE]}

[NOTFORREPLICATION]

AS

{IFUPDATE(column)

[{AND|OR}UPDA