user_constraints,user_cons_columns

Oracle 查看一个表对应的主键和外键的约束关系，查看的语句：

 

select a.owner 主键拥有者

         ,a.table_name 主键表

        ,b.column_name 主键列

        ,C.OWNER 外键拥有者

        ,c.table_name 外键表

       ,d.column_name 外键列
from user_constraints  a
left join user_cons_columns b

            on  a.constraint_name=b.constraint_name
left join user_constraints C

            ON C.R_CONSTRAINT_NAME=a.constraint_name
left join user_cons_columns d

            on c.constraint_name=d.constraint_name
where  a.constraint_type='P'

    and  a.table_name='XXX' --需要查看主外键关系的表
order by a.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	表的所有者
CONSTRAINT_NAME	约束名
CONSTRAINT_TYPE	约束类型
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 USERNAME
BAD	Creating this constraint should give ORA-02436.  Rewrite it before2000 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	Default 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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31349667/viewspace-2125066/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31349667/viewspace-2125066/