sql_server_2000實驗指導(dǎo)-實驗4

1、,實驗4 完整性約束,,數(shù)據(jù)庫的完整性是指數(shù)據(jù)的正確性和相容性。例如，在性別字段中，只能取“男”或“女”兩個值中的一個，沒有第三個可取值；學(xué)生的學(xué)號必須唯一。在SQL Server 2000中，系統(tǒng)提供多種強制數(shù)據(jù)完整性的機制，以便確保數(shù)據(jù)庫中的數(shù)據(jù)質(zhì)量。 【知識要點】1．完整性約束作用的對象關(guān)系：若干元組間、關(guān)系集合上以及關(guān)系之間的聯(lián)系的約束。元組：元組中各個字段間的聯(lián)系的約束。列：列的類型、取值范圍、精度、唯一性、為空

2、性、缺省定義、CHECK約束、主鍵約束、外鍵約束2．完整性約束類型實體完整性；域完整性；參照完整性；用戶定義完整性。3．實體完整性 實體完整性將行定義為特定表的唯一實體。實體完整性作用的對象是列，強制表的標識符列或主鍵的完整性（通過UNIQUE 約束、PRIMARY KEY 約束或 IDENTITY 屬性）。,,實驗4 完整性約束,,4．域完整性 域完整性作用的對象是列，是指給定列的輸入有效性。強制域有

3、效性的方法有：限制類型（通過數(shù)據(jù)類型）、格式（通過 CHECK 約束和規(guī)則）或可能值的范圍（通過 FOREIGN KEY 約束、CHECK 約束、DEFAULT 定義、NOT NULL 定義和規(guī)則）。5．引用完整性 引用完整性作用的對象是關(guān)系。在輸入或刪除記錄時，引用完整性保持表之間已定義的關(guān)系。在 Microsoft® SQL Server? 2000 中，引用完整性基于子表外鍵與主表主鍵之間或子表外鍵與主表唯一鍵

4、之間的關(guān)系（通過 FOREIGN KEY 和 CHECK 約束）。引用完整性確保鍵值在所有表中一致。這樣的一致性要求不能引用不存在的值，如果鍵值更改了，那么在整個數(shù)據(jù)庫中，對該鍵值的所有引用要進行一致的更改。6．用戶定義完整性 用戶定義完整性使您可以定義不屬于其它任何完整性分類的特定業(yè)務(wù)規(guī)則，作用的對象可以是列，也可以是元組或關(guān)系。所有的完整性類型都支持用戶定義完整性，如：CREATE TABLE 中的所有列級和表級約束、存儲

5、過程和觸發(fā)器。,,實驗4 完整性約束,,7．CHECK約束 CHECK 約束通過限制輸入到列中的值來強制域的完整性。CHECK 約束從邏輯表達式返回結(jié)果是TRUE還是FALSE來判斷。例如，通過創(chuàng)建 CHECK 約束可將本科生的Age列的取值范圍限制在14歲至40歲之間，從而防止輸入的年齡超出正常的本科生范圍。其邏輯表達式為： Age >= 14 AND Age <= 40 （1）創(chuàng)建和修改 CHECK

6、 約束 作為表定義的一部分在創(chuàng)建表時創(chuàng)建；添加到現(xiàn)有表中。表和列可以包含多個 CHECK 約束。修改或刪除現(xiàn)有的 CHECK 約束。例如，可以修改表中列的CHECK 約束的表達式。（2）CHECK約束的設(shè)置 在現(xiàn)有表中添加 CHECK 約束時，該約束可以僅作用于新數(shù)據(jù)也可以同時作用于現(xiàn)有的數(shù)據(jù)。默認設(shè)置為 CHECK 約束同時作用于現(xiàn)有數(shù)據(jù)和新數(shù)據(jù)。（3）禁用 CHECK 約束下列情況可以禁用現(xiàn)有 CHECK

