第5章

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

引01索引分類聚集索引非聚集索引索引分類1．聚集索引聚集索引將數(shù)據(jù)行的鍵值在表內(nèi)排序并存儲對應的數(shù)據(jù)記錄，使得數(shù)據(jù)表的物理順序與索引順序一致。SQLServer是按B樹（BTREE）方式組織聚集索引的，不論聚集索引里有表的哪個（或哪些）列，這些列都會按順序保存在表中。由于存在這種排序，所以每個表只會有一個聚集索引。2．非聚集索引非聚集索引完全獨立于數(shù)據(jù)行的結(jié)構(gòu)，它也按B樹方式組織。在非聚集索引內(nèi)，從索引行指向數(shù)據(jù)行的指針稱為行定位器。行定位器的結(jié)構(gòu)取決于數(shù)據(jù)頁的存儲方式是堆集還是聚集。對于堆集，行定位器是指向行的指針。對于有聚集索引的表，行定位器是聚集索引鍵。只有在表上創(chuàng)建聚集索引時，表內(nèi)的行才按特定順序存儲，這些行按聚集索引鍵順序存儲。如果一個表只有非聚集索引，則它的數(shù)據(jù)行將按無序的堆集方式存儲。一個表中可有一個或多個非聚集索引。當在SQLServer上創(chuàng)建索引時，可指定是按升序還是按降序存儲鍵。02索引的創(chuàng)建創(chuàng)建索引復合索引在計算列和視圖上創(chuàng)建索引索引的創(chuàng)建使用CREATEINDEX語句可以為表創(chuàng)建索引。語法格式如下。CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名 ON

表或視圖名 (列名[ASC|DESC]) WITH索引選項說明：（1）UNIQUE：表示為表或視圖創(chuàng)建唯一索引（即不允許存在索引值相同的兩行）。例如，對于xsb表，根據(jù)學號創(chuàng)建唯一索引，即不允許有兩個相同的學號出現(xiàn)。（2）CLUSTERED|NONCLUSTERED：指定創(chuàng)建聚集索引還是非聚集索引，前者表示創(chuàng)建聚集索引，后者表示創(chuàng)建非聚集索引。（3）索引名：索引名在表或視圖中必須唯一，但在數(shù)據(jù)庫中不必唯一；參數(shù)“表或視圖名”用于指定包含索引列的表名或視圖名，指定表名、視圖名時可包含數(shù)據(jù)庫和所屬架構(gòu)。（4）列名：指定建立索引的列，可以為索引指定多個列。指定索引列時，注意表或視圖索引列的類型不能為ntext、text或image。（5）WITH索引選項：設定索引記錄數(shù)據(jù)的有效性規(guī)則。索引的創(chuàng)建1.創(chuàng)建索引【例5.1】先為kcb1表的“課程名”列創(chuàng)建索引。再為“課程號”列創(chuàng)建唯一聚集索引，如果輸入了重復課程號，將忽略該INSERT或UPDATE語句。1）在xscj創(chuàng)建一個kcb1表USExscjCREATETABLEkcb1(

課程號 char(3) NOTNULL,

課程名 char(16) NOTNULL,

學分 tinyint)2）為kcb1表創(chuàng)建索引CREATEINDEXkc_name ONkcb1(課程名)CREATEUNIQUECLUSTEREDINDEXkc_id ONkcb1(課程號) WITHIGNORE_DUP_KEY索引的創(chuàng)建3）插入記錄INSERTINTOkcb1VALUES('401','就業(yè)指導',1)INSERTINTOkcb1VALUES('401','就業(yè)指導',1)顯示信息如圖。

