É possível que uma restrição (constraint) no Oracle esteja no estado ENABLE VALIDATE e ainda assim permitir que dados existentes violem a restrição?

Olá,

Este artigo foi escrito após eu participar de um tópico (Thread) no Fórum de Discussão da Oracle na qual um membro do fórum postou a seguinte pergunta: Can some one explain this behavior?, ou seja, "alguém poderia explicar este comportamento?". 

Antes de expor o meu ponto de vista quanto ao tópico discutido, vamos recapitular sobre alguns conceitos sobre os estados das restrições (constraints) possíveis de se criar em um banco de dados Oracle. Bom, sabemos que o Oracle trabalha com os 5 tipos de restrições padrões como qualquer outro SGBD relacional: são elas NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY e CHECK. Uma restrição de integridade pode estar ativada (ENABLE) ou desativada (DISABLE). Se uma restrição estiver ativada (ENABLED), os dados serão verificados quando informados ou atualizados no banco de dados. Os dados que não forem compatíveis com a regra da restrição não poderão ser informados. Se uma restrição estiver desativada (DISABLED), os dados que não forem compatíveis com a restrição poderão ser informados no banco de dados. Para fins de demonstração, irei utilizar a restrição de integridade referencial (FOREIGN KEY) nos exemplos abaixo. Então para começar, uma restrição de integridade pode estar em um dos seguintes estados: 

DISABLE NOVALIDATE: Uma restrição neste estado não é verificada. Portanto, os dados contidos na tabela, assim como os novos dados informados ou atualizados, podem não estar em conformidade com as regras definidas pela restrição. Para exemplificar este estado, segue a demonstração abaixo:

SQL> create table pai (id number constraint pk_pai primary key);

Tabela criada.

SQL> create table filho (id number constraint fk_filho_pai references pai);

Tabela criada.

SQL> insert into pai values (1);

1 linha criada.

SQL> insert into filho values (1);

1 linha criada.

SQL> insert into filho values (2);
insert into filho values (2)
*
ERRO na linha 1:
ORA-02291: restrição de integridade (TEST.FK_FILHO_PAI) violada - chave mãe não localizada

SQL> alter table filho modify constraint fk_filho_pai disable novalidate;

Tabela alterada.

SQL> insert into filho values (2);

1 linha criada.

SQL> select constraint_name,status,validated from user_constraints where table_name='FILHO';

CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- --------------
FK_FILHO_PAI                   DISABLED  NOT VALIDATED

Podemos perceber que após desabilitar a constraint FK_FILHO_PAI, foi possível violar a restrição de integridade inserindo o registro de ID 2 na tabela filho mesmo que o registro de ID 2 não exista na tabela pai.

DISABLE VALIDATE: Se houver uma restrição neste estado, nenhuma modificação será permitida nas colunas restringidas, ou melhor, qualquer comando DML não será permitido.

SQL> alter table filho modify constraint fk_filho_pai disable validate;

Tabela alterada.

SQL> insert into filho values (2);
insert into filho values (2)
*
ERRO na linha 1:
ORA-25128: Não há inserção/atualização/deleção na tabela com restrição (TEST.FK_FILHO_PAI)
desativada e validada

SQL> update filho set id=2;
update filho set id=2
*
ERRO na linha 1:
ORA-25128: Não há inserção/atualização/deleção na tabela com restrição (TEST.FK_FILHO_PAI)
desativada e validada

SQL> delete from filho;
delete from filho
*
ERRO na linha 1:
ORA-25128: Não há inserção/atualização/deleção na tabela com restrição (TEST.FK_FILHO_PAI)
desativada e validada

SQL> select constraint_name,status,validated from user_constraints where table_name='FILHO';

CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
FK_FILHO_PAI                   DISABLED VALIDATED

ENABLE NOVALIDATE: Se uma restrição estiver neste estado, os novos dados que violarem a restrição não poderão ser informados. No entanto, a tabelas podem conter dados inválidos, ou seja, dados que violam a restrição.

SQL> select * from pai;

ID
----------
         1

SQL> select * from filho;

ID
----------
         1

-- Irei desabilitar a constraint para poder colocar um valor inválido
SQL> alter table filho modify constraint fk_filho_pai disable novalidate;

Tabela alterada.

SQL> insert into filho values (5);

1 linha criada.

SQL> alter table filho modify constraint fk_filho_pai enable novalidate;

Tabela alterada.

SQL> select * from filho;

ID
----------
         1
         5

SQL> insert into filho values (6);
insert into filho values (6)
*
ERRO na linha 1:
ORA-02291: restrição de integridade (TEST.FK_FILHO_PAI) violada - chave mãe não localizada

De acordo com o exemplo acima, podemos perceber que o ID 5 na tabela filho viola a restrição de integridade pelo fato de o mesmo não existir na tabela pai, mas por outro lado, novos dados que violam a restrição não podem ser informados, como no caso do ID 6, que não existe um valor correspondente na tabela pai. O estado NOVALIDATE é realmente muito útil quando por algum motivo é necessário manter dados inválidos existentes na tabela, mas garantir que novos dados inseridos ou atualizados estejam em conformidade com a restrição.