7、約束： INSERT 和 UPDATE 語句，可以允許不經(jīng)約束確認修改表中的數(shù)據(jù)。在執(zhí)行 INSERT 和 UPDATE 語句時，如果新數(shù)據(jù)違反約束或約束應(yīng)只適用于數(shù)據(jù)庫中已有的數(shù)據(jù)，那么可禁用 CHECK 約束。復(fù)制處理。如果該約束為源數(shù)據(jù)庫所特有，則在復(fù)制時請禁用 CHECK 約束。,,實驗4 完整性約束,,8．規(guī)則（Rule） 所謂規(guī)則（Rule）是指當你對表做插入或更新操作時，輸入值或新數(shù)據(jù)必須符合對應(yīng)列預(yù)先設(shè)

8、定的條件。如果不符合，SQL Server就不會讓該輸入值插入到表內(nèi)或不允許用該新數(shù)據(jù)更新原有數(shù)據(jù)。當某個規(guī)則被創(chuàng)建后，有一個唯一的名字，并且成為數(shù)據(jù)庫中的一個對象。 用戶要使用規(guī)則時，需要把規(guī)則綁定至表中相應(yīng)的一列或多列上或某個用戶定義的數(shù)據(jù)類型上。不使用時再將綁定解除。9．缺省值（Default） 所謂缺省值（Default）是指當你向表中插入數(shù)據(jù)時，如果某些列未明確給出插入值，那么SQL Server將用預(yù)先在這

9、些列上定義的值作為插入值。當某個缺省值被創(chuàng)建后，有一個唯一的名字，并且成為數(shù)據(jù)庫中的一個對象。用戶要使用缺省值時，需要把缺省值對象綁定至表中相應(yīng)的一列或多列上或某個用戶定義的數(shù)據(jù)類型上。不使用時再將綁定解除。,【實驗?zāi)康摹空J識完整性約束對數(shù)據(jù)庫的重要性；掌握實體完整性的創(chuàng)建、修改、維護；掌握域完整性的創(chuàng)建、修改、維護；掌握參照完整性的創(chuàng)建、修改、維護；掌握用戶定義的完整性的創(chuàng)建、修改、維護。,,實驗4 完整性約束,,實驗4

10、．1 實體完整性約束 【實驗?zāi)康摹空莆沼闷髽I(yè)管理器創(chuàng)建PRIMARY KEY 約束；掌握用SQL創(chuàng)建PRIMARY KEY 約束；掌握用企業(yè)管理器創(chuàng)建UNIQUE約束；掌握用SQL創(chuàng)建UNIQUE約束；掌握用SQL創(chuàng)建IDENTITY 屬性列?！緦嶒瀮?nèi)容】用企業(yè)管理器為數(shù)據(jù)表S創(chuàng)建PRIMARY KEY 約束；用企業(yè)管理器創(chuàng)建新表TEST_SC，并創(chuàng)建PRIMARY KEY 約束，TEST_SC表的結(jié)構(gòu)定義如下：

11、 表名：TEST_SC包含的列如下： 學(xué)號：SNO CHAR（2）； 課號：CNO CHAR（2）； 成績：SCORE TINYINT； 主鍵：（SNO，CNO）。,,實驗4 完整性約束,,用SQL為現(xiàn)有表T在TNO列上創(chuàng)建PRIMARY KEY 約束；用SQL創(chuàng)建新表TEST_C，并以列約束形式創(chuàng)建PRIMARY KEY 約束。TEST_C表的結(jié)構(gòu)定義如下：

12、 表名：TEST_C 包含的列如下： 課號：CNO CHAR（2） 課名：CN CHAR（2） 課時：CT TINYINT 主鍵：CNO 主鍵約束名：PK_TEST_C用SQL創(chuàng)建新表TEST_TC，并以表約束形式創(chuàng)建PRIMARY KEY 約束。TEST_TC表的結(jié)構(gòu)定義如下：表名：TEST_TC包含的列如下：教師號：TNO CHAR（2）

