Oracle 主键、外键查询

select a.table_name 外键表名,a.column_name 外键列名,b.table_name 主键表名,b.column_name 主键列名

from

(select a.constraint_name,b.table_name,b.column_name,a.r_constraint_name

from user_constraints a, user_cons_columns b

WHERE a.constraint_type='R'

and a.constraint_name=b.constraint_name

) a,

(select distinct a.r_constraint_name,b.table_name,b.column_name

from user_constraints a, user_cons_columns b

WHERE a.constraint_type='R'

and

a.r_constraint_name=b.constraint_name)

b

where a.r_constraint_name=b.r_constraint_name

查询所有主键

select distinct a.r_constraint_name,b.table_name,b.column_name

from user_constraints a, user_cons_columns b

WHERE a.constraint_type='R'

and

a.r_constraint_name=b.constraint_name

   

查询某个表的主键

select distinct a.r_constraint_name,b.table_name,b.column_name

from user_constraints a, user_cons_columns b

WHERE a.constraint_type='R'

and

a.r_constraint_name=b.constraint_name and b.table_name = 'Sh';

   

查询所有外键

select a.constraint_name,b.table_name,b.column_name,a.r_constraint_name

from user_constraints a, user_cons_columns b

WHERE a.constraint_type='R'

and a.constraint_name=b.constraint_name

   

查询某个表的所有外键

select a.constraint_name 外键名,a.table_name 外键表名,a.column_name 外键列名,b.table_name 主键表名,b.column_name 主键列名

from

(select a.constraint_name,b.table_name,b.column_name,a.r_constraint_name

from user_constraints a, user_cons_columns b

WHERE a.constraint_type='R'

and a.constraint_name=b.constraint_name

) a,

(select distinct a.r_constraint_name,b.table_name,b.column_name

from user_constraints a, user_cons_columns b

WHERE a.constraint_type='R'

and

a.r_constraint_name=b.constraint_name and b.table_name = 'Evt')

b

where a.r_constraint_name=b.r_constraint_name