Finding foreign keys on an Oracle database

If you create your tables with foreign key constraints you can query the all_constraints table using the query below.

select owner, constraint_name, constraint_type,

       table_name, r_owner, r_constraint_name

 from all_constraints

where constraint_type='R'

  and r_constraint_name in  

                (

                 select constraint_name

                   from all_constraints

                  where constraint_type in ('P','U')

                    and table_name='TABLE NAME'

                 );

However not all of us have such luck. The other option, if your naming structure allows, is to search for foreign keys by querying column names in the user_tab_columns. At my current workplace we use two different naming standards but both make it easy to find foreign keys. The first is the use of FK_TABLE_NAME_ID for foreign keys. So to find the foreign keys we can use the query below.

select table_name, column_name 

  from user_tab_columns
 where column_name like '%K_AGREEMENT_ID'

The second naming structure in use is were we prefix each column with a TLA for the name and name keys with a _LKEY suffix. So to find all customer (CST) foreign keys we can use

select table_name, column_name 

  from user_tab_columns
 where column_name like '%_CST_LKEY'

Happy New Year.


Follow on post: Finding tables by name in MySQL and Oracle