13、 課號：CNO CHAR（2）主鍵：（TNO，CNO）主鍵約束名：PK_TEST_TC,,實驗4 完整性約束,,用企業(yè)管理器為現(xiàn)有表TEST_TC中的CNO和TNO列創(chuàng)建“UNIQUE約束”；用SQL為現(xiàn)有表C中的CN列創(chuàng)建“UNIQUE約束”；用企業(yè)管理器為現(xiàn)有表TEST_SC增加新列ID_SC，并創(chuàng)建此列屬性為IDENTITY;用SQL為現(xiàn)有表TEST_TC增加新列ID_TC，并創(chuàng)建此列屬性為IDENTITY；用

14、企業(yè)管理器刪除表C中CN列的UNIQUE約束；用SQL為刪除現(xiàn)有表TEST_SC的主鍵PK_TEST_SC和取消ID_SC列的標識屬性。,【實驗步驟】 1.用企業(yè)管理器為數(shù)據(jù)表S創(chuàng)建PRIMARY KEY 約束,在表設(shè)計器中為SNO創(chuàng)建主鍵,,實驗4 完整性約束,,2．用企業(yè)管理器創(chuàng)建新表TEST_SC，并創(chuàng)建PRIMARY KEY 約束,創(chuàng)建主鍵（SNO，CNO）,3．用SQL為現(xiàn)有表T在TNO列上創(chuàng)建PRIMARY KEY

15、約束,用SQL為表C創(chuàng)建PRIMARY KEY,表T的主鍵約束,,實驗4 完整性約束,,4．用SQL創(chuàng)建新表TEST_C，并以列約束形式創(chuàng)建PRIMARY KEY 約束,創(chuàng)建TEST_C表及其主鍵,創(chuàng)建的新表TEST_C及其主鍵,,實驗4 完整性約束,,5．用SQL創(chuàng)建新表TEST_TC，并以表約束形式創(chuàng)建PRIMARY KEY 約束,創(chuàng)建的新表TEST_TC及其主鍵對象,創(chuàng)建新表TEST_TCJ及其主鍵,,實驗4 完整性約

16、束,,6．用企業(yè)管理器為現(xiàn)有表TEST_TC中的CNO和TNO列創(chuàng)建“UNIQUE約束”,在表屬性窗口中創(chuàng)建UNIQUE約束,TEST_TC表的屬性窗口,,實驗4 完整性約束,,7．用SQL為現(xiàn)有表C中的CN列創(chuàng)建“UNIQUE約束”,給表C創(chuàng)建UNIQUE約束,表C中的對象,,實驗4 完整性約束,,8．用企業(yè)管理器為現(xiàn)有表TEST_SC增加新列ID_SC，并創(chuàng)建此列屬性為IDENTITY,插入新列命令,設(shè)置IDENTITY列,

17、,實驗4 完整性約束,,9．用SQL為現(xiàn)有表TEST_TC增加新列ID_TC，并創(chuàng)建此列屬性為IDENTITY,給表TEST_TC創(chuàng)建標識列,表TEST_TC的屬性窗口,,實驗4 完整性約束,,10．在企業(yè)管理器中刪除現(xiàn)有表TEST_SC的主鍵PK_TEST_SC和取消ID_SC列的標識屬性,表TEST_TC的結(jié)構(gòu),刪除主鍵的兩個主屬性,,實驗4 完整性約束,,11．用SQL刪除表C中CN列的UNIQUE約束UNIQUE_C

18、,刪除UNIQUE約束的SQL語句,表C中的對象,,實驗4 完整性約束,,實驗4.2 域完整性約束 【實驗?zāi)康摹空莆沼闷髽I(yè)管理器為列創(chuàng)建DEFAULT約束；掌握用SQL為列創(chuàng)建DEFAULT約束；掌握用企業(yè)管理器創(chuàng)建CHECK約束；掌握用SQL創(chuàng)建CHECK約束?！緦嶒瀮?nèi)容】 用企業(yè)管理器為現(xiàn)有表T創(chuàng)建DEFAULT和CHECK約束。 （1）性別列SEX的DEFAULT約束：DEFAULT=’男’ （2）

