求主外键的关系的脚本(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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271283/viewspace-987742/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271283/viewspace-987742/