在資料庫設計變更、刪除父表資料、影響分析時,常需快速查詢「有多少張表透過外鍵參考某張父表」。Oracle 透過 USER_CONSTRAINTS 與 USER_CONS_COLUMNS 字典視圖即可輕鬆查詢。本文提供兩步驟標準查詢法,並延伸至 DBA 視圖跨 Schema 查詢、停用/啟用約束腳本生成。
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)。
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_ 或 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;
大量刪除父表資料前,常需暫時停用子表外鍵:
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';
-- 僅啟用不驗證既有資料 (快速,適合確信資料乾淨)
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。