數(shù)據(jù)完整性和索引

第三章數(shù)據(jù)庫完整性和索引3.1數(shù)據(jù)完整性的概述一、數(shù)據(jù)完整性的定義數(shù)據(jù)完整性（DataIntegrity）是指數(shù)據(jù)的精確性和可靠性。定義數(shù)據(jù)完整性的目的：

1、防止數(shù)據(jù)庫中存在不符合語義規(guī)定的數(shù)據(jù)；

2、防止錯(cuò)誤信息的輸入和輸出而造成無效操作；3.1數(shù)據(jù)完整性的概述二、數(shù)據(jù)完整性分類1、實(shí)體完整性（EntityIntegrity）

將數(shù)據(jù)表中的每一行看作一個(gè)實(shí)體，所有行具有非空且沒有重復(fù)的標(biāo)識(shí)字段。實(shí)現(xiàn)手段：唯一索引、主鍵約束、唯一約束、列的Identity屬性限制3.1數(shù)據(jù)完整性的概述二、數(shù)據(jù)完整性分類

2、域完整性（DomainIntegrity）

域（或列）完整性是指定列指定一組有效的值并決定是否可為空值。實(shí)現(xiàn)手段：

1.數(shù)據(jù)類型或是NOTNULL；

2.檢查約束和規(guī)則；

3.外鍵約束、默認(rèn)值等規(guī)則限制。3.1數(shù)據(jù)完整性的概述二、數(shù)據(jù)完整性分類3、參照完整性（ReferentialIntegrity）

參照完整性確保主鍵（在被引用表中）和外鍵（在引用表中）之間的關(guān)系得到保持。

例如修改父表中關(guān)鍵字值后，子表關(guān)鍵字值未做相應(yīng)改變；刪除父表的某記錄后，子表的相應(yīng)記錄未刪除，致使這些記錄稱為孤立記錄；對(duì)于子表插入的記錄，父表中沒有相應(yīng)關(guān)鍵字值的記錄；3.2約束學(xué)號(hào)姓名專業(yè)性別出生時(shí)間990201王俊鵬計(jì)算機(jī)男1983/10/1990202王巍計(jì)算機(jī)女1987/9/5990203李海燕計(jì)算機(jī)女1985/4/3學(xué)號(hào)課程號(hào)成績學(xué)分990201101804990201102902990202201784990202202954990203101882主鍵外鍵被引用表引用表學(xué)生信息表學(xué)生課程表3.1數(shù)據(jù)完整性的概述二、數(shù)據(jù)完整性分類4、用戶定義的完整性（User-definedIntegrity）用戶自定義完整性指針對(duì)某一具體關(guān)系數(shù)據(jù)庫的約束條件，它反映某一具體應(yīng)用所涉及的數(shù)據(jù)必須滿足的語義要求。

3.1數(shù)據(jù)完整性的概述二、實(shí)施數(shù)據(jù)完整性的方法1、用定義約束實(shí)現(xiàn)數(shù)據(jù)完整性通過針對(duì)表和字段定義聲明的約束，將數(shù)據(jù)完整性成為數(shù)據(jù)定義的一部分，使用約束實(shí)現(xiàn)數(shù)據(jù)完整性。2、用編寫程序?qū)崿F(xiàn)數(shù)據(jù)完整性可以使用存儲(chǔ)過程或觸發(fā)器實(shí)施程序化數(shù)據(jù)完整性。3、選取數(shù)據(jù)完整性的實(shí)施方法由于約束在SQLSERVER的可執(zhí)行部分有一段代碼路徑,執(zhí)行速度比其它方式快，所以應(yīng)優(yōu)先選用約束。需要在多個(gè)表之間保持?jǐn)?shù)據(jù)的一致性，可選擇使用約束中的主鍵和外鍵約束；需要特別復(fù)雜的完整性檢驗(yàn),則使用觸發(fā)器和存儲(chǔ)過程

3.2約束一、

