實驗9 數(shù)據(jù)完整性實驗

1、德州學(xué)院德州學(xué)院 數(shù)據(jù)庫技術(shù)與應(yīng)用實驗數(shù)據(jù)庫技術(shù)與應(yīng)用實驗 實驗實驗9 數(shù)據(jù)完整性實驗數(shù)據(jù)完整性實驗 拓展練習(xí)拓展練習(xí) 實驗步驟實驗步驟 實驗要求實驗要求 實驗?zāi)康膶嶒災(zāi)康?預(yù)備知識預(yù)備知識 預(yù)備知識預(yù)備知識 -約束機制的分類約束機制的分類v 為了保證數(shù)據(jù)完整性，為了保證數(shù)據(jù)完整性，Sql Server 2008提供提供了下列約束機制：了下列約束機制： （1）PRIMARY KEY約束（主鍵約束），用于實現(xiàn)實約束（主鍵約束），用于實現(xiàn)實體完整性；體完整性； （2）FOREIGN KEY約束（外鍵約束），用于實現(xiàn)參約束（外鍵約束），用于實現(xiàn)參照完整性；照完整性； （3）UNIQUE約束（唯一約束

2、）、約束（唯一約束）、NOT NULL約束約束(非空約束非空約束)、CHECK約束（檢查約束）和約束（檢查約束）和DEFAULT約束（默認(rèn)值約束），用于實現(xiàn)用戶自定義的完整性。約束（默認(rèn)值約束），用于實現(xiàn)用戶自定義的完整性。 預(yù)備知識預(yù)備知識 -創(chuàng)建約束創(chuàng)建約束 在在Sql Server 2008中，所有約束即可以在中，所有約束即可以在創(chuàng)建表時同時創(chuàng)建，也可以在表創(chuàng)建完畢創(chuàng)建表時同時創(chuàng)建，也可以在表創(chuàng)建完畢后添加。約束又分列級約束和表級約束，后添加。約束又分列級約束和表級約束，除除NOT NULL只能做列級約束外，其余約只能做列級約束外，其余約束即可做列級約束，也可以做表級約束，束即可做列級約

3、束，也可以做表級約束，只是列級約束只能引用被約束的列，而表只是列級約束只能引用被約束的列，而表級約束能引用表中的任意列。級約束能引用表中的任意列。 預(yù)備知識預(yù)備知識 -創(chuàng)建約束創(chuàng)建約束 PRIMARY KEY 約束v 列級約束格式： CONSTRAINT 約束名 PRIMARY KEY CLUSTERED | NONCLUSTEREDv 表級約束格式： CONSTRAINT 約束名 PRIMARY KEY CLUSTERED | NONCLUSTERED (字段名) 關(guān)鍵字CLUSTERED和NONCLUSTERED指定主鍵約束是聚集索引還是非聚集索引。如果省略，并且沒有為unique約束指定

4、聚集索引，則對該主鍵約束使用CLUSTERE 預(yù)備知識預(yù)備知識 -創(chuàng)建約束創(chuàng)建約束 【例9-1】建立CourseInfo表，并指定Cno為主鍵。CREATE TABLE CourseInfo( Cno char(6) CONSTRAINT PK_CourseInfo PRIMARY KEY, /*列級主鍵約束*/Cname varchar(50) NULL,Cpno char(6) NULL,Credit decimal(2, 1) NULL) 預(yù)備知識預(yù)備知識 -創(chuàng)建約束創(chuàng)建約束 FOREIGN KEY約束v CONSTRAINT 約束名 FOREIGN KEY (關(guān)聯(lián)字段) REFEREN