SQL> select constraint_name,status,validated from user_constraints where table_name='FILHO';

CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
FK_FILHO_PAI                   ENABLED  NOT VALIDATED

ENABLE VALIDATE: Este é os estado padrão quando se cria qualquer tipo de restrição. Portanto, se uma restrição estiver neste estado, a linha que violar a restrição não poderá ser inserida na tabela. No entanto, enquanto a restrição estiver desativada como mostrado no primeiro exemplo, a linha poderá ser inserida e essa linha será considerada uma exceção à restrição. Se a restrição estiver no estado ENABLE VALIDATE, as violações resultantes dos dados informados enquanto a restrição estava desativada permanecerão. Em resumo, para que uma restrição esteja no estado ENABLE VALIDATE, as linhas que violam a restrição deverão ser alteradas ou até mesmo deletadas para que a restrição possa ficar habilitada.

SQL> select * from pai;

ID
----------
         1

SQL> select * from filho;

ID
----------
         1
         5

SQL> alter table filho modify constraint fk_filho_pai enable validate;
alter table filho modify constraint fk_filho_pai enable validate
                      *
ERRO na linha 1:
ORA-02298: não é possível validar (TEST.FK_FILHO_PAI) - chaves mães não localizadas

SQL> delete from filho where id=5;

1 linha deletada.

SQL> alter table filho modify constraint fk_filho_pai enable validate;

Tabela alterada.

SQL> select constraint_name,status,validated from user_constraints where table_name='FILHO';

CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
FK_FILHO_PAI                   ENABLED  VALIDATED

Para complementar, ENABLE implica VALIDATE, a menos que NOVALIDATE esteja especificado, e DISABLE implica NOVALIDATE, a menos que VALIDATE esteja especificado.

SQL> alter table filho modify constraint fk_filho_pai disable;

Tabela alterada.

SQL> select constraint_name,status,validated from user_constraints where table_name='FILHO';

CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
FK_FILHO_PAI                   DISABLED NOT VALIDATED

SQL> alter table filho modify constraint fk_filho_pai enable;

Tabela alterada.

SQL> select constraint_name,status,validated from user_constraints where table_name='FILHO';

CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
FK_FILHO_PAI                   ENABLED  VALIDATED

No mais, é possível que uma restrição esteja no estado ENABLE VALIDATE e ainda assim permitir que dados existentes violem a restrição? Por incrível que pareça, a resposta é SIM (bug confirmado), e realmente não consigo ver um sentido nisso. Será possível que a Oracle permitiu este tipo de inconformidade durante todos esses anos em todas as versões, até mesmo no recém lançado Oracle 11g? Utilizando as tabelas criadas anteriormente, irei demonstrar este comportamento um tanto esquisito.

SQL> select * from pai;

ID
----------
         1

SQL> select * from filho;

ID
----------
         1

SQL> alter table filho modify constraint fk_filho_pai disable validate;

Tabela alterada.

SQL> delete from pai;

1 linha deletada.

SQL> alter table filho modify constraint fk_filho_pai enable validate;

Tabela alterada.

SQL> select * from pai;

não há linhas selecionadas

SQL> select * from filho;

ID
----------
         1

SQL> select constraint_name,status,validated from user_constraints where table_name='FILHO';

CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
FK_FILHO_PAI                   ENABLED  VALIDATED

Podemos perceber acima que o ID 1 na tabela filho não possui um registro correspondente na tabela pai, ou seja, o mesmo é um registro órfão. A pergunta é: Porque foi possível alterar o estado da restrição de DISABLE VALIDATE para ENABLE VALIDATE sem que o Oracle emitisse um erro informando que há registros existentes na tabela filho que violam a restrição? A resposta é que na transição de DISABLE VALIDATE para ENABLE VALIDATE, os dados existentes não foram checados pelo fato de os mesmos já estarem validados (estado VALIDATE). Para resolver o problema será necessário alterar o estado da restrição de modo que a mesma possa checar os dados existentes:

SQL> alter table filho modify constraint fk_filho_pai enable novalidate;

Tabela alterada.

SQL> alter table filho modify constraint fk_filho_pai enable validate;
alter table filho modify constraint fk_filho_pai enable validate
                      *
ERRO na linha 1:
ORA-02298: não é possível validar (TEST.FK_FILHO_PAI) - chaves mães não localizadas

No meu ponto de vista, eu considero este comportamento uma falha grave no que se refere à segurança da integridade dos dados e, a menos que alguém me convença do contrário, acho que a Oracle deveria dar um pouco mais de atenção sobre este "problema", ou melhor, se isso realmente for um "problema".

Para finalizar, eu postei um comentário na página de documentação da Oracle no que se refere ao Gerenciamento de Restrições de Integridade relatando o estranho comportamento.

Estágios de inicialização do Oracle (NOMOUNT | MOUNT | OPEN)

Olá,

