Home » RDBMS Server » Server Administration » need to know in how many table this type of relation exists.
need to know in how many table this type of relation exists. [message #273667] Thu, 11 October 2007 04:51 Go to next message
novice1
Messages: 15
Registered: September 2007
Junior Member
I am having some 60 tables.and we have created some primary key(PK) and foreign key relation ships(FK).

for example there are two table A and B. Table A contains a primary key and is referencing to table B. and Table B contains a primary key and is referncing to table A. my aim is to find how many such table exists with that type of relation in our database.

because i cannot insert or retrive data from child table with having data in master table.

below is the query i have generated..
Pls check the querry and advice any changes that need to be made.

<Code>
select xx.constraint_name,xx.tab1,yy.tab2 from
(select a.constraint_name,a.constraint_type,
a.table_name tab1,b.TABLE_NAME tab2 from
USER_CONSTRAINTS A, USER_CONSTRAINTS B
WHERE
(A.CONSTRAINT_NAME=B.R_CONSTRAINT_NAME
and a.constraint_type in ('P') )) xx,
(select a.constraint_name,a.constraint_type,
a.table_name tab1,b.TABLE_NAME tab2 from
USER_CONSTRAINTS A, USER_CONSTRAINTS B
WHERE
(A.CONSTRAINT_NAME=B.R_CONSTRAINT_NAME and a.constraint_type in ('P') )) yy
where xx.tab1=yy.tab2 and yy.tab1=xx.tab2
order by xx.tab1
</code>

I am using oracle 10g database and my OS is unix.

[Updated on: Thu, 11 October 2007 05:11]

Report message to a moderator

Re: need to know in how many table this type of relation exists. [message #273669 is a reply to message #273667] Thu, 11 October 2007 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: need to know in how many table this type of relation exists. [message #273787 is a reply to message #273667] Thu, 11 October 2007 12:25 Go to previous message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member
Use view ALL_CONSTRAINTS with proper CONSTRAINT_TYPE and ALL_CONS_COLUMNS ...check page 2-27 Oracle 9i Database Reference

[Edit MC: url to poster blog removed. We don't care about your blog in this post. If you have an answer on your site, post a link to this answer. If you don't, post your blog url in Marketplace]

May we let people make his own decisions about what they care about; my blog doesn't have harmful or ofensive content, is not commercial directly or indirectly, therefore will be missplaced at marketplace, don't you think?

Is there an explicit writen policy regarding this?

[MC] You promote yourself, this is marketing.

[Updated on: Fri, 12 October 2007 00:01] by Moderator

Report message to a moderator

Previous Topic: ORA-03297: Problem
Next Topic: ora-12560:TNS: protocol adapter error
Goto Forum:
  


Current Time: Thu Dec 08 02:29:53 CST 2016

Total time taken to generate the page: 0.14251 seconds