第04章約束表關(guān)系多對(duì)

數(shù)據(jù)庫(kù)操作與管理語(yǔ)言O(shè)racleSQL

表的約束本章目標(biāo)數(shù)據(jù)完整性主鍵、外鍵、非空、默認(rèn)Check添加、刪除約束啟用、禁用約束表關(guān)系與主外鍵約束及關(guān)聯(lián)查詢一對(duì)一一對(duì)多多對(duì)多數(shù)據(jù)完整性存儲(chǔ)在數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)值均正確的狀態(tài)。如果數(shù)據(jù)庫(kù)中存儲(chǔ)有不正確的數(shù)據(jù)值，則該數(shù)據(jù)庫(kù)稱為已失去數(shù)據(jù)完整性。數(shù)據(jù)完整性(DataIntegrity)是指數(shù)據(jù)的精確性(Accuracy)和可靠性(Reliability)。它是應(yīng)防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義規(guī)定的數(shù)據(jù)和防止因錯(cuò)誤信息的輸入輸出造成無(wú)效操作或錯(cuò)誤信息而提出的。數(shù)據(jù)完整性-1數(shù)據(jù)類型年齡使用什么類型？格式是否正確身份證號(hào)，可以是15位也可以是18位范圍性別可選擇“男”、“女”、“未知”是否允許重復(fù)銀行卡號(hào)不允許重復(fù)特定業(yè)務(wù)要求銀行貸款數(shù)據(jù)完整性-2實(shí)體完整性域完整性引用完整性自定義完整性實(shí)體完整性實(shí)體完整性實(shí)體完整性要求每一個(gè)表中的主鍵字段都不能為空或者重復(fù)的值。實(shí)現(xiàn)方法唯一約束主鍵約束域完整性域完整性域完整性指列的值域的完整性。如數(shù)據(jù)類型、格式、值域范圍、是否允許空值等實(shí)現(xiàn)方法限制數(shù)據(jù)類型外鍵約束默認(rèn)值非空約束引用完整性引用完整性也稱之為參照完整性，當(dāng)更新、刪除、插入一個(gè)表中的數(shù)據(jù)時(shí)，通過(guò)參照引用相互關(guān)聯(lián)的另一個(gè)表中的數(shù)據(jù)，來(lái)檢查對(duì)表的數(shù)據(jù)操作是否正確。引用完整性要求關(guān)系中不允許引用不存在的實(shí)體。引用完整性與實(shí)體完整性是關(guān)系模型必須滿足的完整性約束條件。實(shí)現(xiàn)方法外鍵約束自定義完整性自定義完整性用戶自定義完整性指針對(duì)某一具體關(guān)系數(shù)據(jù)庫(kù)的約束條件，它反映某一具體應(yīng)用所涉及的數(shù)據(jù)必須滿足的語(yǔ)義要求。實(shí)現(xiàn)方法存儲(chǔ)過(guò)程觸發(fā)器表約束表約束的目的：確保表中數(shù)據(jù)的完整性常用的約束類型：主鍵約束(PRIMARYKEY)：要求主鍵列數(shù)據(jù)唯一，并且不允許為空非空約束(NOTNULL)：指定的列的值不允許為空唯一鍵約束(UNIQUE)：要求該列唯一，允許為空，但只能出現(xiàn)一個(gè)空值檢查約束(CHECK)：指定表中一列或多列可以接受的數(shù)據(jù)值格式默認(rèn)約束(DEFAULT)：指定某列的默認(rèn)值外鍵約束(FOREIGNKEY)：用于建立和加強(qiáng)兩個(gè)表數(shù)據(jù)之間連接的一列或多列。通過(guò)將表中的主鍵列添加到另一個(gè)表中?？梢詣?chuàng)建兩個(gè)表之間的連接。這個(gè)主鍵的列就稱為第二個(gè)表的外鍵。外鍵約束就可以確保添加到外鍵表中的任何行都在主表中都存在相應(yīng)的行主鍵：選擇主鍵的原則最少性盡量選擇單個(gè)鍵作為主鍵必須保證唯一。穩(wěn)定性盡量選擇數(shù)值更新少的列作為主鍵一般選擇沒(méi)有意義，且用戶不關(guān)心，但程序員關(guān)心的列做為表的主鍵?？梢允褂孟到y(tǒng)的sys_guid作主鍵的值。可以使用自動(dòng)增長(zhǎng)的integer列做為主鍵的值。主鍵約束的特點(diǎn)：唯一、不可重復(fù)。非空NULL。其他特點(diǎn)用戶不關(guān)心。對(duì)用戶沒(méi)有任何意義。但對(duì)程序開(kāi)發(fā)人員，意義重大。創(chuàng)建主鍵約束的語(yǔ)法：有三種：第一種，在創(chuàng)建表時(shí)，直接指定主鍵

