Introdução ao conceito de restrições de integridade adiáveis (DEFERRABLE CONSTRAINTS)

Olá,

Neste artigo irei demonstrar a aplicação e uso da restrição (constraint) adiável (deferrable) que é um recurso disponível desde a versão 8 do Oracle. Veja 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> select constraint_name,deferrable,deferred
  2  from user_constraints
  3  where table_name in ('PAI','FILHO');

CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
PK_PAI                         NOT DEFERRABLE IMMEDIATE
FK_FILHO_PAI                   NOT DEFERRABLE IMMEDIATE

Podemos ver acima que, por padrão, toda constraint, seja ela uma chave primária ou chave estrangeira, sempre é criada como NOT DEFERRABLE IMMEDIATE, ou seja, a violação da restrição é checada imediatamente e não é adiável. Bom, mas o que isso significa? Isso significa que a validação de violação da restrição será checada a cada instrução DML executada como demonstrado no exemplo abaixo:

SQL> insert into pai values (1);

1 linha criada.

SQL> insert into pai values (1);
insert into pai values (1)
*
ERRO na linha 1:
ORA-00001: restrição exclusiva (SCOTT.PK_PAI) violada

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

Podemos ver acima que os dois últimos comandos falharam pois os mesmos violaram as respectivas restrições de integridade. Isso acontece porque não podemos inserir um registro duplicado (violando a chave primária) e muito menos inserir na tabela filha, um registro que não possui um correspondente na tabela pai (violando a chave estrangeira). Bom, e se por algum motivo, quiséssemos que a validação das restrições acontecessem somente ao final da transação? Neste caso poderíamos manipular os registros em qualquer ordem, ou seja, inserir registros na tabela filha antes mesmo de inserir registros na tabela pai, ou deletar registros da tabela pai mesmo que existam registros filhos, etc...

Para executar tal cenário, bastará recriarmos a respectiva restrição como sendo adiável (deferrable). Irei demonstrar abaixo como poderemos fazer isso através de alguns procedimentos simples.

SQL> alter table filho drop constraint fk_filho_pai;

Tabela alterada.

SQL> alter table filho add constraint fk_filho_pai
  2  foreign key (id) references pai
  3  deferrable;

Tabela alterada.

SQL> select constraint_name,deferrable,deferred
  2  from user_constraints
  3  where table_name = 'FILHO';

CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
FK_FILHO_PAI                   DEFERRABLE     IMMEDIATE

SQL> set constraints all deferred;

Conjunto de restrições.

SQL> insert into filho values (5);

1 linha criada.

SQL> select * from pai;

não há linhas selecionadas

SQL> commit;
commit
*
ERRO na linha 1:
ORA-02091: transação repetida
ORA-02291: restrição de integridade (SCOTT.FK_FILHO_PAI) violada - chave mãe não localizada

Podemos verificar acima que a validação da restrição de integridade referencial (FK_FILHO_PAI) só foi checada após eu ter efetuado o COMMIT da transação. Vale a pena salientar que o comando SET CONSTRAINTS somente tem validade durante a transação corrente. Se eu repetir o mesmo comando de inserção novamente, veremos que a instrução falhará de forma imediata:

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

Se quisermos que as restrições continuem adiáveis durante toda a sessão, precisaremos utilizar o comando abaixo:

SQL> alter session set constraints=deferred;

Sessão alterada.

SQL> insert into filho values (5);

1 linha criada.

SQL> insert into pai values (5);

1 linha criada.

SQL> commit;

Commit concluído.

SQL> insert into filho values (6);

1 linha criada.

SQL> insert into pai values (6);

1 linha criada.

SQL> commit;

Commit concluído.

Para voltar ao modo padrão, bastará executar o comando "alter session set constraints=immediate". Vale a pena salientar que não é possível utilizar este recurso para as restrições não sejam adiáveis.

SQL> alter table filho drop constraint fk_filho_pai;

Tabela alterada.

SQL> alter table filho add constraint fk_filho_pai
  2  foreign key (id) references pai;

Tabela alterada.

SQL> select constraint_name,deferrable,deferred
  2  from user_constraints
  3  where table_name = 'FILHO';

CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
FK_FILHO_PAI                   NOT DEFERRABLE IMMEDIATE

SQL> alter table filho modify constraint fk_filho_pai initially deferred;
alter table filho modify constraint fk_filho_pai initially deferred
*
ERRO na linha 1:
ORA-02447: não pode diferir uma restrição que não é diferível 

Portanto, as palavras chave INITIALLY DEFERRED e NOT DEFERRABLE são mutuamente exclusivas. No mais, se quisermos que a restrição fique eternamente adiável sem que seja necessário executar qualquer comando que altere a sessão do usuário, bastará apenas criar a restrição de integridade como INITIALLY DEFERRED como demonstrado abaixo:

SQL> alter table filho drop constraint fk_filho_pai;

Tabela alterada.

SQL> alter table filho add constraint fk_filho_pai
  2  foreign key (id) references pai
  3  deferrable initially deferred;

Tabela alterada.

SQL> select constraint_name,deferrable,deferred
  2  from user_constraints
  3  where table_name = 'FILHO';

CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
FK_FILHO_PAI                   DEFERRABLE     DEFERRED

SQL> insert into filho values (7);

1 linha criada.

SQL> insert into filho values (8);

1 linha criada.

SQL> insert into pai values (7);

1 linha criada.

SQL> insert into pai values (8);

1 linha criada.

SQL> commit;

Commit concluído.

SQL> select * from pai;

        ID
----------
         7
         8

SQL> select * from filho;

        ID
----------
         7
         8

Em resumo:

• INITIALLY IMMEDIATE significa que a validação será checada imediatamente a cada instrução DML executada.

• INITIALLY DEFERRED significa que a validação será checada somente ao final da transação (COMMIT).

Quando é que este recurso poderá vir a ser útil?

Como demonstrado no artigo, dependendo da lógica e da regra de negócio de uma aplicação às vezes precisaremos inserir registros filhos antes dos registros pais, ou até mesmo deletar registros pais antes mesmo de deletar os registros filhos, etc... Uma outra característica é a velocidade. Qualquer um que já tenha tratado com a inserção de grandes quantidades de dados em uma tabela, sabe do custo do carregamento (inserção) ou até mesmo de deleção de registros em tabelas quando existe uma checagem das restrições de integridade existentes. Neste contexto, adiar a validação destas restrições de integridade para depois que os dados já tiverem sido carregados ou manipulados, poderia economizar um tempo considerável em se tratando de grandes volumes de dados.