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;

版權聲明：本文為博主原創文章，未經博主允許不得轉載。