Home > dev-tools > Show foreign keys in Oracle SQL Developer

Show foreign keys in Oracle SQL Developer

The “Constraints” tab of a table in Oracle SQL Developer shows the foreign keys pointing from the table to other tables. But there is no list of foreign keys pointing from other tables to the table.

To display such a list, we can add a custom extension.

The following instructions are derived from a question/answer on stackoverflow. A big “Thank you” goes to user junaling whose solution I recount here:

Define the extension

Put the following into an XML file, e.g. “fk_ref.xml”:

<items>
    <item type="editor" node="TableNode" vertical="true">
    <title><![CDATA[FK References]]></title>
    <query>
        <sql>
            <![CDATA[select a.owner,
                            a.table_name,
                            a.constraint_name,
                            a.status
                     from   all_constraints a
                     where  a.constraint_type = 'R'
                            and exists(
                               select 1
                               from   all_constraints
                               where  constraint_name=a.r_constraint_name
                                      and constraint_type in ('P', 'U')
                                      and table_name = :OBJECT_NAME
                                      and owner = :OBJECT_OWNER)
                               order by table_name, constraint_name]]>
        </sql>
    </query>
    </item>
</items>

Add the extension

Add it to SQL Developer via menu:

Tools > Preferences – Database > User Defined Extensions – Click “Add Row” button – In Type choose “EDITOR”, Location is where you saved the xml file above – Click “Ok” then restart SQL Developer

Test it

Navigate to any table and you should now see an additional tab next to SQL one, labelled “FK References”, which displays the new FK information.

Advertisements
  1. May 24, 2016 at 10:59

    You can also click on the model tab, we display a relational diagram showing related tables that are one level ‘up’ and one level ‘down’ – that is, tables that this table references and tables that directly reference the current table. This feature is new for version 4.1

    • May 24, 2016 at 21:20

      Cool, that’s good to know. Thanks, Jeff!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: