| need to know in how many table this type of relation exists. [message #273667] |
Thu, 11 October 2007 04:51  |
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 #273787 is a reply to message #273667] |
Thu, 11 October 2007 12:25  |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
