SQL server 2008 索引與數(shù)據(jù)完整性課件

1、第6章 索引與數(shù)據(jù)完整性,數(shù)據(jù)完整性,綜合應(yīng)用訓(xùn)練,6.1 索引,6.1.1 索引的分類 1. 聚集索引 聚集索引將數(shù)據(jù)行的鍵值在表內(nèi)排序并存儲對應(yīng)的數(shù)據(jù)記錄，使得數(shù)據(jù)表物理順序與索引順序一致。SQL Server 2008是按B樹（BTREE）方式組織聚集索引的，B樹方式構(gòu)建為包含了多個節(jié)點的一棵樹。頂部的節(jié)點構(gòu)成了索引的開始點，叫做根。每個節(jié)點中含有索引列的幾個值，一個節(jié)點中的每個值又都指向另一個節(jié)點或者指向表中的一行，一個節(jié)點中的值必須是有序排列的。指向一行的一個節(jié)點叫做葉子頁。葉子頁本身也是相互連接的，一個葉子頁有一個指針指向下一組。這樣，表中的每一行都會在索引中有一個對應(yīng)值。查詢的時

2、候就可以根據(jù)索引值直接找到所在的行。 聚集索引中B樹的葉節(jié)點存放數(shù)據(jù)頁信息。聚集索引在索引的葉級保存數(shù)據(jù)。這意味著不論聚集索引里有表的哪個（或哪些）字段，這些字段都會按順序被保存在表中。由于存在這種排序，所以每個表只會有一個聚集索引。,6.1.1 索引的分類,2. 非聚集索引 非聚集索引完全獨立于數(shù)據(jù)行的結(jié)構(gòu)。SQL Server 2008也是按B樹組織非聚集索引的，與聚集索引不同之處在于：非聚集索引B樹的葉節(jié)點不存放數(shù)據(jù)頁信息，而是存放非聚集索引的鍵值，并且每個鍵值項都有指針指向包含該鍵值的數(shù)據(jù)行。 對于非聚集索引，表中的數(shù)據(jù)行不按非聚集鍵的次序存儲。 在非聚集索引內(nèi)，從索引行指向數(shù)據(jù)行的指

3、針稱為行定位器。行定位器的結(jié)構(gòu)取決于數(shù)據(jù)頁的存儲方式是堆集還是聚集。對于堆集，行定位器是指向行的指針。對于有聚集索引的表，行定位器是聚集索引鍵，只有在表上創(chuàng)建聚集索引時，表內(nèi)的行才按特定順序存儲。這些行按聚集索引鍵順序存儲。如果一個表只有非聚集索引，它的數(shù)據(jù)行將按無序的堆集方式存儲。,6.1.2 系統(tǒng)表sysindexes,在此，將介紹另一系統(tǒng)表sysindexes。當(dāng)用戶創(chuàng)建數(shù)據(jù)庫時，系統(tǒng)將自動創(chuàng)建系統(tǒng)表sysindexes，用戶創(chuàng)建的每個索引均將在系統(tǒng)表sysindexes中登記，當(dāng)創(chuàng)建一個索引時，如果該索引已存在，則系統(tǒng)將報錯，因此，創(chuàng)建一個索引前，應(yīng)先查詢sysindexes表，若待定

4、義的索引已存在，則先刪除之，然后再創(chuàng)建索引；當(dāng)然，也可采用其它措施，如檢測到待定義的索引已存在，則不創(chuàng)建該索引。系統(tǒng)表sysindexes的主要字段如表6.1所示。,表6-1 系統(tǒng)表sysindexes的主要字段,6.1.3 索引的創(chuàng)建,1界面方式創(chuàng)建索引 下面以XS表中按借書證號建立主鍵索引及按姓名建立非惟一索引（索引組織方式為非聚集索引）為例，介紹索引的創(chuàng)建方法。 啟動“SQL Server Management Studio”在“對象資源管理器”中展開“數(shù)據(jù)庫XSBOOK”選擇“表”中的“dbo.XS”右擊其中的“索引”項，在彈出的快捷菜單上選擇“新建索引(N)”菜單項。,6.1.3 索