Neste artigo, farei uma explanação sobre os estágios envolvidos na abertura de um banco de dados Oracle. São eles NOMOUNT, MOUNT e OPEN. O que realmente cada um destes estágios realiza?

Apenas para relembrar, uma instância Oracle consiste na estrutura de memória SGA (System Global Area) e nos processos de segundo plano como SMON, PMON, DBWn, LGWR, CKPT, entre outros usados para gerenciar o banco dados. A instância só pode abrir e usar um banco de dados por vez. Em resumo, um servidor Oracle consiste em uma instância Oracle (estruturas de memória e processos) e um banco de dados Oracle (arquivos físicos).

NOMOUNT
 

Este estágio inicializa a instância sem montar o banco de dados. Isso significa que todas as estruturas de memória e os processos de segundo plano estão posicionados, mas ainda sem ter um banco de dados associado a essa instância. É através deste estágio que é possível criar um banco de dados Oracle. Portanto, uma instância só deverá ser iniciada no estágio NOMOUNT durante a criação do banco de dados ou na recriação de arquivos de controle (control files). Em resumo, a inicialização de uma instância Oracle inclui as seguintes tarefas:

1. Leitura do arquivo de inicialização SPFILE_SID.ora ou SPFILE.ora ou init.ora, exatamente nesta ordem porque, por padrão, à partir do Oracle 9i, primeiro ele tenta abrir o spfile_sid.ora, se o arquivo não for encontrado ele tentará abrir o spfile.ora, se o mesmo não for encontrado ele tentará ler o arquivo init.ora. Se o Oracle não encontrar nenhum arquivo de inicialização ou se o DBA não fornecer um valor explícito para PFILE, a inicialização falhará. A especificação do parâmetro PFILE com STARTUP sobrepõe o comportamento default.
2. Alocação da SGA.
3. Inicialização dos processos de segundo plano.
4. Abertura do arquivo de alerta (ALERT_SID.ora) e dos arquivos de rastreamento.

Observe que nem os arquivos de controle nem os arquivos de dados e redo log on-line estão abertos neste estágio.

MOUNT
  

Após a instância já ter sido inicializada pelo estágio anterior, então é realizada a leitura do arquivo de controle de modo a "associar" o banco de dados à instância já inicializada anteriormente. A opção "startup mount" é muito útil em situações onde é necessário executar algumas operações específicas de manutenção como renomeação de arquivos de dados, ativação e desativação de opções de arquivamento de redo log (ARCHIVELOG) e operações de recuperação do banco de dados. É importante salientar que se a instância já estiver inicializada "startup nomount", mas o banco não estiver montado, então será necessário utilizar o comando "alter database mount". Resumindo, montar o banco de dados inclui as tarefas a seguir:

1. Associação do banco de dados a uma instância iniciada anteriormente.
2. Localização e abertura dos arquivos de controle (control files) especificados no arquivo de inicialização.
3. Leitura dos arquivos de controle para obtenção dos nomes e status dos arquivos de dados (data files) e arquivos de redo log on-line (log files).

É importante salientar que neste estágio não é verificada a existência dos arquivos de dados e arquivos de redo log on-line, mas se os arquivos de controle não forem localizados conforme a localização especificada no parâmetro CONTROL_FILES no arquivo de inicialização, então um erro será retornado, o banco de dados não será montado e a instância permanecerá no estado NOMOUNT. Se o DBA utilizar a opção "startup mount" para inicializar o Oracle e quiser abrir o banco de dados, então será necessário utilizar o comando "alter database open".

OPEN 

Neste terceiro e último estágio, o banco de dados é disponibilizado para acesso dos usuários. A operação normal de um banco de dados significa que uma instância é iniciada e o banco de dados é montado e aberto. Portanto, qualquer usuário válido pode conectar ao banco de dados e executar operações comuns de acesso a dados. Durante esse estágio final, o servidor Oracle verifica se é possível abrir todos os arquivos de dados e arquivos de redo log on-line, além de verificar a consistência do banco de dados onde neste caso, o processo de segundo plano SMON (System Monitor) iniciará a recuperação da instância se necessário. Resumindo, a abertura do banco de dados inclui as seguintes tarefas:

1. Abertura dos arquivos de dados (data files).
2. Abertura dos arquivos de redo log on-line (log files).
3. Chama o processo SMON se necessário para a recuperação da instância em caso de uma falha de instância ocorrida enquanto o banco de dados estava aberto e em operação anteriormente.

É importante salientar que, se algum arquivo de dados ou arquivo de redo log on-line não estiver presente, então o banco de dados não abrirá, permanecendo no estado MOUNT e o servidor Oracle retornará um erro.

Ativação (startup)

SQL> startup
Instância ORACLE iniciada.

Total System Global Area  188743680 bytes
Fixed Size                  1286460 bytes
Variable Size              88084164 bytes
Database Buffers           96468992 bytes
Redo Buffers                2904064 bytes
Banco de dados montado.
Banco de dados aberto.

Desativação (shutdown)

SQL> shutdown
Banco de dados fechado.
Banco de dados desmontado.
Instância ORACLE desativada.