在日常數據維護中，經常刪除數據，要是這些數據所在的表有外鍵關聯，又不設置成級聯刪除的話，就需要先清空子表的相關數據了。要找出所有的主外鍵的關聯是一個比較頭疼的事情，下面給出一個例子，可以得到某用戶下的所有主外鍵關系：
select
rpad(pk.pk_con,25,'.')||pk_table||'('||pk_col||')' pk_info ,
rpad(fk.fk_con,35,'.')||fk_table||'('||fk_col||')' fk_info
from
(select
a.constraint_name pk_con,
a.table_name pk_table,b.column_name pk_col,
a.owner pk_owner
from user_constraints a,user_cons_columns b
where (a.constraint_type='P' or a.constraint_type='U')
and a.constraint_name=b.constraint_name
and a.owner=b.owner) pk,
(select c.constraint_name fk_con,
c.table_name fk_table,
d.column_name fk_col,
c.R_OWNER r_pk_owner,
c.R_CONSTRAINT_NAME r_pk_con,
c.owner fk_owner
from user_constraints c,user_cons_columns d
where c.constraint_type='R'
and c.constraint_name=d.constraint_name
and c.owner=d.owner) fk
where pk.pk_owner=fk.r_pk_owner
and pk.pk_con=fk.r_pk_con
order by pk.pk_con
;
PK_INFO FK_INFO
------------------------------------------------------- --------------------------------------------------------------------
PK_CATEGORY..............CATEGORY(CATEGORY_ID) FK_TAG_GROU_TAG_GROUP_CATEGORY.....TAG_GROUP(CATEGORY_ID)
PK_CHANNEL...............CHANNEL(CHANNEL_ID) FK_CATEGORY_CATEGORY__CHANNEL......CATEGORY(CHANNEL_ID)
PK_CLOB_CONTENT..........CLOB_CONTENT(CLOB_CONTENT_ID) FK_ITEM_ITEM_REF__CLOB_CON.........ITEM(INFO_CONTENT_ID)
PK_ITEM..................ITEM(ITEM_ID) FK_ITEM_TAG_ITEM_TAG__ITEM.........ITEM_TAG(ITEM_ID)
PK_TAG...................TAG(TAG_ID) FK_CATEGORY_REF_TAG................CATEGORY(CATEGORYS_SELF_TAG_ID)
PK_TAG...................TAG(TAG_ID) FK_ITEM_TAG_ITEM_TAG__TAG..........ITEM_TAG(TAG_ID)
PK_TAG...................TAG(TAG_ID) FK_SELLER_T_SELLER_TA_TAG..........SELLER_TAG(TAG_ID)
PK_TAG...................TAG(TAG_ID) FK_ITEM_ITEM_ITEM_TAG..............ITEM(ITEM_SELF_TAG_ID)
PK_TAG_GROUP.............TAG_GROUP(TAG_GROUP_ID) FK_TAG_TAG_REF_T_TAG_GROU..........TAG(TAG_GROUP_ID)
PK_USERS.................USERS(USER_ID) FK_FLEA_MARKET_INFO_USER_ID........FLEA_MARKET_INFO(USER_ID)
PK_USERS.................USERS(USER_ID) FK_SELLER_T_SELLER_TA_USERS........SELLER_TAG(SELLER_ID)
PK_USERS.................USERS(USER_ID) FK_REVIEWRA_REFERENCE_USERS........REVIEWRATE(USER_ID)
12 rows selected
不過這個只適用于沒有組合主外鍵的情況，如果庫中包含組合的主外鍵關系，則需要先建立一個函數
--構造函數返回組合主鍵和外鍵對應的列
CREATE OR REPLACE FUNCTION get_str(p_constraint_name varchar2)
RETURN VARCHAR2
IS
l_column_name VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT column_name,position FROM user_cons_columns
WHERE CONSTRAINT_NAME=p_constraint_name order by position) LOOP
if cur.position=1 or cur.position is null then
l_column_name := cur.column_name;
else
l_column_name := l_column_name||','||cur.column_name;
end if;
END LOOP;
RETURN l_column_name;
END;
--包含組合主鍵與組合外鍵的聯系（包含普通主外鍵的聯系）
select
rpad(fk.fk_con,35,'.')||fk_table||'('||fk_col||')' fk_info,
rpad(pk.pk_con,35,'.')||pk_table||'('||pk_col||')' pk_info
from
(select distinct
a.constraint_name pk_con,
a.table_name pk_table,
get_str(a.constraint_name) pk_col,
a.owner pk_owner
from user_constraints a,user_cons_columns b
where (a.constraint_type='P' or a.constraint_type='U')
and a.constraint_name=b.constraint_name
and a.owner=b.owner) pk,
(select distinct
c.constraint_name fk_con,
c.table_name fk_table,
get_str(c.constraint_name) fk_col,
c.R_OWNER r_pk_owner,
c.R_CONSTRAINT_NAME r_pk_con,
c.owner fk_owner
from user_constraints c,user_cons_columns d
where c.constraint_type='R'
and c.constraint_name=d.constraint_name
and c.owner=d.owner) fk
where pk.pk_owner=fk.r_pk_owner
and pk.pk_con=fk.r_pk_con
;
FK_INFO PK_INFO
-------------------------------------------------- --------------------------------------------------
FK_T2..............................T2(B) PK_T1..............................T1(A)
FK_T4..............................T4(E,F) PK_T3..............................T3(A,B)
轉自：http://space6212.itpub.net/post/12157/107584