oracle主表和外表的关系,Oracle数据库中，晓得一张表，查询与其有主外键关系的表...

Oracle数据库中，知道一张表，查询与其有主外键关系的表

--查外键表NC56.CSCI有无对应的主键表

SELECT a.owner,

A.TABLE_NAME primary_table_name,

A.CONSTRAINT_NAME primary_table_key_name,

b.owner,

B.TABLE_NAME foreign_table_name,

B.CONSTRAINT_NAME foreign_table_foreign_key_name,

B.STATUS foreign_table_foreign_key_stat

FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B

WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME

and B.CONSTRAINT_TYPE = 'R'

AND b.TABLE_NAME = 'CSCI' and a.owner='NC56' and b.owner='NC56'

ORDER BY 1, 2, 3, 4;--查主键表NC56.CBH有无对应的外键表

SELECT a.owner,

A.TABLE_NAME primary_table_name,

A.CONSTRAINT_NAME primary_table_key_name,

b.owner,

B.TABLE_NAME foreign_table,

B.CONSTRAINT_NAME foreign_table_foreign_key_name,

B.STATUS foreign_table_foreign_key_stat

FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B

WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME

and B.CONSTRAINT_TYPE = 'R'

AND a.TABLE_NAME = 'CBH' and a.owner='NC56' and b.owner='NC56'

ORDER BY 1, 2, 3, 4;

版权声明：本文为博主原创文章，未经博主允许不得转载。