Vistas actualizables, ‘key-preserved tables’ y ORA-01779

El concepto de vistas actualizables se apoya el el concepto de tablas reservadas (protegidas) por clave (‘key-preserved tables’). Este concepto de Vistas actualizables es uno de los primeros con los que se topan los que vienen de entornos SQLServer/Sybase, donde se permite la construcción UPDATE … FROM TABLA1, TABLA2…

Así es: en SQLServer/Sybase se pueden incorporar ‘joins’ de diferentes tablas a la hora de efectuar un UPDATE. En Oracle, en cambio, eso se hace mediante las mencionadas ‘vistas actualizables’.

Lo que en SQLServer/Sybase se escribe como

UPDATE
  FROM PRUEBA01 a,
       PRUEBA02 b
   SET b.C_TXT = a.C_TXT
 WHERE a.ID_N = b.ID_N
go

en Oracle se escribe (entre otras formas) como:

UPDATE ( SELECT a.ID_N a_ID_N,
                a.C_TXT a_C_TXT,
                b.ID_N b_ID_N,
                b.C_TXT b_C_TXT
           FROM PRUEBA01 a,
                PRUEBA02 b
          WHERE a.ID_N = b.ID_N)
    SET b_C_TXT = a_C_TXT;

¿Mejor? ¿Peor? Como siempre: simplemente distinto. El problema aparece cuando estas sentencias empiezan a arrojar los ‘ORA-01779: no se puede modificar una columna que se corresponde con una tabla no reservada por clave’ (‘ORA-01779: cannot modify a column which maps to a non key-preserved table’). Es aquí donde la mayoría de la gente se comienza a liar con esto de las ‘key-preserved tables’.

Lo primero que hay que decir es que el concepto de ‘key-preserved tables’ no tiene que ver con los datos, sino con la estructura de los objetos. No importa que la relación entre las filas de dos tablas sea ‘de facto’ 1:1, si el optimizador (que es quien decide) interpreta que las tablas no son ‘key-preserved’ porque su estructura no lo indica, saltará el error.

Veámoslo con un ejemplo:

SQL*Plus: Release 10.2.0.1.0 - Production on Jue Oct 25 14:31:19 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Introduzca el nombre de usuario: carlos@db01.xxxxxx
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

carlos@db01.xxxxxx> CREATE TABLE PRUEBA01(ID_N NUMBER NOT NULL,
 2  C_TXT VARCHAR2(25));

Tabla creada.

carlos@db01.xxxxxx> CREATE TABLE PRUEBA02(ID_N NUMBER NOT NULL,
 2  C_TXT VARCHAR2(25));

Tabla creada.

carlos@db01.xxxxxx> INSERT INTO PRUEBA01 VALUES (1,'UNO');

1 fila creada.

carlos@db01.xxxxxx> INSERT INTO PRUEBA02 VALUES (1,NULL);

1 fila creada.

carlos@db01.xxxxxx> COMMIT;

Confirmación terminada.

carlos@db01.xxxxxx> SELECT * FROM PRUEBA01;

      ID_N C_TXT
---------- -------------------------
         1 UNO

carlos@db01.xxxxxx> SELECT * FROM PRUEBA02;

      ID_N C_TXT
---------- -------------------------
         1

carlos@db01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N,
  2                  a.C_TXT a_C_TXT,
  3                  b.ID_N b_ID_N,
  4                  b.C_TXT b_C_TXT
  5             FROM PRUEBA01 a,
  6                  PRUEBA02 b
  7            WHERE a.ID_N = b.ID_N)
  8      SET b_C_TXT = a_C_TXT;
    SET b_C_TXT = a_C_TXT
        *
ERROR en línea 8:
ORA-01779: no se puede modificar una columna que se corresponde con una tabla no reservada por clave

Lo segundo que hay que entender es que una tabla es ‘key-preserved’ si todas las claves de dicha tabla podrían ser claves del resultado del ‘join’. Esto, dicho así, puede no parecer tan fácil de entender, pero la traducción a ‘román paladino’ sería que una fila de una tabla no puede ser actualizada más de una vez como resultado de la condición del ‘join’ (esto es: que coincida con más de una fila de la segunda tabla).