索引的創(chuàng)建2.復合索引由多列構(gòu)成的索引稱為復合索引。【例5.2】根據(jù)cjb表的“學號”列和“課程號”列創(chuàng)建復合索引。語句如下：CREATEINDEXcjb_ind ONcjb(學號,課程號)說明：如果替換已經(jīng)存在的cjb_ind索引需要加WITH子句。CREATEINDEXcjb_ind ONcjb(學號,課程號) WITH(DROP_EXISTING=ON)索引的創(chuàng)建3．在計算列和視圖上創(chuàng)建索引（1）在計算列上創(chuàng)建索引。對于UNIQUE或PRIMARYKEY索引，只要滿足索引條件，就可以包含計算列，但計算列必須具有確定性和精確性。若計算列中帶有函數(shù)，則使用該函數(shù)時有相同的參數(shù)輸入，輸出的結(jié)果也一定相同時，該計算列是確定的。而對于有些函數(shù)，如GETDATE()，每次調(diào)用時都輸出不同的結(jié)果，這時就不能在計算列上定義索引。（2）在視圖上創(chuàng)建索引?？梢栽谝晥D上定義索引。索引視圖是一種在數(shù)據(jù)庫中存儲視圖結(jié)果集的方法，可減少動態(tài)生成結(jié)果集的開銷。索引視圖還能自動反映出創(chuàng)建索引后對基本表數(shù)據(jù)所做的修改。索引的創(chuàng)建【例5.3】基于xsb表創(chuàng)建一個視圖，并為該視圖創(chuàng)建索引。語句如下：USExscjGOCREATEVIEWxsv1WITHSCHEMABINDING AS SELECT學號,姓名 FROMdbo.xsbGOCREATEUNIQUECLUSTEREDINDEXinx1 ONxsv1(學號)GO03重建索引重建索引索引使用一段時間后，可能需要重新生成原來的索引。語法格式如下。ALTERINDEX索引名|ALL ON表或視圖名REBUILD【例5.4】先重建kcb1表上的kc_name索引，然后重建kcb1表上的所有索引。語句如下。ALTERINDEXkc_nameONkcb1REBUILDALTERINDEXALLONkcb1REBUILD04索引的刪除索引的刪除從當前數(shù)據(jù)庫中刪除一個或多個索引。語法格式如下。DROPINDEX索引名 ON

表或視圖名說明：DROPINDEX語句可以一次刪除一個或多個索引?！纠?.5】刪除cjb表cjb_ind索引。語句如下。IFEXISTS(SELECTnameFROMsysindexesWHEREname='cjb_ind') DROPINDEXcjb.cjb_ind第5章

