高職數(shù)據(jù)庫第三章數(shù)據(jù)完整性和索引

1、SQL SERVER 2005 SQL SERVER 2005 案例教程案例教程一、數(shù)據(jù)完整性分類一、數(shù)據(jù)完整性分類 1、實(shí)體完整性（、實(shí)體完整性（Entity Integrity）2、域完整性（、域完整性（Domain Integrity）3、參照完整性（、參照完整性（Referential Integrity）4、用戶定義的完整性（、用戶定義的完整性（User-defined Integrity）二、實(shí)施數(shù)據(jù)完整性的方法二、實(shí)施數(shù)據(jù)完整性的方法 1、用定義約束實(shí)現(xiàn)數(shù)據(jù)完整性、用定義約束實(shí)現(xiàn)數(shù)據(jù)完整性通過針對表和字段定義聲明的約束，將數(shù)據(jù)完整性成為數(shù)據(jù)定通過針對表和字段定義聲明的約束，將數(shù)據(jù)

2、完整性成為數(shù)據(jù)定義的一部分，使用約束實(shí)現(xiàn)數(shù)據(jù)完整性。義的一部分，使用約束實(shí)現(xiàn)數(shù)據(jù)完整性。2、用編寫程序?qū)崿F(xiàn)數(shù)據(jù)完整性、用編寫程序?qū)崿F(xiàn)數(shù)據(jù)完整性可以使用存儲過程或觸發(fā)器實(shí)施程序化數(shù)據(jù)完整性可以使用存儲過程或觸發(fā)器實(shí)施程序化數(shù)據(jù)完整性 。3、選取數(shù)據(jù)完整性的實(shí)施方法、選取數(shù)據(jù)完整性的實(shí)施方法l由于約束在由于約束在SQL SERVER的可執(zhí)行部分有一段代碼路徑的可執(zhí)行部分有一段代碼路徑,執(zhí)執(zhí)行速度比其它方式快，所以應(yīng)優(yōu)先選用約束。行速度比其它方式快，所以應(yīng)優(yōu)先選用約束。l需要在多個表之間保持?jǐn)?shù)據(jù)的一致性，可選擇使用約束中的需要在多個表之間保持?jǐn)?shù)據(jù)的一致性，可選擇使用約束中的主鍵和外鍵約束；主鍵和外鍵

3、約束；l需要特別復(fù)雜的完整性檢驗(yàn)需要特別復(fù)雜的完整性檢驗(yàn),則使用觸發(fā)器和存儲過程則使用觸發(fā)器和存儲過程 一、一、 主鍵約束（主鍵約束（Primary Key Constraint）1、主鍵約束：、主鍵約束：主鍵是能夠唯一標(biāo)識表中某一行的屬性或?qū)傩越M主鍵是能夠唯一標(biāo)識表中某一行的屬性或?qū)傩越M2、使用、使用SSMS創(chuàng)建主鍵創(chuàng)建主鍵 在創(chuàng)建表結(jié)構(gòu)的時候，選定列名或列名組合在創(chuàng)建表結(jié)構(gòu)的時候，選定列名或列名組合,再單擊工具欄處的小鑰匙圖標(biāo)再單擊工具欄處的小鑰匙圖標(biāo) 。【例【例 3.5】將】將CJGL數(shù)據(jù)庫中數(shù)據(jù)庫中XS表表XSDM（學(xué)生（學(xué)生代碼）設(shè)置為主鍵代碼）設(shè)置為主鍵3、使用、使用Transac

4、t-SQL語言創(chuàng)建主鍵語言創(chuàng)建主鍵 （1） 建立表文件的同時建立主鍵約束建立表文件的同時建立主鍵約束CREATE TABLE 表名稱表名稱（字段屬性定義，（字段屬性定義，CONSTRAINT 約束名約束名 PRIMARY KEY (列名或列名組合列名或列名組合) （2）修改表結(jié)構(gòu)時添加主鍵約束）修改表結(jié)構(gòu)時添加主鍵約束ALTER TABLE 表名稱表名稱 ADD CONSTRAINT 約束名約束名 PRIMARY KEY(列名或列名或列名組合列名組合)【例【例 3.6】 在在CJGL數(shù)據(jù)庫中建立數(shù)據(jù)庫中建立XS2表表,有有XSDM (學(xué)學(xué)生代碼生代碼),XH(學(xué)號學(xué)號)，XM(姓名姓名)，CS

