Constraint-related SQL queries

The following tables are needed to retrieve Oracle constraint information.
  1. user_constraints / dba_constraints
  2. user_cons_columns / dba_cons_columns
  3. all_constraints

--find constraint by name...
select   a.table_name
        , a.constraint_name
        , a.constraint_type
        , b.table_name
        , b.column_name
        , b.position
from     user_constraints a
        , user_cons_columns b
where a.constraint_name = 'POT_MLOC_FK'

Find constraint reference table.

--find maint_diag table as constraint in other tables ("referended table")
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
  from all_constraints
 where constraint_type='R'
   and r_constraint_name in (select constraint_name
                     from all_constraints
                     where table_name='MAINT_DIAGNOSIS');
Posted by ViSO at 9/12/2011 9:27 AM
Categories: Code Snippets

Previous Post
Next Post

 

What did you think of this article?




Trackbacks
Trackback specific URL for this post
  • Trackbacks are closed for this post.
Comments
  • No comments exist for this post.
Leave a comment

Comments are closed.