Thanks for the question, vinod.
Asked: July 01, 2016 - 6:42 am UTC
Last updated: July 02, 2016 - 1:22 pm UTC
Version: Oracle 11g
Viewed 1000+ times
You Asked
Hi,
I will pass table_name tabl1 and its primary key value pk=(some value).
My requirement is suppose there is table1, its has primary key pk1 and also it has unique keys ak1,ak2 ,
And its one of unique keys is foreign key to table2, now i require it should go to go table2 and get its unique keys and similar to above table1, finally output should get is
select pk1 from table1 where ak1=(val) and ak2=(select (pk of table2) from table2 where ak=..) like this process.
Is it possible to get.....
Help me to solve this requirement..
and Chris said...
You can find the columns a foreign key references using the *_constraints and *_cons_columns views:
create table t1 (
w int primary key,
x int unique
);
create table t2 (
y int references t1(x),
z int primary key
);
select ucc.table_name, ucc.column_name
from user_constraints uc
join user_cons_columns ucc
on uc.r_constraint_name = ucc.constraint_name
where uc.table_name = 'T2'
and uc.constraint_type = 'R';
TABLE_NAME COLUMN_NAME
---------- -----------
T1 X
To find the primary key of the referenced table, look for the constraint_type = 'P' on the referenced table:
select ucc.table_name, ucc.column_name
from user_constraints uc1
join user_constraints uc2
on uc1.r_constraint_name = uc2.constraint_name
join user_constraints uc3
on uc2.table_name = uc3.table_name
join user_cons_columns ucc
on uc3.constraint_name = ucc.constraint_name
where uc1.table_name = 'T2'
and uc1.constraint_type = 'R'
and uc3.constraint_type = 'P';
TABLE_NAME COLUMN_NAME
---------- -----------
T1 W
Repeat as necessary.