5、引的創(chuàng)建,這時，用戶可以在彈出的“新建索引”窗口中輸入索引名稱（索引名在表中必須唯一），如PK_XS，選擇索引類型為“聚集”、勾選“唯一”復(fù)選框單擊新建索引窗口的“添加”按鈕在彈出選擇列窗口（如圖6.1所示）中選擇要添加的列添加完畢后，單擊“確定”按鈕，在主界面中為索引鍵列設(shè)置相關(guān)的屬性單擊“確定”按鈕，即完成索引的創(chuàng)建工作。,圖6.1 添加索引鍵列,6.1.3 索引的創(chuàng)建,在表設(shè)計器窗口創(chuàng)建索引的方法如下： （1）右擊XSBOOK數(shù)據(jù)庫中的“dbo.XS”表，在彈出的快捷菜單中選擇“設(shè)計”菜單項，打開“表設(shè)計器”窗口。,（2）在“表設(shè)計器”窗口中，選擇“借書證號”屬性列，右擊鼠標(biāo)，在彈出的快

6、捷菜單中選擇“索引/鍵”菜單項。在打開的“索引/鍵”窗口中單擊“添加”按鈕，并在右邊的“標(biāo)識”屬性區(qū)域的“名稱”一欄中確定新索引的名稱（用系統(tǒng)缺省的名或重新取名）。在右邊的常規(guī)屬性區(qū)域中的“列”一欄后面單擊“ ”按鈕，可以修改要創(chuàng)建索引的列。如果將“是唯一的”一欄設(shè)定為“是”則表示索引是唯一索引。在“表設(shè)計器”欄下的“創(chuàng)建為聚集的”選項中，可以設(shè)置是否創(chuàng)建為聚集索引，由于XS表中已經(jīng)存在聚集索引，所以這里的這個選項不可修改，如圖6.2所示。,圖6.2 “索引/鍵”窗口,6.1.3 索引的創(chuàng)建,2使用SQL命令創(chuàng)建索引 使用CREATE INDEX語句可以為表創(chuàng)建索引。語法格式： CREATE

7、UNIQUE /*指定索引是否唯一*/ CLUSTERED | NONCLUSTERED /*索引的組織方式*/ INDEX index_name /*索引名稱*/ ON database_name. schema_name . | schema_name. table_or_view_name ( column ASC | DESC ,.n ) /*索引定義的依據(jù)*/ INCLUDE ( column_name ,.n ) WITH ( ,.n ) /*索引選項*/ ON partition_scheme_name ( column_name ) /*指定分區(qū)方案*/ | /*指定索引文件所

8、在的文件組*/ | default | partition_scheme_name | NULL /*指定數(shù)據(jù)的位置*/ ; ,6.1.3 索引的創(chuàng)建,其中： := PAD_INDEX = ON | OFF | FILLFACTOR = fillfactor | SORT_IN_TEMPDB = ON | OFF | IGNORE_DUP_KEY = ON | OFF | STATISTICS_NORECOMPUTE = ON | OFF | DROP_EXISTING = ON | OFF | ONLINE = ON | OFF | ALLOW_ROW_LOCKS = ON | OFF |

9、ALLOW_PAGE_LOCKS = ON | OFF | MAXDOP = max_degree_of_parallelism 說明：,6.1.3 索引的創(chuàng)建,【例6.1】 對于JY表，按借書證號+ISBN創(chuàng)建索引。 /*創(chuàng)建簡單索引*/ USE XSBOOK GO IF EXISTS (SELECT name FROM sysindexes WHERE name = JY_num_ind ) DROP INDEX JY.JY_num_ind GO CREATE INDEX JY_num_ind ON JY (借書證號,ISBN) GO 【例6.2】 根據(jù)BOOK表的ISBN列創(chuàng)建惟一聚集索

10、引，因為指定了 CLUSTERED 子句，所以該索引將對磁盤上的數(shù)據(jù)進(jìn)行物理排序。 /*創(chuàng)建唯一聚集索引*/ CREATE UNIQUE CLUSTERED INDEX book_id_ind ON book(ISBN) GO,6.1.3 索引的創(chuàng)建,【例6.3】 根據(jù)XS表中的借書量字段創(chuàng)建索引，例中使用了FILLFACTOR子句。 CREATE NONCLUSTERED INDEX num_ind ON XS (借書量) WITH FILLFACTOR = 60 【例6.4】 根據(jù)XS表中借書證號字段創(chuàng)建惟一聚集索引。如果輸入了重復(fù)鍵值，將忽略該INSERT或UPDATE語句。 CREAT

11、E UNIQUE CLUSTERED INDEX xs_ind ON XS(借書證號) WITH IGNORE_DUP_KEY,6.1.3 索引的創(chuàng)建,創(chuàng)建索引有如下幾點要說明： （1）在計算列上創(chuàng)建索引。對于UNIQUE或PRIMARY KEY索引，只要滿足索引條件，就可以包含計算列，但計算列必須具有確定性、必須精確。若計算列中帶有函數(shù)時，使用該函數(shù)時有相同的參數(shù)輸入，輸出的結(jié)果也一定相同時該計算列是確定的。而有些函數(shù)如getdate()每次調(diào)用時都輸出不同的結(jié)果，這時就不能在計算列上定義索引。計算列為text、ntext或image列時也不能在該列上創(chuàng)建索引。 （2）在視圖上創(chuàng)建索引?？梢?/p>