19、PROF列與COMM列之間限定取值關(guān)系的CHECK約束?，F(xiàn)實中的情況是：不同的職稱對應(yīng)不同的崗位工資，語義規(guī)則如下表所示。,,實驗4 完整性約束,,故為T創(chuàng)建表達此語義規(guī)則的CHECK約束。邏輯表達式為： （PROF=‘教授’ AND COMM=4000）OR （PROF=‘副教授’ AND COMM=2000）OR（PROF=‘講師’ AND COMM=1500）OR（PROF=‘助教’ AND

20、COMM=1000）要求此約束對表T中已有數(shù)據(jù)不做檢查。用SQL給現(xiàn)有表S創(chuàng)建CHECK約束。根據(jù)語義本科生的年齡限制在14~40歲之間。故為現(xiàn)有表S中的AGE列創(chuàng)建CHECK約束，邏輯表達式為： AGE>=14 AND AGE<=40要求此約束對表SC中已有數(shù)據(jù)做檢查。用SQL創(chuàng)建一新表TEST_S，表的結(jié)構(gòu)定義如下表所示。,,實驗4 完整性約束,,用SQL刪除表T中列PROF和COMM之間

21、的CHECK約束CHECK_T。用企業(yè)管理器刪除表TEST_S中列SEX的DEFAULT約束及AGE列的CHECK約束。,【實驗步驟】 1.用企業(yè)管理器為現(xiàn)有表T創(chuàng)建DEFAULT和CHECK約束（要求CHECK約束對已有數(shù)據(jù)不做檢查）,給表T的SEX列設(shè)置DEFAULT約束,給表T設(shè)置約束,,實驗4 完整性約束,,2.用SQL給現(xiàn)有表S中的年齡列AGE創(chuàng)建取值范圍在14至40歲之間的CHECK約束，并檢查表中的現(xiàn)有數(shù)據(jù)是否符

22、合此CHECK約束,創(chuàng)建CHECK約束窗口,新創(chuàng)建的CHECK對象,,實驗4 完整性約束,,3．用SQL創(chuàng)建一新表TEST_S，包含DEFAULT和CHECK約束。,為TEST_S創(chuàng)建DEFAULT和CHECK約束,表TEST_S中的對象,,實驗4 完整性約束,,4．用SQL刪除表T中的PROF和COMM之間的CHECK約束,表T中約束對象的變化,刪除表T中的CHECK約束,,實驗4 完整性約束,,5．用企業(yè)管理器刪除表TE

23、ST_S中列SEX的DEFAULT約束和AGE列的CHECK約束,表TEST_S的結(jié)構(gòu)定義,SEX列的DEFAULT約束被清除,表TEST_T的屬性窗口,,實驗4 完整性約束,,實驗4.3 引用完整性約束 【實驗?zāi)康摹空莆談?chuàng)建主表和子表關(guān)聯(lián)關(guān)系的方法；掌握通過外鍵實現(xiàn)引用完整性約束；掌握修改、刪除引用完整性約束的方法。【實驗內(nèi)容】用企業(yè)管理器創(chuàng)建表S與表SC之間的引用關(guān)系。表S與表SC通過外鍵SNO實現(xiàn)引用完整

24、性約束（約束名采用系統(tǒng)默認）：子表SC中SNO的取值，要參照主表S中SNO的取值。并要求：取消“創(chuàng)建中檢查現(xiàn)有數(shù)據(jù)”；選擇“級聯(lián)更新相關(guān)字段”。用SQL創(chuàng)建表T與表TC之間的引用關(guān)系。表T與表TC通過外鍵TNO實現(xiàn)引用完整性約束FK_T_TC：子表TC中TNO的取值，要參照主表T中TNO的取值。并要求：取消“創(chuàng)建中檢查現(xiàn)有數(shù)據(jù)”；選擇“級聯(lián)刪除相關(guān)記錄”。,,實驗4 完整性約束,,創(chuàng)建數(shù)據(jù)庫jxsk中5個表之間的關(guān)聯(lián)關(guān)系圖。5個

