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

德州學院計算機系

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

數(shù)據(jù)庫技術與應用實驗實驗9數(shù)據(jù)完整性實驗拓展練習實驗步驟實驗要求實驗目的

預備知識AnIntroductiontoDatabaseSystem預備知識-約束機制的分類

為了保證數(shù)據(jù)完整性，SqlServer2008提供了下列約束機制：（1）PRIMARYKEY約束（主鍵約束），用于實現(xiàn)實體完整性；（2）FOREIGNKEY約束（外鍵約束），用于實現(xiàn)參照完整性；（3）UNIQUE約束（唯一約束）、NOTNULL約束(非空約束)、CHECK約束（檢查約束）和DEFAULT約束（默認值約束），用于實現(xiàn)用戶自定義的完整性。AnIntroductiontoDatabaseSystem預備知識-創(chuàng)建約束在SqlServer2008中，所有約束即可以在創(chuàng)建表時同時創(chuàng)建，也可以在表創(chuàng)建完畢后添加。約束又分列級約束和表級約束，除NOTNULL只能做列級約束外，其余約束即可做列級約束，也可以做表級約束，只是列級約束只能引用被約束的列，而表級約束能引用表中的任意列。

AnIntroductiontoDatabaseSystem預備知識-創(chuàng)建約束

PRIMARYKEY約束列級約束格式：[CONSTRAINT約束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED]表級約束格式：[CONSTRAINT約束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED](字段名)

關鍵字CLUSTERED和NONCLUSTERED指定主鍵約束是聚集索引還是非聚集索引。如果省略，并且沒有為unique約束指定聚集索引，則對該主鍵約束使用CLUSTEREAnIntroductiontoDatabaseSystem預備知識-創(chuàng)建約束

【例9-1】建立CourseInfo表，并指定Cno為主鍵。CREATETABLECourseInfo( Cnochar(6)CONSTRAINTPK_CourseInfoPRIMARYKEY,/*列級主鍵約束*/ Cnamevarchar(50)NULL, Cpnochar(6)NULL, Creditdecimal(2,1)NULL)AnIntroductiontoDatabaseSystem預備知識-創(chuàng)建約束

FOREIGNKEY約束[CONSTRAINT約束名]FOREIGNKEY(關聯(lián)字段)REFERENCES被參照(被關聯(lián)字段)[ONDELETECASCADE|NOACTION|SETNULL|SETDEFAULT][ONUPDATECASCADE|NOACTION|SETNULL|SETDEFAULT]其中，ONDELETECASCADE|NOACTION|SETNULL|SETDEFAULT是指當被參照表刪除元組時，可以采用以下四種方式之一保證參照完整性：級聯(lián)刪除參照表中相應元組，或者被參照表拒絕刪除元組，或者將參照表中相應元組的值設置為null，或者將參照表中相應元組的值設置為默認值。主鍵表更新元組時，也可以采用這四種方式中的一種。AnIntroductiontoDatabaseSystem預備知識-創(chuàng)建約束

【例9-2】建立TeachTasksInfo表，并將字段Cno與CourseInfo表中的Cno建立外鍵聯(lián)系，指定當被參照表（CourseInfo表）刪除元組造成與參照表（TeachTasksInfo表）數(shù)據(jù)不一致時，拒絕刪除被參照表中的元組；指定當被參照表（CourseInfo表）更新元組時，則級聯(lián)更新參照表（TeachTasksInfo表）中相應的元組數(shù)據(jù)。AnIntroductiontoDatabaseSystem預備知識-創(chuàng)建約束

CREATETABLETeachTasksInfo(TeachTaskIDchar(26)primarykey,Cnochar(6),TeacherIDchar(5),AcadYearchar(9),Termchar(1),ExamMethodvarchar(10),Departvarchar(30),TimeTablevarchar(50),CONSTRAINTFK_TeachTaskCourseInfoFOREIGNKEY(Cno)REFERENCESCourseInfo(Cno)ONUPDATECASCADE/*當更新CourseInfo中的Cno時，級聯(lián)更新TeachTasksInfo*/ONDELETENOACTION/*當刪除CourseInfo表中的元組造成不一致時，拒絕刪除*/)AnIntroductiontoDatabaseSystem預備知識-創(chuàng)建約束

