Oracle Get Foreign Keys

List of foreign keys and the tables they reference in Oracle DB

The referenced primary key is described in the columns r_owner and r_constraint_name of the table ALL_CONSTRAINTS. This will give you the info you want:

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 
       -- referenced pk
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE c.constraint_type = 'R'
   AND a.table_name = :TableName

How to find all foreign key constraints in oracle?

I would do it like that ;

If you want it aggregated with all column on the same row

select distinct c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name, 
listagg(c2.column_name,',') WITHIN GROUP ( ORDER BY C2.POSITION) OVER ( PARTITION BY c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name ) column_list
from dba_constraints c1
JOIN dba_cons_columns c2
ON c1.R_CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.r_owner=c2.owner
where C1.constraint_type = 'R'

or like that if you want one row per column

select  c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name, c2.column_name
from dba_constraints c1
JOIN dba_cons_columns c2
ON c1.R_CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.r_owner=c2.owner
where C1.constraint_type = 'R'
order by c1.owner, c1.table_name, c1.constraint_name, c2.position

or if you want referencing column details AND referenced columns details

with constraint_colum_list as ( select owner, table_name, constraint_name, listagg(column_name,',') WITHIN GROUP ( order by position ) as column_list
                                FROM DBA_CONS_COLUMNS GROUP BY owner, table_name, constraint_name )
select distinct c1.owner, c1.table_name, c1.constraint_name, c2.column_list, c3.owner, c3.table_name, c3.constraint_name, c3.column_list
from DBA_constraints c1
JOIN constraint_colum_list c2 ON c1.CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.owner=c2.owner
JOIN constraint_colum_list c3 ON C1.R_CONSTRAINT_NAME=C3.CONSTRAINT_NAME AND C1.R_OWNER=C3.owner
where C1.constraint_type = 'R' 
-- AND c1.owner = 'YOUR_SCHEMA';

Oracle get foreign keys

found it!

this is what i was looking for, thanks everybody for helping.

SELECT a.table_name, a.column_name, uc.table_name, uc.column_name 
                FROM all_cons_columns a
                JOIN all_constraints c ON a.owner = c.owner
                    AND a.constraint_name = c.constraint_name
                JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                       AND c.r_constraint_name = c_pk.constraint_name
                join USER_CONS_COLUMNS uc on uc.constraint_name = c.r_constraint_name
                WHERE  C.R_OWNER = 'myschema'

How can I find which tables reference a given table in Oracle SQL Developer?

No. There is no such option available from Oracle SQL Developer.

You have to execute a query by hand or use other tool (For instance PLSQL Developer has such option). The following SQL is that one used by PLSQL Developer:

select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = :r_table_name
   and owner = :r_owner
 )
order by table_name, constraint_name

Where r_owner is the schema, and r_table_name is the table for which you are looking for references. The names are case sensitive

Be careful because on the reports tab of Oracle SQL Developer there is the option "All tables / Dependencies" this is from ALL_DEPENDENCIES which refers to "dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links.". Then, this report have no value for your question.

Oracle : display foreign key columns

You can use user_cons_columns dictionary view joining with the query j and then LISTAGG() through aggregation in order to concat multiple columns such as

WITH f AS
(
    SELECT constraint_name, table_name, r_constraint_name
      FROM user_constraints
     WHERE constraint_type = 'R'
), p AS 
(
    SELECT constraint_name, table_name
      FROM user_constraints
     WHERE constraint_type = 'P'
),j (child_table, f_key, column_name, parent_table, p_key) AS 
(
    SELECT f.table_name, f.constraint_name, c.column_name, 
           p.table_name, f.r_constraint_name
      FROM p
      JOIN f
        ON p.constraint_name = f.r_constraint_name
      LEFT JOIN user_cons_columns c
        ON c.constraint_name = f.constraint_name
    UNION ALL
    SELECT 'PARENT', 
           (SELECT constraint_name 
              FROM p
             WHERE table_name = 'PARENT'), NULL, NULL, NULL
      FROM dual
)
 SELECT MAX(level) AS lvl, child_table, f_key, 
        LISTAGG(column_name,',') WITHIN GROUP (ORDER BY column_name) AS f_key, 
        parent_table, p_key
   FROM j
  START WITH parent_table IS NULL
CONNECT BY nocycle parent_table = prior child_table
GROUP BY child_table, f_key, parent_table, p_key
ORDER BY lvl, parent_table, child_table

Demo

How to find reference column of foreign in key in Oracle?

This query helped me get the info I needed:

SELECT * FROM all_cons_columns WHERE constraint_name IN ( SELECT c_pk.constraint_name FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name WHERE c.constraint_type = 'R' AND a.column_name = 'COMPONENT_ID' AND a.table_name = 'WIDGET' )

How can I find out which table a foreign key references?

You say you're using SQL Developer.

So, just open the table.

Go to the constraints page.

Find your foreign key.

Look at the R_TABLE_NAME column.

Sample Image

How to find tables having foreign key to a table in Oracle?

SELECT d.table_name,

       d.constraint_name "Primary Constraint Name",

       b.constraint_name "Referenced Constraint Name"

FROM user_constraints d,

     (SELECT c.constraint_name,

             c.r_constraint_name,

             c.table_name

      FROM user_constraints c 

      WHERE table_name='EMPLOYEES' --your table name instead of EMPLOYEES

      AND constraint_type='R') b

WHERE d.constraint_name=b.r_constraint_name

Are recursive foreign keys also marked as primary keys? (In Oracle SQL Developer)

Would Mgr be marked as a primary key or is this code correct?

Code is correct (table gets created), but mgr will not be part of a primary key constraint; a table can have only one primary key. If you meant to say that it'll be a composite primary key (i.e. it would have two columns), it will not either. See the result of the 2nd query - only empno is a primary key column.

SQL> create table Employee ( EMPNO number(10) not null, Mgr number(10), constraint pk_Employee primary key (EMPNO), constraint fk_emp_emp foreign key (Mgr) references Employee );

Table created.

SQL> select a.constraint_type,
  2         b.column_name
  3  from user_cons_columns b join user_constraints a on a.constraint_name = b.constraint_name
  4  where a.table_name = 'EMPLOYEE';

CONSTRAINT_TYPE      COLUMN_NAME
-------------------- --------------------
C                    EMPNO
P                    EMPNO          --> constraint type = P = primary key
R                    MGR

SQL>

Oracle: get all constraints/columns (primary and foreign keys)

Join the constraint column view to the pk constraint view:

SELECT a.table_name
     , a.column_name
     , a.constraint_name
     , c.owner
     , c.r_owner
     , c_pk.table_name      r_table_name
     , c_pk.constraint_name r_pk
     , cc_pk.column_name    r_column_name
  FROM all_cons_columns a
  JOIN all_constraints  c       ON (a.owner                 = c.owner                   AND a.constraint_name   = c.constraint_name     )
  JOIN all_constraints  c_pk    ON (c.r_owner               = c_pk.owner                AND c.r_constraint_name = c_pk.constraint_name  )
  JOIN all_cons_columns cc_pk   on (cc_pk.constraint_name   = c_pk.constraint_name      AND cc_pk.owner         = c_pk.owner            )
 WHERE a.owner = 'BRANCH_DEV'
   AND a.table_name IN ( 'table1','table2' )
     ;