Menu de navegação

[airoospParticipante]

Boa tarde pessoal,

Tentei executar o drop de uma tabela no banco 10g (windows) e apareceu o erro abaixo:

ora-02449 – unique/primary keys in table referenced by foreign keys

O erro ocorreu mesmo após ter desabilitado todas as constraints que fazem referência a tabela utilizada no processo.

Esta atualização é do banco de produção para homologação. Poderia ter feito a atualização de todas as tabelas do sistema, mas minha colega informou que apenas 3 tabelas precisavam ser atualizadas.

Na internet encontrei algumas consultas para saber informações sobre as dependências, segue uma que utilizei:

select table_name, constraint_name, status, owner
from all_constraints
where r_owner = ‘FAT’
and constraint_type = ‘R’
and r_constraint_name in
(
select constraint_name
from all_constraints
where constraint_type in (‘P’, ‘U’)
and table_name = ‘EMPRESA’
and owner = ‘FAT’
)
order by table_name, constraint_name

Se alguém tiver alguma dica, agradeço.

Obrigado.

Airton

[spernegaParticipante]

Bom dia,

Quando eu preciso dropar alguma tabela eu dropo primeiro as FK das filhas.
E depois de, no seu caso, importar a tabela, eu recrio as FK das filhas.

Acho que não basta desabilitar as FK.

[José Laurindo ChiappaModerador]

Sim, é isso mesmo : quando vc faz um DROP numa tabela se houver Constraints referenciando uma das chaves da tabela (seja a PK, seja a UK) o RDBMS ** não ** vai permitir o DROP, e isso Não É Erro, é validação Básica de integridade de dados…
Um exemplo :

scott@BDTEST:srv-testl-DESENV:SQL>DROP TABLE DEPT2;
DROP TABLE DEPT2
*
ERRO na linha 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

scott@BDTEST:srv-testl-DESENV:SQL>

==>> Isso está nos dizendo que a tabela DEPT2 tem constraints de PK (Primary key) ou de UK (Unique Key) referenciadas em alguma outra tabela…. Em ves de sair copiando sem entender, vamos analisar a query – a primeira necessidade é descobrirmos o NOME da PK ou da UK da tabela DEPT2 que estou tentando dropar :

system@BDTEST:srv-test-DESENV:SQL>select A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE,
2 FROM DBA_CONSTRAINTS A
4 WHERE A.OWNER = ‘SCOTT’
5 AND A.TABLE_NAME = ‘DEPT2’
6 —
7 AND A.CONSTRAINT_TYPE in (‘P’, ‘U’)
8 /

CONSTRAINT_NAME C

---

PK_DEPT2 P

system@BDTEST:srv-test-DESENV:SQL>

==> IMPORTANTE : eu prefiro conectar como usuário SYSDBA ou equivalente e usar a DBA_CONSTRAINTS porque a ALL_CONSTRAINTS *** não mostra *** as constraints todas do banco, sim ???
OK, Bem simples até agora, né ? Para eu saber o Nome da Tabela, o dono dessa tabela (uma FK pode *** SIM **** ser filha de uma PK ou UK de OUTRO SCHEMA!!!) e o nome da constraint FK que usa/acessa essa constraint PK_DEPT2 basta eu acessar novamente a tabela DBA_CONSTRAINTS procurando por esse nome de constraint…
Além disso, o outro conceito que vc tem que saber é que nesse outro registro da DBA_CONSTRAINTS vai estar marcado nas colunas R_OWNER e R_CONSTRAINT_NAME o nome do usuário e o nome da constraint que usam/referenciam essa PK_DEPT2 que queremos saber…
Tudo isso pode ser feito com IN, com EXISTS, com um simples JOIN, vou optar pelo JOIN, e no caso vou usar a sintaxe Oracle com a qual estou mais acostumado, facilmente poderia ser também um ANSI JOIN, colocando a keyword JOIN ao invés de usar o operador de igual :

system@BDTEST:srv-test-DESENV:SQL>select A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE,
2 B.OWNER OWNER_REF, B.TABLE_NAME TABLE_REF, B.CONSTRAINT_NAME CONSTRAINT_REF
3 FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B
4 WHERE A.OWNER = ‘SCOTT’
5 AND A.TABLE_NAME = ‘DEPT2’
6 —
7 AND A.CONSTRAINT_TYPE in (‘P’, ‘U’)
8 AND B.R_OWNER = A.OWNER
9 AND B.R_CONSTRAINT_NAME = A.CONSTRAINT_NAME
10 /

CONSTRAINT_NAME C OWNER_REF TABLE_REF CONSTRAINT_REF

---

PK_DEPT2 P SCOTT EMP2 FK_DEPTNO2

system@BDTEST:srv-test-DESENV:SQL>

==> Realmente, só desabilitando a constraint não adianta :

system@BDTEST:srv-test-DESENV:SQL>alter table SCOTT.EMP2 disable constraint FK_DEPTNO2;