UNIQUE約束列級約束格式：

[CONSTRAINT約束名]UNIQUE表級約束格式：

[CONSTRAINT約束名]UNIQUE(字段名)【例9-3】建立StudentInfo表，并指定Sno和TeachTaskID聯(lián)合列值唯一。 CREATETABLESC ( Snochar(12), TeachTaskIDchar(26), Gradedecimal(5,2), UNIQUE(Sno,TeachTaskID) ）AnIntroductiontoDatabaseSystem預備知識-創(chuàng)建約束

CHECK約束：列級CHECK約束和表級CHECK約束格式相同：[CONSTRAINT約束名]CHECK(檢驗表達式)【例9-4】建立StudentInfo表，并指定Sex只能取“男”或“女”，LengSch的值應該在1和7之間。 CREATETABLE[dbo].[StudentInfo] ( Snochar(12)PRIMARYKEY, Snamenvarchar(20), Sexchar(2), Birthdaydate, Departvarchar(30), Majorvarchar(50), LengSchtinyint, CONSTRAINTC_SexCHECK(Sexin('男','女')),/*Sex只能取“男”或“女”*/ CONSTRAINTC_LengSchCHECK(LengSch>=1andLengSch<=7) /*LengSch的值應該在1和7之間，等價于CHECK(LengSchbetween1and7)*/ )AnIntroductiontoDatabaseSystem預備知識-創(chuàng)建約束

DEFAULT約束列級約束格式： [CONSTRAINT約束名]DEFAULT默認值表級約束格式： [CONSTRAINT約束名]DEFAULT默認值FOR字段名【例9-5】建立StudentInfo表，并指定Sex的默認值為“女”，LengSch的默認值為4。 CREATETABLE[dbo].[StudentInfo] ( Snochar(12)PRIMARYKEY, Snamenvarchar(20), Sexchar(2), Birthdaydate, Departvarchar(30), Majorvarchar(50), LengSchtinyintDEFAULT4,/*LengSch的默認值為4*/ CONSTRAINTDefault_SexDEFAULT'女'FORSex/*Sex的默認值為“女”*/ )AnIntroductiontoDatabaseSystem預備知識-創(chuàng)建約束表創(chuàng)建后添加約束如果表已經(jīng)存在，則可以通過ALTERTABLE語句添加約束，格式為：

ALTERTABLE表名

ADD[CONSTRAINT約束名]約束

具體約束的格式與在創(chuàng)建表的同時創(chuàng)建約束時的表級約束格式相同。

AnIntroductiontoDatabaseSystem預備知識-創(chuàng)建約束【例9-6】假設StudentInfo表已經(jīng)建立，且沒有建立任何約束。為StudentInfo添加主鍵約束，且要求Sno(字符型，長度為6)全部為數(shù)字。 ALTERTABLEStudentInfo ADDPRIMARYKEY(Sno) ALTERTABLEStudentInfo ADDcheck(Snolike'[0-9][0-9][0-9][0-9][0-9][0-9]')AnIntroductiontoDatabaseSystem預備知識–刪除約束刪除約束的格式為：ALTERTABLE表名

DROPCONSTRAINT約束名【例9-7】刪除【例9-5】中Default_Sex約束。

ALTERTABLEStudentInfoDROPDefault_SexAnIntroductiontoDatabaseSystem實驗9數(shù)據(jù)完整性實驗拓展練習實驗步驟實驗要求

實驗目的預備知識AnIntroductiontoDatabaseSystem實驗目的理解數(shù)據(jù)完整性的實現(xiàn)方式和作用。掌握應用ManagementStudio創(chuàng)建和刪除約束的方法。掌握應用T-SQL語句創(chuàng)建和刪除約束的方法。AnIntroductiontoDatabaseSystem實驗9數(shù)據(jù)完整性實驗拓展練習實驗步驟

實驗要求

實驗目的預備知識AnIntroductiontoDatabaseSystem實驗要求為不帶任何約束的樣例數(shù)據(jù)庫ST中的各表建立適當?shù)募s束。對樣例數(shù)據(jù)庫ST中各表中的約束進行管理。撰寫實驗報告，并附實驗結果與相應實驗語句。AnIntroductiontoDatabaseSystem實驗9數(shù)據(jù)完整性實驗拓展練習

