CODEWITHSUNDEEP
sqloracle-databaseselectforeign-keys
LeaLil
LeaLil

Reputation: 11

Table with ForeignKey to table x

I've got an Oracle-DB with ~50 Tables.

Now, i'm looking for all tables with a foreign key to Table 'xyz'. is there a way to do this?

greetings, Lea

Upvotes: 1

Views: 92

Answers (4)

Ahmed
Ahmed

Reputation: 450

for sql try this :


SELECT  K.TABLE_NAME ,
        K.COLUMN_NAME ,
        K.CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
        JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME
                                                         AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
                                                         AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
                                                         AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE   C.CONSTRAINT_TYPE = 'FOREIGN KEY'     /*FOR FOREIGN KEY U NEED TO REPLACE CONSTRAINT_TYPE WITH FOREIGN KEY*/
        AND K.COLUMN_NAME IN ( SELECT   COLUMN_NAME
                               FROM     INFORMATION_SCHEMA.COLUMNS )

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

It seems, that you can query User_Constraints view, something like

select distinct Table_Name
           from User_Constraints 
          where Constraint_Type = 'R' and
                R_Constraint_Name in ( 
                    select Constraint_Name
                      from User_Constraints
                     where Constraint_Type = 'P' and
                           Table_Name = Upper('xyz')) -- <- Your table name
       order by Table_Name -- <- may be redundant

Upvotes: 0

schurik
schurik

Reputation: 7928

select fk.table_name from all_constraints fk , all_constraints pk
where 
pk.table_name = 'XYZ'
and fk.constraint_type = 'R'
and fk.r_constraint_name = pk.constraint_name

Upvotes: 0

APC
APC

Reputation: 146199

Foreign keys reference primary (or unique) keys, not tables. So first thing is to establish the primary key(s) for XYZ. Then we can look up the foreign keys which reference it.

 select p.constraint_name
        , p.constraint_type
        , f.owner
        , f.table_name
        , f.constraint_name
 from all_constraints p
      left join all_constraints f
      on ( f.r_constraint_name = p.constraint_name)
 where p.table_name = 'XYZ'
 and p.constraint_type in ('P', 'U')
 and f.constraint_type = 'R'

I've done this as an OUTER JOIN so it will return something even if no tables reference a key on XYZ. Your table might be referenced by tables in other schemas. That's why I suggest using ALL_CONSTRAINTS rather than USER_CONSTRAINTS.

Upvotes: 3

Related Questions