SQL>createglobaltemporarytablet1(idintprimarykey,--這兒的primarykey指定了id為主鍵

namevarchar(30));第二種,在create中單獨(dú)一行createglobaltemporarytablet3(idint,namevarchar(30),--單獨(dú)一行創(chuàng)建主鍵。此時(shí)必須要指定主鍵的名稱。

constraintt3_pkprimarykey(id));第三種，通過(guò)修改表創(chuàng)建主鍵--通過(guò)修改表，創(chuàng)建主鍵createglobaltemporarytablet4(idint,namevarchar(30));--修改表，添加主鍵altertablet4addconstraintt4_pkprimarykey(id);案例：創(chuàng)建主鍵：CREATETABLEorder_status2(idINTEGERCONSTRAINTorder_status2_pkPRIMARYKEY,statusVARCHAR2(10),last_modifiedDATEDEFAULTSYSDATE);可以通過(guò)以下兩個(gè)系統(tǒng)視圖，查詢約束信息：

user_constraints--表上的約束User_cons_columns–列上的約束主鍵約束：向主鍵字段寫(xiě)入一個(gè)null值，將會(huì)出現(xiàn)以下錯(cuò)誤：在行23上開(kāi)始執(zhí)行命令時(shí)出錯(cuò):insertintot4(id,name)values(null,'jack')錯(cuò)誤報(bào)告:SQL錯(cuò)誤:ORA-01400:無(wú)法將NULL插入

("HR"."T4"."ID")01400.00000-"cannotinsertNULLinto(%s)"*Cause:*Action:

將主鍵中寫(xiě)入重復(fù)的數(shù)據(jù)，將出現(xiàn)以下錯(cuò)誤insertintot4(id,name)values(88,'jack')錯(cuò)誤報(bào)告:SQL錯(cuò)誤:ORA-00001:違反唯一約束條件

(HR.T4_PK)00001.00000-"uniqueconstraint(%s.%s)violated"*Cause:AnUPDATEorINSERTstatementattemptedtoinsertaduplicatekey.ForTrustedOracleconfiguredinDBMSMACmode,youmayseethismessageifaduplicateentryexistsatadifferentlevel.*Action:Eitherremovetheuniquerestrictionordonotinsertthekey.刪除主鍵約束：--創(chuàng)建帶有主鍵的列createtablet1(idintnotnull,nmvarchar(30),constraintt1_pkprimarykey(id));--刪除主鍵altertablet1dropconstraintt1_pk;--添加多行數(shù)據(jù)insertintot1values(1,1);select*fromt1;--必須先將重復(fù)的數(shù)據(jù)刪除以后才可以添加主鍵,默認(rèn)只留下一行deletefromt1whererownum=1;--然后再添加主鍵altertablet1addconstraintt1_pk2primarykey(id);禁用和啟動(dòng)主鍵約束：--禁用約束altertablet1disableconstraintt1_pk2;--啟動(dòng)主鍵約束altertablet1enableconstraintt1_pk2;唯一約束以下ALTERTABLE語(yǔ)句給order_status2表的status列添加UNIQUE約束ALTERTABLEorder_status2ADDCONSTRAINTorder_status2_status_uqUNIQUE(status);在往order_status2表中添加任意行時(shí)，行的status列的值必須要唯一。唯一鍵約束：Unique是唯一約束，也可稱為候選鍵Unique創(chuàng)建的約束列上，不可以存在相同的值。但可以保存兩個(gè)null.createglobaltemporarytablet5(idintunique,namevarchar(30));向唯一約束中寫(xiě)入兩個(gè)相同的值：SQL錯(cuò)誤:ORA-00001:違反唯一約束條件(HR.SYS_C007008)00001.00000-"uniqueconstraint(%s.%s)violated"*Cause:AnUPDATEorINSERTstatementattemptedtoinsertaduplicatekey.ForTrustedOracleconfiguredinDBMSMACmode,youmayseethismessageifaduplicateentryexistsatadifferentlevel.*Action:Eitherremovetheuniquerestrictionordonotinsertthekey.刪除/啟用/禁用唯一約束--創(chuàng)建唯一約束createtablet2(idint,ageint,constraintt2_uqunique(id));--寫(xiě)入記錄insertintot2values(1,1);select*fromt2;--刪除唯一約束altertablet2dropconstraintt2_uq;--添加唯一約束,如果存在重復(fù)的記錄則添加不成功altertablet2addconstraintt2_uq2unique(id);--刪除重復(fù)的數(shù)據(jù)deletefromt2whererownum=1;--禁用唯一約束altertablet2disableconstraintt2_uq2;--啟用唯一約束altertablet2enableconstraintt2_uq2;非空約束非空約束給status列添加非空約束ALTERTABLEorder_status2MODIFYstatusCONSTRAINTorder_status2_status_nnNOTNULL;給modified_by列添加非空約束ALTERTABLEorder_status2MODIFYmodified_byCONSTRAINTorder_status2_modified_by_nnNOTNULL;給last_modified列添加非空約束，不指定約束名ALTERTABLEorder_status2MODIFYlast_modifiedNOTNULL;非空約束:--以下創(chuàng)建一個(gè)非空約束createglobaltemporarytablet8(idintnotnull,nmvarchar(30));insertintot8values(null,'ddd');--外部添加createglobaltemporarytablet9(idint,ageint);--修改為非空約束altertablet9modifyidnotnull;insertintot9values(null,33);刪除/添加/啟用/禁用非空約束：--以下是非空約束createtablet3(idintnotnull,ageint);--查詢非空約束select*fromuser_constraintswheretable_name='T3';--寫(xiě)入空值，出錯(cuò)insertintot3values(null,33);deletefromt3;--刪除非空約束altertablet3dropconstraintSYS_C007024;--添加非空約束altertablet3modifyidnotnull;--禁用altertablet3disableconstraintSYS_C007025;--啟動(dòng)altertablet3enableconstraintSYS_C007025;默認(rèn)值約束是指用戶沒(méi)有給定此列的值時(shí)，則使用默認(rèn)值

