|
Re: how to view the table is parent or child. and if it is child table.how to find its parent table [message #187178 is a reply to message #187173] |
Fri, 11 August 2006 04:06   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Have you actually set up a primary key on the parent table with a Foreign key constraint pointing to it on the child table.
If so, you can determine parent child relationships with a query like this:
select pa.table_name parent_table
,ch.table_name child_table
from user_constraints ch
,user_constraints pa
where pa.constraint_name = ch.r_constraint_name
and ch.constraint_type = 'R'
and pa.constraint_type in ('P','U')
ORDER by parent_table,child_table;
If you haven't got these constraints set up, then you don't have any parent and child tables. You just have two tables that happen to have the same values in some columns.
|
|
|
|
|
|
|
Re: how to view the table is parent or child. and if it is child table.how to find its parent table [message #187538 is a reply to message #187535] |
Mon, 14 August 2006 06:52   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
OK, you are a newbie, but all you were required to do was to copy-paste JRowbottom's query. If you knew how to create those tables, how come you can't re-type his query?SQL> CREATE TABLE sn(name VARCHAR2(20),rno NUMBER PRIMARY KEY);
Table created.
SQL>
SQL> CREATE TABLE sn1(rno NUMBER REFERENCES sn(rno),marks NUMBER);
Table created.
SQL>
SQL> SELECT pa.table_name parent_table
2 ,ch.table_name child_table
3 FROM user_constraints ch
4 ,user_constraints pa
5 WHERE pa.constraint_name = ch.r_constraint_name
6 AND ch.constraint_type = 'R'
7 AND pa.constraint_type IN ('P','U')
8 ORDER BY parent_table,child_table;
PARENT_TABLE CHILD_TABLE
------------------------------ ------------------------------
SN SN1
SQL>
|
|
|
Re: how to view the table is parent or child. and if it is child table.how to find its parent table [message #187556 is a reply to message #187173] |
Mon, 14 August 2006 07:45  |
youthsen
Messages: 45 Registered: August 2006
|
Member |
|
|
hi pals
thanks a lot. it is working.
hi JRowbottom, pls forgive me. i did not enter what you said insted i put the parent table name(sn) and child table name(sn1) in respective parent_table and child_table.
and i did not enter the full commands what you entered. since i thought that it is syntax for query. but you entered the original query. very sorry for my mistake.
hi Littlefoot,
thanks for giving real output of query. then only i realized that.
thanks a lot to all you guys.
|
|
|