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

德州學(xué)院計算機(jī)系

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

數(shù)據(jù)庫技術(shù)與應(yīng)用實驗實驗9數(shù)據(jù)完整性實驗拓展練習(xí)實驗步驟實驗要求實驗?zāi)康?/p>

預(yù)備知識AnIntroductiontoDatabaseSystem預(yù)備知識-約束機(jī)制的分類

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

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

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

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

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

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

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

CHECK約束：列級CHECK約束和表級CHECK約束格式相同：[CONSTRAINT約束名]CHECK(檢驗表達(dá)式)【例9-4】建立StudentInfo表，并指定Sex只能取“男”或“女”，LengSch的值應(yīng)該在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的值應(yīng)該在1和7之間，等價于CHECK(LengSchbetween1and7)*/ )AnIntroductiontoDatabaseSystem預(yù)備知識-創(chuàng)建約束

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

ALTERTABLE表名

ADD[CONSTRAINT約束名]約束

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

AnIntroductiontoDatabaseSystem預(yù)備知識-創(chuàng)建約束【例9-6】假設(shè)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預(yù)備知識–刪除約束刪除約束的格式為：ALTERTABLE表名

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

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

實驗?zāi)康念A(yù)備知識AnIntroductiontoDatabaseSystem實驗?zāi)康睦斫鈹?shù)據(jù)完整性的實現(xiàn)方式和作用。掌握應(yīng)用ManagementStudio創(chuàng)建和刪除約束的方法。掌握應(yīng)用T-SQL語句創(chuàng)建和刪除約束的方法。AnIntroductiontoDatabaseSystem實驗9數(shù)據(jù)完整性實驗拓展練習(xí)實驗步驟

實驗要求

實驗?zāi)康念A(yù)備知識AnIntroductiontoDatabaseSystem實驗要求為不帶任何約束的樣例數(shù)據(jù)庫ST中的各表建立適當(dāng)?shù)募s束。對樣例數(shù)據(jù)庫ST中各表中的約束進(jìn)行管理。撰寫實驗報告，并附實驗結(jié)果與相應(yīng)實驗語句。AnIntroductiontoDatabaseSystem實驗9數(shù)據(jù)完整性實驗拓展練習(xí)

實驗步驟實驗要求

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

ADDCONSTRAINTUN_Sno

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

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

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'中插入重復(fù)鍵。

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約束，并指定當(dāng)修改被參照表StudentInfo的元組造成與SC不一致時，采用級聯(lián)修改策略，當(dāng)刪除被參照表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表中不存在學(xué)號“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','服裝系','服裝設(shè)計與工程(專科)',3)消息窗口中會顯示以下信息，表示插入操作失?。合?47，級別16，狀態(tài)0，第1行INSERT語句與CHECK約束"Ck_Sex"沖突。該沖突發(fā)生于數(shù)據(jù)庫"ST"，表"dbo.StudentInfo",column'Sex'。AnIntroductiontoDatabaseSystem實驗步驟5.為StudentInfo表的Depart列添加DEFAULT約束，設(shè)置默認(rèn)值為“計算機(jī)系”。并添加記錄，體會DEFAULT約束的作用。(1)在查詢窗口中輸入以下為Depart添加DEFAULT約束的語句，并執(zhí)行： ALTERTABLEStudentInfo ADDCONSTRAINTDK_DepartDEFAULT‘計算 機(jī)系'forDepartAnIntroductiontoDatabaseSystem實驗步驟(2)輸入以下插入記錄的SQL語句，并執(zhí)行：INSERTINTOStudentInfo(Sno,Sname,Sex,Birthday,Major,LengSch)values('201011801017','王鵬飛','男','1991-01-05','服裝設(shè)計與工程(?？?',3)(3)查看StudentInfo新添加記錄值為： ('201011801017','王鵬飛','男','1991-01-05','計算機(jī)系','服裝設(shè)計與工程(?？?',3)AnIntroductiontoDatabaseSystem實驗步驟6.為StudentInfo表中的Sname列設(shè)置NOTNULL約束，添加記錄，體會非空約束的作用。(1)在查詢窗口中輸入以下更新數(shù)據(jù)列屬性的SQL語句，并執(zhí)行：ALTERTABLEStudentInfoALTERCOLUMNSnamevarchar(20)NOTNULLAnIntroductiontoDatabaseSystem實驗步驟(2)輸入以下插入記錄的SQL語句，并執(zhí)行：INSERTINTOStudentInfovalues('201011801018',NULL,'女','1991-09-09','服裝系','服裝設(shè)計與工程(1＋升本)',4)消息窗口中會顯示以下信息，表示插入操作失?。合?15，級別16，狀態(tài)2，第1行不能將值NULL插入列'Sname'，表'ST.dbo.StudentInfo'；列不允許有Null值。INSERT失敗。AnIntroductiontoDatabaseSystem實驗步驟7.刪除以上建立的約束，體會約束之間的依賴關(guān)系。(1)在查詢窗口中輸入刪除StudentInfo表中的唯一性約束的SQL語句，并執(zhí)行： ALTERTABLEStudentInfo DROPCONSTRAINTUN_Sno消息窗口中會顯示以下信息，表示刪除約束操作失敗：消息3725，級別16，狀態(tài)0，第1行