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,
Find FK's a given table in the Bank?
- stcoutinho
- Moderador
- Posts: 850
- Joined: Wed, 11 May 2011 5:15 pm
- Location: são Paulo - SP
Hello WARRERENCE,
Use these queries to obtain the information you want:
] If by accident you do not have access to the DBA views, you can connect as the user itself and run the following queries:
99]]
Hugs,
Sergio Coutinho
Use these queries to obtain the information you want:
--- 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;
--- 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;
--- 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;
--- 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;
Hugs,
Sergio Coutinho
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,
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
- Rank: Programador Sênior
- Posts: 61
- Joined: Sat, 10 Dec 2011 10:56 am
Gustavo Antonio Pereira
OCA Oracle Database 10g Administrator Certified Associate
OCA Oracle Database 10g Administrator Certified Associate
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.
"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.
- dr_gori
- Moderador
- Posts: 5024
- Joined: Mon, 03 May 2004 3:08 pm
- Location: Portland, OR USA
- Contact:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
See if this query helps you: http://en.glufke.net/oracle/viewtopic.php?t=883
-
- Rank: Estagiário Sênior
- Posts: 8
- Joined: Fri, 12 Oct 2012 11:26 am
- Location: Santa ROSA
- Contact:
Cristiano Schmitt
Administrador de Banco de Dados e Tecnologia
Administrador de Banco de Dados e Tecnologia
Child_table column shows which table belongs to fk
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
-
- Information
-
Who is online
Users browsing this forum: No registered users and 17 guests