求主外來鍵的關係的指令碼(ZT)

在日常資料維護中，經常刪除資料，要是這些資料所在的表有外來鍵關聯，又不設定成級聯刪除的話，就需要先清空子表的相關資料了。要找出所有的主外來鍵的關聯是一個比較頭疼的事情，下面給出一個例子，可以得到某使用者下的所有主外來鍵關係：[@more@]

在日常資料維護中，經常刪除資料，要是這些資料所在的表有外來鍵關聯，又不設定成級聯刪除的話，就需要先清空子表的相關資料了。要找出所有的主外來鍵的關聯是一個比較頭疼的事情，下面給出一個例子，可以得到某使用者下的所有主外來鍵關係：

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)