oracle 主外键数量怎么查_查看oracle数据库表的主外键的关系【转】

在日常数据维护中，经常删除数据，要是这些数据所在的表有外键关联，又不设置成级联删除的话，就需要先清空子表的相关数据了。要找出所有的主外键的关联是一个比较头疼的事情，下面给出一个例子，可以得到某用户下的所有主外键关系：

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

posted on 2009-03-09 15:44 chenlh 阅读(806) 评论(0)  编辑  收藏