5、CES 被參照(被關(guān)聯(lián)字段) ON DELETE CASCADE | NO ACTION | SET NULL | SET DEFAULT ON UPDATE CASCADE | NO ACTION | SET NULL | SET DEFAULTv 其中，ON DELETE CASCADE | NO ACTION | SET NULL | SET DEFAULT是指當(dāng)被參照表刪除元組時，可以采用以下四種方式之一保證參照完整性：級聯(lián)刪除參照表中相應(yīng)元組，或者被參照表拒絕刪除元組，或者將參照表中相應(yīng)元組的值設(shè)置為null，或者將參照表中相應(yīng)元組的值設(shè)置為默認(rèn)值。主鍵表更新元組時，也可以采用這四種

6、方式中的一種。 預(yù)備知識預(yù)備知識 -創(chuàng)建約束創(chuàng)建約束v 【例9-2】建立TeachTasksInfo表，并將字段Cno與CourseInfo表中的Cno建立外鍵聯(lián)系，指定當(dāng)被參照表（CourseInfo表）刪除元組造成與參照表（TeachTasksInfo表）數(shù)據(jù)不一致時，拒絕刪除被參照表中的元組；指定當(dāng)被參照表（CourseInfo表）更新元組時，則級聯(lián)更新參照表（TeachTasksInfo表）中相應(yīng)的元組數(shù)據(jù)。 預(yù)備知識預(yù)備知識 -創(chuàng)建約束創(chuàng)建約束 CREATE TABLE TeachTasksInfo( TeachTaskID char(26) primary key, Cno cha

7、r(6), TeacherID char(5), AcadYear char(9), Term char(1), ExamMethod varchar(10), Depart varchar(30), TimeTable varchar(50), CONSTRAINT FK_TeachTaskCourseInfo FOREIGN KEY(Cno) REFERENCES CourseInfo(Cno) ON UPDATE CASCADE /*當(dāng)更新當(dāng)更新CourseInfo中的中的Cno時，級聯(lián)更新時，級聯(lián)更新TeachTasksInfo */ ON DELETE NO ACTION /*當(dāng)刪除

8、當(dāng)刪除CourseInfo表中的元組造成不一致時，拒絕刪除表中的元組造成不一致時，拒絕刪除*/) 預(yù)備知識預(yù)備知識 -創(chuàng)建約束創(chuàng)建約束 UNIQUE約束v 列級約束格式： CONSTRAINT 約束名約束名 UNIQUEv 表級約束格式： CONSTRAINT 約束名約束名 UNIQUE (字段名字段名)v 【例9-3】建立StudentInfo表，并指定Sno和TeachTaskID聯(lián)合列值唯一。CREATE TABLE SC(Sno char(12),TeachTaskID char(26),Grade decimal(5, 2),UNIQUE (Sno,TeachTaskID)） 預(yù)備知

9、識預(yù)備知識 -創(chuàng)建約束創(chuàng)建約束 CHECK約束：列級CHECK約束和表級CHECK約束格式相同：v CONSTRAINT 約束名 CHECK(檢驗表達式)v 【例9-4】建立StudentInfo表，并指定Sex只能取“男”或“女”，LengSch的值應(yīng)該在1和7之間。CREATE TABLE dbo.StudentInfo(Sno char(12) PRIMARY KEY,Sname nvarchar(20) ,Sex char(2),Birthday date,Depart varchar(30),Major varchar(50),LengSch tinyint,CONSTRAINT C

10、_Sex CHECK(Sex in (男男,女女), /* Sex只能取只能取“男男”或或“女女”*/CONSTRAINT C_LengSch CHECK(LengSch=1 and LengSch=7 )/*LengSch的值應(yīng)該在的值應(yīng)該在1和和7之間，等價于之間，等價于CHECK( LengSch between 1 and 7)*/) 預(yù)備知識預(yù)備知識 -創(chuàng)建約束創(chuàng)建約束 DEFAULT約束v 列級約束格式：CONSTRAINT 約束名 DEFAULT 默認(rèn)值v 表級約束格式：CONSTRAINT 約束名 DEFAULT 默認(rèn)值 FOR 字段名v 【例例9-5】建立建立StudentI

11、nfo表，并指定表，并指定Sex的默認(rèn)值為的默認(rèn)值為“女女”，LengSch的默認(rèn)值為的默認(rèn)值為4。CREATE TABLE dbo.StudentInfo(Sno char(12) PRIMARY KEY,Sname nvarchar(20) ,Sex char(2),Birthday date,Depart varchar(30), Major varchar(50),LengSch tinyint DEFAULT 4, /* LengSch的默認(rèn)值為4*/CONSTRAINT Default_Sex DEFAULT 女 FOR Sex /* Sex的默認(rèn)值為“女”*/) 預(yù)備知識預(yù)備知識

12、 -創(chuàng)建約束創(chuàng)建約束表創(chuàng)建后添加約束表創(chuàng)建后添加約束v如果表已經(jīng)存在，則可以通過ALTER TABLE語句添加約束，格式為： ALTER TABLE 表名 ADD CONSTRAINT 約束名 約束 具體約束的格式與在創(chuàng)建表的同時創(chuàng)建約束時的表級約束格式相同。 預(yù)備知識預(yù)備知識 -創(chuàng)建約束創(chuàng)建約束v【例9-6】假設(shè)StudentInfo表已經(jīng)建立，且沒有建立任何約束。為StudentInfo添加主鍵約束，且要求Sno(字符型，長度為6)全部為數(shù)字。ALTER TABLE StudentInfo ADD PRIMARY KEY (Sno)ALTER TABLE StudentInfo ADD c

13、heck (Sno like 0-90-90-90-90-90-9) 預(yù)備知識預(yù)備知識 刪除刪除約束約束v刪除約束的格式為： ALTER TABLE 表名 DROP CONSTRAINT 約束名v【例9-7】刪除【例9-5】中Default_Sex約束。ALTER TABLE StudentInfo DROP Default_Sex 實驗實驗9 數(shù)據(jù)完整性實驗數(shù)據(jù)完整性實驗 拓展練習(xí)拓展練習(xí) 實驗步驟實驗步驟 實驗要求實驗要求 實驗?zāi)康膶嶒災(zāi)康?預(yù)備知識預(yù)備知識 實驗?zāi)康膶嶒災(zāi)康?1. 理解數(shù)據(jù)完整性的實現(xiàn)方式和作用。理解數(shù)據(jù)完整性的實現(xiàn)方式和作用。2. 掌握應(yīng)用掌握應(yīng)用Management

