1

I am using oracle database.

Is there a way I can check if the primary key value in one table is being used as a foreign key value (have any associations) in any other table?

Example: if I have the Student_id as primary key in STUDENT table. And there are other tables in the schema like CLASS, MARKS, SPORTS etc.

I have student_id=100 with me. I need to find out all the references of this student_id value '100' in the entire schema wherever it is linked as a foreign key from the parent table STUDENT.

3

You can check dba_constraints:

Get the primary key constraint name:

select constraint_name 
from dba_constraints 
where owner = '<SCHEMA_NAME>' and table_name = 'STUDENT' and constraint_type = 'P';

Then check where that constraint is used:

select owner, table_name, constraint_name
from dba_constraints 
where r_constraint_name = '<result from previous query>' and constraint_type = 'R';

To get the column name of the referential constraint, you can use dba_cons_columns:

select owner, table_name, column_name 
from  dba_cons_columns
where constraint_name = '<constraint name from previous query>';

Plugging all of this together, you can do something like:

  SELECT fk.owner, fk.table_name, col.column_name
    FROM dba_constraints pk
    JOIN dba_constraints fk
      ON pk.constraint_name = fk.r_constraint_name
     AND fk.constraint_type = 'R'
    JOIN dba_cons_columns col
      ON fk.constraint_name = col.constraint_name
   WHERE pk.owner = :owner AND pk.table_name = 'STUDENT' AND pk.constraint_type = 'P';
|improve this answer|||||
  • Thanks Kris. But my problem is little extended. I need to find the references of the value of that constraint. Not the actual constraint name reference. As I explained I need to find all the references of students with student_id 100 in the schema. – user81935 Dec 3 '15 at 20:26
  • shouldn't it be.. JOIN user_cons_columns col ON fk.constraint_name = col.constraint_name – user81935 Dec 3 '15 at 21:12
0

I'm not as up on my Oracle as I should be, but I think this query should give you the Primary/Foreign Key relationship lookup you're after.

SELECT *
FROM sys.all_constraints a
INNER JOIN sys.all_constraints b
    ON a.constraint_name = b.constraint_name
INNER JOIN sys.all_cons_columns c
    on c.constraint_name = b.constraint_name
WHERE a.constraint_type = 'P'
  AND b.constraint_type = 'R'
|improve this answer|||||
  • Thank you.I am confused, how to drill down based on the primay key columns value. Is there any easy way – user81935 Dec 3 '15 at 20:15
  • I don't think simple is exactly the right word. I believe you'd need to get the objectid of whatever column it was in question. Then take that ID and search in the other tables where that field served as a FK and from there query for the specific value. You'd end up having several sub-queries in order to accomplish this. The query pertaining to the data would probably be your outter query, then the others would be interior queries. nested within one another I believe. – agpoweredmg Dec 3 '15 at 20:29
-2
select fk.table_name, col.column_name
from USER_constraints fk
JOIN user_cons_columns col
    ON fk.constraint_name = col.constraint_name
where constraint_type='R'
 and r_constraint_name in (
     select constraint_name 
     from user_constraints 
     where constraint_type in ('P','U') 
       and table_name='TABLE_NAME'
);
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.