Oracle 查詢外鍵相依階層 find foreign key dependency hierarchy

Oracle 查詢資料表間外鍵相依的階層方法如下。

例如資料庫有兩資料表DEPARTMENT及EMPLOYEE。外鍵EMPLOYEE.DEPARTMENT_ID參照至DEPARTMENT.ID。

使用下面sql查詢DEPARTMENT及EMPLOYEE的foreign key dependency階層。透過查詢USER_CONSTRAINTS取得目前使用者的外鍵依賴資訊，然後利用Hierarchical Queries查詢被參照與參照資料表間的循環階層。

select t.*, LEVEL from (
    select
        a.TABLE_NAME "REF_TABLE", -- 被參照的表
        b.TABLE_NAME "REF_BY" -- 參照的表
    from USER_CONSTRAINTS a, USER_CONSTRAINTS b 
    where b.CONSTRAINT_TYPE = 'R'
    and a.CONSTRAINT_NAME = b.R_CONSTRAINT_NAME
    and a.TABLE_NAME in ('EMPLOYEE', 'DEPARTMENT') -- 要查詢的資料表名稱
) t
CONNECT BY PRIOR REF_TABLE = REF_BY;

查詢結果如下：

+------------+----------+-------+
| REF_TABLE  | REF_BY   | LEVEL |
+------------+----------+-------+
| DEPARTMENT | EMPLOYEE | 1     |
+------------+----------+-------+

在執行多筆資料的insert into敘述時可能因為外鍵限制而無法正確新增資料，此時就可利用以上查出資料表的外鍵依賴階層順序並依此順序新增。

• Oracle 查詢外鍵依賴 find foreign key constraints
• USER_CONSTRAINTS