Menu de navegação

[eversonpizaParticipante]

Olá amigos,

Tem algumas situações onde eu apago uma PK usando o comando drop constraint e ele dropa junto o índice, mas tem outras onde ele mantem o índice.

Alguém sabe me dizer pq isso acontece?

Preciso montar alguns scripts de atualização de base, e não sei se devo ou não colocar o ‘drop index’, pois se colocar e ele já ter sido removido junto com a constraint vai dar erro na execução.

Obrigado,
Everson

[Rodrigo AlmeidaParticipante]

Everson,

Isso é possível sim, devido a separação do índice na utilização de melhoria de consulta/restrição de dados ou na aplicação de integridade de dados ao modelo de dados relacional.

Resumindo, toda PRIMARY KEY que você cria, implicitamente para o Oracle é um UNIQUE INDEX para a coluna, ou seja, não será aceito dados duplicados para a coluna ou conjunto de colunas.

Porém, dependendo do modo que você criou o seu schema a partir do modelo de dados, quando se excluí uma Primary key, não necessáriamente está excluindo o seu índice junto, pois ele poderá permanecer para continuar restringindo duplicidade de dados, porém, não mais para manter a integridade geral do modelo em seus relacionamentos 1:N ou 1:1, e sim, apenas para não aceitar dados duplicados na coluna.

Abaixo, fiz uma simulação de como isso pode funcionar:


SQL> create table TESTE (a number(5), b date);

Tabela criada.

SQL> declare
2 contador integer;
3 begin
4 contador := 1;
5 while contador select count(*) from TESTE;

COUNT(*)

  1000

SQL> select table_name, constraint_name, constraint_type, status
2 from dba_constraints
3 where table_name = 'TESTE';

nÒo hß linhas selecionadas

SQL> select index_name, index_type, status
2 from dba_indexes
3 where table_name = 'TESTE';

nÒo hß linhas selecionadas

SQL> alter table TESTE add constraint TESTE_PK primary key (a);

Tabela alterada.

SQL> select table_name, constraint_name, constraint_type, status
2 from dba_constraints
3 where table_name = 'TESTE';

TABLE_NAME CONSTRAINT_NAME C STATUS

---

TESTE TESTE_PK P ENABLED

SQL> select index_name, index_type, status
2 from dba_indexes
3 where table_name = 'TESTE';

INDEX_NAME INDEX_TYPE STATUS

---

TESTE_PK NORMAL VALID

SQL> alter table TESTE drop primary key;

Tabela alterada.

SQL> select table_name, constraint_name, constraint_type, status
2 from dba_constraints
3 where table_name = 'TESTE';

nÒo hß linhas selecionadas

SQL> select index_name, index_type, status
2 from dba_indexes
3 where table_name = 'TESTE';

nÒo hß linhas selecionadas

SQL> create unique index TESTE_PK_IDX on TESTE (a);

═ndice criado.

SQL> select table_name, constraint_name, constraint_type, status
2 from dba_constraints
3 where table_name = 'TESTE';

nÒo hß linhas selecionadas

SQL> select index_name, index_type, status
2 from dba_indexes
3 where table_name = 'TESTE';

INDEX_NAME INDEX_TYPE STATUS

---

TESTE_PK_IDX NORMAL VALID

SQL> alter table TESTE add constraint TESTE_PK_IDX primary key (a);

Tabela alterada.

SQL> select table_name, constraint_name, constraint_type, status
2 from dba_constraints
3 where table_name = 'TESTE';

TABLE_NAME CONSTRAINT_NAME C STATUS

---

TESTE TESTE_PK_IDX P ENABLED

SQL> select index_name, index_type, status
2 from dba_indexes
3 where table_name = 'TESTE';

INDEX_NAME INDEX_TYPE STATUS

---

TESTE_PK_IDX NORMAL VALID

SQL> alter table TESTE drop primary key;

Tabela alterada.

SQL> select table_name, constraint_name, constraint_type, status
2 from dba_constraints
3 where table_name = 'TESTE';

nÒo hß linhas selecionadas

SQL> select index_name, index_type, status
2 from dba_indexes
3 where table_name = 'TESTE';

INDEX_NAME INDEX_TYPE STATUS

---

TESTE_PK_IDX NORMAL VALID

SQL> drop index TESTE_PK_IDX;

═ndice eliminado.

SQL> select table_name, constraint_name, constraint_type, status
2 from dba_constraints
3 where table_name = 'TESTE';

nÒo hß linhas selecionadas

SQL> select index_name, index_type, status
2 from dba_indexes
3 where table_name = 'TESTE';

nÒo hß linhas selecionadas

SQL> drop table TESTE purge;

Tabela eliminada.

Veja, que seu eu crio uma PK usando o comando ALTER TABLE, ele será uma chave primária que será utilizada no modelo e automaticamente, criará um índice único para a coluna.

Porém, quando faço o inverso, ou seja, primeiro crio o índice único, mas ele não faz parte do modelo de dados para garantir integridade, eu tenho um índice, mas não tenho umas constraint.

Se criou uma primary key, com o mesmo nome do índice único, automaticamente, aquele índice agora será uma primary key para a tabela e também irá validar o modelo relacional de dados, garantindo a integridade dos dados.

Se eu excluir ele, estou apenas retirando a integridade, porém meu índice continua.

Muitos softwares de Modelo de dados, como por exemplo o CA ErWin, quando você manda ele gerar os scripts do seu modelo de dados físico, ele irá gerar as Primary keys com um CREATE UNIQUE INDEX e depois um ALTER TABLE ADD CONSTRAINT para criar a integridade dos dados e conseguir criar as FKs posteriormente.

O script fica maior e será necessário acrescentar os DROP INDEX para poder excluir tudo como desejado.

Existem mais teorias de como tudo isso funciona, essa é uma parte resumida que deve lhe ajudar e entender como funciona.

Abraços,

Rodrigo Almeida

[eversonpizaParticipante]

Olá Rodrigo,
Grande explicação.

Na verdade essa base que estou fazendo o teste é uma base criada recentemente de um export da produção, vc sabe dizer como o import cria a PK, se é de uma unica vez, ou cria primeiro o índice e depois a constraint?

Tem no dicionário de dados algum atributo que me diz se o índice vai ser dropado junto com a constraint?

De qquer forma encontrei uma opção do drop primary key que deve resolver o meu problema, que é o ‘drop index’, segue exemplo:

alter table drop primary key drop index;

Acredito que com esse comando não terei problema com o índice.

Atn,
Everson

[Autor]

Posts