Hi
I must to show thefind foreign key and primary key that could be candidates for a join, example
Create Table TMP_A
( id int not null, A int, B int, C int,
Constraint pk_a Primary Key(ID),
constraint uk_ab Unique (A,B)
);
Create Table TMP_B
( a_id int, F int,
Constraint fk_n_a Foreign Key (A_ID) References TMP_A(ID)
);
I tried a query
select UCC.table_name,UCC.CONSTRAINT_NAME, UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE, UC.SEARCH_CONDITION, UC2.TABLE_NAME as REFERENCES_TABLE
from USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC, USER_CONSTRAINTS UC2
where UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
and UC.R_CONSTRAINT_NAME = UC2.CONSTRAINT_NAME(+)
and UC.CONSTRAINT_TYPE in ('P','R')
and UCC.TABLE_NAME IN ( 'TMP_A','TMP_B')
order by UCC.CONSTRAINT_NAME
I would like to show columns that are part of FK, and the respective columns that are part of PK,
like
Obviously keys (PK, FK ) can have more than one column in some cases, but I have no idea how I would do it.
How can I to do It ?
Using: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production