Saltar al contenido

¿Cómo puedo romper la integridad referencial brevemente, dentro de una transacción, sin deshabilitar la key ¿restricción?

Al fin luego de tanto batallar ya encontramos la solución de este asunto que muchos de nuestros usuarios de nuestro sitio web han tenido. Si tienes algún detalle que compartir puedes compartir tu comentario.

Solución:

Lo que quiere es una ‘restricción diferida’.

Puede elegir entre los dos tipos de restricciones diferibles, ‘INICIALMENTE INMEDIATO’ y ‘INICIALMENTE DIFERIDO’ para impulsar el comportamiento predeterminado: si la base de datos debe verificar la restricción por defecto después de cada declaración, o si debe estar predeterminada para verificar solo las restricciones al final de la transacción.

Respondió más lento que Chi, pero consideró que sería bueno incluir un ejemplo de código, para que la respuesta se pudiera encontrar en SO.

Como respondió Chi, las restricciones diferibles lo hacen posible.

SQL> drop table t;

Table dropped.

SQL> create table T (ID number
  2      , parent_ID number null
  3      , name varchar2(40) not null
  4      , constraint T_PK primary key (ID)
  5      , constraint T_HIREARCHY_FK foreign key (parent_ID)
  6          references T(ID) deferrable initially immediate);

Table created.

SQL> insert into T values (1, null, 'Big Boss');

1 row created.

SQL> insert into T values (2, 1, 'Worker Bee');

1 row created.

SQL> commit;

Commit complete.

SQL> -- Since initially immediate, the following statement will fail:
SQL> update T
  2  set ID = 1000
  3  where ID = 1;
update T
*
ERROR at line 1:
ORA-02292: integrity constraint (S.T_HIREARCHY_FK) violated - child record found


SQL> set constraints all deferred;

Constraint set.

SQL> update T
  2  set ID = 1000
  3  where ID = 1;

1 row updated.

SQL> update T
  2  set parent_ID = 1000
  3  where parent_ID = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from T;

        ID  PARENT_ID NAME
---------- ---------- ----------------------------------------
      1000            Big Boss
         2       1000 Worker Bee

SQL> -- set constraints all deferred during that transaction
SQL> -- and the transaction has commited, the next
SQL> -- statement will fail
SQL> update T
  2  set ID = 1
  3  where ID = 1000;
update T
*
ERROR at line 1:
ORA-02292: integrity constraint S.T_HIREARCHY_FK) violated - child record found

Creo, pero no pude encontrar la referencia, que la aplazabilidad se define en el momento de la creación de la restricción y no se puede modificar más adelante. El valor predeterminado es no diferible. Para cambiar a restricciones diferibles, deberá hacer una eliminación única y agregar restricción. (Debidamente programado, controlado, etc.)

SQL> drop table t;

Table dropped.

SQL> create table T (ID number
  2      , parent_ID number null
  3      , name varchar2(40) not null
  4      , constraint T_PK primary key (ID)
  5      , constraint T_HIREARCHY_FK foreign key (parent_ID)
  6          references T(ID));

Table created.

SQL> alter table T drop constraint T_HIREARCHY_FK;

Table altered.

SQL> alter table T add constraint T_HIREARCHY_FK foreign key (parent_ID)
  2      references T(ID) deferrable initially deferred;

Table altered.

El consejo común con escenarios como este es emplear restricciones diferibles. Sin embargo, creo que estas situaciones son casi siempre una falla de la lógica de la aplicación o del modelo de datos. Por ejemplo, insertar un registro hijo y un registro padre en la misma transacción puede ser un problema si lo ejecutamos como dos declaraciones:

Mis datos de prueba:

SQL> select * from t23 order by id, parent_id
  2  /

        ID  PARENT_ID NAME
---------- ---------- ------------------------------
       110            parent 1
       111            parent 2
       210        110 child 0
       220        111 child 1
       221        111 child 2
       222        111 child 3

6 rows selected.

SQL>

La forma incorrecta de hacer las cosas:

SQL> insert into t23 (id, parent_id, name) values (444, 333, 'new child')
  2  /
insert into t23 (id, parent_id, name) values (444, 333, 'new child')
*
ERROR at line 1:
ORA-02291: integrity constraint (APC.T23_T23_FK) violated - parent key not
found


SQL> insert into t23 (id, parent_id, name) values (333, null, 'new parent')
  2  /

1 row created.

SQL>

Sin embargo, Oracle admite una sincronización INSERT de varias tablas que nos permite insertar los registros principal y secundario en la misma declaración, obviando así la necesidad de restricciones diferibles:

SQL> rollback
  2  /

Rollback complete.

SQL> insert all
  2      into t23 (id, parent_id, name)
  3          values (child_id, parent_id, child_name)
  4      into t23 (id, name)
  5          values (parent_id, parent_name)
  6  select  333 as parent_id
  7          , 'new parent' as parent_name
  8          , 444 as child_id
  9          , 'new child' as child_name
 10  from dual
 11  /

2 rows created.

SQL>

La situación en la que se encuentra es similar: desea actualizar el key del registro principal, pero no puede debido a la existencia de los registros secundarios: y no puede actualizar los registros secundarios porque no hay registros principales key. 22 capturas:

SQL> update t23
  2      set id = 555
  3  where id = 111
  4  /
update t23
*
ERROR at line 1:
ORA-02292: integrity constraint (APC.T23_T23_FK) violated - child record found


SQL> update t23
  2      set parent_id = 555
  3  where parent_id = 111
  4  /
update t23
*
ERROR at line 1:
ORA-02291: integrity constraint (APC.T23_T23_FK) violated - parent key not
found


SQL>

Una vez más la solución es hacerlo en un solo comunicado:

SQL> update t23
  2      set id = decode(id, 111, 555, id)
  3          , parent_id = decode(parent_id, 111, 555, parent_id)
  4  where id = 111
  5     or parent_id = 111
  6  /

4 rows updated.

SQL> select * from t23 order by id, parent_id
  2  /

        ID  PARENT_ID NAME
---------- ---------- ------------------------------
       110            parent 1
       210        110 child 0
       220        555 child 1
       221        555 child 2
       222        555 child 3
       333            new parent
       444        333 new child
       555            parent 2

8 rows selected.

SQL>

La sintaxis de la instrucción UPDATE es un poco torpe, pero las torpezas suelen serlo. El punto es que no deberíamos tener que actualizar la key columnas muy a menudo. De hecho, como la inmutabilidad es una de las características de key-ness “no deberíamos tener que actualizarlos en absoluto. Necesitar hacerlo es una falla del modelo de datos. Una forma de evitar tales fallas es usar un primario sintético (sustituto) keyy simplemente hacer cumplir la singularidad de lo natural (también conocido como negocio) key con una restricción única.

Entonces, ¿por qué Oracle ofrece restricciones diferibles? Son útiles cuando realizamos migraciones de datos o cargas masivas de datos. Nos permiten limpiar los datos de la base de datos sin preparar tablas. Realmente no deberíamos necesitarlos para las tareas habituales de la aplicación.

Tienes la posibilidad comunicar este ensayo si te fue útil.

¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)



Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *