《oracle约束键解析6732.docx》由会员分享，可在线阅读，更多相关《oracle约束键解析6732.docx（74页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、 一、 数据库约束的作用和分类 prrimaryykey 和uniqque他们的的作用是为了了不让表中有有重复的数据据（一个表） chheck表示示表中列的数数据的范围性性的定义（一一个表） deefult表表示表中的默默认值 fooreignnkey 表表与表之间的的联系（两个个或两个以上上的表）【uniquee是可以包含含null值值的,并且一一个表中可以以有多个unnique connstraiint而prrimaryykey在一一个表中只能能有一个.并并且不能有nnull值】二、针对约束创创建有两种不不同的思想 第一一、创建标的的时候一同将将约束创建了了 第二二、你的表已已经创建好了了
2、，向表中添添加约束 三、下面我就一一一的介绍他他们 一一、 priimary key 主主键约束 两种写法 在已已经创建的表表中添加约束束 解解释：添加约约束的时候一一定要修改表表故用（allter修修改） Alteer tabble taable1 Add pprimarry keyy (列名11，列名2) 同表表一起创建 CCreatee tablle tabble1 (列名1 数据类型 是否为空空 primmary kkey , 列列名1 数数据类型 是否为空 ) 上上面的是两种种情景为一个个字段设置主主键，接下来来是以多个列列作为主键。 Crreate tablee tablle1 (
3、 列名1 数据类类型 是是否为空， 列名2 数据类型型 是否否为空， 列名3 数据类类型 是是否为空， Primmary kkey (列列名1 ，列列名2) ) 二、Chheck约束束 这个个和主键约束束差不多 Alterr tablle tabble1 Add uuniquee（列名） 三三、外键约束束 好多的的同学一直也也都不理解外外键约束，到到底什么才是是外键？主键键在那个表上上？外键在那那个表上？用用T-SQLL语句写的时时候是主表在在前，还是外外间表在前？等等一系列列的问题！ 接下来来我讲给大家家说说这个主主外键到底怎怎么弄的。 其实，主主外键之间正正如它的名字字一样，他们们之间有一
4、个个主从关系，一一个主表，一一个从表，主主表上面设置置的是主键，从从表上面设置置的是外键（fforeiggn keyy），主键表表中的数据发发生变化的时时，外键（fforeiggn keyy）表的数据据立刻就发生生改变。 向已有的表表中添加一个个主键（没有有就是添加，添添加就是修改改表） AAlter tablee tablle1 AAdd fooreignn key (列名1)referrencess tablle2（列名名2） 创建表的的时候一同创创建外键 Creatte tabble taable1 （列列名4 数据据类型 是是否为空 pprimarry keyy， 列名33 数据类型型
5、 是否为为空， 列名1 数据类型 refeerencees tabble2 （列列名2） ） 注： Tablle1外键表表 Tabble2主键键表 列名11是主表列 列名22是外键表列列 最一一般常用的是是主表的列和和外键表的列列的字段是一一样的 一般多多为ID 可可以作为主键键的。这是两两个之间的交交互。 四、CCheck约约束 他也是基基于一个表中中的约束，限限定表中列直直接爱你数据据的参照关系系。 向一个以以创建的表中中添加约束 Alterr tabeel tabble1 Addd consstrainnt 约束名名 checck（列名对对于列的限制制条件） 多多是一些可以以找到范围的的
6、数据，例如如年龄，身高高，体重。 五五、defaault 默默认约束 他是一个个对于某一个个值的约束，他他是唯一的一一个用forr关联词的句句子 向表中添添加一个默认认约束 Altter taabletaable1 Addd deffault 默认值for 列列名 六、删除除约束 删删除约束也是是对于表的修修改故： AAlter tablee tablle1 DDrop cconstrraint 约束名 注：上面就是我我所理解的一一些约束。想想要记住他们们并不难 1 开头的一般般都是 allter ttable 表名 或或是creaate taable 表表名 2 在已有的表表中添加约束束用
7、altter 3 与表同时创创建用 crreate 4 他们所有的的关键字： 主键 约约束：priimary key 唯一性约约束：uniique 外键 约束：frroeignn key.refeerencees(参照).两个个表之间的关关系，该是互互相参照 查询 约约束：. coonstraaint（约约束）.cheeck对字字段查询，对对字段约束 默认 约束：.defaault.foor（给）. 将前前面默认的值值 给 后边边的字段ORACLE约约束总结2012-055-13 008:36 by 潇湘湘隐者, 6682 viisits,收藏,编辑你对ORACLLE约束的了了解如何？比比较模
8、糊还是是相当透彻？如果你对下下面几个问题题了如指掌的的话，恭喜你你，你已经对对约束掌握得得比较好了，不不用看这篇文文章了。ORRACLE的的约束有啥功功能作用？ 有哪些类型型约束（不同同版本ORAACLE是否否不同）？视视图有约束吗吗？约束是否否会影响SQQL性能？ 约束信息存存储在哪些系系统视图、数数据字典中？约束能否修修改名称？能能否禁用约束束？延迟约束束有啥好处.约束定义约束是强加在表表上的规则或或条件。确保保数据库满足足业务规则。保保证数据的完完整性。当对对表进行DMML或DDLL操作时，如如果此操作会会造成表中的的数据违反约约束条件或规规则的话，系系统就会拒绝绝执行这个操操作。约束可
9、可以是列一级级别的 也可可以是表级别别的。定义约约束时没有给给出约束的名名字，ORAACE系统将将为该约束自自动生成一个个名字，其格格式为SYSS_Cn，其其中n为自然然数(强烈建建议各位在创创建表或增加加约束时，给给约束定义名名称。).约束功能约束的功能：实实现一些业务务规则，防止止无效的垃圾圾数据进入数数据库，维护护数据库的完完整性(完整整性指正确性性与一致性)。从而使数数据库的开发发和维护都更更加容易。约束分类约束分分为6类： 非空（NOOT NULLL）约束、 唯一（UNNIQUE）约约束、主键（PPRIMARRY KEYY）约束、外外键（FORREIGN KEY）约约束、条件（CCH
10、ECK）约约束、REFF约束。非空（NOT NULL）约约束：顾名思思义，所约束束的列不能为为NULL值值。否则就会会报错唯一（UNIQQUE）约束束：在表中每每一行中所定定义的这列或或这些列的值值都不能相同同。必须保证证唯一性。否否则就会违法法约束条件。主键（PRIMMARY KKEY）约束束：唯一的标标识表中的每每一行，不能能重复，不能能为空。 创创建主键或唯唯一约束后，OORACLEE会自动创建建一个与约束束同名的索引引（UNIQQUENESS为UNIQQUE唯一索索引）。需要要注意的是：每个表只能能有且有一个个主键约束。外键（FOREEIGN KKEY）约束束：用来维护护从表（Chhi
11、ld TTable）和和主表（Paarent Tablee）之间的引引用完整性. 外键约束束是个有争议议性的约束，它它一方面能够够维护数据库库的数据一致致性，数据的的完整性。防防止错误的垃垃圾数据入库库； 另外一一方面它会增增加表插入、更更新等SQLL性能的额外外开销，不少少系统里面通通过业务逻辑辑控制来取消消外键约束。例例如在数据仓仓库中，就推推荐禁用外键键约束。条件（CHECCK）约束：表中每行都都要满足该约约束条件。条条件约束既可可以在表一级级定义也可以以在列一级定定义。在一列列上可以定义义任意多个条条件约束。REF约束的定定义：REFF coluumn byy defiinitioon
12、 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/
13、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/BB2835
14、99_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 loogg
15、ingg对于H、S类型型，尚不清楚楚情况，而且且从上面我在在11g数据据库实验结果果看，也没发发现这两种类类型。(有了了解的朋友可可以告知一二二)约束命名规范约约束名称建议议自己定义一一套命名规则则，否则使用用系统生成的的约束名，很很难能把它和和对应的表、字字段联系起来来。非空约束 NNN_表名_列名唯唯一约束 UKK_表名_列列名主键约束 PPK_表名外键键约束 FK_表名_列名名条条件约束 CKK_表名_列列名默认约束 DDF_表名_列名如果约束名称超超过32位长长度，建议应应该缩写表名名，而不应用用NN_表名名_数字。不不过具体视情情况而定，很很多时候 DDF_表名_列名 这样样命名，往
16、往往超出了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
17、 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 T
18、BSS_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这这样的约束，如如果不仔细核核
19、对后面的字字段，很难一一眼看出约束束类型、约束束对应字段。主键约束的限制制文档原文如下：(翻译在下下)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, LONG
20、G, 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或用户户定义类
21、型。然然而它可以包包含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 combiinatioo
22、n 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.创建一个个继承层次结结构中的子视视图时，你不不能指定一个个主键。主键键
23、可以唯一指指定的顶层（根根）视图。外外键约束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：当主表中中的一行数据据被删
24、除时，OOraclee系统会自动动地将所有从从表中依赖于于它的数据记记录删除；注：如果在外键键定义中使用用了ON DDELETEE SET NULL 或ON DDELETEE CASCCADE，无无论删除操作作是在父表这这一端还是子子表这一端都都不会产生违违反引用完整整性的问题，但但是却留下了了安全隐患。这这两个子句要要谨慎使用，只只有在不得已已时使用，谨谨记谨记！外键约束对DMML与DDLL的影响：INSERT：只有操作是是在子表或从从表这一端时时才会产生违违反引用完整整性约束的问问题，父表则则不然。DELETE：只有操作是是在父表或主主表这一端时时才会产生违违反引用完整整性约束的问问题，子
25、表则则不然。UPDATE：子表父表直直接操作都会会违反引用完完整性约束。两两种解决方法法：1）先更新子表表的引用列为为空，再更新新父表的主键键的列的值，然然后把子表的的引用列更新新成新的父表表的值；2）使用ON DELETTE SETT NULLL，先更新父父表，然后将将子表外键为为空的记录更更新为新的值值。DDL语句：DDROP TTABLE与与TRUNCCATE TTABLE，操操作父表，违违反引用完整整性约束，子子表则不然。外键约束的限制制：Resttrictiions oon Forreign Key CConstrraintss Foreeign kkey coonstraaints
26、 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 WWIT
27、H 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 aal
28、readdy 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 mmu
29、st 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 con
30、sttraintt 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语语句
31、。加上一些类似其其它约束的限限制条件约束非空约束非空约束的限制制：1： 你你不能在视图图约束上指定定字段为NUULL或NOOT NULLL值2： 你不能指定定一个对象的的属性为NUULL或NOOT NULLL。相反，而而应使用的IIS NOOTNULL条条件CHECCK约束条件件约束中的条条件与查询语语句中的条件件相同，但是是不能包括以以下内容：11）CURRRVAL,NNETXTVVAL,LEEVEL和RROWNUMM这样的伪列列（PSEUUDOCOLLUMNS）；2）引用其其它行中值的的查询语句；3）SYSSDATE,USER,USEREENV和UIID的函数调调用。文档原原文如下：RRe
32、striictionns 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
33、 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
34、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 ke
35、yy 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
36、 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_M
37、GRR NOT NULL);添加其他约束ALTER TTABLE DM.EMMPADD CONNSTRAIINT CKK_EMP_SAL CCHECK(SAL 0 AND SAAL 200000);重命名约束束ALTER TTABLE METAOONE.T11RENAME CONSTTRAINTT SYS_C00599699 TTO PK_T1约束维护禁禁用约束ALTER TTABLE 表 DISSABLE CONSTTRAINTT 约束名 CASCCADE;ALTER TTABLE DM.EMMPDISABLEE CONSSTRAINNT UK_EMP_CCOMM;ALTER TTABLE
38、TEST DISABBLE PRRIMARYY KEY CASCAADE;参数CASCAADE子句用用来关闭存在在有完整性关关系的约束，比比如DISAABLE一个个主键，如果果没有CASSCADE子子句就会出错错，此时使用用CASCAADE子句DDISABLLE主键可以以将该主键与与相关外键一一起DISAABLE掉。使使用的情况：例如在数据据库系统中大大规模装入数数据时，为了了系统的效率率您不得不牺牺牲数据的一一致性来关闭闭一些约束，甚甚至删除一些些约束将主表表主键禁止的的同时，也将将禁止依赖于于此主键的外外键禁止了使用上面脚本禁禁用约束的时时候会删除索索引，如果你你想禁用约束束的时候，保保留
39、索引，可可以使用如下下脚本(当然然你再启用约约束的时候会会重新重建索索引，这对大大表显然开销销相当大)ALTER TTABLE DM.EMMPDISABLEE CONSSTRAINNT UK_EMP_CCOMM KKEEP IINDEX;启用约束如果关关闭主键约束束时使用了CCASCADDE，此时使使用ENABBLE不会将将主键约束与与外键约束同同时打开，只只能对主键约约束与外键约约束分别使用用ENABLLE CONNSTRAIINT 子句句打开。推迟约束Defferrinng Connstraiint Chhecks当前事务的Coonstraaint CCheckss全部推迟SSET COO
40、NSTRAAINTS ALL DDEFERRRED;SEET CONNSTRAIINT XXXX DEFFERREDD;当前会话的Coonstraaint CCheckssALTEER SSSSSION SET CCONSTRRAINTSS ALL deferrred;AALTER SSSSIION SEET CONNSTRAIINT xxxx defferredd;约束信息SELECT * FROM DDBA_COONSTRAAINTS;SELECT * FROM AALL_COONSTRAAINTS;SELECT * FROM UUSER_CCONSTRRAINTSS;SELECT * FROM DDBA_COONS_COOLUMNSS;SELECT * FROM AALL_COONS_COOLUMNSS;SELECT * FROM UUSER_CCONS_CCOLUMNNS;约束有四种状态态：ENABBLE、VAALID、IINVALIID、DISSABLE约束的检查机制制IMMEDDIATE语语句级：Orracle perfoorms iits