I need a way to select all the foreign key of a particular pk . In other words, I want to get a particular primary key and select all the foreign keys that "point" to it.
Preferably I want to get the name of the column that has the key, but can also be the name of the same key.
You can use the following select
select r.owner,
r.table_name as tabela,
r.constraint_name as nome_chave,
u.COLUMN_NAME
from user_constraints r,
user_constraints o,
user_cons_columns u
where
r.r_owner = o.owner and
u.CONSTRAINT_NAME = r.CONSTRAINT_NAME and
r.r_constraint_name = o.constraint_name and
o.constraint_type in ('P','U') and
r.constraint_type = 'R' and
o.table_name = '&NOME_DA_SUA_TABELA'
Based on Nesse Article
PLSQL Developer implements the following query to get the list of references:
SELECT table_name,
constraint_name,
status,
owner
FROM all_constraints
WHERE r_owner = :r_owner
AND constraint_type = 'R'
AND r_constraint_name IN (SELECT constraint_name
FROM all_constraints
WHERE constraint_type IN ( 'P', 'U' )
AND table_name = :r_table_name
AND owner = :r_owner)
ORDER BY table_name,
constraint_name
:r_owner is mentioned because in Oracle schema s different may have different tables that have the same name.
Source:
link