Kaley's (mostly) Oracle Database Blog
Hi! I'm Kaley Crum, Oracle DBA.
Welcome to my (mostly) Oracle database blog.

Archive: Dec 2015

Searching for Junction Tables in Oracle

If you have two tables that can be joined in a many-to-many fashion (for example, if you have a MOVIES table and an ACTORS table), and you want to find the table that joins the two, it’s easy if the architect used foreign keys.  Below is a modified version of the query used to search for child tables of a given parent table in a foreign-key relationship.  As always, if you don’t have permission to select from DBA_CONSTRAINTS, ask your DBA nicely for the SELECT ANY DICTIONARY system privilege or the SELECT_ANY_CATALOG role, or use ALL_CONSTRAINTS instead.