在日常數(shù)據(jù)維護(hù)中，經(jīng)常刪除數(shù)據(jù)，要是這些數(shù)據(jù)所在的表有外鍵關(guān)聯(lián)，又不設(shè)置成級(jí)聯(lián)刪除的話，就需要先清空子表的相關(guān)數(shù)據(jù)了。要找出所有的主外鍵的關(guān)聯(lián)是一個(gè)比較頭疼的事情，下面給出一個(gè)例子，可以得到某用戶下的所有主外鍵關(guān)系：
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
不過這個(gè)只適用于沒有組合主外鍵的情況，如果庫(kù)中包含組合的主外鍵關(guān)系，則需要先建立一個(gè)函數(shù)
--構(gòu)造函數(shù)返回組合主鍵和外鍵對(duì)應(yīng)的列
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;
--包含組合主鍵與組合外鍵的聯(lián)系（包含普通主外鍵的聯(lián)系）
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)
轉(zhuǎn)自：http://space6212.itpub.net/post/12157/107584