5、RQ(出生日期出生日期)四四個字段，定義個字段，定義XSDM為主鍵。為主鍵。 【例【例 3.7】將】將CJGL數(shù)據(jù)庫中已經(jīng)建立結(jié)構(gòu)的數(shù)據(jù)庫中已經(jīng)建立結(jié)構(gòu)的BJ(班級班級)表中字段表中字段BJDM(班級代碼班級代碼)設(shè)置主鍵，避免不同班級設(shè)置主鍵，避免不同班級輸入相同編號或者某個班級沒有班級編號輸入相同編號或者某個班級沒有班級編號 【例【例 3.8】將】將CJGL數(shù)據(jù)庫中已經(jīng)建立的數(shù)據(jù)表數(shù)據(jù)庫中已經(jīng)建立的數(shù)據(jù)表CJ（成（成績）基于字段績）基于字段XSDM（學(xué)生代碼）、（學(xué)生代碼）、KCDM（課程（課程代碼）代碼） 和和XQDM（學(xué)期代碼）三個字段組合設(shè)置主（學(xué)期代碼）三個字段組合設(shè)置主鍵，能夠唯

6、一區(qū)別出成績值所對應(yīng)學(xué)期、學(xué)生和課鍵，能夠唯一區(qū)別出成績值所對應(yīng)學(xué)期、學(xué)生和課程信息。程信息。二、外鍵約束（二、外鍵約束（Foreign Key Constraint）1、外鍵約束：、外鍵約束：定義外鍵的目的是在于限制可以保存在外鍵所在表中的數(shù)定義外鍵的目的是在于限制可以保存在外鍵所在表中的數(shù)據(jù)。外鍵由一個列或多個列的組合構(gòu)成，用來實(shí)現(xiàn)兩張表據(jù)。外鍵由一個列或多個列的組合構(gòu)成，用來實(shí)現(xiàn)兩張表之間的數(shù)據(jù)聯(lián)系，維護(hù)兩個表間的一致性關(guān)系。之間的數(shù)據(jù)聯(lián)系，維護(hù)兩個表間的一致性關(guān)系。2、外鍵的作用點(diǎn)、外鍵的作用點(diǎn)l修改父表主鍵時檢查修改父表主鍵時檢查l刪除父表記錄時檢查刪除父表記錄時檢查l在子表中插入數(shù)

7、據(jù)時檢查在子表中插入數(shù)據(jù)時檢查3、創(chuàng)建外鍵約束的方法、創(chuàng)建外鍵約束的方法（1）使用）使用SSMS方式方式（2）使用）使用Transact-SQL語言方式語言方式方法一：在創(chuàng)建新表時創(chuàng)建外鍵約束，語法格式為：方法一：在創(chuàng)建新表時創(chuàng)建外鍵約束，語法格式為：CREATE TABLE 表名稱表名稱 ( 字段定義，字段定義， CONSTRAINT 約束名約束名 FOREIGN KEY (列名列名) REFERENCES 主表主表(列名列名) )【例【例 3.9】CJGL庫中庫中CJ表字段表字段XSDM（學(xué)生代碼）參（學(xué)生代碼）參照主表照主表XS（表（表3-13）中的主鍵字段）中的主鍵字段XSDM，將，將

8、CJ表表中中XSDM字段創(chuàng)建外鍵約束，命名為字段創(chuàng)建外鍵約束，命名為FK_CJ_學(xué)習(xí)學(xué)習(xí)_XS。三、惟一約束（三、惟一約束（Unique Constraint）1、惟一約束：、惟一約束： 在表指定的列或組列上不允許兩行是具有重復(fù)值時，則需要該列或組列在表指定的列或組列上不允許兩行是具有重復(fù)值時，則需要該列或組列上指定上指定UNIQUE完整性約束。在完整性約束。在UNIQUE約束定義中的列或組列稱為唯一碼。約束定義中的列或組列稱為唯一碼。2、主鍵與、主鍵與UNIQUE約束都能控制數(shù)據(jù)的唯一性，它們的區(qū)別主約束都能控制數(shù)據(jù)的唯一性，它們的區(qū)別主要為：要為：UNIQUE約束，主要用在非主鍵的一列或多

9、列上要求數(shù)據(jù)惟一的約束，主要用在非主鍵的一列或多列上要求數(shù)據(jù)惟一的情況。情況。UNIQUE約束，允許該列上存在約束，允許該列上存在NULL值，而主鍵決不允許出現(xiàn)值，而主鍵決不允許出現(xiàn)這種情況。圖這種情況。圖3-15 “管理索引和鍵管理索引和鍵”工具工具可在一個表上設(shè)置多個可在一個表上設(shè)置多個UNIQUE約束，而在一個表中只能設(shè)置一約束，而在一個表中只能設(shè)置一個主鍵約束。個主鍵約束。3、創(chuàng)建惟一約束的方法：、創(chuàng)建惟一約束的方法： （1）使用）使用SSMS創(chuàng)建惟一約束創(chuàng)建惟一約束 “對象資源管理器對象資源管理器”右擊表名稱，選擇快捷菜單中的右擊表名稱，選擇快捷菜單中的“修改修改”，進(jìn)入表結(jié)構(gòu)修改狀