Tabela alterada.

system@BDTEST:srv-test-DESENV:SQL>

scott@BDTEST:srv-testl-DESENV:SQL>DROP TABLE DEPT2;
DROP TABLE DEPT2
*
ERRO na linha 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

==> tem que dropar mesmo :

system@BDTEST:srv-test-DESENV:SQL>alter table SCOTT.EMP2 drop constraint FK_DEPTNO2;

Tabela alterada.

system@BDTEST:srv-test-DESENV:SQL>

==> Agora vai :

scott@BDTEST:srv-testl-DESENV:SQL>DROP TABLE DEPT2;

Tabela eliminada.

scott@BDTEST:srv-testl-DESENV:SQL>

**** IMPORTANTE ***** :

a) ÓBVIO, antes de dropar seja o que for vc ** TEM ** que ter o DDL necessário para reconstruir : vc pode extrair ele com um export, com DBMS_METADATA, com um SELECT nas tabelas/views internas do RDBMS… Óbvio…

b) eu criei esse exemplo APENAS PARA REFERÊNCIA e para APRENDIZADO : desde a versão 9ir2 (ou 10gR1, não lembro de cabeça mas foi em alguma dessas versões pré-históricas aí) que o DBA **** não precisa MAIS *** dessa traquitana toda, pois a Oracle INCLUI no DROP a cláusula CASCADE CONSTRAINTS, que já AUTOMATIZA essa pesquisa, ok ? Veja só :

==> volto ao que estava antes :

scott@BDTEST:srv-testl-DESENV:SQL>CREATE TABLE DEPT2 (
2 DEPTNO NUMBER(2) CONSTRAINT PK_DEPT2 PRIMARY KEY,
3 DNAME VARCHAR2(14) ,
4 LOC VARCHAR2(13) CONSTRAINT FK_LOC REFERENCES LOCAL) ;

Tabela criada.

scott@BDTEST:srv-testl-DESENV:SQL>INSERT INTO DEPT2 VALUES (10,’ACCOUNTING’ ,’NEW YORK’);
scott@BDTEST:srv-testl-DESENV:SQL>INSERT INTO DEPT2 VALUES (20,’RESEARCH’ ,’DALLAS’);
scott@BDTEST:srv-testl-DESENV:SQL>INSERT INTO DEPT2 VALUES (30,’SALES’ ,’CHICAGO’);
scott@BDTEST:srv-testl-DESENV:SQL>INSERT INTO DEPT2 VALUES (40,’OPERATIONS’ ,’BOSTON’);
scott@BDTEST:srv-testl-DESENV:SQL>alter table EMP2 add CONSTRAINT FK_DEPTNO2 FOREIGN KEY (DEPTNO) REFERENCES DEPT2;

==> Tento dropar :

scott@BDTEST:srv-testl-DESENV:SQL>drop table DEPT2;
drop table DEPT2
*
ERRO na linha 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

==> ** AÍ VEM O COMANDO MÁGICO ** , que implica que o RDBMS vai dropar as constraints que dependem das PKs/UKs da DEPT2 sozinho … Aproveito para demonstrar também o (INJUSTAMENTE!!) sempre esquecido PURGE, pois sem ele a tabela dropada fica na LATA DE LIXO do database, ocupando ESPAÇO potencialmente valioso :

scott@BDTEST:srv-testl-DESENV:SQL>drop table dept2 CASCADE CONSTRAINTS PURGE;

Tabela eliminada.

scott@BDTEST:srv-testl-DESENV:SQL> select * from recyclebin;

não ha linhas selecionadas

scott@BDTEST:srv-testl-DESENV:SQL> drop table LOCAL cascade constraints;

Tabela eliminada.

scott@BDTEST:srv-testl-DESENV:SQL> select * from recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION

---

TYPE TS_NAME CREATETIME

---

DROPTIME DROPSCN PARTITION_NAME CAN CAN

---

RELATED BASE_OBJECT PURGE_OBJECT SPACE

---

BIN$uWO0tPmlToiFlJkBUGmfzw==$0 PK_LOC DROP
INDEX USER_DATA 2017-08-17:10:02:33
2017-08-17:11:21:03 1,2411E+12 NO YES
107652 107652 107653 8

OBJECT_NAME ORIGINAL_NAME OPERATION

---

TYPE TS_NAME CREATETIME

---

DROPTIME DROPSCN PARTITION_NAME CAN CAN

---

RELATED BASE_OBJECT PURGE_OBJECT SPACE

---

BIN$q9PGDdQPQpGgRQ3fJa0wdw==$0 LOCAL DROP
TABLE USER_DATA 2017-08-17:10:02:33
2017-08-17:11:21:03 1,2411E+12 YES YES
107652 107652 107652 8

scott@BDTEST:srv-testl-DESENV:SQL>

[]s

Chiappa

[Autor]

Posts