• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12601
  • Last Modified:

Select query on user_constraints.

The view USER_CONSTRAINTS from the oracle data dictionary includes the following columns:
 
CONSTRAINT_NAME                        NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE                         VARCHAR2(1) ('R' if a foreign key)
TABLE_NAME                                    VARCHAR2(30)
R_CONSTRAINT_NAME                   VARCHAR2(30)

Where R_CONSTRAINT_NAME contains the name of the primary key constraint that is referenced in the case of a foreign key.

What be the sql to select the info about foreign keys only in the following form?

FK Const Name      FK Table      Pk Const Name      Pk Table
EMP_DEPT_FK      EMP            DEPT_PK            DEPT
0
generali
Asked:
generali
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select *
from  USER_CONSTRAINTS F
join USER_CONSTRAINTS P
  on p.constraint_name = f.r_constraint_name
0
 
geotigerCommented:
Here it is:

SELECT r.r_constraint_name, r.table_name,
       p.constraint_name, p.table_name
FROM (
SELECT owner, constraint_name, constraint_type, table_name,
       r_owner, r_constraint_name
  FROM dba_constraints
 WHERE owner = 'VIEWPOINT'
   AND constraint_type = 'R'
     ) r,
     (
SELECT owner, constraint_name, constraint_type, table_name,
       r_owner, r_constraint_name
  FROM dba_constraints
 WHERE owner = 'VIEWPOINT'
   AND constraint_type = 'P'
) p
WHERE r.r_constraint_name = p.constraint_name


You can change dba_constraints to all_ constraints if you do not have DBA access.
0
 
geotigerCommented:
Please remove the owner condition, I was testing in my system:

SELECT r.r_constraint_name, r.table_name,
       p.constraint_name, p.table_name
FROM (
SELECT owner, constraint_name, constraint_type, table_name,
       r_owner, r_constraint_name
  FROM user_constraints
   WHERE constraint_type = 'R'
     ) r,
     (
SELECT owner, constraint_name, constraint_type, table_name,
       r_owner, r_constraint_name
  FROM user_constraints
   WHERE constraint_type = 'P'
) p
WHERE r.r_constraint_name = p.constraint_name
0
 
Mark GeerlingsDatabase AdministratorCommented:
I use this query to answer that question.
Notes:
1. You can remove the line: "and c.table_name like upper('&table_name')" if you just want to run this for all tables.
2. The "break" command is for SQL*Plus.  If you run this in TOAD or another tool, remove the "break" line.

-- The decode on position offsets all but the first column of multi-column keys 1 space right
break on "FK Table" skip 1 on "PK Table";
select substr(decode(cc1.position,1,'',' ')||cc1.column_name,1,30) "FK const name",
substr(c.table_name,1,30) "FK Table",
substr(decode(cc2.position,1,'',' ')||cc2.column_name,1,30) "PK const name",
substr(r.table_name,1,30) "PK Table"
from user_cons_columns cc1, user_cons_columns cc2, user_constraints r, user_constraints c
where c.constraint_type = 'R'
and r.constraint_name = c.r_constraint_name
and cc1.owner = c.owner
and cc1.constraint_name = c.constraint_name
and cc1.table_name = c.table_name
and cc2.owner = r.owner
and cc2.constraint_name = r.constraint_name
and cc2.table_name = r.table_name
and cc2.position = cc1.position
and c.table_name like upper('&table_name')
order by c.table_name, c.constraint_name, cc1.position;
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now