實驗步驟實驗要求

實驗目的預備知識AnIntroductiontoDatabaseSystem實驗步驟為StudentInfo表的Sno添加UNIQUE約束，并添加記錄，體會唯一約束的作用。(1)在查詢窗口中輸入添加UNIQUE約束的SQL語句，并執(zhí)行：USESTALTERTABLEStudentInfo

ADDCONSTRAINTUN_Sno

UNIQUE(Sno)(2)輸入以下添加記錄的SQL語句，并執(zhí)行2次：INSERTINTOStudentInfo

VALUES('201001903029','明梅','女','1991-03-15','計算機系','計算機科學與技術(本科)',4)第一次執(zhí)行成功，記錄成功插入，第二次執(zhí)行在消息窗口中顯示以下消息，表示執(zhí)行插入操作失?。合?627，級別14，狀態(tài)1，第4行違反了UNIQUEKEY約束'UN_Sno'。不能在對象'dbo.StudentInfo'中插入重復鍵。

AnIntroductiontoDatabaseSystem實驗步驟2.為SC表添加PRIMARKYKEY約束，并添加記錄，體會主鍵約束的作用。(1)在查詢窗口中輸入添加PRIMARYKEY約束的SQL語句，并執(zhí)行：ALTERTABLESCADDCONSTRAINTPK_SCPRIMARYKEY(Sno,TeachTaskID)(2)輸入以下添加記錄的SQL語句，并執(zhí)行2次：INSERTINTOSCVALUES('201001903029','(2009-2010-1)-140173-04001',85)第一次執(zhí)行成功，記錄成功插入，第二次執(zhí)行在消息窗口中顯示以下消息，表示執(zhí)行插入操作失?。合?627，級別14，狀態(tài)1，第1行違反了PRIMARYKEY約束'PK_SC'。不能在對象'dbo.SC'中插入重復鍵。

AnIntroductiontoDatabaseSystem實驗步驟(3)輸入以下添加記錄的SQL語句，并執(zhí)行：INSERTINTOSC(TeachTaskID,Grade) VALUES('(2009-2010-1)-140173-04001',90)消息窗口中顯示以下消息，表示執(zhí)行插入操作失?。合?15，級別16，狀態(tài)2，第1行不能將值NULL插入列'Sno'，表'ST.dbo.SC'；列不允許有Null值。INSERT失敗。AnIntroductiontoDatabaseSystem實驗步驟3.為SC表添加FOREIGNKEY約束，并指定當修改被參照表StudentInfo的元組造成與SC不一致時，采用級聯(lián)修改策略，當刪除被參照表StudentInfo的元組造成與SC不一致時，采用拒絕策略。并更新表中記錄，體會外鍵約束的作用。(1)在查詢窗口中輸入添加外鍵約束的SQL語句，并執(zhí)行： ALTERTABLESC ADDCONSTRAINTFK_SnoFOREIGNKEY(Sno) REFERENCESStudentInfo(Sno) ONDELETENOACTION ONUPDATECASCADEAnIntroductiontoDatabaseSystem實驗步驟(2)輸入以下為SC表添加記錄的SQL語句，并執(zhí)行： INSERTINTOSCVALUES('201001903028','(2009-2010-1)-140173-04001',85)消息窗口中顯示以下信息，表示插入操作失敗，因為StudentInfo表中不存在學號“201001903028”： 消息547，級別16，狀態(tài)0，第1行 INSERT語句與FOREIGNKEY約束"FK_Sno"沖突。該沖突發(fā)生于數(shù)據(jù)庫"new"，表"dbo.StudentInfo",column'Sno'。

AnIntroductiontoDatabaseSystem實驗步驟(3)輸入以下修改StudentInfo表中記錄的SQL語句，并執(zhí)行： UPDATEStudentInfo SETSno='201001903028'WHERESno ='201001903029'

