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.