主鍵約束（PrimaryKeyConstraint）1、主鍵約束：主鍵是能夠唯一標(biāo)識(shí)表中某一行的屬性或?qū)傩越M。語法：[CONSTRAINT約束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(列[,...n])}應(yīng)用PRIMARYKEY約束的注意事項(xiàng)

1、每張表只能有一個(gè)PRIMARYKEY約束2、輸入的值必須是惟一的3、不允許空值4、將在指定列上創(chuàng)建惟一索引PRIMARYKEY約束（續(xù)）PRIMARYKEY約束示例在Customers表上創(chuàng)建PRIMARYKEY約束，指明表的主鍵值是CustomerID，并且創(chuàng)建非聚集索引以強(qiáng)制約束4.4.3PRIMARYKEY約束USENorthwindALTERTABLEdbo.Customers

ADDCONSTRAINTPK_CustomersPRIMARYKEYNONCLUSTERED(CustomerID)排序規(guī)則指定表示每個(gè)字符的位模式以及存儲(chǔ)和比較字符所使用的規(guī)則。

Collate是一個(gè)子句，可應(yīng)用于數(shù)據(jù)庫定義或列定義以定義排序規(guī)則，或應(yīng)用于字符串表達(dá)式以應(yīng)用排序規(guī)則投影。語法：Collatecollation_name

排序規(guī)則排序規(guī)則名稱由兩部份構(gòu)成，前半部份是指本排序規(guī)則所支持的字符集。

如：

Chinese_PRC_CS_AI_WS

前半部份：指UNICODE字符集，Chinese_PRC_指針對(duì)大陸簡體字UNICODE的排序規(guī)則。

排序規(guī)則的后半部份即后綴含義：

_BIN二進(jìn)制排序

_CI(CS)是否區(qū)分大小寫，CI不區(qū)分，CS區(qū)分

_AI(AS)是否區(qū)分重音，AI不區(qū)分，AS區(qū)分

區(qū)分大小寫:如果想讓比較將大寫字母和小寫字母視為不等，請(qǐng)選擇該選項(xiàng)。