12、在視圖上定義索引，索引視圖能自動反映出創(chuàng)建索引后對基表數(shù)據(jù)所做的修改。,6.1.3 索引的創(chuàng)建,【例6.5】 創(chuàng)建一個視圖，并為該視圖創(chuàng)建索引。 /*定義視圖，如下例子中，由于使用了WITH SCHEMABINDING子句，因此，定義視圖時，SELECT子句中表名必須為：架構(gòu)名名.視圖名的形式。*/ CREATE VIEW VIEW1 WITH SCHEMABINDING AS SELECT 索書號,書名,姓名 FROM dbo.JY, dbo.BOOK, dbo.XS WHERE JY.ISBN=BOOK.ISBN AND XS.借書證號=JY.借書證號 GO /*在視圖VIEW1上定義索引

13、*/ CREATE UNIQUE CLUSTERED INDEX Ind1 ON dbo.VIEW1(索書號 ASC) GO,6.1.4 索引的刪除,1通過界面方式刪除索引 通過界面方式刪除索引的主要步驟如下：啟動“SQL Server Management Studio”在“對象資源管理器”中展開數(shù)據(jù)庫“XSBOOK”“表”“dbo.XS”“索引”，選擇其中要刪除的索引，單擊鼠標(biāo)右鍵，在彈出的快捷菜單上選擇“刪除”菜單項。在打開的“刪除對象”窗口單擊“確定”按鈕即可。 2通過SQL命令刪除索引 語法格式： DROP INDEX index_name ON table_or_view_name

14、 ,.n | table_or_view_name.index_name ,.n 【例6.6】 刪除例6.1為XSBOOK數(shù)據(jù)庫中表JY創(chuàng)建的索引JY_num_ind。 USE XSBOOK GO IF EXISTS (SELECT name FROM sysindexes WHERE name = JY_num_ind) DROP INDEX JY. JY_num_ind GO,6.2數(shù)據(jù)完整性,6.2.1 數(shù)據(jù)完整性的分類 1域完整性 域完整性又稱為列完整性，指列數(shù)據(jù)輸入的有效性。實現(xiàn)域完整性的方法有：限制類型（通過數(shù)據(jù)類型）、格式（通過CHECK約束和規(guī)則）或可能的取值范圍（通過CHEC

15、K約束、DEFALUT定義、NOT NULL定義和規(guī)則）等。 CHECK約束通過顯示輸入到列中的值來實現(xiàn)域完整性；DEFAULT定義后，如果列中沒有輸入值則填充默認(rèn)值來實現(xiàn)域完整性；通過定義列為NOT NULL限制輸入的值不能為空也能實現(xiàn)域完整性。 例如：對于數(shù)據(jù)庫XSBOOK的XS表，如果允許讀者當(dāng)前的在借圖書量最多為20本，為了對讀者當(dāng)前的在借圖書量進(jìn)行限制，可以在定義XS表時，規(guī)定：“0借書量20”的約束條件達(dá)到目的。,6.2.1 數(shù)據(jù)完整性的分類,【例6.7】 定義表XS的同時定義借書量字段的約束條件。 USE XSBOOK GO CREATE TABLE XS ( 借書證號 char

16、(8) NOT NULL PRIMARY KEY, 姓名 char(8) NOT NULL, 專業(yè) char(12) NOT NULL, 性別 bit NOT NULL DEFAULT 1, 出生時間 date NOT NULL , /*如下語句定義字段的同時定義約束條件*/ 借書量 int CHECK (借書量 =0 AND 借書量=20) NOT NULL, 照片 varbinary(MAX) NULL ) GO,6.2.1 數(shù)據(jù)完整性的分類,2實體完整性 實體完整性又稱為行的完整性，要求表中有一個主鍵，其值不能為空且能惟一地標(biāo)識對應(yīng)的記錄。通過索引、UNIQUE 約束、PRIMARY K