--添加默認(rèn)值約束createglobaltemporarytablet10(idint,ageintdefault0);createglobaltemporarytablet11(idint,ageint);--添加默認(rèn)值altertablet11modifyagedefault11;CHECK約束添加CHECK約束ALTERTABLEorder_status2ADDCONSTRAINTorder_status2_status_ckCHECK(statusIN('PLACED','PENDING','SHIPPED'));測(cè)試數(shù)據(jù)正確數(shù)據(jù)INSERTINTOorder_status2(id,status,last_modified,modified_by)VALUES(1,'PENDING','01-1月-2005',1);錯(cuò)誤數(shù)據(jù)INSERTINTOorder_status2(id,status,last_modified,modified_by)VALUES(2,'CLEARED','01-1月-2005',2);提示錯(cuò)誤：ORA-02290:違反檢查約束條件(STORE.ORDER_STATUS2_STATUS_CK)Check約束性別：--check約束createtablet4(idint,sexchar(1)constraintsex_chkcheck(sexin('1','0')));--寫(xiě)入一行記錄insertintot4values(2,'0');check約束區(qū)間：createtablet5(idint,ageint);--限制年齡在0~100altertablet5addconstraintt5_chkcheck(age>=0andage<=100);--查詢select*fromuser_constraintswheretable_name='T5';--寫(xiě)入數(shù)據(jù),age>100將不成功insertintot5values(3,144);deletefromt5;--禁用altertablet5disableconstraintt5_chk;--啟用altertablet5enableconstraintt5_chk;Check約束字符串：--添加一個(gè)是檢查，只可以寫(xiě)入以M開(kāi)始的數(shù)據(jù)altertablepersonaddconstraintp_ckcheck(namelike'M%');主外鍵約束與范式：范式：第一范式：對(duì)于表中的每一行，必須且僅僅有唯一的行值.在一行中的每一列僅有唯一的值并且具有原子性.第二范式要求非主鍵列是主鍵的子集，非主鍵列活動(dòng)必須完全依賴整個(gè)主鍵。主鍵必須有唯一性的元素,一個(gè)主鍵可以由一個(gè)或更多的組成唯一值的列組成。一旦創(chuàng)建，主鍵無(wú)法改變，外鍵關(guān)聯(lián)一個(gè)表的主鍵。主外鍵關(guān)聯(lián)意味著一對(duì)多的關(guān)系.第三范式要求非主鍵列互不依賴.第四范式禁止主鍵列和非主鍵列一對(duì)多關(guān)系不受約束第五范式將表分割成盡可能小的塊，為了排除在表中所有的冗余.主鍵與外鍵主鍵與外鍵是維護(hù)多表關(guān)系的有效手段。注意事項(xiàng)當(dāng)主表中沒(méi)有對(duì)應(yīng)的記錄時(shí)，不能將記錄添加到子表不能更改主表中的值而導(dǎo)致子表中的記錄孤立子表存在與主表對(duì)應(yīng)的記錄，不能從主表中刪除該記錄刪除主表前，先刪子表E-R模型E-R模型的特點(diǎn)

