查看表中有哪些完整性约束（user_constraints 和 user_cons_columns）

完整性约束
　　DBA_CONSTRAINTS、ALL_CONSTRAINTS和USER_CONSTRAINST  显示有关约束的一般信息。
　　DBA_CONS_COLUMNS、ALL_CONS_COLUMNS和USER_CONS_COLUMNS 显示有关列的相关约束的一般信息。

 

ALL_CONS_COLUMNS 视图和DBA_CONS_COLUMNS 视图与USER_CONS_COLUMNS有相同的列定义。

ALL_CONS_COLUMNS 视图能够显示用户可以访问的所有表上约束的列信息，而不管所有者是谁。
DBA_CONS_COLUMNS 视图列出了整个数据库的列级约束信息。
USER_CONS_COLUMNS

 

user_constraints 和 user_cons_columns表得作用及其联系

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