完整性約束模板

1、數(shù)據(jù)庫實驗報告 完整性約束、實驗?zāi)康?.熟悉主鍵，外鍵，check等完整性約束;2.掌握完整性約束的建立，刪除，有效化和無效化;3.熟悉相關(guān)的DDL命令。4.學(xué)會看數(shù)據(jù)庫模型圖。二、實驗內(nèi)容1. 主鍵約束a.按圖1所示，建立所有表的主鍵約束。b.運行下列SQL語句:Xia ngta n Uni versityXia ngta n Uni versityin sert into branch values(Xiangtan ,4000000);in sert into branch values(Xiangtan ,5000000);記錄結(jié)果并分析原因。2. 外鍵約束a.按照圖1所示，建立所有的

2、外鍵約束。b.運行以下的兩條SQL語句: insert into loan values( L001, Xiangtan University ,1000);in sert in to loa n values(L-002 , Rain Lake ,2000);記錄結(jié)果并分析原因。C.運行以下的SQL語句: delete branch;記錄結(jié)果并分析原因。3. unique 約束a.在loan表上建立一個unique約束，保證amount是唯一的。b.運行以下SQL語句: insert into loan values( L-002 , Xiangtan University ,2000);記

3、錄結(jié)果并分析原因。c.使得剛才建立的約束無效化，并再次運行上一條SQL語句，記錄結(jié)果并分析原因。d.刪除掉這個約束。4. check 約束a.在loan表上建立一個check約束，保證amount必須為正數(shù)。b.運行以下SQL語句: insert into loan values( L-003 , Xiangtan University ,-2000);記錄結(jié)果并分析原因。三、實驗結(jié)果1、建立所有表的主鍵約束。ALTER TABLE accountADD CONSTRAINT AP K_num P RIMARYKEY (account_number);ALTER TABLE borrowerA

4、DD CONSTRAINT BCU_name P RIMARY KEY (customer_name,loan_number);ALTERTABLE branch ADD CONSTRAINT BH_name P RIMARY KEY (branch_name);ALTERTABLE customer ADD CONSTRAINT CC_name P RIMARY KEY (customer_name);ALTERTABLE dep ositor ADD CONSTRAINT DC_name P RIMARY KEY(customer_name,account_number);ALTER TA

5、BLE loan ADD CONSTRAINT Lnum P RIMARY KEY (loan_number);2、運行下列SQL語句：into branch values( Xiangtan University into branch values( Xiangtan Universityin sertin sert,Xiangtan ,4000000);,Xiangtan ,5000000);F Oracle立拌舗觀麗與賦運廿QP 3曰日尋毎少筑6 虧連接.I.J確日屁洼接$ 瞬 srcL:白值表:1*1 目 KHUtfT Q SKlODMia:a Sbiohck. EHKHCHJfHJ

6、I：g Haufnt_cnT 圧韶15:由 ScirsTuizB；H QmrasiTaK:由冒inuri 由 latfiS:田閑烹引;由闖程序ei Gi Qi過程i由將函數(shù)i申f賦列i由（6臥列表i曲時歯Si由啟笑型:a Cd序列i由園實直北規(guī)si申底兵化觀日志:田ifl同文詞i a-QiiJ共BA詞lii | IMMErfrfdU(f頤本ft 誓IS工具O r 孫 A rf2A S 顯 O & 毎 & 0. 96SEA6 sc-coaiJsIhEert iBto biarich uaHieftf XLanfltan Unlveialty jJDOaDDO);口 bE如亡hTJhivei；l.

7、T , 5000000)；口些果 園腳豐痢出？:：耐 h訂自動ia味I：目DMS岳岀kAWft岳出 Q a I1 e在行z上開結(jié)執(zhí)廳由傘吋出(S：Loaeiit Into bianch values Xlflngc.arL Univeralfy 5DDaciDD)鍛倏fS皆：SQL 錯螢：ORA-OMOl:違皮唯一釣噪豊辟日HJAHERDDOOI. QDOOQ - immufi conflcialni. t軸號町 vLolaced Causet Ayi UFDJTTH oi msmT ataxcBeiiG atceupced pd itiser匚 a dupllcaM key-Fbt TEua

8、tfrd aeaclfr cahfigted tn t-ETG IDUC nadt ycni aay Jfrc thin munnagu if a duplicate flntcy uxist? nt a diffticnt level.Action:Eiiht: leuome tJm utinjue cestcLctiom dz do tuat insert die Key,0代atI ： ij, S 69 ?11 I 34A I 榷改日期I Mrvk CEKtJ 跖切班=1腳本：1行已插入在行2上開始執(zhí)行命令時出錯：insert into branch values(Xia ngta n

9、Uni versity,Xia ngta n,5000000) 錯誤報告：SQL 錯誤：ORA-00001:違反唯一約束條件(2013960833.BH_NAME) 00001. 00000 - un ique con strai nt (%s.%s) violated*Cause: AnUPDAT&r INSERTstatement attempted to insert a duplicatekey.For Trusted Oracle con figured in DBMS MAC mode, you may seethismessageif a duplicate entry exis