¿Y dónde mira el optimizador? A la estructura de la tabla (las PRIMARY KEYS):

carlos@db01.xxxxxx> ALTER TABLE PRUEBA01
 2  ADD CONSTRAINT PRUEBA01_PK PRIMARY KEY(ID_N);

Tabla modificada.

carlos@db01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N,
  2                  a.C_TXT a_C_TXT,
  3                  b.ID_N b_ID_N,
  4                  b.C_TXT b_C_TXT
  5             FROM PRUEBA01 a,
  6                  PRUEBA02 b
  7            WHERE a.ID_N = b.ID_N)
  8      SET b_C_TXT = a_C_TXT;

1 fila actualizada.

Y, por último: el mensaje de error es un poco confuso ya que:

carlos@db01.xxxxxx> ALTER TABLE PRUEBA01 DROP CONSTRAINT PRUEBA01_PK;

Tabla modificada.

carlos@db01.xxxxxx> ALTER TABLE PRUEBA02
 2  ADD CONSTRAINT PRUEBA02_PK PRIMARY KEY(ID_N);

Tabla modificada.

carlos@db01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N,
  2                  a.C_TXT a_C_TXT,
  3                  b.ID_N b_ID_N,
  4                  b.C_TXT b_C_TXT
  5             FROM PRUEBA01 a,
  6                  PRUEBA02 b
  7            WHERE a.ID_N = b.ID_N)
  8      SET b_C_TXT = a_C_TXT;
    SET b_C_TXT = a_C_TXT
        *
ERROR en línea 8:
ORA-01779: no se puede modificar una columna que se corresponde con una tabla no reservada por clave

La columna que estamos modificando pertenece a una tabla que SÍ tiene PK, pero NO ES ‘key-preserved’ (ya que si hubiese dos filas en PRUEBA01 con ID_N=1 provocaría que la misma fila en PRUEBA02 fuese actualizada con valores de más de una fila de PRUEBA01).

Pero hay que tener en cuenta que una fila de una ‘key-preserved table’ SÍ PUEDE actualizar más de una fila de la otra tabla:

carlos@db01.xxxxxx> ALTER TABLE PRUEBA02 DROP CONSTRAINT PRUEBA02_PK;

Tabla modificada.

carlos@db01.xxxxxx> ALTER TABLE PRUEBA01
 2  ADD CONSTRAINT PRUEBA01_PK PRIMARY KEY(ID_N);

Tabla modificada.

carlos@db01.xxxxxx> INSERT INTO PRUEBA02 VALUES (1,'ONE');

1 fila creada.

carlos@db01.xxxxxx> SELECT * FROM PRUEBA02;

      ID_N C_TXT
---------- -------------------------
         1 UNO
         1 ONE

carlos@db01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N,
  2                  a.C_TXT a_C_TXT,
  3                  b.ID_N b_ID_N,
  4                  b.C_TXT b_C_TXT
  5             FROM PRUEBA01 a,
  6                  PRUEBA02 b
  7            WHERE a.ID_N = b.ID_N)
  8      SET b_C_TXT = a_C_TXT;

2 filas actualizadas.

carlos@db01.xxxxxx> SELECT * FROM PRUEBA02;

      ID_N C_TXT
---------- -------------------------
         1 UNO
         1 UNO

Nota: El ‘hint’ BYPASS_UJVC evita toda la comprobación por parte del optimizador de las ‘key-preserved tables’, pero es un ‘hint’ indocumentado y -en teoría- los mortales no deberíamos utilizarlo, o hacerlo ‘bajo nuestra propia responsabilidad’…

carlos@db01.xxxxxx>  SELECT * FROM PRUEBA01;

      ID_N C_TXT
---------- -------------------------
         1 UNO

carlos@db01.xxxxxx>  SELECT * FROM PRUEBA02;

      ID_N C_TXT
---------- -------------------------
         1

