Integridad virtual con la base de datos Oracle 11g

Supongamos que nuestra base de datos Oracle dispone de una tabla en la que uno de los campos está formado realmente por muchos subcampos concatenados. El campo en cuestión no puede modificarse pero necesitamos validar y forzar la integridad de uno de los subcampos. Por ejemplo, queremos que los valores devueltos al ejecutar la función SUBSTR(column,7,3) sobre dicho campo estén incluidos en una tabla de referencia o lookup table.

Para versiones de la base de datos Oracle anteriores a la 11g no existía una forma sencilla de lidiar con este tipo de requerimientos ya que la validación de valores almacenados en una determinada columna, obligaba a que dichos valores estuvieran almacenados en una columna real que apuntase a la tabla de referencia o lookup table. En nuestro problema no existe una columna real sino que estamos tratando con una función aplicada sobre una columna. En la versión de la base de datos Oracle 10g y anteriores, lo más razonable sería crear una columna adicional en la tabla y, mediante el uso de un trigger PL/SQL, insertar o actualizar el valor retornado por la función SUBSTR en dicha nueva columna. A continuación podéis ver el código correspondiente a la implementación de esta solución:

SQL > CREATE TABLE tabla_referencia
2   ( validacion VARCHAR2(3) PRIMARY KEY,
3     descripcion VARCHAR2(20) );
Table created.

SQL > CREATE TABLE tabla_datos
2   ( concatenados VARCHAR2(20) PRIMARY KEY,
3     validacion VARCHAR2(3) NOT NULL
4     REFERENCES tabla_referencia );
Table created.

SQL > CREATE TRIGGER tabla_datos
2   BEFORE INSERT OR UPDATE 
3   OF concatenados ON tabla_datos
4   FOR EACH ROW
5   BEGIN
6     :new.validacion := 
7       SUBSTR(:new.concatenados,7,3);
8   END;
9   /
Trigger created.

SQL > INSERT INTO tabla_referencia (validacion)
2   VALUES ('XYZ');
1 row created.

SQL > INSERT INTO tabla_datos (concatenados)
VALUES ('123456XYZ012345');
1 row created.

SQL > INSERT INTO tabla_datos (concatenados)
2   VALUES ('123456ABC012345');
INSERT INTO .....
*
ERROR at line 2:
ORA-02291: Integrity constraint violated - 
parent key not found

La solución propuesta conlleva un par de problemas. El primero se trata de un obvio problema de almacenamiento ya que estamos almacenando el mismo dato dos veces, una vez en el campo concatenados y otra en el campo validacion. El segundo problema viene dado por la necesidad de utilizar un trigger PL/SQL y las implicaciones que este hecho puede conllevar sobre el rendimiento y los tiempos de ejecución de los comandos INSERT y UPDATE.

El caso es que con la versión de la base de datos Oracle 11g podemos forzar la integridad utilizando funciones aplicadas sobre una determinada columna; es decir, no estamos limitados a utilizar columnas reales, podemos utilizar una columna virtual. Así pues, con Oracle 11g podemos prescindir del trigger PL/SQL. Veamos a continuación como debemos crear nuestra tabla de datos para conseguir que esto ocurra:

SQL > CREATE TABLE tabla_datos
2   ( concatenados VARCHAR2(20) PRIMARY KEY,
3     validacion AS 
4       (SUBSTR(concatenados,7,3))
5     REFERENCES tabla_referencia );
Table created.

En este caso hemos definido una columna virtual con el nombre concatenados, columna que es simplemente un metadato, que no se almacena de manera redundante, y que no requiere de un trigger PL/SQL para su mantenimiento, pero que si que se puede utilizar para forzar la integridad de nuestra base de datos cruzando dicho valor con los valores almacenados en nuestra tabla de referencia o lookup table.

Artículos relacionados: Oracle lanza la versión 11g de su base de datos.