17、EY 約束或 IDENTITY 屬性等可實現(xiàn)數(shù)據(jù)的實體完整性。例如，對于XSBOOK數(shù)據(jù)庫中XS表，借書證號作為主鍵，每一個讀者的借書證號能惟一的標(biāo)識該讀者對應(yīng)的行記錄信息，那么在輸入數(shù)據(jù)時，則不能有相同借書證號的行記錄，通過對借書證號這一字段建立主鍵約束可實現(xiàn)表XS的實體完整性。 3參照完整性 參照完整性又稱為引用完整性。參照完整性保證主表中的數(shù)據(jù)與從表中數(shù)據(jù)的一致性。SQL Server 2008中，參照完整性的實現(xiàn)是通過定義外鍵（外碼）與主鍵（主碼）之間或外鍵與惟一鍵之間的對應(yīng)關(guān)系實現(xiàn)的。參照完整性確保鍵值在所有表中一致。,6.2.1 數(shù)據(jù)完整性的分類,XS和JY表的對應(yīng)關(guān)系如表6.2、

18、和表6.3所示。,6.2.1 數(shù)據(jù)完整性的分類,如果定義了兩個表之間的參照完整性，則要求： （1）從表不能引用不存在的鍵值。例如：對于JY表中行記錄出現(xiàn)的借書證號必須是XS表中已存在的。 （2）如果主表中的鍵值更改了，那么在整個數(shù)據(jù)庫中，對從表中該鍵值的所有引用要進(jìn)行一致的更改。例如：如果對XS表中的某一借書證號修改，JY表中所有對應(yīng)借書證號也要進(jìn)行相應(yīng)的修改。 （3）如果要刪除主表中的某一記錄，應(yīng)先刪除從表中與該記錄匹配的相關(guān)記錄。,6.2.2 域完整性的實現(xiàn),1CHECK約束的定義與刪除 對于timestamp和identity兩種類型字段不能定義CHECK約束。 （1）通過界面方式創(chuàng)建與

19、刪除CHECK約束。 對于XSBOOK數(shù)據(jù)庫的XS表，若要求讀者的借書證號必須由8個數(shù)字字符構(gòu)成，并且不能為“”，通過企業(yè)管理器對借書證號字段定義這一約束可按如下步驟進(jìn)行： 啟動“SQL Server Management Studio”在“對象資源管理器”中展開“數(shù)據(jù)庫”“XSBOOK”“表”選擇“dbo.XS”，右擊鼠標(biāo)選擇“設(shè)計”菜單項。 在打開的“表設(shè)計器”窗口中選擇“借書證號”屬性列，右擊鼠標(biāo)選擇“CHECK約束”菜單項。,6.2.2 域完整性的實現(xiàn), 在打開的 “CHECK約束”窗口（如圖6.3所示）中，單擊“添加”按鈕，添加一個“CHECK約束”。在常規(guī)屬性區(qū)域中的“表達(dá)式”一欄

20、后面單擊“ ”按鈕（或直接在文本框中輸入內(nèi)容），打開“CHECK約束表達(dá)式”窗口，并編輯相應(yīng)的CHECK約束表達(dá)式為：“借書證號 LIKE 0-90-90-90-90-90-90-90-9 AND 借書證號00000000”。,圖6.3 CHECK選項卡屬性窗口,6.2.2 域完整性的實現(xiàn),（2）使用SQL語句在創(chuàng)建表時創(chuàng)建CHECK約束 利用T-SQL命令可以使用兩種方式定義約束：作為列的約束或作為表的約束。 語法格式： CREATE TABLE table_name /*指定表名*/ (column_name datatype NOT NULL | NULL /*指定為空性*/ | DEF

21、AULT constraint_expression /*指定默認(rèn)值*/ | CONSTRAINT constraint_name CHECK ( logical_expression )/*CHECK約束表達(dá)式*/ ,n CONSTRAINT constraint_name CHECK ( logical_expression ),n ),6.2.2 域完整性的實現(xiàn),【例6.8】 對于數(shù)據(jù)庫XSBOOK中的表BOOK，要求書的最高限價為250元，請重新定義BOOK表。 USE XSBOOK GO CREATE TABLE BOOK ( ISBN char(16)NOT NULL PRIMAR

22、Y KEY, 書名 char(26) NOT NULL, 作者 char(8) NOT NULL, 出版社 char(20) NOT NULL, 價格float NOT NULL CHECK (價格= 250), 復(fù)本量 int NOT NULL, 庫存量int NOT NULL ),6.2.2 域完整性的實現(xiàn),【例6.9】 創(chuàng)建表student，有學(xué)號、最好成績和平均成績?nèi)?，要求最好成績必須大于平均成績?CREATE TABLE student2 ( 學(xué)號 char(6) NOT NULL, 最好成績 int NOT NULL, 平均成績 int NOT NULL, CHECK(最好成績

23、平均成績) ),6.2.2 域完整性的實現(xiàn),（3）使用SQL語句在修改表時創(chuàng)建CHECK約束 語法格式： ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (logical_expression) 【例6.10】 通過修改XSBOOK數(shù)據(jù)庫的XS表，增加借書證號字段的CHECK約束：要求借書證號必須全由8個數(shù)字字符構(gòu)成，并且不等于00000000。 ALTER TABLE XS ADD CONSTRAINT card_constraint CHECK (借書證號 LIKE 0-90-90-90-90-90-90-90-9 A

24、ND 借書證號00000000) GO,6.2.2 域完整性的實現(xiàn),（4）使用SQL語句刪除CHECK約束 CHECK約束的刪除可在對象資源管理器中通過界面刪除，讀者可以自己試一試，在此介紹如何利用SQL命令刪除。 使用ALTER TABLE語句的DROP子句可以刪除CHECK約束。語法格式： ALTER TABLE table_name DROP CONSTRAINT check_name 【例6.11】 刪除XSBOOK數(shù)據(jù)庫中XS表借書證號字段的CHECK約束。 IF EXISTS (SELECT name FROM sysobjects WHERE name = card_constr

25、aint AND type = C) BEGIN ALTER TABLE XS DROP CONSTRAINT card_constraint END,6.2.2 域完整性的實現(xiàn),2規(guī)則對象的定義、使用與刪除 規(guī)則是一組使用T-SQL語句組成的條件語句，規(guī)則提供了另外一種在數(shù)據(jù)庫中實現(xiàn)域完整性與用戶定義完整性的方法。規(guī)則對象的使用方法如下： 定義規(guī)則對象； 將規(guī)則對象綁定到列或用戶自定義類型； 在SQL Server 2008中規(guī)則對象的定義可以利用CREATE RULE語句來實現(xiàn)。 （1）規(guī)則對象的定義 語法格式： CREATE RULE schema_name. rule_name AS

