實(shí)驗(yàn)數(shù)據(jù)完整性實(shí)驗(yàn)

實(shí)驗(yàn)數(shù)據(jù)完整性實(shí)驗(yàn)第一頁(yè)，共三十五頁(yè)，2022年，8月28日預(yù)備知識(shí)-約束機(jī)制的分類

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

第三頁(yè)，共三十五頁(yè)，2022年，8月28日預(yù)備知識(shí)-創(chuàng)建約束

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

關(guān)鍵字CLUSTERED和NONCLUSTERED指定主鍵約束是聚集索引還是非聚集索引。如果省略，并且沒有為unique約束指定聚集索引，則對(duì)該主鍵約束使用CLUSTERE第四頁(yè)，共三十五頁(yè)，2022年，8月28日預(yù)備知識(shí)-創(chuàng)建約束

【例9-1】建立CourseInfo表，并指定Cno為主鍵。CREATETABLECourseInfo( Cnochar(6)CONSTRAINTPK_CourseInfoPRIMARYKEY,/*列級(jí)主鍵約束*/ Cnamevarchar(50)NULL, Cpnochar(6)NULL, Creditdecimal(2,1)NULL)第五頁(yè)，共三十五頁(yè)，2022年，8月28日預(yù)備知識(shí)-創(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)被參照表刪除元組時(shí)，可以采用以下四種方式之一保證參照完整性：級(jí)聯(lián)刪除參照表中相應(yīng)元組，或者被參照表拒絕刪除元組，或者將參照表中相應(yīng)元組的值設(shè)置為null，或者將參照表中相應(yīng)元組的值設(shè)置為默認(rèn)值。主鍵表更新元組時(shí)，也可以采用這四種方式中的一種。第六頁(yè)，共三十五頁(yè)，2022年，8月28日預(yù)備知識(shí)-創(chuàng)建約束

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

UNIQUE約束列級(jí)約束格式：

[CONSTRAINT約束名]UNIQUE表級(jí)約束格式：

[CONSTRAINT約束名]UNIQUE(字段名)【例9-3】建立StudentInfo表，并指定Sno和TeachTaskID聯(lián)合列值唯一。 CREATETABLESC ( Snochar(12), TeachTaskIDchar(26), Gradedecimal(5,2), UNIQUE(Sno,TeachTaskID) ）第九頁(yè)，共三十五頁(yè)，2022年，8月28日預(yù)備知識(shí)-創(chuàng)建約束

CHECK約束：列級(jí)CHECK約束和表級(jí)CHECK約束格式相同：[CONSTRAINT約束名]CHECK(檢驗(yàn)表達(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之間，等價(jià)于CHECK(LengSchbetween1and7)*/ )第十頁(yè)，共三十五頁(yè)，2022年，8月28日預(yù)備知識(shí)-創(chuàng)建約束

DEFAULT約束列級(jí)約束格式： [CONSTRAINT約束名]DEFAULT默認(rèn)值表級(jí)約束格式： [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)值為“女”*/ )第十一頁(yè)，共三十五頁(yè)，2022年，8月28日預(yù)備知識(shí)-創(chuàng)建約束表創(chuàng)建后添加約束如果表已經(jīng)存在，則可以通過ALTERTABLE語(yǔ)句添加約束，格式為：

ALTERTABLE表名

ADD[CONSTRAINT約束名]約束

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

第十二頁(yè)，共三十五頁(yè)，2022年，8月28日預(yù)備知識(shí)-創(chuàng)建約束【例9-6】假設(shè)StudentInfo表已經(jīng)建立，且沒有建立任何約束。為StudentInfo添加主鍵約束，且要求Sno(字符型，長(zhǎng)度為6)全部為數(shù)字。 ALTERTABLEStudentInfo ADDPRIMARYKEY(Sno) ALTERTABLEStudentInfo ADDcheck(Snolike'[0-9][0-9][0-9][0-9][0-9][0-9]')第十三頁(yè)，共三十五頁(yè)，2022年，8月28日預(yù)備知識(shí)–刪除約束刪除約束的格式為：ALTERTABLE表名

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

ALTERTABLEStudentInfoDROPDefault_Sex第十四頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)9數(shù)據(jù)完整性實(shí)驗(yàn)拓展練習(xí)實(shí)驗(yàn)步驟實(shí)驗(yàn)要求

實(shí)驗(yàn)?zāi)康念A(yù)備知識(shí)第十五頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)?zāi)康睦斫鈹?shù)據(jù)完整性的實(shí)現(xiàn)方式和作用。掌握應(yīng)用ManagementStudio創(chuàng)建和刪除約束的方法。掌握應(yīng)用T-SQL語(yǔ)句創(chuàng)建和刪除約束的方法。第十六頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)9數(shù)據(jù)完整性實(shí)驗(yàn)拓展練習(xí)實(shí)驗(yàn)步驟

實(shí)驗(yàn)要求