執(zhí)行完畢后查看SC中記錄，會發(fā)現(xiàn)原來Sno值為“201001903029”的記錄已經(jīng)被級聯(lián)修改為“201001903028”。

AnIntroductiontoDatabaseSystem實驗步驟(4）輸入以下刪除StudentInfo中記錄的SQL語句，并執(zhí)行： DELETEStudentInfo WHERESno='201001903028' 消息窗口中顯示以下信息，表示刪除操作失?。?消息547，級別16，狀態(tài)0，第1行 DELETE語句與REFERENCE約束"FK_Sno"沖突。該沖突發(fā)生于數(shù)據(jù)庫"ST"，表"dbo.SC",column'Sno'。AnIntroductiontoDatabaseSystem實驗步驟4.為StudentInfo表的Sex列添加CHECK約束，保證性別只能取“男”或“女”，并添加記錄，體會CHECK約束的作用。(1)在查詢窗口中輸入以下為Sex添加CHECK約束的SQL語句，并執(zhí)行： ALTERTABLEStudentInfo ADDCONSTRAINTCk_SexCHECK(SexIN('男','女'))AnIntroductiontoDatabaseSystem實驗步驟(2)輸入以下插入記錄的SQL語句，并執(zhí)行： INSERTINTOStudentInfo VALUES('201011801017','王鵬飛','家','1991- 01-05','服裝系','服裝設計與工程(?？?',3)消息窗口中會顯示以下信息，表示插入操作失?。合?47，級別16，狀態(tài)0，第1行INSERT語句與CHECK約束"Ck_Sex"沖突。該沖突發(fā)生于數(shù)據(jù)庫"ST"，表"dbo.StudentInfo",column'Sex'。AnIntroductiontoDatabaseSystem實驗步驟5.為StudentInfo表的Depart列添加DEFAULT約束，設置默認值為“計算機系”。并添加記錄，體會DEFAULT約束的作用。(1)在查詢窗口中輸入以下為Depart添加DEFAULT約束的語句，并執(zhí)行： ALTERTABLEStudentInfo ADDCONSTRAINTDK_DepartDEFAULT‘計算 機系'forDepartAnIntroductiontoDatabaseSystem實驗步驟(2)輸入以下插入記錄的SQL語句，并執(zhí)行：INSERTINTOStudentInfo(Sno,Sname,Sex,Birthday,Major,LengSch)values('201011801017','王鵬飛','男','1991-01-05','服裝設計與工程(?？?',3)(3)查看StudentInfo新添加記錄值為： ('201011801017','王鵬飛','男','1991-01-05','計算機系','服裝設計與工程(?？?',3)AnIntroductiontoDatabaseSystem實驗步驟6.為StudentInfo表中的Sname列設置NOTNULL約束，添加記錄，體會非空約束的作用。(1)在查詢窗口中輸入以下更新數(shù)據(jù)列屬性的SQL語句，并執(zhí)行：ALTERTABLEStudentInfoALTERCOLUMNSnamevarchar(20)NOTNULLAnIntroductiontoDatabaseSystem實驗步驟(2)輸入以下插入記錄的SQL語句，并執(zhí)行：INSERTINTOStudentInfovalues('201011801018',NULL,'女','1991-09-09','服裝系','服裝設計與工程(1＋升本)',4)消息窗口中會顯示以下信息，表示插入操作失?。合?15，級別16，狀態(tài)2，第1行不能將值NULL插入列'Sname'，表'ST.dbo.StudentInfo'；列不允許有Null值。INSERT失敗。AnIntroductiontoDatabaseSystem實驗步驟7.刪除以上建立的約束，體會約束之間的依賴關系。(1)在查詢窗口中輸入刪除StudentInfo表中的唯一性約束的SQL語句，并執(zhí)行： ALTERTABLEStudentInfo DROPCONSTRAINTUN_Sno消息窗口中會顯示以下信息，表示刪除約束操作失敗：消息3725，級別16，狀態(tài)0，第1行