Olá, pessoal! Hoje vou abordar um tema muito curioso e pouco conhecido por desenvolvedores no mundo Oracle: o modo de validação das constraints. Existe uma máxima no conceito relacional que diz que “não pode existir um filho sem pai”, entretanto, não é bem assim… Isso depende quando a consistência irá ocorrer.

No Oracle as constraints podem ser configuradas para efetuarem suas consistências em dois momentos:

• Immediate: Durante a execução do comando de DML, que é o default quando criada a constraint.

alter table children_table add constraint children_table_fk foreign key (pk_father) references father_table (pk_father);

• Deferred: No final da transação, quando é executado o comando de commit. Existem duas formas de efetuar a validação das constraints no final da transação:

A primeira é criar a constraint já com o seu modo em deferred; neste caso, será válido para todas as transações. Por default, as constraints são criadas com o modo de validação imediata e não é possível modificar este modo; deve-se apagar e recriá-la.

alter table children_table add constraint children_table_fk foreign key (pk_father) references father_table (pk_father) initially deferred;

A segunda, e mais aconselhável, é criar a constraint com a opção de deferrable, ou seja, permite que seja modificado o modo da constraint para transações específicas.

alter table children_table add constraint children_table_fk foreign key (pk_father) references father_table (pk_father) deferrable;

Para alterar o modo da constraint durante uma transação, basta executar o comando set constraint conforme abaixo:

set constraint children_table_fk deferred;

Exemplos:

Criação das tabelas:

drop table children_table;
drop table father_table;

Criar a tabela pai:

create table father_table (pk_father          number,
                           description_father varchar2(40));

Definir a primary key da tabela pai:

alter table father_table add constraint father_table_pk primary key (pk_father);

Criar a tabela filha:

create table children_table(pk_children          number,
                            pk_father            number,
                            description_children varchar2(40));

Definir a primary key da tabela filha:

alter table children_table add constraint children_table_pk primary key (pk_children);

Definir a foreign key da tabela filha, com a opção de deferrable permitindo a mudança de seu modo de validação em transações especifícas.

alter table children_table add constraint children_table_fk foreign key (pk_father) references father_table (pk_father) deferrable;

Limpar as tabelas:

delete children_table;
delete father_table;

commit;

Testes

• Primeiro caso de teste: inserir um registro na tabela pai e na tabela filha para teste de integridade das constraints. Deve ocorrer com sucesso.

insert into father_table values (1,'Test 1');
 
insert into children_table values (1,1,'Test 1');
 
commit;

• Segundo caso de teste: atualizar o registro da tabela filho para um ID de um pai que ainda não existe. Deve ocorrer erro durante o comando de update, pois a constraint está setada com o modo de validação imediata.

 select count(1)
   from father_table
  where pk_father = 2;


update children_table 
   set pk_father   = 2
 where pk_children = 1;

• Terceiro caso de teste: atualizar o registro da tabela filho para um ID de um pai ainda não existente. Deve ocorrer erro apenas quando o comando de commit for executado, pois o modo de validação da transação foi alterado para deferred.

set constraint children_table_fk deferred;

 select count(1)
   from father_table
  where pk_father = 2;

update children_table 
   set pk_father   = 2
 where pk_children = 1;
 
  
commit;

• Quarto caso de teste: Atualizar o registro da tabela filho para um ID de um pai que ainda não existe, após a atualização efetuar o insert do registro pai. Não deverá ocorrer erro, pois o modo de validação da transação foi alterado para deferred e no momento em que for executado o commit, o registro existirá na tabela pai.

set constraint children_table_fk deferred;

 select count(1)
   from father_table
  where pk_father = 2;

update children_table 
   set pk_father   = 2
 where pk_children = 1;
 
 
insert into father_table values (2,'Test 2');

commit;

• Quinto caso de teste: apagar os registros da tabela pai, mesmo que existam filhos. Deve ocorrer erro apenas quando o comando de commit for executado, pois o modo de validação da transação foi alterado para deferred.

set constraint children_table_fk deferred;

delete father_table;

commit;

 

Conclusão

Quando é importante modificar o tipo da constraint?

Geralmente, em customizações de pacotes fechados de sistemas, como no EBS ou SAP. Existem momentos em que pode ser necessário inserir um registro filho antes da existência do pai atráves de uma trigger, por exemplo. Em processos de cargas onde a melhor alternativa é a execução da carga das tabelas filhos primeiro.

Riscos em modificar o tipo da constraint

É necessário analisar o código corretamente antes de alterar a constraint para o tipo deferred, pois isso pode ocasionar alguns problemas como: a não gravação de logs de erros esperados durante o processo de atualização ou inserção de algum registro, ou a execução de processos autônomos indevidamente, pois o erro ocorrerá apenas quando um commit for efetuado – pode acontecer de existir a ativação de um processo autônomo entre o comando e o commit.

Cuidado com as triggers, pois a mudança da ordem da execução dos comandos pode impactar em suas lógicas. Oneração no processo pode acontecer de ter que processar uma grande massa de dados até que um commit ocorra, e assim o erro vir a acontecer.

É realmente muito interessante a possibilidade desse tipo de variação no modo de validação das contraints. Infelizmente ela ainda não é muito conhecida por muitos desenvolvedores. Uma boa alternativa para a solução de alguns problemas que não seriam resolvidos facilmente.

Referências

• http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10003.htm
• http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm