How to retrieve foreign key constraints data
After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution:
SELECT
CONS.CONSTRAINT_NAME,
CONS.TABLE_NAME,
COLS.COLUMN_NAME,
CONS.R_CONSTRAINT_NAME,
CONS_R.TABLE_NAME R_TABLE_NAME,
COLS_R.COLUMN_NAME R_COLUMN_NAME
FROM USER_CONSTRAINTS CONS
LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R'
ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAME
SQL Developer ships with a report that does just this.
It does it for the login schema only, but it's a quick fix to make it go get every single FK in the database - although you might want to omit schemas like 'APEX...' and 'SYS.'
It also omits things like, tables in the recycle bin.
The original report is in the Reports panel, in the data dictionary reports.
Here's the amended query to get ALL the FKs.
SELECT
c.owner "Owner",
c.table_name "Table_Name",
c.constraint_name "Constraint_Name",
c.delete_rule "Delete_Rule",
d.columns,
c.r_owner "Owner of Related Table",
(
SELECT
r.table_name
FROM
sys.all_constraints r
WHERE
c.r_owner = r.owner
AND
c.r_constraint_name = r.constraint_name
) "Related Table",
c.r_constraint_name "Related Constraint"
FROM
sys.all_constraints c,
(
SELECT
a.owner,
a.table_name,
a.constraint_name,
MAX(
DECODE(position,1,substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,2,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,3,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,4,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,5,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,6,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,7,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,8,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,9,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,10,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,11,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,12,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,13,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,14,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,15,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,16,','
|| substr(column_name,1,30),NULL)
) columns
FROM
sys.all_constraints a,
sys.all_cons_columns b
WHERE
a.constraint_name = b.constraint_name
AND
a.owner = b.owner
AND
a.constraint_type = 'R'
AND
substr(a.table_name,1,4) != 'BIN$'
AND
substr(a.table_name,1,3) != 'DR$'
AND (
:table_name IS NULL
OR
instr(upper(a.table_name),upper(:table_name) ) > 0
) GROUP BY
a.owner,
a.table_name,
a.constraint_name
) d
WHERE
c.owner = d.owner
AND
c.table_name = d.table_name
AND
c.constraint_name = d.constraint_name
ORDER BY
c.owner,
c.table_name,
c.constraint_name
And here's what that report looks like.