posts - 0,  comments - 3,  trackbacks - 0
desc  后发现 
 user_constraints是表约束的视图,描述的是约束类型(constraint_type)是什么,属于哪些表(table_name),如果约束的类型为R(外键)的话,那么r_constraint_name字段存放的就是被引用主表中的主键约束名。   
 user_cons_columns是表约束字段的视图,说明表中的和约束相关的列参与了哪些约束。这些约束有主键约束,外键约束,索引约束.
  两者可以通过(owner,constraint_name,table_name)关联

select
a.owner 外键拥有者,
a.table_name 外键表,
substr(c.column_name,1,127) 外键列,
b.owner 主键拥有者,
b.table_name 主键表,
substr(d.column_name,1,127) 主键列
from
user_constraints a,
user_constraints b,
user_cons_columns c,
user_cons_columns d
where
    a.r_constraint_name=b.constraint_name
and a.constraint_type='R'
and b.constraint_type='P'
and a.r_owner=b.owner
and a.constraint_name=c.constraint_name
and b.constraint_name=d.constraint_name
and a.owner=c.owner
and a.table_name=c.table_name
and b.owner=d.owner
and b.table_name=d.table_name

数据字典表列说明:

desc user_constraints

Name                                                                                   Comments                                                                   
-----------------                ---------------------------------------------------------------------------
OWNER                                                                   Owner of the table                                                         
CONSTRAINT_NAME                                             Name associated with constraint definition                                 
CONSTRAINT_TYPE                                              Type of constraint definition                                              
TABLE_NAME                                                          Name associated with table with constraint definition                      
SEARCH_CONDITION                                             Text of search condition for table check                                   
R_OWNER                                                                 Owner of table used in referential constraint                              
R_CONSTRAINT_NAME                                          Name of unique constraint definition for referenced table                  
DELETE_RULE                                                          The delete rule for a referential constraint                               
STATUS                                                                      Enforcement status of constraint -  ENABLED or DISABLED                    
DEFERRABLE                                                           Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE                
DEFERRED                                                                 Is the constraint deferred by default -  DEFERRED or IMMEDIATE             
VALIDATED                                                       Was this constraint system validated? -  VALIDATED or NOT VALIDATED        
GENERATED                                         Was the constraint name system generated? -  GENERATED NAME or USER NAME   
BAD                                                                        Creating this constraint should give ORA-02436.  Rewrite it before 2000 AD.
RELY                                                                                       If set, this flag will be used in optimizer                                
LAST_CHANGE                                                               The date when this column was last enabled or disabled                     
INDEX_OWNER                                                                The owner of the index used by the constraint                              
INDEX_NAME                                                                    The index used by the constraint                                           
INVALID                                                                                         
VIEW_RELATED     
desc user_cons_columns;


Name                                                                                Comments                                                                                        
--------------- -------------- -------- ------- ------------------------------------------------------------------------------------------------
OWNER                                                                         Owner of the constraint definition                                                              
CONSTRAINT_NAME                                               Name associated with the constraint definition                                                  
TABLE_NAME                                                        Name associated with table with constraint definition                                           
COLUMN_NAME                    Name associated with column or attribute of object column specified in the constraint definition
POSITION                                                                      Original position of column or attribute in definition  

 

posted on 2009-07-30 16:53 月光记忆 阅读(10008) 评论(1)  编辑  收藏 所属分类: ORCLE

FeedBack:
# re: user_constraints 和user_cons_columns表得作用及其联系
2009-12-02 09:12 | zmz2008
太谢谢你了!正在弄与“关系”相关的项目!你的文章太及时了!  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航:
 
<2009年12月>
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

留言簿(2)

随笔分类

文章分类

文章档案

搜索

  •  

最新评论