10、ts at a differentlevel.*Action: Either remove the unique restrictionor do not insert the key.分析：已經(jīng)插入一行，再插入同名的主鍵列會出錯。違反唯一約束條件。3、建立所有的外鍵約束。ALTER TABLE borrowerADD CONSTRAINT FK1FOREIGN KEY (customer_name) REFERENCES customer(customer_name);ALTER TABLE borrowerADD CONSTRAINT FK2FOREIGN KEY (loan_number

11、) REFERENCES loan(loan_number);ALTER TABLE loanADD CONSTRAINT FK3FOREIGN KEY (branch_name) REFERENCES branch(branch_name);ALTER TABLE depo sitorADD CONSTRAINT FK4FOREIGN KEY (customer_name) REFERENCES customer(customer_name);ALTER TABLE dep ositorADD CONSTRAINT FK5FOREIGN KEY (account_number) REFERE

12、NCES account(account_number);ALTER TABLE accountADD CONSTRAINT FK6FOREIGN KEY (branch_name) REFERENCES branch(branch_name);4、運行以下的兩條SQL語句:in sert in to loa n values( 1_-001 , Xiangtan University ,1000);in sert in to loa n values(L002Rain Lake ,2000);口EX交拌褊粉ISS運fl暹，3占ag曄囂舊6亂連接. I口日國洼接$ 囲 41-eL；表:1*1

13、艮 MCQUKT:B OKlKfllDMia:i CUSTOWEhJfJJJE i血JJilS旗 H SfUHCKH ScUSlDHIfl i+i SBETasjTqji B fS IflUf:w冉視ffl:申ag理序包;由復(fù)過程:申回函數(shù);吐曲阪列:由畫臥列S；由la觸荒貿(mào)i a占裘型i由La序列i曲3買體世銀田i由吧實啊現(xiàn)日宙i申iiffl義詞i由妞菖共同戈詞I申電救ffi庫程按i由悔公共數(shù)氏僭陪I申蚌自錄由EliprC3Si即血Tmi由-崖；（NL方實i a -副fi站；畝Q苴抽用戶申融 SCdtta 3 gAI 魅 19 & o.EOJierisluert lit口 loan ralv