26、condition_expression,6.2.2 域完整性的實現(xiàn),創(chuàng)建規(guī)則時，一般使用局部變量表示UPDATE 或 INSERT 語句輸入的值。另外有如下幾點需說明： 創(chuàng)建的規(guī)則對先前已存在于數(shù)據(jù)庫中的數(shù)據(jù)無效。 單個批處理中，CREATE RULE語句不能與其它T-SQL語句組合使用。 規(guī)則表達(dá)式的類型必須與列的數(shù)據(jù)類型兼容，不能將規(guī)則綁定到text、image或 timestamp列。要用單引號（）將字符和日期常量引起來，在十六進(jìn)制常量前加0 x。 對于用戶定義數(shù)據(jù)類型，當(dāng)在該類型的數(shù)據(jù)列中插入值，或更新該類型的數(shù)據(jù)列時，綁定到該類型的規(guī)則才會激活。規(guī)則不檢驗變量，所以在向用戶定義數(shù)據(jù)

27、類型的變量賦值時，不能與列綁定的規(guī)則沖突。 如果列同時有默認(rèn)值和規(guī)則與之關(guān)聯(lián)，則默認(rèn)值必須滿足規(guī)則的定義，與規(guī)則沖突的默認(rèn)值不能關(guān)聯(lián)到列。,6.2.2 域完整性的實現(xiàn),（2）將規(guī)則對象綁定到自定義類型或列 語法格式： sp_bindrule rulename = rule , objname = object_name , futureonly = futureonly_flag 說明：參數(shù)rule為 CREATE RULE 語句創(chuàng)建的規(guī)則名，要用單引號括起來。參數(shù)object_name為綁定到規(guī)則的列或用戶定義的數(shù)據(jù)類型，如果object_name采用 表名.字段名 格式，則認(rèn)為綁定到表的列

28、，否則綁定到用戶定義數(shù)據(jù)類型；參數(shù)futureonly_flag僅當(dāng)將規(guī)則綁定到用戶定義的數(shù)據(jù)類型時才使用，如果futureonly_flag設(shè)置為futureonly ，用戶定義數(shù)據(jù)類型的現(xiàn)有列不繼承新規(guī)則。如果 futureonly_flag 為 NULL，當(dāng)被綁定的數(shù)據(jù)類型當(dāng)前無規(guī)則時，新規(guī)則將綁定到用戶定義數(shù)據(jù)類型的每一列。,6.2.2 域完整性的實現(xiàn),（3）應(yīng)用舉例 【例6.12】 如下程序創(chuàng)建一個規(guī)則，并綁定到XSBOOK數(shù)據(jù)庫XS表的借書證號字段。 USE XSBOOK GO CREATE RULE num_rule AS num like 1-61-61-9 1-9 1-9 1

