Home » SQL & PL/SQL » SQL & PL/SQL » Displaying the foreign key details in oracle 9i (oracle 9i, windows, xp)
Displaying the foreign key details in oracle 9i [message #364743] Tue, 09 December 2008 22:15 Go to next message
JenniferBabes
Messages: 18
Registered: March 2006
Location: Student
Junior Member

Hello. I was wondering if there was a way with oracle, via SQL, (sql worksheet) to view the foreign key relationships created on a particular table. I could use the describe table_name statement but it does not show the details of the foreign keys. is this possible.

I was recently searching and found what I wanted but with MySql. It has a SHOW CREATE TABLE TABLE_NAME statement that shows the details of the table created but particularly the foreign keys. Is there an identical statement that I could use with oracle 9i?

Any help would be appreciated.

Jennifer.
Re: Displaying the foreign key details in oracle 9i [message #364748 is a reply to message #364743] Tue, 09 December 2008 23:00 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
SELECT d1.constraint_type, d1.table_name child_table,
       d2.table_name parent_table, c1.column_name parent_table_column
  FROM dba_constraints d1, dba_constraints d2, all_cons_columns c1
 WHERE d1.table_name = :TABLE_NAME
   AND d1.r_constraint_name = d2.constraint_name
   AND d1.constraint_name = c1.constraint_name
   AND d1.table_name = c1.table_name
Re: Displaying the foreign key details in oracle 9i [message #364755 is a reply to message #364743] Tue, 09 December 2008 23:25 Go to previous messageGo to next message
JenniferBabes
Messages: 18
Registered: March 2006
Location: Student
Junior Member

I tried this using your code:

SELECT d1.constraint_type, d1.table_name child_table,
d2.table_name parent_table, c1.column_name parent_table_column
FROM dba_constraints d1, dba_constraints d2, all_cons_columns c1
WHERE d1.table_name = :'registration'
AND d1.r_constraint_name = d2.constraint_name
AND d1.constraint_name = c1.constraint_name
AND d1.table_name = c1.table_name;

However i got the following error message:


WHERE d1.table_name = :'registration'
*
ERROR at line 4:
ORA-01745: invalid host/bind variable name

When i took out the : and left

WHERE d1.table_name = 'registration'

I got the following error message:

FROM dba_constraints d1, dba_constraints d2, all_cons_columns c1
*
ERROR at line 3:
ORA-00942: table or view does not exist

I am sure that the registration table exists.

Re: Displaying the foreign key details in oracle 9i [message #364756 is a reply to message #364743] Tue, 09 December 2008 23:28 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
BOSS!!! :table_name is a bind variable

Please replace with :TABLE_NAME with your table_name

Remove Coln. : Sad
Re: Displaying the foreign key details in oracle 9i [message #364757 is a reply to message #364755] Tue, 09 December 2008 23:31 Go to previous messageGo to next message
subhra_88
Messages: 14
Registered: April 2007
Location: Bangalore
Junior Member
Please check if you have select privileges on the dba_constraints table.
Re: Displaying the foreign key details in oracle 9i [message #364758 is a reply to message #364743] Tue, 09 December 2008 23:36 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
yes,, check the prvileges and also table name should be in UPPER case
Re: Displaying the foreign key details in oracle 9i [message #365057 is a reply to message #364743] Wed, 10 December 2008 14:59 Go to previous message
JenniferBabes
Messages: 18
Registered: March 2006
Location: Student
Junior Member

Ok thanks for the help it worked. I didnt realise it was suppose to be uppercase.

Thanks again,
Jennifer.

P.S. Boss lol, that made me laugh, you're funny...
Previous Topic: SQL Query required for self join
Next Topic: Problem with concatinatnating percentage in the select statement
Goto Forum:
  


Current Time: Wed Dec 07 06:51:38 CST 2016

Total time taken to generate the page: 0.19656 seconds