10、態(tài)。單擊工具欄處進(jìn)入表結(jié)構(gòu)修改狀態(tài)。單擊工具欄處“管理索引和鍵管理索引和鍵”按鈕按鈕 （2）使用）使用Transact-SQL語言創(chuàng)建惟一約束語言創(chuàng)建惟一約束方法一：在創(chuàng)建表結(jié)構(gòu)的同時創(chuàng)建惟一約束，語法格式為：方法一：在創(chuàng)建表結(jié)構(gòu)的同時創(chuàng)建惟一約束，語法格式為： CREATE TABLE 表名稱表名稱 (字段定義，字段定義， CONSTRAINT 約束名約束名 UNIQUE (列名列名) ) 方法二：在已經(jīng)建立結(jié)構(gòu)的表定義中添加惟一約束，語法格式：方法二：在已經(jīng)建立結(jié)構(gòu)的表定義中添加惟一約束，語法格式：ALTER TABLE 表名稱表名稱ADD CONSTRAINT 約束名約束名 UNIQUE

11、 (字段名字段名)四、檢查約束四、檢查約束(Check Constraint)1、檢查約束：、檢查約束：限制輸入到一列或多列中的可能值限制輸入到一列或多列中的可能值,只有符合條件與格式只有符合條件與格式的數(shù)據(jù)才能被存放，它通過檢查輸入表列的數(shù)據(jù)的值來的數(shù)據(jù)才能被存放，它通過檢查輸入表列的數(shù)據(jù)的值來維護(hù)值域的完整性。維護(hù)值域的完整性。 2、創(chuàng)建檢查約束的方法、創(chuàng)建檢查約束的方法（1）使用）使用SSMS創(chuàng)建檢查約束創(chuàng)建檢查約束在在“對象資源管理器對象資源管理器”右擊表名稱，選擇快捷菜單中的右擊表名稱，選擇快捷菜單中的“修修改改”，進(jìn)入表結(jié)構(gòu)修改狀態(tài)。單擊工具欄處，進(jìn)入表結(jié)構(gòu)修改狀態(tài)。單擊工具欄處“

12、管理管理CHECK約束約束”按鈕按鈕 。（2）使用）使用Transact-SQL語言創(chuàng)建語言創(chuàng)建方法一：建立表結(jié)構(gòu)同時創(chuàng)建檢查約束，語法格式：方法一：建立表結(jié)構(gòu)同時創(chuàng)建檢查約束，語法格式：CREETE TABLE 表名稱表名稱(字段定義，字段定義，CONSTRAINT 約束名約束名 CHECK 邏輯表邏輯表達(dá)式達(dá)式) 方法二：表建立后增加檢查約束，語法格式如下：方法二：表建立后增加檢查約束，語法格式如下：ALTER TABLE 表名稱表名稱 ADD CONSTRAINT 約束名約束名 CHECK 邏輯表達(dá)式邏輯表達(dá)式【例【例 3.11】在】在CJGL數(shù)據(jù)庫中為數(shù)據(jù)庫中為XS表字段表字段XB（性

13、別）設(shè)置檢查約束，要求（性別）設(shè)置檢查約束，要求XB字段值只能字段值只能為為“男男”和和“女女”。ALTER TABLE XS ADD CONSTRAINT CK_XB CHECK 性別性別IN(男男,女女)五、默認(rèn)值約束（五、默認(rèn)值約束（Default Constraint） 1、默認(rèn)值約束：、默認(rèn)值約束： 可用于給列賦予一個常量值，用戶在插入新的數(shù)可用于給列賦予一個常量值，用戶在插入新的數(shù)據(jù)行時據(jù)行時, 系統(tǒng)將默認(rèn)該列以常量值為指定數(shù)據(jù)。系統(tǒng)將默認(rèn)該列以常量值為指定數(shù)據(jù)。2、創(chuàng)建默認(rèn)值約束的方法、創(chuàng)建默認(rèn)值約束的方法（1）使用）使用SSMS創(chuàng)建默認(rèn)值約束創(chuàng)建默認(rèn)值約束 在在“對象資源管理器