區(qū)分重音:如果想讓比較將重音和非重音字母視為不等，請(qǐng)選擇該選項(xiàng)。如果選擇該選項(xiàng),比較還將重音不同的字母視為不等。3.2約束二、外鍵約束（ForeignKeyConstraint）1、外鍵約束：定義外鍵的目的是在于限制可以保存在外鍵所在表中的數(shù)據(jù)。外鍵由一個(gè)列或多個(gè)列的組合構(gòu)成，用來實(shí)現(xiàn)兩張表之間的數(shù)據(jù)聯(lián)系，維護(hù)兩個(gè)表間的一致性關(guān)系。語法：[CONSTRAINT約束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表

[(引用列

[,…n])]3.2約束學(xué)號(hào)姓名專業(yè)性別出生時(shí)間990201王俊鵬計(jì)算機(jī)男1983/10/1990202王巍計(jì)算機(jī)女1987/9/5990203李海燕計(jì)算機(jī)女1985/4/3學(xué)號(hào)課程號(hào)成績學(xué)分990201101804990201102902990202201784990202202954990203101882主鍵外鍵主表從表學(xué)生信息表學(xué)生課程表2、外鍵約束注意要點(diǎn)創(chuàng)建外鍵約束的前提是完成主表的主鍵定義；外鍵約束允許存在NULL值；外鍵約束不能用于臨時(shí)表和不同的數(shù)據(jù)庫之間。FOREIGNKEY約束（續(xù)）FOREIGNKEY約束的示例使用FOREIGNKEY約束，確保Orders表中的客戶標(biāo)識(shí)與Customers表中的有效的客戶標(biāo)識(shí)相關(guān)聯(lián)USENorthwindALTERTABLEdbo.Orders

ADDCONSTRAINTFK_Orders_CustomersFOREIGNKEY(CustomerID)REFERENCESdbo.Customers(CustomerID)3.2約束三、惟一約束（UniqueConstraint）1、惟一約束：

在表指定的列或組列上不允許兩行是具有重復(fù)值時(shí)，則需要該列或組列上指定UNIQUE完整性約束。在UNIQUE約束定義中的列或組列稱為唯一碼。2、主鍵與UNIQUE約束的區(qū)別主要為：

UNIQUE約束，主要用在非主鍵的一列或多列上要求數(shù)據(jù)惟一的情況。

UNIQUE約束，允許該列上存在NULL值，而主鍵不允許?？稍诒碇性O(shè)置多個(gè)UNIQUE約束，而在只能設(shè)置一個(gè)主鍵約束。應(yīng)用UNIQUE約束的注意事項(xiàng)應(yīng)用UNIQUE約束的注意事項(xiàng)允許一個(gè)空值在一個(gè)表上允許多個(gè)UNIQUE約束可在一個(gè)或者多個(gè)列上定義是通過一個(gè)惟一索引強(qiáng)制約束的3.2約束3、創(chuàng)建惟一約束的方法：

（1）使用SSMS創(chuàng)建惟一約束“對(duì)象資源管理器”右擊表名稱，選擇快捷菜單中的“修改”，進(jìn)入表結(jié)構(gòu)修改狀態(tài)。單擊工具欄處“管理索引和鍵”按鈕（2）使用Transact-SQL語言創(chuàng)建惟一約束方法一：在創(chuàng)建表結(jié)構(gòu)的同時(shí)創(chuàng)建惟一約束，語法格式為：

CREATETABLE表名稱(字段定義，CONSTRAINT約束名UNIQUE(列名))方法二：在已經(jīng)建立結(jié)構(gòu)的表定義中添加惟一約束，語法格式：ALTERTABLE表名稱ADDCONSTRAINT約束名UNIQUE(字段名)3.2約束四、檢查約束(CheckConstraint)1、檢查約束：

限制輸入到一列或多列中的可能值,它通過檢查輸入表列的數(shù)據(jù)的值來維護(hù)值域的完整性。注意：1、一個(gè)表可以定義多個(gè)檢查約束，但每個(gè)建庫命令只能為每列定義一個(gè)檢查約束；2、在每次插入記錄或是修改記錄時(shí)，檢查約束會(huì)做相應(yīng)檢查；3、自動(dòng)編號(hào)字段不能應(yīng)用檢查約束。應(yīng)用CHECK約束的注意事項(xiàng)在每次執(zhí)行INSERT或者UPDATE語句的時(shí)候校驗(yàn)數(shù)據(jù)值可以引用同表中的其他列不能包含子查詢列級(jí)CHECK約束可省略名字，讓系統(tǒng)自動(dòng)生成表達(dá)式可以用AND以及OR連接以表示復(fù)雜邏輯表級(jí)CHECK約束可引用同行中的多列，但不能跨行或跨表可用CHECK約束來防止輸入NULL值，例如CHECK(輸入值

ISNOTNULL)3.2約束（2）使用Transact-SQL語言創(chuàng)建方法一：建立表結(jié)構(gòu)同時(shí)創(chuàng)建檢查約束，語法格式：CREETETABLE表名稱(字段定義，CONSTRAINT約束名CHECK

邏輯表達(dá)式)

方法二：表建立后增加檢查約束，語法格式如下：ALTERTABLE表名稱ADDCONSTRAINT約束名CHECK

邏輯表達(dá)式3.2約束【例3.11】在CJGL數(shù)據(jù)庫中為XS表字段XB（性別）設(shè)置檢查約束，要求XB字段值只能為“男”和“女”。

ALTERTABLEXSADDCONSTRAINTCK_XBCHECK性別IN(‘男’,’女’)禁用約束當(dāng)在表上添加約束的時(shí)候，我們可以禁用對(duì)已有數(shù)據(jù)的約束檢查

語法：ALTERTABLE表名[WITHCHECKWITHNOCHECK]ADDCONSTRAINT約束名[FOREIGNKEY][(column[,…n])]REFERENCES引用表

[(引用列[,…n])][CHECK(搜索條件)]

禁用現(xiàn)有數(shù)據(jù)上的約束檢查（續(xù)）對(duì)已有數(shù)據(jù)禁用約束檢查的注意事項(xiàng)只能禁用CHECK和FOREIGNKEY約束當(dāng)為一個(gè)已有數(shù)據(jù)的表添加CHECK或FOREIGNKEY約束的時(shí)候，使用WITHNOCHECK選項(xiàng)來禁用對(duì)已有數(shù)據(jù)的約束檢查當(dāng)現(xiàn)有數(shù)據(jù)不再變化的時(shí)候，使用WITHNOCHECK選項(xiàng)。若數(shù)據(jù)被更新，則它的新值必須符合CHECK約束確定禁用約束檢查是恰當(dāng)?shù)摹Ｔ跊Q定添加約束前，可修改現(xiàn)有數(shù)據(jù)在加載新數(shù)據(jù)時(shí)禁用約束檢查為了避免約束檢查的開銷，有時(shí)候可能希望禁用約束已經(jīng)確保數(shù)據(jù)符合約束數(shù)據(jù)并不符合約束，但稍后可以更改其值并重用約束語法：ALTERTABLE表名{CHECK|NOCHECK}CONSTRAINT{ALL|約束名[,...n]}查看約束是啟用還是禁用的狀態(tài)系統(tǒng)存儲(chǔ)過程sp_help系統(tǒng)函數(shù)OBJECTPROPERTY的CnstIsDisabled