Entities：實(shí)體Attributes：屬性Relationships：關(guān)系通常有許多表通常是滿足3NF的主鍵/外鍵1對(duì)多映射建立E-R模型是數(shù)據(jù)庫(kù)概念設(shè)計(jì)的重要內(nèi)容，而概念設(shè)計(jì)是設(shè)計(jì)階段的組成部分。同時(shí)建立E-R模型的工作，屬于軟件生命周期的設(shè)計(jì)階段。E-R圖示例外鍵約束-1添加外鍵約束--創(chuàng)建主外鍵關(guān)系createtableperson(idvarchar（32),namevarchar(30),constraintp_pkprimarykey(id));--創(chuàng)建外表createtablecar(idvarchar(32),namevarchar(30),pidvarchar(32),constraintc_pkprimarykey(id),

constraintc_fkforeignkey(pid)referencesperson(id));請(qǐng)插入數(shù)據(jù)、刪除數(shù)據(jù)、刪除約束、添加約束進(jìn)行測(cè)試。外鍵約束-2在創(chuàng)建外鍵約束時(shí)可以添加ONDELETECASCADE選項(xiàng)，那么當(dāng)主表的數(shù)據(jù)被刪除時(shí)，子表對(duì)應(yīng)的行同樣也自動(dòng)被刪除。例如--創(chuàng)建主外鍵關(guān)系且設(shè)置級(jí)聯(lián)刪除createtablep2(idint,nmvarchar2(30),constraintp2_pkprimarykey(id）);--下面的ondeletecascade就是刪除時(shí)級(jí)聯(lián)createtablec2(idint,nmvarchar2(30),pidint,constraintc2_pkprimarykey(id),constraintc2_fkforeignkey(pid)referencesp2(id)ondeletecascade);外鍵約束-3在創(chuàng)建外鍵約束時(shí)可以添加ONDELETESETNULL選項(xiàng)，那么當(dāng)主表的數(shù)據(jù)被刪除時(shí)，子表匹配的相關(guān)行的列會(huì)被設(shè)置為NULL值，而不是被刪除例如設(shè)置外鍵ALTERTABLEorder_status2ADDCONSTRAINTorder_status2_modified_by_fkmodified_byREFERENCESemployees(employee_id)ONDELETESETNULL;表與表的關(guān)系：一對(duì)一一對(duì)一的場(chǎng)景一對(duì)一的實(shí)現(xiàn)方案主鍵對(duì)主鍵的一對(duì)一主鍵對(duì)唯一的一對(duì)一一對(duì)一的查詢innerjoin,leftjoin,rightjoing一對(duì)多一對(duì)多的場(chǎng)景主鍵對(duì)外鍵的引用一對(duì)多的查詢多對(duì)多多對(duì)多的場(chǎng)景多的多的實(shí)現(xiàn)使用中間表實(shí)現(xiàn)多對(duì)多多對(duì)多的查詢大量案例查看和修改約束信息：修改約束-1刪除約束通過(guò)ALTERTABLE的DROPCONSTRAINT選項(xiàng)刪除約束ALTERTABLEorder_status2DROPCONSTRAINTorder_status2_status_uq;停用約束約束在創(chuàng)建完之后就生效了，也可以在創(chuàng)建時(shí)在CONSTRAINT關(guān)鍵字末尾加DISABLE關(guān)鍵字，默認(rèn)停用它。ALTERTABLEorder_status2ADDCONSTRAINTorder_status2_status_uqUNIQUE(status)DISABLE;也可以停用已經(jīng)啟用的約束，例如約束order_status2_status_nnALTERTABLEorder_status2DISABLECONSTRAINTorder_status2_status_nn;修改約束-2啟用約束可以使用ALTERTABLE的ENABLECONSTRAINT選項(xiàng)將已存在的約束失效啟用。例如ALTERTABLEorder_status2ENABLECONSTRAINTorder_status2_status_uq;說(shuō)明在啟用約束時(shí)，表中所有的數(shù)據(jù)行的數(shù)據(jù)必須要滿足這個(gè)約束。也可以通過(guò)設(shè)置ENABLENOVALIDATE，對(duì)新的數(shù)據(jù)行增加約束ALTERTABLEorder_status2ENABLENOVALIDATECONSTRAINTorder_status2_status_uq;說(shuō)明默認(rèn)是ENABLEVALIDATE，對(duì)存在的數(shù)據(jù)行也要求符合約束延遲約束延遲約束(DeferredConstraint)就是在事務(wù)提交的時(shí)候才生效可以在約束中加DEFERRABLE選項(xiàng)約