Deferrable Constraints no Oracle
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 foreignkey (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á valido 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, devendo-se apagá-la 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;Exemplo
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 esta 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, por exemplo.
Existe momentos que pode ser necessário inserir um registro filho antes da existência do pai atrávez 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.
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
