萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 判斷表被多少張表參考(外鍵關聯)的 SQL 查詢方法

前言

在資料庫設計變更、刪除父表資料、影響分析時,常需快速查詢「有多少張表透過外鍵參考某張父表」。Oracle 透過 USER_CONSTRAINTS 與 USER_CONS_COLUMNS 字典視圖即可輕鬆查詢。本文提供兩步驟標準查詢法,並延伸至 DBA 視圖跨 Schema 查詢、停用/啟用約束腳本生成。

一、標準兩步驟查詢法(當前使用者 Schema)

步驟 1:查詢父表的主鍵/唯一約束名稱

SELECT constraint_name, constraint_type, table_name, status
FROM user_constraints
WHERE table_name = 'PARENT_TABLE_NAME'  -- 大寫表名
  AND constraint_type IN ('P', 'U');    -- P=Primary Key, U=Unique

記錄輸出的 CONSTRAINT_NAME(如 PK_PARENT_TABLE)。

步驟 2:查詢所有參考該約束的子表與欄位

SELECT b.table_name AS child_table,
       b.column_name AS child_column,
       a.constraint_name AS fk_constraint_name,
       a.status AS fk_status,
       a.delete_rule  -- CASCADE/SET NULL/NO ACTION
FROM user_constraints a
JOIN user_cons_columns b ON a.constraint_name = b.constraint_name
WHERE a.r_constraint_name = 'PK_PARENT_TABLE'  -- 步驟1得到的父表約束名
  AND a.constraint_type = 'R';                 -- R = Referential (Foreign Key)

輸出範例

CHILD_TABLE   CHILD_COLUMN   FK_CONSTRAINT_NAME   FK_STATUS   DELETE_RULE
------------  -------------  -------------------  ----------  -----------
ORDERS        CUST_ID        FK_ORDERS_CUST       ENABLED     NO ACTION
ORDER_ITEMS   CUST_ID        FK_ITEMS_CUST        ENABLED     CASCADE
SHIPMENTS     CUSTOMER_ID    FK_SHIP_CUST         DISABLED    SET NULL

二、進階:跨 Schema / DBA 視角查詢

若需掃描全資料庫或指定 Schema,改用 DBA_ 或 ALL_ 視圖:

-- 1. 找父表約束 (指定 OWNER)
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE owner = 'PARENT_SCHEMA'
  AND table_name = 'PARENT_TABLE'
  AND constraint_type IN ('P','U');

-- 2. 找所有參考該約束的子表 (跨 Schema)
SELECT a.owner AS child_owner,
       a.table_name AS child_table,
       b.column_name AS child_column,
       a.constraint_name AS fk_name,
       a.status,
       a.delete_rule
FROM dba_constraints a
JOIN dba_cons_columns b ON a.owner = b.owner AND a.constraint_name = b.constraint_name
WHERE a.r_owner = 'PARENT_SCHEMA'
  AND a.r_constraint_name = 'PK_PARENT_TABLE'
  AND a.constraint_type = 'R'
ORDER BY a.owner, a.table_name;

三、實務延伸:生成停用/啟用外鍵腳本

大量刪除父表資料前,常需暫時停用子表外鍵:

3.1 生成停用腳本

SELECT 'ALTER TABLE ' || a.owner || '.' || a.table_name || 
       ' DISABLE CONSTRAINT ' || a.constraint_name || ';'
FROM dba_constraints a
WHERE a.r_owner = 'PARENT_SCHEMA'
  AND a.r_constraint_name = 'PK_PARENT_TABLE'
  AND a.constraint_type = 'R'
  AND a.status = 'ENABLED';

3.2 生成啟用腳本(含驗證選項)

-- 僅啟用不驗證既有資料 (快速,適合確信資料乾淨)
SELECT 'ALTER TABLE ' || a.owner || '.' || a.table_name || 
       ' ENABLE NOVALIDATE CONSTRAINT ' || a.constraint_name || ';'
FROM dba_constraints a
WHERE a.r_owner = 'PARENT_SCHEMA'
  AND a.r_constraint_name = 'PK_PARENT_TABLE'
  AND a.constraint_type = 'R';

-- 完整啟用並驗證 (預設,較慢)
SELECT 'ALTER TABLE ' || a.owner || '.' || a.table_name || 
       ' ENABLE VALIDATE CONSTRAINT ' || a.constraint_name || ';'
FROM dba_constraints a
WHERE a.r_owner = 'PARENT_SCHEMA'
  AND a.r_constraint_name = 'PK_PARENT_TABLE'
  AND a.constraint_type = 'R';

四、常見問題與注意事項

問題說明
約束名稱不固定Oracle 自動產生的約束名稱 (SYS_Cxxxxx) 難以辨識,建議命名規範:PK_表名、FK_子表_父表
複合主鍵/外鍵查詢 USER_CONS_COLUMNS 會列出所有構成欄位,按 POSITION 排序
停用約束後資料異動停用期間插入違反 FK 的資料,重新啟用 VALIDATE 會失敗,需先清理
CASCADE 刪除遞迴若子表又被孫表參考且均為 CASCADE,刪除父表會遞迴刪除整棵樹,需謹慎評估資料量

五、結語

掌握 USER_CONSTRAINTS + USER_CONS_COLUMNS 兩步驟查詢法,能快速掌握表之間的參考關係。配合動態生成 DDL 腳本,可安全執行大量資料變更、結構調整或影響分析。建議將此查詢封裝為 PL/SQL 程序或 Shell 腳本,納入變更管理 SOP。

copyright © 萬盛學電腦網 all rights reserved