Oracle/constraint

Advertisement


Question
QUESTION: I saw your post on checking constraints; I want to add a another another level to that. I want to see which column the FK constraint associates with in table a

ANSWER: Good day Camron

You will need to use the table dba_cons_columns for it.  

Here is a query that you can use to retrieve the information

select con.owner, con.constraint_name, cols.table_name, cols.column_name
from dba_cons_Columns cols, dba_constraints con
where con.constraint_name = cols.constraint_name
and con.constraint_name = '<CONSTRAINT_NAME>';

Hope it helps.

Elliot

---------- FOLLOW-UP ----------

QUESTION: Elliot thank you for getting back to me. I am sad to say that didn't return any results. This is the syntax I used from an earlier post of yours.
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.owner = b.owner
and      a.constraint_name = b.constraint_name
and      a.constraint_type = 'P'
union all
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.owner = b.owner
and      a.r_constraint_name = b.constraint_name
and      a.constraint_type = 'R'
order by 1, 2, 3, 4, 5
/

it works great except I need to find out what column holds the constraint. in table 'a'.

Answer
Hi Camron,

The SQL that you provided in the reply works as expected.  I think the reason why you are not getting any result is due to the following

(1) The SQL is assuming that you are logging on using the schema owner, i.e., if you want all the constraints information from "HR" schema, you will have to logon as "HR" to run the SQL

I changed the SQL so that it will retrieve constraints information for all schemas.  Please keep in mind the you need to have read access to all the DBA_ tables (like DBA or sysdba privilege).

select a.owner
      , a.table_name
      , a.constraint_name
      , a.constraint_type
      , b.table_name
      , b.column_name
      , b.position
from     dba_constraints a
      , dba_cons_columns b
where    a.owner = b.owner
and      a.constraint_name = b.constraint_name
and      a.constraint_type = 'P'
union all
select a.owner
      , a.table_name
      , a.constraint_name
      , a.constraint_type
      , b.table_name
      , b.column_name
      , b.position
from     dba_constraints a
      , dba_cons_columns b
where    a.owner = b.owner
and      a.r_constraint_name = b.constraint_name
and      a.constraint_type = 'R'
order by 1, 2, 3, 4, 5
/

Hope it helps.  Please let me know if you shall have any questions :)

Elliot

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Elliot Mak

Expertise

I can answer questions with regarding to Oracle DB (8i, 9i, 10g, 11g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).

Experience

I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.

Education/Credentials
Bachelor of Science in Management System

©2016 About.com. All rights reserved.