oracle约束键解析

一、 数据库约束的作用和分类 prrimaryykey 和uniqque他们的的作用是为了了不让表中有有重复的数据据（一个表） chheck表示示表中列的数数据的范围性性的定义（一一个表） deefult表表示表中的默默认值 fooreignnkey 表表与表之间的的联系（两个个或两个以上上的表）【uniquee是可以包含含null值值的,并且一一个表中可以以有多个unnique connstraiint而prrimaryykey在一一个表中只能能有一个.并并且不能有nnull值】二、针对约束创创建有两种不不同的思想 第一一、创建标的的时候一同将将约束创建了了 第二二、你的表已已经创建好了了，向表中添添加约束 三、下面我就一一一的介绍他他们 一一、 priimary key 主主键约束 两种写法 在已已经创建的表表中添加约束束 解解释：添加约约束的时候一一定要修改表表故用（allter修修改） Alteer tabble taable1 Add pprimarry keyy (列名11，列名2) 同表表一起创建 CCreatee tablle tabble1 (列名1 数据类型 是否为空空 primmary kkey , 列列名1 数数据类型 是否为空 ) 上上面的是两种种情景为一个个字段设置主主键，接下来来是以多个列列作为主键。 Crreate tablee tablle1 ( 列名1 数据类类型 是是否为空， 列名2 数据类型型 是否否为空， 列名3 数据类类型 是是否为空， Primmary kkey (列列名1 ，列列名2) ) 二、Chheck约束束 这个个和主键约束束差不多 Alterr tablle tabble1 Add uuniquee（列名） 三三、外键约束束 好多的的同学一直也也都不理解外外键约束，到到底什么才是是外键？主键键在那个表上上？外键在那那个表上？用用T-SQLL语句写的时时候是主表在在前，还是外外间表在前？等等一系列列的问题！ 接下来来我讲给大家家说说这个主主外键到底怎怎么弄的。 其实，主主外键之间正正如它的名字字一样，他们们之间有一个个主从关系，一一个主表，一一个从表，主主表上面设置置的是主键，从从表上面设置置的是外键（fforeiggn keyy），主键表表中的数据发发生变化的时时，外键（fforeiggn keyy）表的数据据立刻就发生生改变。 向已有的表表中添加一个个主键（没有有就是添加，添添加就是修改改表） AAlter tablee tablle1 AAdd fooreignn key (列名1)referrencess tablle2（列名名2） 创建表的的时候一同创创建外键 Creatte tabble taable1 （列列名4 数据据类型 是是否为空 pprimarry keyy， 列名33 数据类型型 是否为为空， 列名1 数据类型 refeerencees tabble2 （列列名2） ） 注： Tablle1外键表表 Tabble2主键键表 列名11是主表列 列名22是外键表列列 最一一般常用的是是主表的列和和外键表的列列的字段是一一样的 一般多多为ID 可可以作为主键键的。这是两两个之间的交交互。 四、CCheck约约束 他也是基基于一个表中中的约束，限限定表中列直直接爱你数据据的参照关系系。 向一个以以创建的表中中添加约束 Alterr tabeel tabble1 Addd consstrainnt 约束名名 checck（列名对对于列的限制制条件） 多多是一些可以以找到范围的的数据，例如如年龄，身高高，体重。 五五、defaault 默默认约束 他是一个个对于某一个个值的约束，他他是唯一的一一个用forr关联词的句句子 向表中添添加一个默认认约束 Altter taabletaable1 Addd deffault 默认值for 列列名 六、删除除约束 删删除约束也是是对于表的修修改故： AAlter tablee tablle1 DDrop cconstrraint 约束名 注：上面就是我我所理解的一一些约束。想想要记住他们们并不难 1 开头的一般般都是 allter ttable 表名 或或是creaate taable 表表名 2 在已有的表表中添加约束束用 altter 3 与表同时创创建用 crreate 4 他们所有的的关键字： 主键 约约束：priimary key 唯一性约约束：uniique 外键 约束：frroeignn key.refeerencees(参照).两个个表之间的关关系，该是互互相参照 查询 约约束：. coonstraaint（约约束）.cheeck对字字段查询，对对字段约束 默认 约束：.defaault.foor（给）. 将前前面默认的值值 给 后边边的字段ORACLE约约束总结2012-055-13 008:36 by 潇湘湘隐者, 6682 viisits,收藏,编辑你对ORACLLE约束的了了解如何？比比较模糊还是是相当透彻？如果你对下下面几个问题题了如指掌的的话，恭喜你你，你已经对对约束掌握得得比较好了，不不用看这篇文文章了。ORRACLE的的约束有啥功功能作用？ 有哪些类型型约束（不同同版本ORAACLE是否否不同）？视视图有约束吗吗？约束是否否会影响SQQL性能？ 约束信息存存储在哪些系系统视图、数数据字典中？约束能否修修改名称？能能否禁用约束束？延迟约束束有啥好处.约束定义约束是强加在表表上的规则或或条件。确保保数据库满足足业务规则。保保证数据的完完整性。当对对表进行DMML或DDLL操作时，如如果此操作会会造成表中的的数据违反约约束条件或规规则的话，系系统就会拒绝绝执行这个操操作。约束可可以是列一级级别的 也可可以是表级别别的。定义约约束时没有给给出约束的名名字，ORAACE系统将将为该约束自自动生成一个个名字，其格格式为SYSS_Cn，其其中n为自然然数(强烈建建议各位在创创建表或增加加约束时，给给约束定义名名称。).约束功能约束的功能：实实现一些业务务规则，防止止无效的垃圾圾数据进入数数据库，维护护数据库的完完整性(完整整性指正确性性与一致性)。从而使数数据库的开发发和维护都更更加容易。约束分类约束分分为6类： 非空（NOOT NULLL）约束、 唯一（UNNIQUE）约约束、主键（PPRIMARRY KEYY）约束、外外键（FORREIGN KEY）约约束、条件（CCHECK）约约束、REFF约束。非空（NOT NULL）约约束：顾名思思义，所约束束的列不能为为NULL值值。否则就会会报错唯一（UNIQQUE）约束束：在表中每每一行中所定定义的这列或或这些列的值值都不能相同同。必须保证证唯一性。否否则就会违法法约束条件。主键（PRIMMARY KKEY）约束束：唯一的标标识表中的每每一行，不能能重复，不能能为空。 创创建主键或唯唯一约束后，OORACLEE会自动创建建一个与约束束同名的索引引（UNIQQUENESS为UNIQQUE唯一索索引）。需要要注意的是：每个表只能能有且有一个个主键约束。外键（FOREEIGN KKEY）约束束：用来维护护从表（Chhild TTable）和和主表（Paarent Tablee）之间的引引用完整性. 外键约束束是个有争议议性的约束，它它一方面能够够维护数据库库的数据一致致性，数据的的完整性。防防止错误的垃垃圾数据入库库； 另外一一方面它会增增加表插入、更更新等SQLL性能的额外外开销，不少少系统里面通通过业务逻辑辑控制来取消消外键约束。例例如在数据仓仓库中，就推推荐禁用外键键约束。条件（CHECCK）约束：表中每行都都要满足该约约束条件。条条件约束既可可以在表一级级定义也可以以在列一级定定义。在一列列上可以定义义任意多个条条件约束。REF约束的定定义：REFF coluumn byy defiinitioon refferencces ann objeect inn anotther oobjectt typee or iin a rrelatiional tablee. A RREF coonstraaint llets yyou fuurtherr desccribe the rrelatiionshiip bettween the RREF coolumn and tthe obbject it reeferennces.ORACLE 11 数据据库的约束类类型如下： ORACLE 10 数据据库的约束类类型如下：在oraclee 官方文档档http:/doccs.oraacle.ccom/cdd/B193306_011/servver.1002/b144237/sstatviiews_11037.hhtm下C(checck connstraiint onn a taable)PP(priimary key)UU(uniique kkey)R(refeerentiial inntegriity)V(withh checck opttion, on a view)O(wiith reead onnly, oon a vview)http:/m/cd/BB283599_01/sserverr.111/b282886/claauses0002.httmhttp:/m/cd/BB283599_01/sserverr.111/b283220/staatviewws_10444.htmm#REFRRN200447CCheckk consstrainnt on a tabblePPPrimarry keyyUUniique kkeyRRRefereentiall inteegrityyVWitth cheeck opption, on aa viewwOWitth reaad onlly, onn a viiewHHHash eexpresssionFFConsstrainnt thaat invvolvess a REEF collumnSSuppllementtal looggingg对于H、S类型型，尚不清楚楚情况，而且且从上面我在在11g数据据库实验结果果看，也没发发现这两种类类型。(有了了解的朋友可可以告知一二二)约束命名规范约约束名称建议议自己定义一一套命名规则则，否则使用用系统生成的的约束名，很很难能把它和和对应的表、字字段联系起来来。非空约束 NNN_表名_列名唯唯一约束 UKK_表名_列列名主键约束 PPK_表名外键键约束 FK_表名_列名名条条件约束 CKK_表名_列列名默认约束 DDF_表名_列名如果约束名称超超过32位长长度，建议应应该缩写表名名，而不应用用NN_表名名_数字。不不过具体视情情况而定，很很多时候 DDF_表名_列名 这样样命名，往往往超出了322字符。所以以有时候需要要缩写表面或或是采用其它它规则。约束操作约束管理创建各类约约束先看一下如如何创建主键键约束CREATE TABLEE DM.DEPPT ( DDEPTNOO NUMBBER(2,0) PRIMMARY KEY, DDNAME VARCCHAR2(14), LLOC VARCCHAR2(13) ) TABBLESPAACE TTBS_DMM_DAT ; COMMENTT ON TABLEE DM.DEPPT ISS 部门表; COMMENTT ON COLUMMN DMM.DEEPT.DEPTNNO IS 部门编编号; COMMENTT ON COLUMMN DMM.DEEPT.DNAMEE IS 部门名名称; COMMENTT ON CLOOUMN DM.DEPT.LOCC IS 部门所所在地方;此时它的约束名名称是系统创创建的。如图图所示：如果我们以下下面的脚本创创建，此时约约束如下所示示 1 CREAATE TABLEE DM.DEPPT 2 ( 3 DEPPTNO NUMBEER(2,0), 4 DNAAME VVARCHAAR2(14) , 5 LOCC VARCHHAR2(13) , 6 CONSSTRAINNT PKK_DEPTT PRIIMARY KEY (DEPTNNO) UUSING INDEXX TABLLESPACCE TBSS_DM_IIN 7 ) TABLEESPACEE TBSS_DM_DDAT ; 8 9 10 11 12 COMMMENT OON TABLEE DM.DEPPT ISS 部门表;13 14 COMMMENT OON COLUMMN DMM.DEEPT.DEPTNNO IS 部门编编号;15 16 COMMMENT OON COLUMMN DMM.DEEPT.DNAMEE ISS 部门名名称;17 18 COMMMENT OON CLOOUMN DM.DEPT.LOCC IS 部门所所在地方;19 如果一个表的约约束类型比较较多，你会看看到一堆SYYS_CN这这样的约束，如如果不仔细核核对后面的字字段，很难一一眼看出约束束类型、约束束对应字段。主键约束的限制制文档原文如下：(翻译在下下)Resstricttions on Prrimaryy Key Consttraintts Priimary consttraintts aree subjject tto thee folllowingg resttrictiions:A tabble orr vieww can have only one pprimarry keyy.一个个表或视图有有且只有一个个主键None of thhe collumns in thhe priimary key ccan bee LOB, LONGG, LONNG RAWW, VARRRAY, NESTEED TABBLE, BBFILE, REF, TIMEESTAMPP WITHH TIMEE ZONEE, or user-definned tyype. HHoweveer, thhe priimary key ccan coontainn a coolumn of TIIMESTAAMP WIITH LOOCAL TTIME ZZONE.主键字段段不能为LOOB、LOGG、LOG RAW、VVARRAYY、NESTTED TAABLE、BBFILE、RREF、TIIMESTAAMP WIITH TIIME ZOONE或用户户定义类型。然然而它可以包包含TIMEESTAMPP WITHH LOCAAL TIMME ZONNE类型的字字段。The ssize oof thee primmary kkey caannot exceeed appproximmatelyy one databbase bblock.主键大大小不能超过过一个数据块块大小。A commpositte priimary key ccannott havee moree thann 32 ccolumnns.主主键组合键不不能超过322列。You ccannott desiignatee the same colummn or combiinatioon of colummns ass bothh a prrimaryy key and aa uniqque keey.你你不能指定一一列或组合列列既是主键又又是唯一键。You ccannott speccify aa primmary kkey whhen crreatinng a ssubvieew in an innherittance hieraarchy. The primaary keey cann be sspeciffied oonly ffor thhe topp-leveel (rooot) vview.创建一个个继承层次结结构中的子视视图时，你不不能指定一个个主键。主键键可以唯一指指定的顶层（根根）视图。外外键约束ALTERR TABLLE chiildTabbleADD CCONSTRRAINT FK_xxxxx_xxxxFOREIIGH KEEY(collumnNaame)REFERRENCESS pareentTallbe(prrimaryyKeyCoolumn) ON DELETTE CASSCADE|SET NNULL;ON DELEETE SEET NULLL子句：当当主表中的一一行数据被删删除时，Orracle系系统会自动地地将所有从表表中依赖于它它的数据记录录的外键改成成空值；ON DELEETE CAASCADEE：当主表中中的一行数据据被删除时，OOraclee系统会自动动地将所有从从表中依赖于于它的数据记记录删除；注：如果在外键键定义中使用用了ON DDELETEE SET NULL 或ON DDELETEE CASCCADE，无无论删除操作作是在父表这这一端还是子子表这一端都都不会产生违违反引用完整整性的问题，但但是却留下了了安全隐患。这这两个子句要要谨慎使用，只只有在不得已已时使用，谨谨记谨记！外键约束对DMML与DDLL的影响：INSERT：只有操作是是在子表或从从表这一端时时才会产生违违反引用完整整性约束的问问题，父表则则不然。DELETE：只有操作是是在父表或主主表这一端时时才会产生违违反引用完整整性约束的问问题，子表则则不然。UPDATE：子表父表直直接操作都会会违反引用完完整性约束。两两种解决方法法：1）先更新子表表的引用列为为空，再更新新父表的主键键的列的值，然然后把子表的的引用列更新新成新的父表表的值；2）使用ON DELETTE SETT NULLL，先更新父父表，然后将将子表外键为为空的记录更更新为新的值值。DDL语句：DDROP TTABLE与与TRUNCCATE TTABLE，操操作父表，违违反引用完整整性约束，子子表则不然。外键约束的限制制：Resttrictiions oon Forreign Key CConstrraintss Foreeign kkey coonstraaints are ssubjecct to the ffollowwing rrestriictionns:None off the colummns inn the foreiign keey cann be oof LOBB, LONNG, LOONG RAAW, VAARRAY, NESTTED TAABLE, BFILEE, REFF, TIMMESTAMMP WITTH TIMME ZONNE, orr userr-defiined ttype. Howevver, tthe prrimaryy key can ccontaiin a ccolumnn of TTIMESTTAMP WWITH LLOCAL TIME ZONE.外键字段不不能为LOBB, LONNG, LOONG RAAW, VAARRAY, NESTTED TAABLE, BFILEE, REFF, TIMMESTAMMP WITTH TIMME ZONNE, orr userr-defiined ttype类型型，主键可以以包含数据类类型为TIMMESTAMMP WITTH LOCCAL TIIME ZOONE的字段段。The refferencced unnique or prrimaryy key consttraintt on tthe paarent tablee or vview mmust aalreaddy be definned.引用用唯一或主键键约束，必须须是父表中已已经定义的。A compoosite foreiign keey cannnot hhave mmore tthan 332 collumns.外键的组合合列不能超过过32列。The chiild annd parrent ttabless mustt be oon thee samee dataabase. To eenablee refeerentiial inntegriity coonstraaints acrosss noddes off a diistribbuted databbase, you mmust uuse daatabasse triiggerss. Seee CREAATE TRRIGGERR.字表和父父表必须在同同一个数据库库。分布式数数据库中，外外键不能跨节节点，但触发发器可以Iff eithher thhe chiild orr pareent obbject is a view, thenn the consttraintt is ssubjecct to all rrestriictionns on view consttraintts. Seee Viiew Coonstraaints.You cannnot ddefinee a fooreignn key consttraintt in aa CREAATE TAABLE sstatemment tthat ccontaiins ann AS ssubqueery cllause. Insttead, you mmust ccreatee the tablee withhout tthe coonstraaint aand thhen addd it laterr withh an AALTER TABLEE stattementt.你不能在在CREATTE TABBLE语句中中包含AS子子查询子句定定义一个外键键约束。相反反，你必须创创建一个没有有约束的表，然然后添加ALLTER TTABLE语语句。加上一些类似其其它约束的限限制条件约束非空约束非空约束的限制制：1： 你你不能在视图图约束上指定定字段为NUULL或NOOT NULLL值2： 你不能指定定一个对象的的属性为NUULL或NOOT NULLL。相反，而而应使用的IIS NOOTNULL条条件CHECCK约束条件件约束中的条条件与查询语语句中的条件件相同，但是是不能包括以以下内容：11）CURRRVAL,NNETXTVVAL,LEEVEL和RROWNUMM这样的伪列列（PSEUUDOCOLLUMNS）；2）引用其其它行中值的的查询语句；3）SYSSDATE,USER,USEREENV和UIID的函数调调用。文档原原文如下：RRestriictionns on NOT NNULL CConstrraintss NOT NULL consttraintts aree subjject tto thee folllowingg resttrictiions:You cannnot sspeciffy NULLL or NOT NNULL iin a vview cconstrraint.You cannnot sspeciffy NULLL or NOT NNULL ffor ann attrributee of aan objject. Insteead, uuse a CHECKK consstrainnt witth thee IS NOT NULL condiition.唯一约束唯一约束的限制制：文档原文如下：Restrrictioons onn Uniqque Coonstraaints Uniquue connstraiints aare suubjectt to tthe foollowiing reestricctionss:None off the colummns inn the uniquue keyy can be off LOB, LONGG, LONNG RAWW, VARRRAY, NESTEED TABBLE, OOBJECTT, REFF, TIMMESTAMMP WITTH TIMME ZONNE, orr userr-defiined ttype. Howevver, tthe unnique key ccan coontainn a coolumn of TIIMESTAAMP WIITH LOOCAL TTIME ZZONE.当当字段类型为为LOB、LLOG、LOONG RAAW、 VAARRAY、NNESTEDD TABLLE、OBJJECT、RREF、TIIMESTAAMP、用户户定义类型时时，不能在字字段上定义唯唯一性约束。当当数据类型为为TIMESSTAMP WITH LOCALL TIMEE ZONEEA compoosite uniquue keyy cannnot haave moore thhan 322 coluumns.组组合字段定义义的唯一索引引，字段个数数不能超过332个You cannnot ddesignnate tthe saame coolumn or coombinaation of coolumnss as bboth aa primmary kkey annd a uuniquee key.你不能指定定同一字段或或组合字段即即为主键又是是唯一键You cannnot sspeciffy a uuniquee key when creatting aa subvview iin an inherritancce hieerarchhy. Thhe uniique kkey caan be speciified only for tthe toop-levvel (rroot) view.约束管理删除约束ALTER TTABLE DM.EMMPDROP COONSTRAAINT SSYS_C0001155525;CASCADEE子句用来删删除存在有完完整性关系的的约束。当然然你也可以用用如下语法AALTER TABLEE DM.EEMPDROOP UNIIQUE (COMM);添加约束添加非空约束ALTER TTABLE DM.EMMPMODIFY(MGR CCONSTRRAINT CK_EMMP_MGRR NOT NULL);添加其他约束ALTER TTABLE DM.EMMPADD CONNSTRAIINT CKK_EMP_SAL CCHECK(SAL 0 AND SAAL crreate tablee exammple (id nuumber primaary keey, naame vaarcharr2(20);Table ccreateedSQL crreate tablee exammple2 (id nnumberr,namee varcchar2(20) ,primaary keey (idd,namee);Table ccreateedSQL deesc exxamplee2;Name Tyype Nullaable DDefaullt Commmentss- - - - -ID NUUMBER NAME VAARCHARR2(20) 2.创建表后，添添加主键约束束：语法：alteer tabble 表名名 add primaary keey (列名名1，列名22，.);可以多列列构成主键。如：SQL allter ttable exampple2 ddrop pprimarry keyy; -删除主键Table aaltereedSQL allter ttable exampple2 aadd prrimaryy key (id,nname); -添加加主键Table aaltereed3.为主键命名名：显式命名主键：语法：alteer tabble 表名名 add consttraintt 约束名称称 primmary kkey (列列名1，列名名2 ，.）；SQL allter ttable exampple2 ddrop pprimarry keyy;Table aaltereedSQL allter ttable exampple2 aadd coonstraaint ppk_exaample primaary keey (idd);再看主键约束：SQL seelect tablee_namee,consstrainnt_namme,connstraiint_tyype,sttatus2 frrom usser_coonstraaints wheree tablle_namme=EXXAMPLEE2;TABLE_NNAME CONSTTRAINTT_NAMEE CONSSTRAINNT_TYPPE STAATUS- - - -EXAMPLEE2 PK_EXXAMPLEE P ENABLLED4.修改主键约约束：4.1 禁用/启用主键：alter tablee 表名 ddisablle/enaable pprimarry keyy;注：禁用后可插插入违法数据据，但有违法法数据时无法法启用主键，将将违法数据修修改才可以。SQL allter ttable exampple2 ddisablle priimary key;Table aaltereedSQL allter ttable exampple2 eenablee primmary kkey;Table aaltereed4.2 重命名名主键：语法：alteer tabble 表名名 renaame coonstraaint 原原主键名称 to 新主主键名称4.3 主键与与索引：当oarclee创建主键时时，会自动创创建一个与主主键同名的索索引。索引并并不会随着主主键的删除而而删除，也不不会为重命名名而改变。查看信息：SQL seelect indexx_namee,tablle_namme,collumn_nname2 frrom usser_innd_collumns3 whhere iindex_name=PK_EEXAMPLLE;INDEX_NNAME TABLEE_NAMEE COLUMMN_NAMME- - -PK_EXAMMPLE EXAMPPLE2 IDoracle笔笔记-外键约约束(2012-01-066 09:447:02)转载标签：外键约束级联删除参照完整性级联更新杂谈分类：DattaBasee一、外键约束简简介：外键的主要作用用是保证数据据的参照完整整性，主表中中有数据时，才才可以在从表表上插入相应应的数据。外外键支持的列列，必须是主主键列，以便便获得唯一记记录。1.1 创建外外键约束：语法：alteer tabble 表名名 add consttraintt 约束名 foreiign keey (外键键列名）reeferennces 主主表 （主表表主键列）；如：先创建custtomer表表：SQL crreate tablee custtomer (2 ccustommer_idd numbber prrimaryy key,3 ccustommer_naame vaarcharr2(20),4 ccustommer_phhone vvarchaar2(200),5 ccustommer_adddresss varcchar2(50)6 );Table ccreateed再创建purcchase_orderr表：SQL crreate tablee purcchase_orderr(2 orderr_id nnumberr primmary kkey,3 produuct_naame vaarcharr2(20),4 produuct_quuantitty nummber,5 custoomer_iid nummber6 );Table ccreateed添加外键：SQL allter ttable purchhase_oorder2 aadd coonstraaint ffk_purrchasee_ordeer3 fforeiggn keyy (cusstomerr_id)4 rrefereences custoomer(ccustommer_idd);Table aaltereed查看外键约束信信息：SQL seelect tablee_namee ,connstraiint_naame,coonstraaint_ttype,rr_consstrainnt_namme2 ffrom uuser_cconstrraintss3 wwhere tablee_namee=PURRCHASEE_ORDEER;TABLE_NNAME CONSTTRAINTT_NAMEE CONSSTRAINNT_TYPPE R_CCONSTRRAINT_NAME- - - -PURCHASSE_ORDDER SYS_CC0055001 P PURCHASSE_ORDDER FK_PUURCHASSE_ORDDER R SYS_CC0055000二、级联更新与与级联删除：为了保护数据的的参照完整性性提供2种校校验类型即时校验（iimmediiate) 和 延迟校校验（defferredd），默认为为即时校验。延延迟校验是当当整个事务结结束时（coommit），才才验证数据的的完整性。1.级联更新：语法：alter ttable 表名 addd conns