Andreas_Sprosec_7439

SQL foreign key constraint in Oracle Database

Discussion created by Andreas_Sprosec_7439 on Sep 2, 2016
Latest reply on Oct 12, 2016 by Andreas_Sprosec_7439
If you need to know which foreign keys are installed in your database you can use the script below.

def OWNER_NAME=AEV11
def Parent_Table_Name='EH'
def Child_Table_Name='EV'

select b.owner,
       b.table_name CHILD_TABLE,
       b.column_name CHILDCOL,
       c.table_name PARENT_TABLE,
       c.column_name PARENTCOL,
       a.constraint_name,
       b.position,
       a.status
    from all_cons_columns b,
         all_cons_columns c,
         all_constraints a
   where b.constraint_name = a.constraint_name
     and a.owner           = b.owner
     and b.position        = c.position
     and c.constraint_name = a.r_constraint_name
     and c.owner           = a.r_owner
     and a.constraint_type = 'R'
     and c.owner      like case when upper('&OWNER_NAME') is null then '%'
                                else upper('&OWNER_NAME') end
     and c.table_name like case when upper('&PARENT_TABLE_NAME') is null then '%'
                                else upper('&PARENT_TABLE_NAME') end
     and b.table_name like case when upper('&CHILD_TABLE_NAME') is null then '%'
                                else upper('&CHILD_TABLE_NAME') end
order by 1,2,3,4,6
/

 

st30jlbx3se3.png

Outcomes