屬性3.2約束五、默認(rèn)值約束（DefaultConstraint）1、默認(rèn)值約束：可用于給列賦予一個(gè)常量值，用戶在插入新的數(shù)據(jù)行時(shí),系統(tǒng)將默認(rèn)該列以常量值為指定數(shù)據(jù)。注意：默認(rèn)值可以是常量、函數(shù)、空值（NULL）等。2、創(chuàng)建默認(rèn)值約束的方法（1）使用SSMS創(chuàng)建默認(rèn)值約束（2）使用Transact-SQL語言DEFAULT約束如果一個(gè)列的值在INSERT語句中沒有指定，DEFAULT約束將自動(dòng)輸入一個(gè)值，可以是預(yù)先指定的常量、NULL或者一個(gè)系統(tǒng)函數(shù)運(yùn)行時(shí)的值

語法：[CONSTRAINT約束名]DEFAULT約束表達(dá)式

創(chuàng)建DEFAULT約束的兩種方法創(chuàng)建一個(gè)默認(rèn)（CREATEDEFAULT），然后使用存儲(chǔ)過程sp_bindefault

將默認(rèn)綁定到一個(gè)列CREATETABLE或ALTERTABLE時(shí)使用DEFAUTL約束應(yīng)用DEFAULT約束的注意事項(xiàng)DEFAULT約束創(chuàng)建時(shí)將檢查表中的現(xiàn)存數(shù)據(jù)DEFAULT約束只對(duì)INSERT語句有效每列只能定義一個(gè)DEFAULT約束DEFAULT約束使用注意事項(xiàng)有可能會(huì)和CHECK約束沖突為具有PRIMARYKEY或UNIQUE約束的列指定默認(rèn)值是沒有意義的常量值外面可以加或者不加括號(hào)，字符或者日期常量必須加上單引號(hào)或雙引號(hào)3.2約束六、約束管理1、查看約束SP_HELPCONSTRAINT表名稱2、刪除約束ALTERTABLE表名稱

DROPCONSTRAINT約束名決定使用何種約束完整性類型約束類型描述域DEFAULT如果在INSERT語句中未顯式提供

