É 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.