索引和數(shù)據(jù)完整性——數(shù)據(jù)完整性01數(shù)據(jù)完整性分類實體完整性域完整性參照完整性數(shù)據(jù)完整性分類1．實體完整性實體完整性又稱為行的完整性，要求表中有一個主鍵，其值不能為空且能唯一地標識對應的記錄。通過索引、UNIQUE約束、PRIMARYKEY約束或IDENTITY屬性可實現(xiàn)數(shù)據(jù)的實體完整性。例如，對于xscj數(shù)據(jù)庫中的xsb表，“學號”作為主鍵，每一個學生的學號能唯一地標識該學生對應的行記錄信息，那么在輸入數(shù)據(jù)時，就不能有相同學號的行記錄。通過對“學號”這一列建立主鍵約束可實現(xiàn)表xsb的實體完整性。2．域完整性域完整性又稱為列完整性，指給定列輸入的有效性。實現(xiàn)域完整性的方法有限制類型（通過數(shù)據(jù)類型）、格式（通過CHECK約束和規(guī)則）或可能的取值范圍（通過CHECK約束、DEFALUT定義、NOTNULL定義和規(guī)則）等。數(shù)據(jù)完整性分類3．參照完整性在SQLServer中，參照完整性的實現(xiàn)是通過定義外鍵與主鍵之間或外鍵與唯一鍵之間的對應關系來實現(xiàn)的。參照完整性確保鍵值在所有表中一致。如果定義了兩個表（例如，xsb表和cjb表）之間的參照完整性，則需要滿足下列要求：（1）從表不能引用不存在的鍵值。例如，cjb表中行記錄出現(xiàn)的學號必須是xsb表中已存在的學號。（2）如果主表中的鍵值被更改，那么對從表中該鍵值的所有引用要進行一致更改。例如，如果對xsb表中的某一學號進行修改，則cjb表中所有對應學號也要進行相應的修改。（3）如果主表中沒有關聯(lián)的記錄，則不能將記錄添加到從表中。例如，xsb表中沒有學號的記錄在cjb表中不能添加。（4）如果要刪除主表中的某一記錄，則應先刪除從表中與該記錄匹配的相關記錄。例如，要刪除xsb表中某學號的記錄，必須先刪除cjb表中所有該學號學生的記錄。02實體完整性PRIMARYKEY約束和UNIQUE約束PRIMARYKEY約束和UNIQUE約束創(chuàng)建實體完整性1.PRIMARYKEY約束和UNIQUE約束如果要確保一個表中的非主鍵列不輸入重復值，則應在該列上定義唯一約束（UNIQUE約束）。例如，對于xscj數(shù)據(jù)庫中的xsb表，“學號”列是主鍵，在xsb表中增加一列“身份證號碼”，可以定義一個UNIQUE約束來要求表中“身份證號碼”列的取值是唯一的。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)生索引，該索引的默認類型為簇索引。創(chuàng)建UNIQUE約束時，系統(tǒng)會自動產(chǎn)生一個UNIQUE索引，默認類型為非簇索引。PRIMARYKEY約束與UNIQUE約束的相同點在于：二者均不允許表中對應列存在重復值。實體完整性2.PRIMARYKEY約束和UNIQUE約束創(chuàng)建利用T-SQL命令可以使用兩種方式定義約束：作為列的約束或作為表的約束。可以在創(chuàng)建表或修改表時定義。1）在創(chuàng)建表的同時創(chuàng)建主鍵約束或唯一性約束其語法格式如下。CREATETABLE表名(

列定義 [CONSTRAINT約束名]PRIMARYKEY|UNIQUE, … [CONSTRAINT約束名]PRIMARYKEY|UNIQUE(列名,…))說明：（1）CONSTRAINT約束名：為約束命名，“約束名”為要指定約束的名稱，在表所屬的數(shù)據(jù)庫中必須唯一。（2）PRIMARYKEY|UNIQUE：定義約束的關鍵字，PRIMARYKEY為主鍵，UNIQUE為唯一鍵。實體完整性【例5.6】在xscj數(shù)據(jù)庫中創(chuàng)建xsb1表，并對學號列創(chuàng)建主鍵約束，對姓名列定義唯一性約束。語句如下：USExscjCREATETABLExsb1(

學號 char(6) NOTNULL CONSTRAINTxh_pkPRIMARYKEY,

姓名 char(8) NOTNULL CONSTRAINTxm_ukUNIQUE,

性別 bit NOTNULL DEFAULT1,

出生日期 date NOTNULL,

專業(yè) varchar(12) NULL,

總學分 int NULL,

備注 varchar(500) NULL)說明：（1）對表建立PRIMARYKEY約束創(chuàng)建主鍵索引，以“pk”為后綴、后跟表名，系統(tǒng)自動按聚集索引方式組織主鍵索引。（2）當表中的主鍵為復合主鍵時，只能定義為一個表的約束。實體完整性2）通過修改表結(jié)構(gòu)創(chuàng)建主鍵約束或唯一性約束可以修改表結(jié)構(gòu)為表中已存在的列或新列定義約束。語法格式如下。ALTERTABLE表名 ADDCONSTRAINT約束名PRIMARYKEY|UNIQUE(列名,…)【例5.7】修改xsb1表結(jié)構(gòu)，向其中添加一個身份證號列，并定義唯一性約束。語句如下：ALTERTABLExsb1ADD

身份證號char(20)CONSTRAINTsfz_uk

UNIQUENONCLUSTERED(身份證號)GO實體完整性3）刪除主鍵約束或唯一性約束語法格式如下。ALTERTABLE表名 DROPCONSTRAINT約束名【例5.8】刪除xsb1姓名列索引和身份證列索引。語句如下：ALTERTABLExsb1 DROP