carlos@db01.xxxxxx>  ALTER TABLE PRUEBA01 DROP CONSTRAINT PRUEBA01_PK;

Tabla modificada.

carlos@db01.xxxxxx>  ALTER TABLE PRUEBA02 DROP CONSTRAINT PRUEBA02_PK;

Tabla modificada.

carlos@db01.xxxxxx> UPDATE /*+ BYPASS_UJVC */ ( SELECT a.ID_N a_ID_N,
  2                  a.C_TXT a_C_TXT,
  3                  b.ID_N b_ID_N,
  4                  b.C_TXT b_C_TXT
  5             FROM PRUEBA01 a,
  6                  PRUEBA02 b
  7            WHERE a.ID_N = b.ID_N)
  8      SET b_C_TXT = a_C_TXT;

1 fila actualizada.

Saludos.

Carlos.

Anuncios

9 respuestas a Vistas actualizables, ‘key-preserved tables’ y ORA-01779

  1. Dudu dice:

    Muy interesante artículo…

  2. […] verdadera causa del error está en que, como ocurría aquí, dos filas de la tabla PRUEBA02 actualizarían una misma fila de la tabla […]

  3. Gracias por este artículo, de mucha ayuda!!!

    saludos

  4. David dice:

    Muchas gracias, me ha sido muy util

  5. Irwin Quintana dice:

    Muchisimas gracias, me fue de mucha ayuda; envío mi implementación personal por si a alguien le sirve:

    Observaciones:
    – la tabla CG (tabla origen) -alias new- tiene una llave primaria en el campo CEGE,
    – la tabla servicios_norte2 (tabla destino) -alias old- tiene un campo n_cg que es del mismo tipo que CG.cg
    – la tabla servicios_norte2 -alias old- tiene algunos registros con valores en null en el campo n_cg por lo que solo se actualizaran los renglones donde haya una correspondencia entre new.CEGE = old.n_cg

    Aqui los queries y una pequeña explicacion:

    — Se llena el campo n_cg de la tabla que vamos a actualizar con valores correctos ( que pueden estar relacionados en la otra tabla )

    update servicios_norte2 set n_cg = to_number ( cg )
    where isnumeric ( cg ) = 1;

    UPDATE
    (
    SELECT
    new.cege as new_cege,
    old.n_cg,
    new.centro as new_centro,
    old.centro,
    new.desc_centro as new_desc_centro,
    old.desc_centro,
    new.descrip_cg as new_descrip_cg,
    old.descrip_cg,
    new.rama as new_rama,
    old.rama
    FROM
    SERVICIOS_NORTE2 old INNER JOIN
    CG new
    ON new.CEGE = old.n_cg
    )
    SET
    CENTRO = NEW_CENTRO,
    DESC_CENTRO = NEW_DESC_CENTRO,
    DESCRIP_CG = NEW_DESCRIP_CG,
    RAMA = NEW_RAMA;

    ESPERO LES SIRVA,
    SALUDOS DESDE MEXICO.

  6. […] Como se ve, primero se hace un ‘JOIN’ entre PRUEBA01 y PRUEBA03 (las que importan) pero, al haber introducido PRUEBA02 en el ‘JOIN’, se produce un ‘CROSS JOIN’ de las dos filas del ‘JOIN’ de PRUEBA01 y PRUEBA03 con todas las filas de PRUEBA02 .Habrá exactamente 10 filas: las dos del primer ‘JOIN’ por las cinco de PRUEBA02 y serán iguales dos a dos, por eso la parte final (MERGE Update) falla con el error “Failure 7547 Target row updated by multiple source rows” (¿Dónde hemos visto esto antes?). […]

  7. Luis Espinosa dice:

    Gracias. muy buen aporte

  8. guindous dice:

    Muchas gracias por la info. Junto a este artículo (https://carlosal.wordpress.com/2007/10/30/merge-y-ora-30926/) también de su autoría pude sacarme todas las dudas respecto a los Update “complicados” en ORACLE.

  9. olmos dice:

    Gracias por el articulo!

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: