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.
Escrito por carlosal 
Escrito por carlosal 
Escrito por carlosal 