值，則指定為列提供的值CHECK指定列中可接受的數(shù)據(jù)值實(shí)體PRIMARYKEY惟一標(biāo)識(shí)每一列，確保用戶沒有輸入重復(fù)的值。同時(shí)創(chuàng)建一個(gè)索引以增強(qiáng)性能。不允許空值UNIQUE確保在非主鍵列中不輸入重復(fù)值，并創(chuàng)建一個(gè)索引以增強(qiáng)性能。允許空值引用FOREIGNKEY定義一列或多列的值與同表或其他表中主鍵的值匹配CHECK基于同表中其他列的值，指定列中可接受的數(shù)據(jù)值使用約束的注意事項(xiàng)可直接在表上創(chuàng)建、更改和刪除約束，而不必刪除并重建表SQLServer里的約束只是“最后防線”當(dāng)給一個(gè)表添加約束的時(shí)候，SQLServer將檢查現(xiàn)有數(shù)據(jù)是否違反約束建議創(chuàng)建約束的時(shí)候指定名稱，否則系統(tǒng)將為約束自動(dòng)產(chǎn)生一個(gè)復(fù)雜的名稱名稱必須惟一，且符合SQLServer標(biāo)識(shí)符的規(guī)則查看約束的信息系統(tǒng)存儲(chǔ)過程：sp_helpconstraint、sp_help創(chuàng)建約束使用CREATETABLE或者ALTERTABLECREATETABLE是在創(chuàng)建表時(shí)創(chuàng)建約束ALTERTABLE是在一個(gè)已有的表上創(chuàng)建約束可以添加約束到已有數(shù)據(jù)的表上可添加單列或多列約束若約束應(yīng)用于單列，稱為列級(jí)約束若約束引用了多列，稱為表級(jí)約束，即使它并沒有引用表中的所有列3.3索引一、索引的作用與類型1、索引的作用索引是一個(gè)再表或視圖上創(chuàng)建的對(duì)象，當(dāng)用戶查詢索引字段時(shí)，它可以快速實(shí)施數(shù)據(jù)檢索操作。借助索引，執(zhí)行查詢時(shí)不必掃描整個(gè)表就能夠快速找到所需要的數(shù)據(jù)。注意：（1）可以在那些經(jīng)常被用來查詢的列上建立索引，以提高查找效率。【例子】在Student表中可以分別建立基于學(xué)號(hào)、姓名的索引（2）索引將占用磁盤空間，并且降低添加、刪除和更新行的速度，所以要慎用！貨號(hào)貨名規(guī)格3002CPU處理器SY88001002計(jì)算機(jī)LX1003計(jì)算機(jī)FZ1001計(jì)算機(jī)LC2002顯示器172001顯示器153001CPU處理器P44001內(nèi)存儲(chǔ)器2564002內(nèi)存儲(chǔ)器512索引編號(hào)指針地址100141002210033200162002530017300214001840029表與索引示例索引的類型2、索引的類型（1）簇索引（CLUSTEREDINDEX）簇索引又稱聚集索引。簇索引在數(shù)據(jù)表中按照索引列的排序規(guī)則排列數(shù)據(jù)的物理順序。（2）非簇索引（NONCLUSTEREDINDEX）非簇索引是具有與表的數(shù)據(jù)完全分離的結(jié)構(gòu)，它不會(huì)改變行的物理存儲(chǔ)順序，但是它是由數(shù)據(jù)行指針和一個(gè)索引值構(gòu)成的。（3）唯一索引（UNIQUEINDEX）唯—索引可以確保所有數(shù)據(jù)行中任意兩行的被索引列不包括NULL在內(nèi)的重復(fù)值。每個(gè)表只能有一個(gè)簇索引，因?yàn)橐粋€(gè)表中的記錄只能以一種物理順序存放。但是，一個(gè)表可以有不止一個(gè)非簇索引。

從建立了簇索引的表中取出數(shù)據(jù)要比建立了非聚簇索引的表快。非簇索引需要大量的硬盤空間和內(nèi)存。

非簇索引可以提高從表中取數(shù)據(jù)的速度，它也會(huì)降低向表中插入和更新數(shù)據(jù)的