Find FK's a given table in the Bank?

[wbarrence]

Hello everyone,

Can anyone answer me as I do to locate all FK's from a particular table in the database? As output I wanted the table name and the name of the column that has FK.

Att,

[stcoutinho]

Hello WARRERENCE,

Use these queries to obtain the information you want:

Select all

 
--- Esta querie mostra todas as FKs de uma Tabela 
SELECT TABLE_NAME, 
       CONSTRAINT_NAME  
  FROM DBA_CONSTRAINTS  
 WHERE OWNER = '<NOME_DONO_TABELA_EM_MAIUSCULAS>'  
   AND TABLE_NAME = '<NOME_TABELA_EM_MAIUSCULAS>' 
   AND CONSTRAINT_TYPE = 'R' 
ORDER BY 1,2; 

Select all

 
--- Esta querie mostra todas as FKs de uma tabela e as colunas que compoe cada uma delas 
--- Lembrando que uma FK pode ser composta por mais de uma coluna 
SELECT CONSTRAINT_NAME,COLUMN_NAME,POSITION 
  FROM DBA_CONS_COLUMNS 
 WHERE CONSTRAINT_NAME IN 
       (SELECT CONSTRAINT_NAME  
          FROM DBA_CONSTRAINTS  
        WHERE OWNER = '<NOME_DONO_TABELA_EM_MAIUSCULAS>'  
            AND TABLE_NAME = '<NOME_TABELA_EM_MAIUSCULAS>' 
           AND CONSTRAINT_TYPE = 'R') 
 ORDER BY 1,2; 

] If by accident you do not have access to the DBA views, you can connect as the user itself and run the following queries:

Select all

 
--- Esta querie mostra todas as FKs de uma Tabela 
SELECT TABLE_NAME, 
       CONSTRAINT_NAME  
  FROM USER_CONSTRAINTS  
WHERE TABLE_NAME = '<NOME_TABELA_EM_MAIUSCULAS>' 
   AND CONSTRAINT_TYPE = 'R' 
ORDER BY 1,2; 

Select all

 
--- Esta querie mostra todas as FKs de uma tabela e as colunas que compoe cada uma delas 
--- Lembrando que uma FK pode ser composta por mais de uma coluna 
SELECT CONSTRAINT_NAME,COLUMN_NAME,POSITION 
  FROM USER_CONS_COLUMNS 
 WHERE CONSTRAINT_NAME IN 
       (SELECT CONSTRAINT_NAME  
          FROM USER_CONSTRAINTS  
        WHERE TABLE_NAME = '<NOME_TABELA_EM_MAIUSCULAS>' 
            AND CONSTRAINT_TYPE = 'R') 
 ORDER BY 1,2; 

99]]
Hugs,

Sergio Coutinho

[wbarrence]

Hello Sergio,

First I want to thank your help, stress that you helped a lot here at work.

I only had a question, what does the 'r' of the constraint_type = 'r' means? And what are the values ??this field can take on?

Att,

[gpereira]

Hi friend.

"Refamential" r ", that is, it is a FK type constraint!

for values, from what I know can receive values ??below:

C = Check
P = Primary Key
R = Foreign Key
U = Unique Key
o = read only in a view
v = check in a view

Correct me It's wrong.

Hugs.

[wbarrence]

Valeu Gustavo,

Thank you for the information.

Att,

[dejambo]

Galera .. Returning the subject, is there any way to show the table name and the user's field that is referenced in FK?

[dr_gori]

See if this query helps you: http://en.glufke.net/oracle/viewtopic.php?t=883

[crishpg]

Child_table column shows which table belongs to fk

Select all

SELECT 
  UC.CONSTRAINT_NAME CONSTRAINT_NAME, 
        UC.TABLE_NAME CHILD_TABLE, 
        UCC.COLUMN_NAME CHILD_COLUMN, 
        UCR.TABLE_NAME PARENT_TABLE, 
        UCCR.COLUMN_NAME PARENT_COLUMN 
FROM USER_CONSTRAINTS UC 
INNER JOIN USER_CONSTRAINTS UCR 
  ON UCR.CONSTRAINT_NAME = UC.R_CONSTRAINT_NAME 
INNER JOIN USER_CONS_COLUMNS UCC 
  ON UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME 
     AND UC.TABLE_NAME = UCC.TABLE_NAME 
INNER JOIN USER_CONS_COLUMNS UCCR 
  ON UCCR.CONSTRAINT_NAME = UCR.CONSTRAINT_NAME 
     AND UCR.TABLE_NAME = UCCR.TABLE_NAME 
     AND UCCR.POSITION = UCC.POSITION 
WHERE UCR.TABLE_NAME IN ('EMPLOYEES') --COLOCAR A TABELA PARA SER ANALISADA 
     AND UCR.CONSTRAINT_TYPE IN( 'P','U') 
ORDER BY CHILD_TABLE, CONSTRAINT_NAME, CHILD_COLUMN