實(shí)驗(yàn)?zāi)康念A(yù)備知識(shí)第十七頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)要求為不帶任何約束的樣例數(shù)據(jù)庫(kù)ST中的各表建立適當(dāng)?shù)募s束。對(duì)樣例數(shù)據(jù)庫(kù)ST中各表中的約束進(jìn)行管理。撰寫實(shí)驗(yàn)報(bào)告，并附實(shí)驗(yàn)結(jié)果與相應(yīng)實(shí)驗(yàn)語(yǔ)句。第十八頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)9數(shù)據(jù)完整性實(shí)驗(yàn)拓展練習(xí)

實(shí)驗(yàn)步驟實(shí)驗(yàn)要求

實(shí)驗(yàn)?zāi)康念A(yù)備知識(shí)第十九頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)步驟為StudentInfo表的Sno添加UNIQUE約束，并添加記錄，體會(huì)唯一約束的作用。(1)在查詢窗口中輸入添加UNIQUE約束的SQL語(yǔ)句，并執(zhí)行：USESTALTERTABLEStudentInfoADDCONSTRAINTUN_SnoUNIQUE(Sno)(2)輸入以下添加記錄的SQL語(yǔ)句，并執(zhí)行2次：INSERTINTOStudentInfoVALUES('201001903029','明梅','女','1991-03-15','計(jì)算機(jī)系','計(jì)算機(jī)科學(xué)與技術(shù)(本科)',4)第一次執(zhí)行成功，記錄成功插入，第二次執(zhí)行在消息窗口中顯示以下消息，表示執(zhí)行插入操作失?。合?627，級(jí)別14，狀態(tài)1，第4行違反了UNIQUEKEY約束'UN_Sno'。不能在對(duì)象'dbo.StudentInfo'中插入重復(fù)鍵。

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

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

第二十四頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)步驟(3)輸入以下修改StudentInfo表中記錄的SQL語(yǔ)句，并執(zhí)行： UPDATEStudentInfo SETSno='201001903028'WHERESno ='201001903029'

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

第二十五頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)步驟(4）輸入以下刪除StudentInfo中記錄的SQL語(yǔ)句，并執(zhí)行： DELETEStudentInfo WHERESno='201001903028' 消息窗口中顯示以下信息，表示刪除操作失敗： 消息547，級(jí)別16，狀態(tài)0，第1行 DELETE語(yǔ)句與REFERENCE約束"FK_Sno"沖突。該沖突發(fā)生于數(shù)據(jù)庫(kù)"ST"，表"dbo.SC",column'Sno'。第二十六頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)步驟4.為StudentInfo表的Sex列添加CHECK約束，保證性別只能取“男”或“女”，并添加記錄，體會(huì)CHECK約束的作用。(1)在查詢窗口中輸入以下為Sex添加CHECK約束的SQL語(yǔ)句，并執(zhí)行： ALTERTABLEStudentInfo ADDCONSTRAINTCk_SexCHECK(SexIN('男','女'))第二十七頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)步驟(2)輸入以下插入記錄的SQL語(yǔ)句，并執(zhí)行： INSERTINTOStudentInfo VALUES('201011801017','王鵬飛','家','1991- 01-05','服裝系','服裝設(shè)計(jì)與工程(?？?',3)消息窗口中會(huì)顯示以下信息，表示插入操作失?。合?47，級(jí)別16，狀態(tài)0，第1行INSERT語(yǔ)句與CHECK約束"Ck_Sex"沖突。該沖突發(fā)生于數(shù)據(jù)庫(kù)"ST"，表"dbo.StudentInfo",column'Sex'。第二十八頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)步驟5.為StudentInfo表的Depart列添加DEFAULT約束，設(shè)置默認(rèn)值為“計(jì)算機(jī)系”。并添加記錄，體會(huì)DEFAULT約束的作用。(1)在查詢窗口中輸入以下為Depart添加DEFAULT約束的語(yǔ)句，并執(zhí)行： ALTERTABLEStudentInfo ADDCONSTRAINTDK_DepartDEFAULT‘計(jì)算 機(jī)系'forDepart第二十九頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)步驟(2)輸入以下插入記錄的SQL語(yǔ)句，并執(zhí)行：INSERTINTOStudentInfo(Sno,Sname,Sex,Birthday,Major,LengSch)values('201011801017','王鵬飛','男','1991-01-05','服裝設(shè)計(jì)與工程(?？?',3)(3)查看StudentInfo新添加記錄值為： ('201011801017','王鵬飛','男','1991-01-05','計(jì)算機(jī)系','服裝設(shè)計(jì)與工程(?？?',3)第三十頁(yè)，共三十五頁(yè)，2022年，8月28日實(shí)驗(yàn)步驟6.為StudentInfo表中的Sname列設(shè)置NOTNULL約束，添加記錄，體會(huì)非空約束的作用。(1)在查詢窗口中輸入以下更新數(shù)據(jù)列屬性的SQL語(yǔ)句，并執(zhí)行：ALTERTABLEStudentInfoALTERCOLUMNSnamevarchar(20)NOTNULL第三十一頁(yè)，共三十五頁(yè)，2022年，8月28日