29、-9 0-9 0-9 0-9 GO EXEC sp_bindrule num_rule, XS.借書證號/*執(zhí)行存儲過程使用EXEC命令*/ GO 【例6.13】 創(chuàng)建一個規(guī)則，用以限制輸入到該規(guī)則所綁定的列的值只能是該規(guī)則中列出的值。 CREATE RULE list_rule AS list IN (機(jī)械工業(yè)出版社, 電子工業(yè)出版社, 郵電出版社, 高等教育出版社,科學(xué)出版社,清華大學(xué)出版社) GO EXEC sp_bindrule list_rule, BOOK.出版社 GO,6.2.2 域完整性的實現(xiàn),【例6.14】 定義一個用戶數(shù)據(jù)類型telphone，及規(guī)則“tel_rule”，然

30、后將規(guī)則“tel_rule”綁定到用戶數(shù)據(jù)類型telphone上，最后定義表XS3，其電話號碼字段的數(shù)據(jù)類型為telphone。 CREATE TYPE telphone FROM char(12) NULL/*創(chuàng)建用戶定義數(shù)據(jù)類型*/ GO CREATE RULE tel_rule/*創(chuàng)建規(guī)則*/ AS tel LIKE 0-90-90-9-0-90-90-90-90-90-90-90-9 GO EXEC sp_bindrule tel_rule, telphone/*將規(guī)則對象綁定到用戶定義數(shù)據(jù)類型*/ GO CREATE TABLE XS3 ( 借書證號 char(8) NOT NULL

31、, 姓名 char(8) NOT NULL, 專業(yè) char(12) NOT NULL, 性別 bit NOT NULL, 出生時間 date NOT NULL, 電話 telphone,/*將電話定義為telphone類型*/ 借書量 tinyint CHECK (借書量 =0 AND 借書量=20) NULL, 照片 varbinary(MAX) NULL ) GO,6.2.2 域完整性的實現(xiàn),（4）規(guī)則對象的刪除 刪除規(guī)則對象前，首先應(yīng)使用系統(tǒng)存儲過程sp_unbindrule解除被綁定對象與規(guī)則對象之間的綁定關(guān)系。 語法格式： sp_unbindrule objname = objec

32、t_name , futureonly = futureonly_flag 在解除列或自定義類型與規(guī)則對象之間的綁定關(guān)系后，就可以刪除規(guī)則對象了。 語法格式： DROP RULE rule ,.n 【例6.15】 解除規(guī)則對象list_rule與XSBOOK數(shù)據(jù)庫BOOK表的出版社字段的綁定關(guān)系，然后刪除規(guī)則對象list_rule。 IF EXISTS (SELECT name FROM sysobjects WHERE name = list_rule AND type = R) BEGIN EXEC sp_unbindrule BOOK.出版社 DROP RULE list_rule EN

33、D GO,6.2.3 實體完整性的實現(xiàn),1使用界面方式創(chuàng)建和刪除PRIMARY KEY約束 （1）創(chuàng)建PRIMARY KEY約束 如果要對XS表按借書證號建立PRIMARY KEY約束，可以按第3章中創(chuàng)建表的第3步中所介紹的設(shè)置主鍵的相關(guān)步驟進(jìn)行。 當(dāng)創(chuàng)建主鍵時，系統(tǒng)將自動創(chuàng)建一個名稱以“PK_”為前綴、后跟表名的主鍵索引，系統(tǒng)自動按聚集索引方式組織主鍵索引。 （2）刪除PRIMARY KEY約束 如果要刪除對表XS中對借書證號字段建立的PRIMARY KEY約束，按如下步驟進(jìn)行：在“對象資源管理器”中選擇dbo.XS表圖標(biāo)，右擊鼠標(biāo)，在彈出的快捷菜單中選擇“設(shè)計”菜單項，進(jìn)入“表設(shè)計器”窗口

34、。選中“XS表設(shè)計器”窗口中主鍵所對應(yīng)的行，右擊鼠標(biāo)，在彈出的快捷菜單中選擇“刪除主鍵”菜單項即可。,6.2.3 實體完整性的實現(xiàn),2使用界面方式創(chuàng)建和刪除UNIQUE約束 （1）創(chuàng)建UNIQUE約束 如果要對XS表中的“姓名”列創(chuàng)建UNIQUE約束，以保證該列取值的唯一性，可按以下步驟進(jìn)行： 進(jìn)入XS表的“表設(shè)計器”窗口，選擇“姓名”屬性列并右擊鼠標(biāo)，在彈出的快捷菜單中選擇“索引/鍵”菜單項，打開“索引/鍵”窗口。,6.2.3 實體完整性的實現(xiàn),在窗口中單擊“添加”按鈕，并在右邊的“標(biāo)識”屬性區(qū)域的“名稱”一欄中輸入唯一鍵的名稱（用系統(tǒng)默認(rèn)的名或重新取名）。在常規(guī)屬性區(qū)域的“類型”一欄中選擇