CONSTRAINTsfz_uk,xm_uk03域完整性CHECK約束的創(chuàng)建規(guī)則對象的定義、使用與刪除域完整性1．CHECK約束的創(chuàng)建1）在創(chuàng)建表時定義CHECK約束語法格式如下。[CONSTRAINT約束名]CHECK[NOTFORREPLICATION](邏輯表達式)關鍵字CHECK表示定義CHECK約束，如果指定NOTFORREPLICATION選項，則當復制代理執(zhí)行插入、更新或刪除操作時，將不會強制執(zhí)行此約束。其后的“邏輯表達式”稱為CHECK約束表達式，返回值為TRUE或FALSE，該表達式只能為標量表達式?！纠?.9】創(chuàng)建一個表student，只考慮“學號”和“性別”兩列，性別只能包含“男”或“女”。語句如下：USExscjCREATETABLEstudent(

學號char(6) NOTNULL,

性別char(2) NOTNULLCHECK(性別IN('男','女')))域完整性說明：（1）這里CHECK約束指定了性別列的約束，其列值只能為“男”或者“女”。（2）列的CHECK約束也可以寫在表的約束中，但如果指定的一個CHECK約束中，要相互比較一個表的兩個或多個列，那么該約束必須定義為表的約束。【例5.10】創(chuàng)建一個表student1，只考慮“學號”和“出生日期”兩列，出生日期必須大于2004年1月1日，并命名CHECK約束。語句如下：CREATETABLEstudent1(

學號 char(6) NOTNULL,

出生日期 datetime NOTNULL,CONSTRAINTDF_student1_cjsjCHECK(出生日期>'2004-01-01'))域完整性2）修改表時創(chuàng)建CHECK約束在使用ALTERTABLE語句修改表時也能定義CHECK約束。修改表時創(chuàng)建CHECK約束的語法格式如下。ALTERTABLE表名 ADD[CONSTRAINT約束名]CHECK(邏輯表達式)【例5.11】修改student1表，增加出生日期列的CHECK約束。語句如下：ALTERTABLEstudent1 ADDCONSTRAINTDF_student1_cjsj1CHECK(出生日期<'2007-01-01')域完整性3）刪除CHECK約束語法格式如下。ALTERTABLE表名 DROPCONSTRAINT約束名【例5.12】刪除student1表出生日期列CHECK約束。語句如下。ALTERTABLEstudent1 DROPCONSTRAINTDF_student1_cjsj,DF_student1_cjsj1在對象資源管理器中，選擇對應的表的約束項下約束名，右擊，選擇“刪除”菜單，就可刪除指定的約束。域完整性2．規(guī)則對象的定義、使用與刪除規(guī)則是一組使用T-SQL語句組成的條件語句，它提供了另外一種在數(shù)據(jù)庫中實現(xiàn)域完整性與用戶定義完整性的方法。1）規(guī)則對象的定義語法格式如下。CREATERULE規(guī)則名 AS條件表達式說明：（1）規(guī)則名：定義的規(guī)則名，規(guī)則名必須符合標識符規(guī)則。（2）條件表達式：規(guī)則的條件表達式，該表達式可為WHERE子句中任何有效的表達式，但規(guī)則表達式中不能包含列或其他數(shù)據(jù)庫對象，可以包含不引用數(shù)據(jù)庫對象的內(nèi)置函數(shù)。域完整性2）將規(guī)則對象綁定到用戶定義數(shù)據(jù)類型或列將規(guī)則對象綁定到用戶定義數(shù)據(jù)類型或列中，可以使用系統(tǒng)存儲過程sp_bindrule。語法格式如下。sp_bindrule[@rulename=]'規(guī)則名', [@objname=]'對象名' [,[@futureonly=]'futureonly標志']說明：（1）規(guī)則名：為CREATERULE語句創(chuàng)建的規(guī)則名，要用單引號引起來。（2）對象名：為綁定到規(guī)則的用戶定義數(shù)據(jù)類型或列。如果“對象名”采用“表名.列名”格式，則認為綁定到表的列，否則綁定到用戶定義數(shù)據(jù)類型。（3）futureonly：僅當將規(guī)則綁定到用戶定義的數(shù)據(jù)類型時才使用。域完整性【例5.13】創(chuàng)建兩個規(guī)則，分別綁定到臨時表kcb1的課程號和課程名列，用于限制輸入范圍。CREATERULEkc_rule1 AS@rangeLIKE'[1-5][0-9][0-9]'GOEXECsp_bindrule'kc_rule1','kcb1.課程號' /*執(zhí)行存儲過程使用EXEC命令*/GOCREATERULElist_rule1 AS@listIN('程序設計與語言','離散數(shù)學','數(shù)據(jù)結(jié)構(gòu)')GOEXECsp_bindrule'list_rule1','kcb1.課程名'GO說明：EXEC前需要GO，也就是說，創(chuàng)建規(guī)則完成后才能綁定。程序如果正確執(zhí)行將提示：“已將規(guī)則綁定到表的列”。域完整性在對象資源管理器kcb1表下右擊“課程號”選項，在彈出的快捷菜單中選擇“屬性”命令，在kcb1表的“列屬性-課程號”窗口中的“規(guī)則”選項中可以查看已經(jīng)新建的規(guī)則。課程名規(guī)則同理顯示?！纠?.14】定義一個用戶數(shù)據(jù)類型course_num，然后將前面定義的規(guī)則“kc_rule1”綁定到用戶數(shù)據(jù)類型course_num上，最后創(chuàng)建表kcb2，其“課程號”的數(shù)據(jù)類型為course_num。語句如下：CREATETYPEcourse_num /*創(chuàng)建用戶定義數(shù)據(jù)類型*/ FROMchar(3)NOTNULL GOEXECsp_bindrule'kc_rule1','course_num' /*將規(guī)則對象綁定到用戶定義數(shù)據(jù)類型*/GOCREATETABLEkcb2(

課程號 course_num, /*將課程號定義為course_num類型*/

課程名 char(16)NOTNULL,

學分 tinyint)GO域完整性3）規(guī)則對象的刪除在刪除規(guī)則對象前，首先應使用系統(tǒng)存儲過程sp_unbindrule解除被綁定對象與規(guī)則對象之間的綁定關系，其語法格式如下。sp_unbindrule[@objname=]'對象名' [,[@futureonly=]'futureonly標志']在解除列或自定義類型與規(guī)則對象之間的綁定關系后，即可刪除規(guī)則對象。其語法格式如下。DROPRULE規(guī)則名域完整性【例5.15】解除規(guī)則kc_rule1與kcb1表課程號列和用戶定義類型course_num的綁定關系，并刪除規(guī)則對象kc_rule1。同理刪除規(guī)則對象list_rule1。語句如下：EXECsp_unbindrule'kcb1.課程號'EXECsp_unbindrule'kcb1.課程名'EXECsp_unbindrule'course_num'GODROPRULEkc_rule1DROPRULElist_rule1說明：（1）只有在解除指定規(guī)則所有綁定關系后才能刪除該規(guī)則。（2）當解除與用戶定義數(shù)據(jù)類型course_num的關系后，系統(tǒng)自動解除使用course_num定義的列與規(guī)則的綁定關系。04參照完整性定義外鍵約束刪除表間的參照關系參照完整性1.定義外鍵約束1）創(chuàng)建表的同時定義外鍵約束語法格式為。CREATETABLE表名(

列定義 [CONSTRAINT約束名]FOREIGNKEY(列名,…)參照定義, …… [CONSTRAINT約束名]FOREIGNKEY(列名,…)參照定義)說明：（1）和主鍵一樣，外鍵也可以定義為列的約束或表的約束。如果定義為列的約束，則直接在列定義后面使用FOREIGNKEY關鍵字定義該列為外鍵。（2）REFERENCES后面是參照定義，其具體格式如下。REFERENCES參照表名(參照列名,…) [ONDELETE參照動作] [ONUPDATE參照動作]（3）定義外鍵時還可以指定參照動作：ONDELETE|ONUPDATE?？梢詾槊總€外鍵定義參照動作。參照完整性【例5.16】在xscj數(shù)據(jù)庫中創(chuàng)建student2表，要求所有的學生學號都必須出現(xiàn)在student1表中。語句如下：USExscjALTERTABLEstudent1 ADDCONSTRAINTxh_pkPRIMARYKEY(學號)GOCREATETABLEstudent2(

學號