14、Studio創(chuàng)建和刪創(chuàng)建和刪除約束的方法。除約束的方法。3. 掌握應(yīng)用掌握應(yīng)用T-SQL語句創(chuàng)建和刪除約束的方語句創(chuàng)建和刪除約束的方法。法。 實驗實驗9 數(shù)據(jù)完整性實驗數(shù)據(jù)完整性實驗 拓展練習(xí)拓展練習(xí) 實驗步驟實驗步驟 實驗要求實驗要求 實驗?zāi)康膶嶒災(zāi)康?預(yù)備知識預(yù)備知識 實驗要求實驗要求 1. 為不帶任何約束的樣例數(shù)據(jù)庫為不帶任何約束的樣例數(shù)據(jù)庫ST中的各中的各表建立適當(dāng)?shù)募s束。表建立適當(dāng)?shù)募s束。2. 對樣例數(shù)據(jù)庫對樣例數(shù)據(jù)庫ST中各表中的約束進行管中各表中的約束進行管理。理。3. 撰寫實驗報告，并附實驗結(jié)果與相應(yīng)實驗撰寫實驗報告，并附實驗結(jié)果與相應(yīng)實驗語句。語句。 實驗實驗9 數(shù)據(jù)完整性實

15、驗數(shù)據(jù)完整性實驗 拓展練習(xí)拓展練習(xí) 實驗步驟實驗步驟 實驗要求實驗要求 實驗?zāi)康膶嶒災(zāi)康?預(yù)備知識預(yù)備知識 實驗步驟實驗步驟1.為為StudentInfo表的表的Sno添加添加UNIQUE約束，并添加記錄，體會唯一約約束，并添加記錄，體會唯一約束的作用。束的作用。(1)在查詢窗口中輸入添加在查詢窗口中輸入添加UNIQUE約束的約束的SQL語句，并執(zhí)行：語句，并執(zhí)行：USE STALTER TABLE StudentInfo ADD CONSTRAINT UN_ Sno UNIQUE(Sno)(2)輸入以下添加記錄的輸入以下添加記錄的SQL語句，并執(zhí)行語句，并執(zhí)行2次：次：INSERT INTO

