oracle数据库设置主外键关系,【学习笔记】Oracle主外键 查询数据库表的主键列与外键列的详细信息关系...

天萃荷净

分享一篇Oracle数据表主键与外键查询语句，含：查询Oracle主键、查询Oracle外键、查询Oracle主键与外键对应关系

一、查询表主键列

select column_name

from dba_cons_columns

where constraint_name in (select constraint_name

from dba_constraints

where table_name = upper('tablename')

and constraint_type = 'P');

二、查询表外键列

select column_name

from dba_cons_columns

where constraint_name in (select constraint_name

from dba_constraints

where table_name = upper('tablename')

and constraint_type = 'R');

三、查询表中列

select column_name

from dba_tab_columns

where table_name = upper('tablename');

四、查询表之间的主外键关系

select b.table_name 主键表名,

b.column_name 主键列名,

a.table_name 外键表名,

a.column_name 外键列名

from (select a.constraint_name,

b.table_name,

b.column_name,

a.r_constraint_name

from dba_constraints a, dba_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 dba_constraints a, dba_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;

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle主外键 查询数据库表的主键列与外键列的详细信息关系