05 September 2008

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.

39 comments:

  1. Thanks, this is exactly what I needed.

    ReplyDelete
  2. Very very useful.
    Thanks from Indonesia

    ReplyDelete
  3. Thanks, this is exactly what I needed.

    ReplyDelete
  4. Very helpful, thank you.

    ReplyDelete
  5. Extremely Helpful - just saved me a full day of analysis to find the Constraint details.

    ReplyDelete
  6. Thanks,
    clear and concise...

    ReplyDelete
  7. thank you very much.

    ReplyDelete
  8. Maybe the oracle is slightly sophisticated than mysql :)

    ReplyDelete
  9. Thank you.Its very useful to me

    ReplyDelete
  10. I think you deserve a couple of wings or so...
    Ty!!!

    ReplyDelete
  11. thanks man.. it s helpful.. I am also having the blog won oracle database www.blogskies.com visit it when you get time.. thx.

    ReplyDelete
  12. Thank you very much

    ReplyDelete
  13. beautiful-thanks!

    ReplyDelete
  14. thx from Latvia

    ReplyDelete
  15. Clear, concise and right to the point. Thanks for this helpful tip and thanks Google for bringing your page to the top :). The search phrase I used was "mysql foreign key details" (without the double quotes).

    ReplyDelete
  16. Now does anyone know how to modify the data type of a column/field in MySQL which has a foreign key constraint to another table? A simple 'ALTER TABLE table_name MODIFY column_name new_column_type' throws an errorno: 150 due to the foreign key constraint.

    Is there a way to get around this by doing something like cascading the changes to the other dependent tables?

    Thanks,
    Karthik

    ReplyDelete
  17. Thanks :) one more to you growing list ;)

    ReplyDelete
  18. Thanks a bunch. I'm new to MySQL and not very fond of the free WorkBench. Glad I found a way to at least see the FKs that are actually referenced in the data.

    ReplyDelete
  19. from argentina.Me was very useful
    Thanks

    ReplyDelete
  20. yoyo.. you rocks man.

    ReplyDelete
  21. I know it's not your fault you're using Oracle.

    That's perfect ))

    ReplyDelete
  22. Perfect! Thank you :)

    ReplyDelete
  23. awesome... thats what i needed. Makes my task so simple.. :)

    ReplyDelete
  24. thanks for sharing!

    ReplyDelete
  25. Ho my god, the holy solution is right here ! I was trying to build the same query, but it seems that i'm not good enough :D

    Thanks dude !

    ReplyDelete
  26. Juste on thing : for the oracle version, you forgot to specify the owner in the "on" clauses, it may be important in most cases.

    ReplyDelete
  27. With this, you have saved me untold misery...
    Thank you so much !

    ReplyDelete
  28. Awesome! This is exactly what I need.

    ReplyDelete
  29. AWesome, thx!!!!

    ReplyDelete
  30. Thanks a lot !
    Note:
    If you have several schemas in your MySQL database, you can add the schema information using:

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

    ReplyDelete
  31. helpfull to create some audit log table.

    Thanx from indonesia.

    ReplyDelete
  32. "I cry when I behold the beauty of Oracle's meta data model"

    I LOL'd so hard when I read that! Oracle db is so needlessly complicated. I can't understand why it's still widely used.

    ReplyDelete