16、 StudentInfo VALUES(201001903029,明梅明梅,女女,1991-03-15,計算機系計算機系,計算機科學(xué)與技術(shù)計算機科學(xué)與技術(shù)(本本科科),4)第一次執(zhí)行成功，記錄成功插入，第二次執(zhí)行在消息窗口中顯示以下消息，表示執(zhí)行插入操作第一次執(zhí)行成功，記錄成功插入，第二次執(zhí)行在消息窗口中顯示以下消息，表示執(zhí)行插入操作失?。菏。合⑾?627，級別，級別14，狀態(tài)，狀態(tài)1，第，第4 行行違反了違反了UNIQUE KEY 約束約束 UN _Sno。不能在對象。不能在對象dbo.StudentInfo 中插入重復(fù)鍵。中插入重復(fù)鍵。 實驗步驟實驗步驟2.為為SC表添加表添加PRI

17、MARKY KEY約束，并添加記錄，體會主鍵約束，并添加記錄，體會主鍵約束的作用。約束的作用。(1)在查詢窗口中輸入添加在查詢窗口中輸入添加PRIMARY KEY約束的約束的SQL語句，并執(zhí)行：語句，并執(zhí)行：vALTER TABLE SCv ADD CONSTRAINT PK_SC PRIMARY KEY(Sno,TeachTaskID)(2)輸入以下添加記錄的輸入以下添加記錄的SQL語句，并執(zhí)行語句，并執(zhí)行2次：次：vINSERT INTO SCvVALUES(201001903029,(2009-2010-1)-140173-04001,85)第一次執(zhí)行成功，記錄成功插入，第二次執(zhí)行在消息

18、窗口中顯示以下消息，第一次執(zhí)行成功，記錄成功插入，第二次執(zhí)行在消息窗口中顯示以下消息，表示執(zhí)行插入操作失?。罕硎緢?zhí)行插入操作失敗：消息消息2627，級別，級別14，狀態(tài)，狀態(tài)1，第，第1 行行違反了違反了PRIMARY KEY 約束約束PK_SC。不能在對象。不能在對象dbo.SC 中插入重復(fù)鍵。中插入重復(fù)鍵。 實驗步驟實驗步驟(3)輸入以下添加記錄的輸入以下添加記錄的SQL語句，并執(zhí)行：語句，并執(zhí)行：vINSERT INTO SC(TeachTaskID,Grade) VALUES(2009-2010-1)-140173-04001,90)消息窗口中顯示以下消息，表示執(zhí)行插入操作失?。合⒋?/p>

19、口中顯示以下消息，表示執(zhí)行插入操作失?。合⑾?15，級別，級別16，狀態(tài)，狀態(tài)2，第，第1 行行不能將值不能將值NULL插入列插入列Sno，表，表ST.dbo.SC；列不允許有；列不允許有Null 值。值。INSERT 失敗。失敗。 實驗步驟實驗步驟3.為為SC表添加表添加FOREIGN KEY約束，并指定當(dāng)修改被參照表約束，并指定當(dāng)修改被參照表StudentInfo的元組造成與的元組造成與SC不一致時，采用級聯(lián)修改策不一致時，采用級聯(lián)修改策略，當(dāng)刪除被參照表略，當(dāng)刪除被參照表StudentInfo的元組造成與的元組造成與SC不一致不一致時，采用拒絕策略。并更新表中記錄，體會外鍵約束的作時

