CODEWITHSUNDEEP
oracle-databaseforeign-keys
FullStackDeveloper
FullStackDeveloper

Reputation: 968

How to find all foreign key constraints in oracle?

In StackOverflow, I can find the post about how to find all foreign key constraints in SQL Server.

I only can find the post about how to find all foreign key constraints for a table in Oralce (List of foreign keys and the tables they reference).

My question is: How can I list all foreign key constraints for all tables, instead of for a table in Oracle. My desire answer will be something like this one, but for Oracle.

P.S. I am using oralce 11g express edition. My oracle GUI management tool is Orace SQL developer.

Upvotes: 1

Views: 14700

Answers (4)

user3447612
user3447612

Reputation: 1

Below query gives Table Name, Column Name of both parent and child tables along with constraint names on each.

select a.table_name child_table, a.column_name child_column, b.table_name parent_table, b.column_name parent_column, a.position, a.constraint_name child_constraint, b.constraint_name parent_constraint
from all_cons_columns a
join all_constraints lc on a.owner = lc.owner and a.constraint_name = lc.constraint_name
join all_constraints rc on lc.r_owner = rc.owner and lc.r_constraint_name = rc.constraint_name 
join all_cons_columns b on rc.owner = b.owner and rc.constraint_name = b.constraint_name and a.position = b.position
where a.owner = '<Schema Name>'
and lc.constraint_type = 'R'
order by a.constraint_name, a.table_name, a.position

Here is the brief explanation of tables used in above SQL:

  • all_constraints - has constraint name, table name, constraint name of the FK parent table. Constraint_type field in this table defines the types of constraint, Foreign key constraints are defined by 'R'.
  • all_cons_columns - has list of column names for each constraint, position column in this table defines the order of the
    column in constraint.

Upvotes: 0

hemalp108
hemalp108

Reputation: 1249

Don't use Table name in where condition and you will get FK constraints for all tables.

SELECT A.TABLE_NAME,
       A.COLUMN_NAME,
       A.CONSTRAINT_NAME, 
       C.OWNER
FROM   ALL_CONS_COLUMNS A, 
       ALL_CONSTRAINTS C  
WHERE  A.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
AND    C.CONSTRAINT_TYPE = 'R'
AND    C.OWNER = 'MyDBLoginName';

Upvotes: 1

FullStackDeveloper
FullStackDeveloper

Reputation: 968

This answer is based on LauDec's solution.

Since I only need all foreign key constraints specified by me/user, so I will use following SQL:

with constraint_colum_list as ( select owner, table_name, constraint_name, listagg(column_name,',') WITHIN GROUP ( order by position ) as column_list
                                FROM USER_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 USER_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';

Upvotes: 0

LauDec
LauDec

Reputation: 548

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';

Upvotes: 3

Related Questions