Home » SQL & PL/SQL » SQL & PL/SQL » how to view the table is parent or child. and if it is child table.how to find its parent table
how to view the table is parent or child. and if it is child table.how to find its parent table [message #187173] Fri, 11 August 2006 03:53 Go to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi pals

i have created two tables 1,india 2,delhi

india - is parent table for delhi

so delhi is child table of india.

if i use query as follows

sql>select * from table;

which shows all tables. but that command dont give enough details to know which is child table and which is parent table.

and if it is child table. i want to know it's parent table.

thanks a lot in advance.
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 Go to previous messageGo to next message
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 #187183 is a reply to message #187178] Fri, 11 August 2006 04:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Idea Why don't you open the first post here ("tips and tricks"). There is a link to the Oracle manuals. It might be interesting for you to bookmark that page. Not that we don't want to answer basic questions, of course... Wink

MHE
Re: how to view the table is parent or child. and if it is child table.how to find its parent table [message #187327 is a reply to message #187173] Sat, 12 August 2006 03:04 Go to previous messageGo to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi JRowbottom

i am newbie to sql. i can't understand your command line pls help me.

give the full command line for my need

my parent table is
sql>create table sn(name varchar2(20),rno number primary key);

table created


my chil table is
sql>create table sn1(rno number references sn(rno),marks number);

table created



now i want to view which is parent table and which is child table.

pls help me.

i exepecting your answer.





Re: how to view the table is parent or child. and if it is child table.how to find its parent table [message #187454 is a reply to message #187327] Mon, 14 August 2006 02:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Expect away.

Did you actually try running the SQL I posted?
Re: how to view the table is parent or child. and if it is child table.how to find its parent table [message #187535 is a reply to message #187173] Mon, 14 August 2006 06:43 Go to previous messageGo to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi JRowbottom

i am new to oracle. so i am not able to understand your command line.

i have mentioned my parent_table(sn) and child_table(sn1).

so pls give the command line by mentioning this parent_table name and child_table name.

or give some other example.

pls reply as soon as possible.

i am expecting your reply.
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 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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 Go to previous message
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.


Previous Topic: Quarter first and last month
Next Topic: Object Types
Goto Forum:
  


Current Time: Sat Dec 03 16:07:01 CST 2016

Total time taken to generate the page: 0.13387 seconds