0

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.

JRG
  • 4,037
  • 3
  • 23
  • 34
FullStackDeveloper
  • 910
  • 1
  • 16
  • 40

4 Answers4

2

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';
LauDec
  • 528
  • 5
  • 10
  • For you last solution, 'AND c1.owner = 'YOUR_SCHEMA';' If my oracle sql developer login name is 'TestDemo', so I need to do 'AND c1.owner = 'TestDemo';' Then I can only output the foreign key constraints that I specify/create. But SQL Server does not need to so, only Oracle needs this? – FullStackDeveloper Jul 31 '17 at 00:28
  • My answer is based on the fact that you asked for ALL foreign keys on ALL tables. If you are interested only in yours replace `DBA_CONSTRAINTS` by `USER_CONSTRAINTS` and `DBA_CONS_COLUMNS` by `USER_CONS_COLUMNS` and you will see only thee one that you own – LauDec Jul 31 '17 at 00:34
  • Great. It works. May I know what are other results typically used for by using DBA_CONSTRAINTS? I only know those foreign key constraints specified by the user/us are useful because I can see the relationships between tables and columns, so I can know the project' business domain. – FullStackDeveloper Jul 31 '17 at 02:16
  • the catalog views sort of all work the same. you have a `DBA_` version which contains data for all the database, `ALL_` which contains objects the calling user has access to and a `USER_` which contains only the objects owner by the user. but there are a lot of them `DBS_OBJECTS` `DBA_TABLE` `DBA_TAB_PRIV` etc. allof them are in the oracle documentation here : https://docs.oracle.com/cd/E11882_01/nav/catalog_views.htm – LauDec Jul 31 '17 at 14:52
0

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';
FullStackDeveloper
  • 910
  • 1
  • 16
  • 40
hemalp108
  • 1,209
  • 1
  • 15
  • 23
  • I think I need to add one more where condition 'AND C.OWNER = MyDBLoginName'. Otherwise, if I only use your code, it will list too many columns which C.OWNER is 'SYS', 'SYSTEM', 'HR', etc, which is not what I want. – FullStackDeveloper Jul 30 '17 at 18:30
  • Can you also output the referenced table and reference column? – FullStackDeveloper Jul 30 '17 at 18:34
  • my solution does it :) – LauDec Jul 30 '17 at 18:35
  • @hemalp108 : You are missing a condition in your join clause i think. Constraint name beeing not unique cross schema, if you have 2 schema with a same constraint_name, you are going to match unrelated rows. you should add a `AND A.OWNER=C.OWNER`. – LauDec Jul 30 '17 at 18:44
  • @LauDec Yes, your last solution is what I want. May I know how do you come to this solution? Any suggested reference document for me, so I can write the solution as you did? – FullStackDeveloper Jul 31 '17 at 00:22
  • @CodeContributor I have my hands in the oracle catalog view all day long :) But the documentation for the views i used can be found https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1044.htm#REFRN20047 and https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1042.htm#REFRN20045 – LauDec Jul 31 '17 at 01:41
0

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';
FullStackDeveloper
  • 910
  • 1
  • 16
  • 40
0

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.
user3447612
  • 1
  • 1