sql - tabla - no se puede quitar el objeto hay una referencia a él en una restricción foreign key

¿Cómo puedo romper la integridad referencial brevemente, dentro de una transacción, sin desactivar la restricción de clave externa?

Question

Tengo una mesa con 3 columnas:

ID, PARENT_ID, NAME

PARENT_ID tiene una relación de clave externa con ID en la misma tabla. Esta tabla está modelando una jerarquía.

En ocasiones, la ID de un registro cambiará. Quiero poder actualizar el ID un registro, luego actualizar los registros dependientes '' PARENT_ID para señalar el nuevo ID .

El problema es que cuando intento actualizar la ID de un registro, se rompe la integridad y falla inmediatamente.

Me doy cuenta de que podría insertar un nuevo registro con la nueva ID , luego actualizar a los niños y luego eliminar el registro anterior, pero tenemos un montón de factores desencadenantes que podrían estropearse si lo hiciera.

¿Hay alguna forma de actualizar temporalmente el padre con la promesa de actualizar los hijos (obviamente fallaría en la confirmación) sin desactivar la clave externa brevemente?

Answer 1

Debe usar una restricción diferible (vea la respuesta de Chi).
De lo contrario, para agregar un valor que fallará la restricción de clave externa, tiene que deshabilitar o soltar y volver a crear la restricción de clave externa.

Situaciones como estas emplean una clave sustituta que puede ser alterada por los usuarios según sea necesario, sin afectar la integridad referencial. Para ampliar esta idea, actualmente la configuración es:

• ID (pk)
• PARENT_ID (clave externa, columna de ID de referencias, por lo que es autorreferencial)

..y las reglas comerciales son que la identificación puede cambiar. Lo cual es fundamentalmente malo desde una perspectiva de diseño: la clave principal es inmutable, única y no puede ser nula. Entonces, la solución a la situación cuando estás construyendo tu modelo de datos es usar:

• ID (pk)
• PARENT_ID (clave externa, columna de ID de referencias, por lo que es autorreferencial)
• SURROGATE_KEY (restricción única)

El SURROGATE_KEY es la columna que admite el cambio sin afectar la integridad referencial: la relación padre-hijo está intacta. Esto significa que un usuario puede modificar la clave sustituta para su deleite de corazón sin necesidad de restricciones diferidas, habilitar / deshabilitar o soltar / recrear restricciones de clave externa, ON ACTUALIZAR CASCADA ...

Como regla general, en el modelado de datos, NUNCA muestra valores clave primarios al usuario debido a situaciones como estas. Por ejemplo, tengo un cliente que quiere que su número de trabajo cambie a comienzos de año, con el año al comienzo del número (IE: 201000001 sería el primer trabajo creado en 2010). ¿Qué sucede cuando el cliente vende la empresa y el nuevo propietario necesita un esquema diferente para su contabilidad? O bien, ¿qué pasa si la numeración no se puede mantener durante la transición a un proveedor de base de datos diferente?

Answer 2

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

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 un sintaxis INSERT multi-tabla que nos permite insertar los registros padre e hijo 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 la clave principal del registro principal pero no puede debido a la existencia de los registros secundarios: Y no puede actualizar los registros secundarios porque no hay una clave principal. 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 una sola declaración:

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 en la declaración UPDATE es un poco torpe, pero los kludges generalmente lo son. El punto es que no deberíamos tener que actualizar las columnas de clave principal muy a menudo. De hecho, como la inmutabilidad es una de las características de la "clave primaria", no deberíamos tener que actualizarlas en absoluto. Necesitarlo es una falla del modelo de datos. Una forma de evitar tales fallas es usar una clave primaria sintética (sustituta) y simplemente aplicar la unicidad de la clave natural (también conocida como negocio) 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 datos en la base de datos sin tablas intermedias. Realmente no deberíamos necesitarlos para las tareas de aplicación regulares.

Answer 3

Las recomendaciones para usar una clave sustituta son excelentes, IMO.

De manera más general, el problema con esta tabla es que carece de una clave principal. Recuerde que una clave principal debe ser de tres maneras:

1. Único
2. No nulo
3. Inalterable

Las bases de datos con las que estoy familiarizado hacen cumplir (1) y (2), pero no creo que hagan cumplir (3), lo cual es desafortunado. Y eso es lo que te está pateando en el trasero: si cambias tu "clave principal", debes buscar todas las referencias a ese campo clave y hacer alteraciones equivalentes si no quieres romper la integridad. La solución, como han dicho otros, es tener una verdadera clave primaria, una que sea única, no nula y que no cambie.

Hay razones para todas estas pequeñas reglas. Esta es una gran oportunidad para comprender la parte "inmutable" de las reglas clave principales.

Comparte y Disfruta.

Answer 4

Lo que quieres es una " restricción diferible ".

Puede elegir entre los dos tipos de restricciones diferibles, ''INICIALMENTE INMEDIATA'' e ''INICIALMENTE DIFERIDA'' para controlar el comportamiento predeterminado, ya sea que la base de datos deba revisar la restricción de forma predeterminada después de cada declaración, o si debe establecer restricciones de manera predeterminada al final de la transacción.

Answer 5

Respondió más lento que Chi, pero consideró que sería bueno incluir una muestra de código para que la respuesta se encuentre en SO.

Como Chi respondió, 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 diferibilidad se define en el momento de creación de la restricción y no se puede modificar más adelante. El valor predeterminado no es diferible. Para cambiar a restricciones diferibles, tendrá que hacer un drop de una sola vez y agregar restricción. (Correctamente 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.

Answer 6

Si esta fuera otra base de datos además de Oracle, podría declarar la clave externa con ON UPDATE CASCADE . Luego, si cambia la identificación de un padre, propagaría el cambio de forma atómica al parent_id del niño.

Desafortunadamente, Oracle implementa eliminaciones en cascada pero no actualizaciones en cascada.

(Esta respuesta solo tiene fines informativos, ya que en realidad no resuelve su problema).