14、es ( L- DOI j aoigcflii rjntve LSi cy 1 DOO); iuEL-t口vdlaeu (-D02 IcJie ；結(jié)果舸4輸岀竜I榊丨曲自動跟跨I鍛価犠出，I 昭 輪出 冷9嗎I1 It efifiAITarc! *E上開皓執(zhí)行*辛?xí)r岀惜：ijiscEt into loan values ( L-QDE KainZQDQ錯詣抿吿：01熾甌CRA-02291:違應(yīng)芫降曲秦件|前口甜8茁訝-耒瞬戍頂關(guān)t*字DS291. (ID DOa - JjTcegEiGy consTLiauiG (宅 a. is) viaLaMd - p 笙 ent ke not found*

15、CausetA q上tLgvt key ualue tifa tie ea匕亡Fiing pEltney kty vbLM.*ction： Delete- the toLcign hey oi odd q saHtiin? pnnaiy Xey-0sofHWtSSP 4rax;le SQL Psvelcxper : orclI栄：行，第2E列 I插丸 I 改日期|仇ndzn CEKLJ Ed川陰腳本:1行已插入在行2上開始執(zhí)行命令時出錯：in sert into loan values(L-002,Ra in Lake,2000)錯誤報告：SQL錯誤:ORA-02291:違反完整約束條件(20

16、13960833.FK3)- 未找到父項關(guān)鍵字02291.00000 - in tegrity con strai nt (%s.%s) violated - pare nt key n ot found*Cause: A foreig n key value has no matchi ng p rimary key value.*Acti on: Delete the foreig n key or add a matchi ng p rimary key.5、運行以下的SQL語句:delete branch;匚叵區(qū)立件彌 IfiS 號銃運百3白日g，唧，牌黒0 6艮連接 n. 隔日國S接

17、$ 園 oi-cL:1*1 囲 KHVKT；曰屈 KlKSmiB；S CUST0WE5NE； 昌 UU町nilBBK:H SsfOHCK；H E3ciiyiDHiaI 1*1DE fa SI ng JiQinur:1*1固視ffl；由索引:由圍程序也；由11過程:由匾函魏；由逼艮列:$畫肚列Si申啟類型i由La序列i W扇買刪即田i由厲實啊現(xiàn)日宙i a fiiB文詞i由la公共同文詞i a 8救ffi庫鏈按i由悔公羯故憔jt(s陪j申曾自Si 由 Applicslifln iKprtisi申血Javai 5 g XhL方秦i申遛囪P陽i Q甘抽用戶申-融scatt誦(f)牘豐ft 誓垃 工具

18、幫*O ” T，A田顯 耳歸 Q o.EoaqSK stccoids1錯果 同轉(zhuǎn)檔礎(chǔ) 助jiRg I韻a動跟跨I価5犠出I 昭 輸出 夕a匕I IT=氣 細 fH SrtI arc! * I窿行I上并執(zhí)行命令時出烤：delece b Lancia錯諛搖皆=SQL 錯iS: QEUi-022;違貞芫SE約束矮件 t2013?t0e3.TK3F - ES渤？退錄tii223S, 00000 - uQteonty c口nsmmt *a斯a) vzalated - child zecoid touLnd Causeta匚IE叫pted to delete a parent ey value iJifl

19、t had a foieLgudfrpeftdencY.Action： delete- dpc-ndcricits Eirt thfln口匸 din心丄u conotraintrf Oracle SQL Develorper : orcl陽本已芫成I 1 ft, sS S ?11 I fifiA I ttSS 日期I 和口麗梆 CBL/LT Ui tan;腳本：在行1上開始執(zhí)行命令時出錯：delete branch錯誤報告：SQL錯誤：ORA-02292:違反完整約束條件(2013960833.FK3)-已找到子記錄02292. 00000 - in tegrity co nstrai nt

20、(%s.%s) violated - child record found*Cause: atte mp ted to delete a parent key value that had a foreig n dependency.*Action: deletedependencies first then parent or disable constraint.6、在loan表上建立一個unique約束，保證amount是唯一的。腳本:ALTER TABLE loan 成功。7、運行以下SQL語句: insert into loan values( L-002 , Xiangtan Un

21、iversity ,2000);F Oracle SQL Develcxper : orcltrig 區(qū)：a&(r頤本ft 誓這 工具d 0 孫D 11 r3 G 孫 j? &0 EOieesse ：*匸血=fliHEcrt lito loan valves ( L- D02 KI aaitfcan UioLrre lsI cy j, 2 DOO);結(jié)果 5曲本輸出 JIMS I匸勺旦動KBS 111015輔出1匚顧A輔出 夕9國IaJce ceible loan 我功1 行 eisxQ*= at*I ij, SB 54 ?11 I 插入 I 日期 I in.dov3 CEL/LT Edit

22、皿&使得剛才建立的約束無效化，并再次運行上一條SQL語句。|4 Oracle SQL Developer : crcl文件tRfl S 6t運行Q)id）廉本化移短工員3(33 * XQ即刼 I他S SkiSS$ Q rcL5血涙ffl 艮 AC8VHT9 Qboxbcwsb:ffi Sbrakch；9 3 CUSTOflZRi S 目 DErOSITOR ；9 Q LOAV；$2|視i s索引S疆覆厚6 sd過程es(S隊列sOM發(fā) aa 笑型9皿序列S實體化祝E9虧實體化楓SB志 ffibn義詞Qia公共n義詞i SBS*f9S9 la 公共 essttff a a*I d hB 繹.o

23、fo *-1 10- SOZSTiS sKorubalter table loan disable ia(nie |fliMVimt|;A繕果 討出 SJiralllS動蹤IQdhc愉出IQova愉出 essalcti cable loanirsg 主 JI Oracle SQL Developer : crcl文件彌S運行Q)3(33 * XQ即刼二 1禪硼7S顯廈接$ rcLffl 目 KCOUHT9 3 BOXBOltZR:ffi Sbrakch；9 3 CUSTOflZR5 S 目 DErOSITOR；9 Q LOAVsa索引I s疆覆厚6sd過程s(S隊列sOM 發(fā) aa 笑型9皿序

24、列S實體化祝E9虧實體化楓SB志 san義詞Q ifl公共n義詞S國8棗*9授9 la 公共 essttff a a*I hB (第 111 第 g 列 I tf入 I an| 肌鈕.cr/u rdm”id) !本化移短工員O fo 1A 園場 O00.50287259 secondsluert iKto loan valves ( L-002 x Xldnqcan UnivetsicY ,2000);A繕果 討出 SJiralllS動蹤IQdhc愉出IQova愉出 09呂Mb區(qū)S3 一即二insect Into loon values(* 1.-002*XiQngtn Onivcrsity* ,2000SQL 檔ORA-00001：理反維一約束條件 0);11、運行以下SQL語句: insert into loan values( L-003 , Xiangtan University ,-2000);f Oracle SQL Develcxper : orclCl&岡fl艮 iSS_. I鬲勵7曰國廷接$ 倉 Orel：甲 Skoqwt 由斕 MKflmia:H 冒 SfUHCK:H -EScuSidhih!