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.
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
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.