35、類型為“唯一鍵”，如圖6.4所示。,圖6.4 創(chuàng)建唯一鍵,（2）刪除UNIQUE約束 打開如圖6.4所示的“索引/鍵”窗口，選擇要刪除的UNIQUE約束，單擊左下方的“刪除”按鈕，單擊“關(guān)閉”按鈕，保存表的修改即可。,6.2.3 實體完整性的實現(xiàn),3使用SQL命令創(chuàng)建及刪除PRIMARY KEY約束或UNIQUE約束 （1）創(chuàng)建表的同時創(chuàng)建PRIMARY KEY約束或UNIQUE約束 語法格式： CREATE TABLE table_name /*指定表名*/ ( column_name datatype /*定義字段*/ CONSTRAINT constraint_name /*約束名*/

36、PRIMARY KEY | UNIQUE /*定義約束類型*/ CLUSTERED | NONCLUSTERED /*定義約束的索引類型*/ ,n ) 說明： PRIMARY KEY | UNIQUE：定義約束的關(guān)鍵字，PRIMARY KEY為主鍵，UNIQUE為唯一鍵。 CLUSTERED | NONCLUSTERED：定義約束的索引類型，CLUSTERED表示聚集索引，NONCLUSTERED表示非聚集索引，與CREATE INDEX語句中的選項相同。,6.2.3 實體完整性的實現(xiàn),【例6.16】 創(chuàng)建XS4表，并對借書證號字段創(chuàng)建PRIMARY KEY約束，對姓名字段定義UNIQUE約束

37、。 USE XSBOOK GO CREATE TABLE XS4 ( 借書證號 char(8) NOT NULL CONSTRAINT XS_PK PRIMARY KEY, 姓名 char(8) NOT NULL CONSTRAINT XM_UK UNIQUE, 專業(yè) char(12) NOT NULL, 性別 bit NOT NULL, 出生時間 date NOT NULL, 借書量 int CHECK (借書量 =0 AND 借書量=20) NULL, 照片 varbinary(MAX) NULL ) GO,6.2.3 實體完整性的實現(xiàn),【例6.17】 創(chuàng)建借閱歷史表JYLS2，由借書證號

38、、索書號、借書時間作為聯(lián)合主鍵。 CREATE TABLE JYLS2 ( 借書證號 char(8) NOT NULL, ISBN char(16) NOT NULL, 索書號 char(10) NOT NULL, 借書時間 date NOT NULL, 還書時間 date NOT NULL, PRIMARY KEY (索書號,借書證號,借書時間)/*定義主鍵*/ ),6.2.3 實體完整性的實現(xiàn),（2）修改表時創(chuàng)建PRIMARY KEY約束或UNIQUE約束 創(chuàng)建PRIMARY KEY約束 語法格式： ALTER TABLE table_name ADD CONSTRAINT constra

39、int_name PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED ( column ,.n ) 【例6.18】 修改XS4表，向其中添加一個“身份證號碼”字段，對該字段定義UNIQUE約束。對“出生時間”字段定義UNIQUE約束。 ALTER TABLE XS4 ADD身份證號碼 char(18) CONSTRAINT SF_UK UNIQUE NONCLUSTERED (身份證號碼) GO ALTER TABLE XS4 ADDCONSTRAINT CJSJ_UK UNIQUE NONCLUSTERED (出生時間),6.2.3 實體完整性的實現(xiàn),

40、（3）刪除PRIMARY KEY約束或UNIQUE約束 刪除PRIMARY KEY約束或UNIQUE約束需要使用ALTER TABLE的DROP子句。 語法格式： ALTER TABLE table_name DROP CONSTRAINT constraint_name ,n 【例6.19】 刪除前面例中在表XS4上創(chuàng)建的PRIMARY KEY約束和UNIQUE約束。 ALTER TABLE XS4 DROPCONSTRAINT XS_PK, XM_UK GO,6.2.4 參照完整性的實現(xiàn),1使用界面方式定義表間的參照關(guān)系 例如：在數(shù)據(jù)庫XSBOOK中要建立XS表與JY表之間的參照完整性，操