20、，采用拒絕策略。并更新表中記錄，體會外鍵約束的作用。用。(1)在查詢窗口中輸入添加外鍵約束的在查詢窗口中輸入添加外鍵約束的SQL語句，并執(zhí)行：語句，并執(zhí)行：ALTER TABLE SCADD CONSTRAINT FK_Sno FOREIGN KEY(Sno) REFERENCES StudentInfo(Sno)ON DELETE NO ACTIONON UPDATE CASCADE 實驗步驟實驗步驟v(2)輸入以下為輸入以下為SC表添加記錄的表添加記錄的SQL語句，并執(zhí)行：語句，并執(zhí)行：INSERT INTO SC VALUES(201001903028,(2009-2010-1)-140

21、173-04001,85)v消息窗口中顯示以下信息，表示插入操作失敗，因為消息窗口中顯示以下信息，表示插入操作失敗，因為StudentInfo表中不存在學(xué)號表中不存在學(xué)號“201001903028”：v消息消息547，級別，級別16，狀態(tài)，狀態(tài)0，第，第1 行行vINSERT 語句與語句與FOREIGN KEY 約束約束FK_Sno沖突。該沖突沖突。該沖突發(fā)生于數(shù)據(jù)庫發(fā)生于數(shù)據(jù)庫new，表，表dbo.StudentInfo, column Sno。 實驗步驟實驗步驟(3)輸入以下修改輸入以下修改StudentInfo表中記錄的表中記錄的SQL語語句，并執(zhí)行：句，并執(zhí)行：UPDATE Stude

