資源描述：
《數(shù)據(jù)庫完整性約束.doc》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。
1、數(shù)據(jù)庫完整性約束第一部分：創(chuàng)建如下兩張表student和course：學(xué)生表中：有如下字段與約束要求：sno/*列級(jí)完整性約束，設(shè)置sno屬性為主鍵*/sname/*列級(jí)完整性約束，要求sname屬性的值唯一*/ssex/*DEFAULT默認(rèn)值約束，CHECK約束限制ssex屬性的取值范圍*/sage/*檢查約束的實(shí)現(xiàn)，范圍可以自行定義*/sdeptCREATETABLESTUDENT(SNOINTCONSTRAINTSTUDENT_SNO_PKPRIMARYKEY,SNAMEVARCHAR(10)UNIQUE,SSEXVARCHAR(2)DEFAULT'男'CONSTRAINTST
2、UDENT_SSEX_CHKCHECK(SSEXIN('男','女')),SAGEINTCONSTRAINTSTUDENT_SAGE_CHKCHECK(SAGE>=0ANDSAGE<=100),SDEPTVARCHAR(20))課程表中:如下字段與要求：cno,cnamecpnoccreditcno/*設(shè)置主鍵*/CREATETABLECOURSE(CNOINT,CNAMEVARCHAR(20),CPNOINT,CCREDITVARCHAR(10),CONSTRAINTCOURSE_CNO_PKPRIMARYKEY(CNO))/*向student表插入記錄*/'200215121',
3、'李勇','男',20,'CS')'200215122','劉晨','女',19,'CS')'200215123','王敏','女',18,'MA')'200215125','張立','男',19,'IS')INSERTINTOSTUDENTVALUES('200215121','李勇','男',20,'CS');INSERTINTOSTUDENTVALUES('200215122','劉晨','女',19,'CS');INSERTINTOSTUDENTVALUES('200215123','王敏','女',18,'MA');INSERTINTOSTUDENTVALUES('2002
4、15125','張立','男',19,'IS');/*向course表插入記錄*/'1','數(shù)據(jù)庫','5',4'2','數(shù)學(xué)',null,2'3','信息系統(tǒng)','1',4'4','操作系統(tǒng)','6',3'5','數(shù)據(jù)結(jié)構(gòu)','7',4'6','數(shù)據(jù)處理',null,2'7','PASCAL語言','6',4INSERTINTOCOURSEVALUES('1','數(shù)據(jù)庫','5',4);INSERTINTOCOURSEVALUES('2','數(shù)學(xué)',null,2);INSERTINTOCOURSEVALUES('3','信息系統(tǒng)','1',4);INSERTINTOCOURSEVA
5、LUES('4','操作系統(tǒng)','6',3);INSERTINTOCOURSEVALUES('5','數(shù)據(jù)結(jié)構(gòu)','7',4);INSERTINTOCOURSEVALUES('6','數(shù)據(jù)處理',null,2);INSERTINTOCOURSEVALUES('7','PASCAL語言','6',4);實(shí)驗(yàn)內(nèi)容：一、創(chuàng)建SC表，并顯式說明違約處理規(guī)則表中字段如下：snoCHAR(9)cnoCHAR(4)gradeSMALLINTgrade是否為空或是否在0到100間sno,cno,/*sno,cno同時(shí)做主鍵*//*sno引用student表的sno做外鍵,并同時(shí)使用ondeletec
6、ascadeCREATETABLESC(SNOINT,CNOINT,GRADESMALLINTCONSTRAINTSC_GRADE_CHKCHECK(GRADEISNOTNULLANDGRADE>=0ANDGRADE<=100),CONSTRAINTSC_SNO_CNO_PKPRIMARYKEY(SNO,CNO),CONSTRAINTSC_STUDENT_SNO_FKFOREIGNKEY(SNO)REFERENCESSTUDENT(SNO)ONDELETECASCADE)插入測試數(shù)據(jù)：'200215121','1',92'200215121','2',85'200215121','3
7、',88'200215122','2',90'200215122','3',80INSERTINTOSCVALUES(200215121,1,92);INSERTINTOSCVALUES(200215121,2,85);INSERTINTOSCVALUES(200215121,3,88);INSERTINTOSCVALUES(200215122,2,90);INSERTINTOSCVALUES(200215122,3,80);輸入如下命令進(jìn)行測試，觀察結(jié)果