41、作步驟如下： （1）按照前面所介紹的方法定義主表的主鍵。由于之前在創(chuàng)建表的時候已經(jīng)定義XS表中的借書證號字段為主鍵，所以這里就不需要再定義主表的主鍵了。 （2）啟動“SQL Server Management Studio”在“對象資源管理器”中展開“數(shù)據(jù)庫”“XSBOOK”選擇“數(shù)據(jù)庫關(guān)系圖”，右擊鼠標(biāo)，在出現(xiàn)的快捷菜單中選擇“新建數(shù)據(jù)庫關(guān)系圖”菜單項，打開“添加表”窗口。 （3）在出現(xiàn)的“添加表”窗口中選擇要添加的表，這里選擇表XS和表JY。單擊“添加”按鈕完成表的添加，之后單擊“關(guān)閉”按鈕退出窗口。 （4）在“數(shù)據(jù)庫關(guān)系圖設(shè)計”窗口將鼠標(biāo)指向主表的主鍵，并拖動到從表，即將XS表中的“借書

42、證號”字段拖動到從表JY中的“借書證號”字段。,6.2.4 參照完整性的實現(xiàn),（5）在彈出的“表和列”窗口中輸入關(guān)系名、設(shè)置主鍵表和列名，如圖6.5所示，單擊“表和列”窗口中的“確定”按鈕，再單擊“外鍵關(guān)系”窗口中的“確認(rèn)”按鈕，進(jìn)入如圖6.6所示的界面。,圖6.5 設(shè)置參照完整性,6.2.4 參照完整性的實現(xiàn),圖6.6 主表和從表的參照關(guān)系圖,6.2.4 參照完整性的實現(xiàn),2使用界面方式刪除表間的參照關(guān)系 如果要刪除前面建立的XS表與JY表之間的參照關(guān)系，可按以下步驟進(jìn)行： （1）在“XSBOOK”數(shù)據(jù)庫的“數(shù)據(jù)庫關(guān)系圖”目錄下選擇要修改的“關(guān)系圖”，如Diagram_0，右擊鼠標(biāo)，在彈出的

43、快捷菜單中選擇“修改”菜單項，打開“數(shù)據(jù)庫關(guān)系圖設(shè)計”窗口。 （2）在“數(shù)據(jù)庫關(guān)系圖設(shè)計”窗口中，選擇已經(jīng)建立的“關(guān)系”，單擊鼠標(biāo)右鍵，選擇“從數(shù)據(jù)庫中刪除關(guān)系”，如圖6.7所示。在隨后彈出的對話框中，單擊“是”按鈕，刪除表之間的關(guān)系。,圖6.7 刪除關(guān)系,6.2.4 參照完整性的實現(xiàn),3使用SQL命令定義表間的參照關(guān)系 前面已介紹了創(chuàng)建主鍵（PRMARY KEY約束）及唯一鍵（UNIQUE約束）的方法，在此將介紹通過SQL命令創(chuàng)建外鍵的方法。 （1）創(chuàng)建表的同時定義外碼約束 語法格式： CREATE TABLE table_name ( column_name datatype CONSTR

44、AINT constraint_name FOREIGN KEY ( column ,.n ) REFERENCES referenced_table_name ( ref_column ,.n ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT NOT FOR REPLICATION ),6.2.4 參照完整性的實現(xiàn),【例6.20】 在XSBOOK數(shù)據(jù)庫中創(chuàng)建主表XS1和BOOK1，借書證號為XS1表的主鍵，ISBN為BO

45、OK1的主鍵，然后定義從表JY1，JY1.借書證號為外鍵，與XS1的主鍵對應(yīng)，當(dāng)對主表進(jìn)行更新和刪除操作時，對從表采用級聯(lián)操作，JY1.ISBN為晚間，與BOOK1的主鍵對應(yīng)，當(dāng)對主表進(jìn)行更新和刪除時，對從表采用NO ACTION方式。 【例6.21】 創(chuàng)建point表，要求表中所有的索書號、借書證號和借書時間組合都必須出現(xiàn)在JYLS表中。 CREATE TABLE point ( 借書證號 char(8) NOT NULL, ISBN char(16) NOT NULL, 索書號 char(10) NOT NULL, 借書時間 date NOT NULL, 還書時間 date NOT NULL, CONSTRAINT FK_point FOREIGN KEY (索書號,借書證號,借書時間) REFERENCES JYLS (索書號,借書證號,借書時間) ON DELETE NO ACTION ),6.2.4 參照完整性的實現(xiàn),（2）通過修改表定義外鍵約束 使用ALTER TABLE語句的ADD子句也可以定義外鍵約束，語法格式： ALTER TABLE table_name ADDCONSTRAINT constraint_name FOREIGN KEY ( column ,.n ) REFERENCES referenced_table_name ( ref_column