List foreign key constraints in Oracle and MySQL

Sometimes you want a list of all the associations in your database. You're supposed to know this from your Hibernate mappings, or from your ActiveRecord declarations, but sometimes you just need to dig this stuff out of a legacy database with its cryptic prehistoric naming conventions layered on top of one another like geological rock strata.

Anyway, I digress. As you might imagine, this is a simple select in MySQL, and a four-table join in Oracle.

Suppose you've had the original idea of building a brilliant e-commerce site. You have Clients, Orders, Products, and LineItems, with the usual associations.

This is the kind of output you want:

+-----------------------+-------------+
| foreign key           | references  |
+-----------------------+-------------+
| orders.client_id      | clients.id  |
| line_items.order_id   | orders.id   |
| line_items.product_id | products.id |
+-----------------------+-------------+

With MySQL:

select 
    concat(table_name, '.', column_name) as 'foreign key',  
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;

With Oracle (9-ish, probably 10 too, it's ages since I've actually used this)

select
    col.table_name || '.' || col.column_name as foreign_key,
    rel.table_name || '.' || rel.column_name as references
from
    user_tab_columns col
    join user_cons_columns con 
      on col.table_name = con.table_name 
     and col.column_name = con.column_name
    join user_constraints cc 
      on con.constraint_name = cc.constraint_name
    join user_cons_columns rel 
      on cc.r_constraint_name = rel.constraint_name 
     and con.position = rel.position
where
    cc.constraint_type = 'R'

I cry when I behold the beauty of Oracle's meta data model. But we'll hold off the religious wars for another day. I know it's not your fault you're using Oracle.

HTH.