25、表中存在的關(guān)聯(lián)關(guān)系，如下表所示。 數(shù)據(jù)庫jxsk中各表之間關(guān)聯(lián)關(guān)系,用企業(yè)管理器刪除表C和表TC之間的引用關(guān)系。,【實驗步驟】,創(chuàng)建關(guān)系,1．用企業(yè)管理器創(chuàng)建表S與表SC之間的引用關(guān)系,,實驗4 完整性約束,,驗證S表與SC表之間的引用完整性約束，即“級聯(lián)更新相關(guān)的字段”。,（a）數(shù)據(jù)表S中原記錄,（b）數(shù)據(jù)表SC中原記錄,（c）修改表S中學(xué)號后的變化,（d）修改表S中學(xué)號后表SC的變化,,實驗4 完整性約束,,2

26、．用SQL創(chuàng)建表T與表TC之間的引用關(guān)系,創(chuàng)建表T和TC的引用關(guān)系,表TC中的外鍵約束,,實驗4 完整性約束,,（a）數(shù)據(jù)表T,（b）數(shù)據(jù)表TC,驗證表T和TC之間的引用關(guān)系：級聯(lián)刪除相關(guān)記錄。,（6）數(shù)據(jù)表TC,（a）數(shù)據(jù)表T,數(shù)據(jù)表T、TC中的數(shù)據(jù)如下表所示：,刪除T表中‘T1’記錄后數(shù)據(jù)表T、TC中的數(shù)據(jù)如下表所示：,,實驗4 完整性約束,,3．創(chuàng)建數(shù)據(jù)庫jxsk中5個表之間的關(guān)聯(lián)關(guān)系圖,數(shù)據(jù)庫jxsk中的關(guān)系圖,數(shù)據(jù)庫j

27、xsk中表之間的關(guān)聯(lián)關(guān)系圖,,實驗4 完整性約束,,4．用企業(yè)管理器刪除表C和表TC之間的引用關(guān)系,表C的關(guān)系屬性窗口,,實驗4 完整性約束,,實驗4.4 規(guī) 則 【實驗?zāi)康摹空莆沼闷髽I(yè)管理器創(chuàng)建、綁定、解除規(guī)則；掌握用SQL創(chuàng)建、綁定、解除規(guī)則?！緦嶒瀮?nèi)容】用企業(yè)管理器為限制某列取值范圍創(chuàng)建、綁定、解除規(guī)則。 要求： 規(guī)則名稱為：RULE_SEX；規(guī)則表達式為：@sex in（‘男’，‘女’） 把此

28、規(guī)則綁定到表T和表S中的性別字段SEX上。用SQL創(chuàng)建、綁定、解除規(guī)則。 要求： 規(guī)則名稱為：RULE_SEX；規(guī)則表達式為：@sex in（‘男’，‘女’） 把此規(guī)則綁定到表T中的性別字段SEX上。,,實驗4 完整性約束,,【實驗步驟】1. 用企業(yè)管理器為限制某列取值范圍創(chuàng)建、綁定、解除規(guī)則,打開規(guī)則屬性窗口命令,所示把規(guī)則綁定到表中的列,創(chuàng)建規(guī)則屬性窗口,違反規(guī)則錯誤提示,,實驗4 完整性約束,,2.用S

29、QL創(chuàng)建、綁定、解除規(guī)則,用SQL綁定規(guī)則到表中的列,解除綁定的SQL,,實驗4 習(xí)題,,【實驗報告】實驗內(nèi)容與要求 針對“實驗2”中的“實驗題”中創(chuàng)建的數(shù)據(jù)庫jiaoxuedb進行下面實驗：1.對各表創(chuàng)建實體完整性；2.創(chuàng)建表之間的參照完整性；3.用不同的方法創(chuàng)建下面約束：（1）“姓名”字段取值唯一；（2）“性別”字段取值為：‘男’或‘女’；缺省值：‘男’；（3）“年齡”和“分數(shù)”字段取值限定為：0到100之間