1

I'm trying to figure out how to look up the name of the column referenced by a foreign key.

Here's an example to demonstrate, keep in mind that I'm trying to look this up dynamically for hundreds of tables.

  • WIDGET
    • ID
    • COMPONENT_ID
    • ...
  • PART
    • ID
    • NAME
    • ....

WIDGET.COMPONENT_ID is a foreign key for PART.ID

I have a query to find PART (table name) but I can't figure out how to find PART.ID from WIDGET_COMPONENT_ID.

EDIT:

I'm trying to find out the referenced column name. I can find the table name but I can't figure out how to find the column name.

EDIT 2:

(sql from List of foreign keys and the tables they reference) For example, I can get the constraint name via:

SELECT c_pk.constraint_name, c_pk.table_name r_table_name FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name WHERE c.constraint_type = 'R' AND a.table_name = 'WIDGET'

This gets me something like COMPONENT_ID_FK and PART. How to go from COMPONENT_ID_FK to PART.ID though I'm not sure.

Misha M
  • 10,979
  • 17
  • 53
  • 65
  • @Corion it's actually not. The closest I found is https://stackoverflow.com/questions/1729996/list-of-foreign-keys-and-the-tables-they-reference/1730054 but it only gets you the table name and not the column being referenced – Misha M Oct 07 '18 at 16:21
  • Oh, indeed, that is only half of the answer. A constraint can have multiple column names, so you will also need to look up the constraint via https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1042.htm#REFRN20045 . If you have an example of tables + constraints, I could provide a solution via SQLFiddle , so maybe post that? – Corion Oct 07 '18 at 16:26
  • @Corion updated with more info – Misha M Oct 07 '18 at 16:34
  • @bob-jaris, That question is a dupe of the one I linked to and returns just the table name, NOT the name of the primary key column(s) from that table name. That's what I'm asking about – Misha M Oct 16 '18 at 10:57

1 Answers1

1

This query helped me get the info I needed:

SELECT * FROM all_cons_columns WHERE constraint_name IN ( SELECT c_pk.constraint_name FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name WHERE c.constraint_type = 'R' AND a.column_name = 'COMPONENT_ID' AND a.table_name = 'WIDGET' )

Misha M
  • 10,979
  • 17
  • 53
  • 65