oracle constraints(3)
oracle constraints Ó¦ÓÃ
oracle constraints¿ÉÒÔÉèÖÃΪÁ¢¼´¼ì²é»òÕßµ±Ê±ÊÂÎñÌύʱ¼ì²é¡£
¿ÉÒÔÔÚ´´½¨Ô¼ÊøµÄʱºòÖ¸¶¨ÊÇdeferrable¡£È»ºóͨ¹ýset constraints xxx set deferred»òÕßimmediate,Ò²¿ÉÒÔÔÚseesion¼¶±ðÉ趨ËùÓÐÔ¼ÊøÎªdeferred»òÕßimmediate(alter seesion set constraints deferred/immediate)¡£
SQL> select t.constraint_name, t.status, t.validated, t.deferrable from user_constraints t;
CONSTRAINT_NAME STATUS VALIDATED DEFERRABLE
------------------------------ -------- ------------- --------------
SYS_C003765 ENABLED VALIDATED NOT DEFERRABLE
SYS_C003768 ENABLED NOT VALIDATED NOT DEFERRABLE
UK_T ENABLED NOT VALIDATED NOT DEFERRABLE
SQL> alter table t2 add primary key(vid) deferrable;
Table altered
SQL>
SQL> select t.constraint_name, t.status, t.validated, t.deferrable from user_constraints t;
CONSTRAINT_NAME STATUS VALIDATED DEFERRABLE
------------------------------ -------- ------------- --------------
SYS_C003765 ENABLED VALIDATED NOT DEFERRABLE
SYS_C003772 ENABLED VALIDATED DEFERRABLE
SYS_C003768 ENABLED NOT VALIDATED NOT DEFERRABLE
UK_T ENABLED NOT VALIDATED NOT DEFERRABLE
SQL> select * from t2;
VID VNAME VSEX
---------- ---------- ----------
1 a y
2 b
3 c x
SQL> insert into t2 values (1,'d','y');
insert into t2 values (1,'d','y')
ORA-00001: Î¥·´Î¨Ò»Ô¼ÊøÌõ¼þ (PORTALDB.SYS_C003772)
SQL> set constraints SYS_C003772 deferred;
Constraints set
SQL> insert into t2 values (1,'d','y');
1 row inserted
SQL> set constraints SYS_C003772 deferred;
Constraints set
SQL> commit;
commit
ORA-02091: ÊÂÎñ´¦ÀíÒÑÖØËã
ORA-00001: Î¥·´Î¨Ò»Ô¼ÊøÌõ¼þ (PORTALDB
Ïà¹ØÎĵµ£º
ÔÚLinuxÉϰ²×°oracleµÄʱºò²»Ð¡ÐݲװÁËÁ½´Îlistener, ¸ãµÃlistenerµÄ¶Ë¿ÚºÅ±ä³ÉÁË1522¶ø²»ÊÇȱʡµÄ1521, ¿Í»§¶ËÁ¬Á˺þö¼Ã»ÓÐÁ¬½ÓÉÏ£¬×îºó²Å·¢ÏÖÊÇlistenerµÄ¶Ë¿ÚºÅ²»¶Ô¡£Ò»ÏÂÊÇÎҸıälistener¶Ë¿ÚºÅµÄ²½Ö裺
1. Ê×ÏÈÐèҪֹͣlistener, ʹÓÃÃüÁîlsnrctl stop
2. listenerÍ£Ö¹ÒԺ󣬵½ÄãµÄ$ORACLE_HOME/network/adminÏÂÕ ......
µÈ´ýʼþµÄÔ´Æð
µÈ´ýʼþµÄ¸ÅÄî´ó¸ÅÊÇ´ÓORACLE 7.0.12ÖÐÒýÈëµÄ£¬´óÖÂÓÐ100¸öµÈ´ýʼþ¡£ÔÚORACLE 8.0ÖÐÕâ¸öÊýÄ¿Ôö´óµ½ÁË´óÔ¼150¸ö£¬ÔÚORACLE 8IÖдóÔ¼ÓÐ220¸öʼþ£¬ÔÚORACLE 9IR2ÖдóÔ¼ÓÐ400¸öµÈ´ýʼþ£¬¶øÔÚ×î½üORACLE 10GR2ÖУ¬´óÔ¼ÓÐ874¸öµÈ´ýʼþ¡£
ËäÈ»²»Í¬°æ±¾ºÍ×é¼þ°²×°¿ÉÄÜ»áÓв»Í¬ÊýÄ¿µÄµÈ´ýʼþ£¬µ«ÊÇÕâЩµÈ´ýÊ ......
ORACLEÖÐÊý¾Ý×ÖµäÊÓͼ·ÖΪ3´óÀà, ÓÃÇ°×ºÇø±ð£¬·Ö±ðΪ£ºUSER£¬ALL ºÍ DBA£¬Ðí¶àÊý¾Ý×ÖµäÊÓͼ°üº¬ÏàËÆµÄÐÅÏ¢¡£
USER_*:ÓйØÓû§ËùÓµÓеĶÔÏóÐÅÏ¢£¬¼´Óû§×Ô¼º´´½¨µÄ¶ÔÏóÐÅÏ¢
ALL_*£ºÓйØÓû§¿ÉÒÔ·ÃÎʵĶÔÏóµÄÐÅÏ¢£¬¼´Óû§×Ô¼º´´½¨µÄ¶ÔÏóµÄÐÅÏ¢¼ÓÉÏÆäËûÓû§´´½¨µÄ¶ÔÏ󵫸ÃÓû§ÓÐȨ·ÃÎʵÄÐÅÏ¢
DBA_* ......
ÎÒÃÇÒª×öµ½²»µ«»áдSQL,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQL,ÒÔÏÂΪ±ÊÕßѧϰ¡¢ÕªÂ¼¡¢²¢»ã×ܲ¿·Ö×ÊÁÏÓë´ó¼Ò·ÖÏí£¡
£¨1£©Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
orACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ, ......
oracle Ô¼ÊøµÄ״̬
oracleÔÚ´´½¨Ô¼ÊøºóĬÈÏ״̬ÊÇenabled VALIDATED
SQL> create table T2
2 (
3 VID NUMBER,
4 VNAME VARCHAR2(10) not null,
5 VSEX VARCHAR2(10) not null
6 )
7 /
Table created
SQL> alter table t2 add constraints PK_T primary key (vid); ......