22、ntInfoSET Sno=201001903028 WHERE Sno =201001903029執(zhí)行完畢后查看執(zhí)行完畢后查看SC中記錄，會發(fā)現(xiàn)原來中記錄，會發(fā)現(xiàn)原來Sno值為值為“201001903029”的記錄已經(jīng)被級聯(lián)修改為的記錄已經(jīng)被級聯(lián)修改為“201001903028”。 實驗步驟實驗步驟(4）輸入以下刪除）輸入以下刪除StudentInfo中記錄的中記錄的SQL語句，并語句，并執(zhí)行：執(zhí)行：DELETE StudentInfo WHERE Sno =201001903028消息窗口中顯示以下信息，表示刪除操作失?。合⒋翱谥酗@示以下信息，表示刪除操作失?。合⑾?47，級別，級別

23、16，狀態(tài)，狀態(tài)0，第，第1 行行DELETE 語句與語句與REFERENCE 約束約束FK_Sno沖突。該沖沖突。該沖突發(fā)生于數(shù)據(jù)庫突發(fā)生于數(shù)據(jù)庫ST，表，表dbo.SC, column Sno。 實驗步驟實驗步驟4.為為StudentInfo表的表的Sex列添加列添加CHECK約束，保證性約束，保證性別只能取別只能取“男男”或或“女女”，并添加記錄，體會，并添加記錄，體會CHECK約束的作用。約束的作用。(1)在查詢窗口中輸入以下為在查詢窗口中輸入以下為Sex添加添加CHECK約束的約束的SQL語句，并執(zhí)行：語句，并執(zhí)行：ALTER TABLE StudentInfoADD CONSTRA

24、INT Ck_Sex CHECK(Sex IN(男男,女女) 實驗步驟實驗步驟v(2)輸入以下插入記錄的輸入以下插入記錄的SQL語句，并執(zhí)行：語句，并執(zhí)行：INSERT INTO StudentInfoVALUES(201011801017,王鵬飛王鵬飛,家家,1991-01-05,服裝系服裝系,服裝設(shè)計與工程服裝設(shè)計與工程(?？茖？?,3)v消息窗口中會顯示以下信息，表示插入操作失敗：消息窗口中會顯示以下信息，表示插入操作失敗：v消息消息547，級別，級別16，狀態(tài)，狀態(tài)0，第，第1 行行vINSERT 語句與語句與CHECK 約束約束Ck_Sex沖突。該沖突發(fā)生沖突。該沖突發(fā)生于數(shù)據(jù)庫于數(shù)

25、據(jù)庫ST，表，表dbo.StudentInfo, column Sex。 實驗步驟實驗步驟5.為為StudentInfo表的表的Depart列添加列添加DEFAULT約束，約束，設(shè)置默認(rèn)值為設(shè)置默認(rèn)值為“計算機系計算機系”。并添加記錄，體會。并添加記錄，體會DEFAULT約束的作用。約束的作用。(1)在查詢窗口中輸入以下為在查詢窗口中輸入以下為Depart添加添加DEFAULT約約束的語句，并執(zhí)行：束的語句，并執(zhí)行：ALTER TABLE StudentInfoADD CONSTRAINT DK_Depart DEFAULT 計算計算機系機系 for Depart 實驗步驟實驗步驟(2)輸入以

26、下插入記錄的輸入以下插入記錄的SQL語句，并執(zhí)行：語句，并執(zhí)行：INSERT INTO StudentInfo(Sno,Sname,Sex,Birthday,Major,LengSch) values(201011801017,王鵬飛王鵬飛,男男,1991-01-05,服裝設(shè)計服裝設(shè)計與工程與工程(?？茖？?,3)(3)查看查看StudentInfo新添加記錄值為：新添加記錄值為：(201011801017,王鵬飛王鵬飛,男男,1991-01-05, 計算機系計算機系,服服裝設(shè)計與工程裝設(shè)計與工程(?？茖？?,3) 實驗步驟實驗步驟6.為為StudentInfo表中的表中的Sname列設(shè)置列設(shè)

27、置NOT NULL約束，約束，添加記錄，體會非空約束的作用。添加記錄，體會非空約束的作用。(1)在查詢窗口中輸入以下更新數(shù)據(jù)列屬性的在查詢窗口中輸入以下更新數(shù)據(jù)列屬性的SQL語句，語句，并執(zhí)行：并執(zhí)行：ALTER TABLE StudentInfo ALTER COLUMN Sname varchar(20) NOT NULL 實驗步驟實驗步驟(2)輸入以下插入記錄的輸入以下插入記錄的SQL語句，并執(zhí)行：語句，并執(zhí)行：INSERT INTO StudentInfovalues(201011801018,NULL,女女,1991-09-09,服裝服裝系系,服裝設(shè)計與工程服裝設(shè)計與工程(1升本升本

28、),4)v消息窗口中會顯示以下信息，表示插入操作失敗：消息窗口中會顯示以下信息，表示插入操作失?。簐消息消息515，級別，級別16，狀態(tài)，狀態(tài)2，第，第1 行行v不能將值不能將值NULL插入列插入列Sname，表，表ST.dbo.StudentInfo；列不允許有列不允許有Null 值。值。INSERT 失敗。失敗。 實驗步驟實驗步驟7.刪除以上建立的約束，體會約束之間的依賴關(guān)系。刪除以上建立的約束，體會約束之間的依賴關(guān)系。(1)在查詢窗口中輸入刪除在查詢窗口中輸入刪除StudentInfo表中的唯一性約束的表中的唯一性約束的SQL語句，并執(zhí)行：語句，并執(zhí)行：ALTER TABLE StudentInfoDROP CONSTRAINT UN_ Snov消息窗口中會顯示以下信息，表示刪除約束操作失敗：消息窗口中會顯示以下信息，表示刪除約束操作失?。簐消息消息3725，級別，級別16，狀態(tài)，狀態(tài)0，第，第1 行行v約束約束 UN_ Sno 正由表正由表SC 的外鍵約束的外鍵約束FK_Sno 引用。引用。v消息消息3727，級別，級別16，狀態(tài)，狀態(tài)0，第，第1 行行v未能刪除約束。請參閱前面的