Olá,
Dando prosseguimento ao que foi escrito no artigo de Fevereiro sobre os estados das constraints, neste artigo irei mostrar como obter as informações dos relacionamentos existentes entre as tabelas no schema do usuário. Sabemos que uma restrição de PRIMARY KEY é uma coluna ou um conjunto de colunas que identifica exclusivamente cada linha de uma tabela e que uma restrição de FOREIGN KEY, ou restrição de integridade referencial, designa uma coluna ou combinação de colunas com uma chave estrangeira e estabelece um relacionamento entre uma chave primária ou uma chave exclusiva na mesma tabela ou em uma tabela diferente. Como obter do dicionário de dados as informações referentes aos relacionamentos entre estas tabelas? A resposta está nas views de dicionário de dados *_CONSTRAINTS e*_CONS_COLUMNS, onde o * [asterisco] deverá ser substituído por USER_ (objetos de propriedade do usuário), ALL_ (objetos acessíveis ao usuário) ou DBA_ (objetos restritos para usuários com atribuição DBA).
C:\>sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Seg Abr 28 13:31:40 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Conectado a:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create user scott identified by tiger
2 default tablespace users quota unlimited on users;
Usuário criado.
SQL> grant connect,resource to scott;
Concessão bem-sucedida.
SQL> connect scott/tiger
Conectado.
SQL> create table tabela_A (id number constraint pk_a primary key);
Tabela criada.
SQL> create table tabela_B (id2 number constraint fk_b_a references tabela_a);
Tabela criada.
SQL> create table tabela_C (id3 number constraint fk_c_a references tabela_a);
Tabela criada.
-- Obtendo informações de quais tabelas fazem referência à tabela_A
SQL> select
2 r.owner,
3 r.table_name,
4 r.constraint_name
5 from
6 user_constraints r,
7 user_constraints o
8 where
9 r.r_owner = o.owner and
10 r.r_constraint_name = o.constraint_name and
11 o.constraint_type in ('P','U') and
12 r.constraint_type = 'R' and
13 o.table_name = 'TABELA_A';
OWNER TABLE_NAME CONSTRAINT_NAME
-------------------- ------------------------ --------------------------
SCOTT TABELA_B FK_B_A
SCOTT TABELA_C FK_C_A
-- Obtendo informações das tabelas, colunas e seus relacionamentos
SQL> select
2 a.table_name||' ('||
3 rtrim(max(decode(c.position,1,c.column_name))||','||
4 max(decode(c.position,2,c.column_name))||','||
5 max(decode(c.position,3,c.column_name))||','||
6 max(decode(c.position,4,c.column_name)),',')||') referencia '||
7 b.table_name||' ('||
8 rtrim(max(decode(d.position,1,d.column_name))||','||
9 max(decode(d.position,2,d.column_name))||','||
10 max(decode(d.position,3,d.column_name))||','||
11 max(decode(d.position,4,d.column_name)),',')||')' relacionamentos
12 from
13 user_constraints a,
14 user_constraints b,
15 user_cons_columns c,
16 user_cons_columns d
17 where
18 a.r_constraint_name=b.constraint_name and
19 a.constraint_name=c.constraint_name and
20 b.constraint_name=d.constraint_name and
21 a.constraint_type='R' and
22 b.constraint_type in ('P', 'U')
23 group by a.table_name, b.table_name
24 order by 1;
RELACIONAMENTOS
------------------------------------------------------------
TABELA_B (ID2) referencia TABELA_A (ID)
TABELA_C (ID3) referencia TABELA_A (ID)
SQL> desc user_constraints Nome Nulo? Tipo ----------------------------- -------- -------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE INDEX_OWNER VARCHAR2(30) INDEX_NAME VARCHAR2(30) INVALID VARCHAR2(7) VIEW_RELATED VARCHAR2(14)
SQL> desc user_cons_columns Nome Nulo? Tipo ----------------------------- -------- -------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) POSITION NUMBER
Leia mais: http://eduardolegatti.blogspot.com/2008/04/obtendo-informaes-de-relacionamentos.html#ixzz3x2x52lbK
Under Creative Commons License: Attribution Non-Commercial
Outra fonte importante:
http://blog.mclaughlinsoftware.com/2009/03/05/validating-foreign-keys/
Be the first to comment on "Obtendo informações dos relacionamentos existentes (FK) no Oracle"