14、對象資源管理器”右擊表名稱，選擇快捷菜右擊表名稱，選擇快捷菜單中的單中的“修改修改”，進(jìn)入表結(jié)構(gòu)修改狀態(tài)。選定設(shè)置，進(jìn)入表結(jié)構(gòu)修改狀態(tài)。選定設(shè)置默認(rèn)值的列，在列定義下方默認(rèn)值的列，在列定義下方“列屬性列屬性”處單擊處單擊“默默認(rèn)值或綁定認(rèn)值或綁定”輸入選定列的默認(rèn)值。輸入選定列的默認(rèn)值。 （2）使用）使用Transact-SQL語言語言方法一：創(chuàng)建表結(jié)構(gòu)的同時定義默認(rèn)值約束方法一：創(chuàng)建表結(jié)構(gòu)的同時定義默認(rèn)值約束CREATE TALBE 表名稱表名稱(字段定義字段定義1，字段名字段名 字段類型（字段寬度）字段類型（字段寬度） DEFAULT 默認(rèn)值默認(rèn)值,字段定義字段定義n)方法二：在已有表中添

15、加默認(rèn)值約束方法二：在已有表中添加默認(rèn)值約束ALTER TABLE 表名稱表名稱ADD CONSTRAINT 約束名約束名 DEFAULT 默認(rèn)值默認(rèn)值 FOR 列名列名【例【例 3.14】在】在CJGL數(shù)據(jù)庫數(shù)據(jù)庫XS表中，為表中，為BZ（民族）字（民族）字段設(shè)置默認(rèn)值段設(shè)置默認(rèn)值“漢漢”六、約束管理六、約束管理 1、查看約束、查看約束SP_HELPCONSTRAINT 表名稱表名稱2、刪除約束、刪除約束ALTER TABLE 表名稱表名稱 DROP CONSTRAINT 約束名約束名一、一、 索引的作用與類型索引的作用與類型1、索引的作用、索引的作用2、索引的類型、索引的類型（1）簇索引（

16、）簇索引（CLUSTERED INDEX）簇索引又稱聚集索引。簇索引在數(shù)據(jù)表中按照索引列的排簇索引又稱聚集索引。簇索引在數(shù)據(jù)表中按照索引列的排序規(guī)則排列數(shù)據(jù)的物理順序。序規(guī)則排列數(shù)據(jù)的物理順序。 （2）非簇索引）非簇索引 （NONCLUSTERED INDEX）非簇索引是具有與表的數(shù)據(jù)完全分離的結(jié)構(gòu)，它不會改變非簇索引是具有與表的數(shù)據(jù)完全分離的結(jié)構(gòu)，它不會改變行的物理存儲順序，但是它是由數(shù)據(jù)行指針和一個索引值行的物理存儲順序，但是它是由數(shù)據(jù)行指針和一個索引值構(gòu)成的。構(gòu)成的。（3）唯一索引）唯一索引 （UNIQUE INDEX）唯唯索引可以確保所有數(shù)據(jù)行中任意兩行的被索引列不包索引可以確保所有數(shù)

17、據(jù)行中任意兩行的被索引列不包括括NULL在內(nèi)的重復(fù)值。在內(nèi)的重復(fù)值。二、創(chuàng)建索引二、創(chuàng)建索引 1、使用、使用SSMS創(chuàng)建索引創(chuàng)建索引 在在“對象資源管理器對象資源管理器”中右擊創(chuàng)建索引的表名稱，中右擊創(chuàng)建索引的表名稱，執(zhí)行快捷菜單中的執(zhí)行快捷菜單中的“修改修改”，進(jìn)入表結(jié)構(gòu)修改狀態(tài)。，進(jìn)入表結(jié)構(gòu)修改狀態(tài)。單擊工具欄處的單擊工具欄處的“管理索引和鍵管理索引和鍵”按鈕按鈕 2、使用、使用Transact-SQL語言創(chuàng)建索引語法格式為：語言創(chuàng)建索引語法格式為：CREATE INDEX 索引名索引名 ON 表名（列名）表名（列名）ASC|DESC ,.n【例【例 3.17】 在在XS表中基于表中基于XSDM創(chuàng)建簇索引創(chuàng)建簇索引IDX_XS?！纠纠?3.18】在】在BJ（班級）表中基于（班級）表中基于ZYDM（專業(yè)代碼）創(chuàng)（專業(yè)代碼）創(chuàng)建非簇索引，名為建非簇索引，名為“屬于屬于1_FK”，升序排列，升序排列,如圖如圖3-22所所示。示?！纠纠?3.19】在】在KCSZ（課程設(shè)置）表中創(chuàng)建唯一索引，該（課程設(shè)置）表中創(chuàng)建唯一索引，該索引基于索引基于XQDM（學(xué)期代碼）（學(xué)期代碼）,KCDM（課程代（課程代碼）碼）,ZYDM（專業(yè)代碼）三個字段而創(chuàng)建，命名為（專業(yè)代碼）三個字段而創(chuàng)建，命名為Index_5 。三、管理