CODEWITHSUNDEEP
databaseoracle-databaseforeign-keysrelational-database
CloudyMarble
CloudyMarble

Reputation: 37566

Constraints / Foreign keys information in Oracle

I have to get the Table and Column name of the Foreign keys on Oracle, can anybody confirm the following statement? 

SELECT a.table_name AS TableWithForeignKey, b.column_name AS ForeignKeyColumn 
FROM user_constraints a INNER JOIN user_cons_columns b 
ON (a.constraint_name = b.constraint_name) AND (a.table_name = b.table_name)
and a.constraint_type = 'R'

The Part I'm not sure about is the INNER JOIN Part (after ON):

(a.constraint_name = b.constraint_name) AND (a.table_name = b.table_name)

As I couldn't find something like constraints_ID, is this enought to match 1:1 rows from both tables user_constraints and user_cons_columns

Thank you.

Upvotes: 2

Views: 372

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16905

I use the 

a.constraint_name = b.constraint_name

for joining the 2 views so, I think it's OK

Upvotes: 2

Related Questions