Tuesday, March 1, 2011

query to show ref integrity constraints

Here is one possible query useful to identify ref integrity constaints between tables. Perhaps most useful when uncommenting and filling in as needed the schema_owner part of the query otherwise as written it shows all ref integrity constraints.

select query.owner, query.parent_table, query.child_table, query.column_name, query.constraint_name, query.constraint_status from
( select a.owner, substr(a.constraint_name,1,length(a.constraint_name)-5) parent_table, substr(a.table_name,1,30) child_table, substr(column_name,1,30) column_name,
substr(a.constraint_name,1,30) constraint_name, b.status constraint_status
from dba_cons_columns a, dba_constraints b
where /* a.owner like 'SRE%' and */
a.owner = b.owner and
a.constraint_name = b.constraint_name
and b.constraint_type = 'R' order by 1, 3 ) query order by 1, 2, 3

1 comment:

  1. Very nice post. I must admit that you have provided an excellent example to identify the referential integrity constraint between tables. I just implemented this query and it worked well. Thanks for posting.
    sap support pack