Teradata Batch Reference Integrity: ‘WITH CHECK OPTION’ BUG?

Teradata, siendo como es una Base de Datos para Data Warehousing (con todo lo que ello supone), se sumó un poco tarde a la implementación de la integridad referencial (‘RI Referencial Integrity‘) que otros fabricantes implementaban ”desde siempre’. Esto no es de extrañar, ya que las Data Warehouses buscan hacer las consultas (y cargas) lo más rápidas posibles, y esto es algo que la RI penalizaría o haría imposible. Por otra parte, los datos deberían llegar ya depurados (provienen de OLTP’s que ya implementan RI) o se depuran una sola vez en el proceso ETL (no suele haber muchas modificaciones ‘transaccionales’ en un Data Warehouse).

No obstante, como hemos dicho, Teradata se subió al carro de la RI y lo hizo adoptando el criterio ‘más general’, aunque a su manera.

La RI en teradata se presenta en tres opciones: ‘Standard‘, ‘Batch‘ y ‘Soft‘.

La RI ‘standard‘ es la de toda la vida: verifica fila a fila la existencia de una Primary Key/Alternate Key en la tabla referida para cada fila de la tabla referente según la columna de relación.

La RI ‘batch‘ hace lo mismo que la anterior, pero en vez de hacerlo fila a fila lo hace a nivel de transacción para todas las filas implicadas en la misma (esto es lo mismo que hace Oracle con las ‘constraints’ ‘DEFERRABLE/DEFERRED’).

la RI ‘soft‘ es una RI virtual: no hace ningún tipo de verificación de integridad (se confía en la integridad de los datos) y sólo sirve para indicar al optimizador relaciones ‘virtuales’ y que éste las tome en cuenta al generar los ‘query plans‘ y sus costes. (Oracle implementa esto con ‘constraints’ ‘DISABLED NOVALIDATE RELY’).

Así pues, las diferencias entre las dos primeras sólo deberían ser a nivel ‘transaccional’, no ‘formal’. Es decir, CUANDO se efectúa la validación (por fila o al terminar la transacción) y no CÓMO se efectúa.

¿Es esto así? Vamos a ver cómo funciona (en este caso en Teradata 12).

Creamos la tabla ‘padre’ y la llenamos:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE MY_DB.LETTERS_ESP ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID_C CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      C_TEXTO VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC
     )
UNIQUE PRIMARY INDEX ( ID_C );

 *** Table has been created.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.LETTERS_ESP(ID_C, C_TEXTO)
                        VALUES ('A','Letra A');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.LETTERS_ESP(ID_C, C_TEXTO)
                        VALUES ('E','Letra E');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.LETTERS_ESP(ID_C, C_TEXTO)
                        VALUES ('I','Letra I');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.LETTERS_ESP(ID_C, C_TEXTO)
                        VALUES ('O','Letra O');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.LETTERS_ESP(ID_C, C_TEXTO)
                        VALUES ('U','Letra U');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

Ahora creamos una tabla ‘hija’ con RI ‘standard’:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE MY_DB.LETTERS_ENG ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID_C CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      C_TEXTO VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
      ID_C_SP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      CONSTRAINT ENG_ESP_FK_FK FOREIGN KEY ( ID_C_SP )
         REFERENCES MY_DB.LETTERS_ESP ( ID_C )
     )
UNIQUE PRIMARY INDEX ( ID_C );

 *** Table has been created.
 *** Total elapsed time was 1 second.

Vamos ahora a insertar tres filas en la tabla ‘hija’: una con un valor RI existente, otra con un valor RI inexistente, y una tercera con un valor RI nulo. La primera debería insertarse OK, la segunda debería fallar y la tercera debería insertarse, pues un valor nulo (NULL) no viola las ‘constraints‘ de RI:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.LETTERS_ENG (ID_C, C_TEXTO, ID_C_SP)
                         VALUES ('A','Letter A','A');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO MY_DB.LETTERS_ENG (ID_C, C_TEXTO, ID_C_SP)
                         VALUES ('B','Letter B','B');

 *** Failure 2700 Referential constraint violation: invalid Foreign Key value.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO MY_DB.LETTERS_ENG (ID_C, C_TEXTO, ID_C_SP)
                         VALUES ('C','Letter C',NULL);

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

En efecto, todo ha sido como se esperaba.

En teoría, un cambio de ‘standard‘ a ‘batch‘ no debería suponer ningún cambio:

DROP TABLE MY_DB.LETTERS_ENG;

 *** Table has been dropped.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE MY_DB.LETTERS_ENG ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID_C CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      C_TEXTO VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
      ID_C_SP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      CONSTRAINT ENG_ESP_FK_FK FOREIGN KEY ( ID_C_SP )
         REFERENCES WITH CHECK OPTION MY_DB.LETTERS_ESP ( ID_C )
     )
UNIQUE PRIMARY INDEX ( ID_C );

 *** Table has been created.
 *** Total elapsed time was 1 second.

(Nótese la opción WITH CHECK OPTION en la definición de la FK).

Si repetimos las tres inserciones, deberíamos esperar los mismos resultados (la documentación no habla de ninguna diferencia en ese sentido, o al menos yo no he encontrado ninguna referencia al respecto). Pero sorprendentemente Teradata devuelve un error en el valor NULL para la RI ‘WITH CHECK OPTION’:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.LETTERS_ENG (ID_C, C_TEXTO, ID_C_SP)
                         VALUES ('A','Letter A','A');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO MY_DB.LETTERS_ENG (ID_C, C_TEXTO, ID_C_SP)
                         VALUES ('B','Letter B','B');

 *** Failure 3513 RI violation.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO MY_DB.LETTERS_ENG (ID_C, C_TEXTO, ID_C_SP)
                         VALUES ('C','Letter C',NULL);

 *** Failure 2620 The format or data contains a bad character.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

¡No hay diferencias en los tipos de datos de las tablas ni en los datos insertados, pero se produce un extraño error que se subsana con un simple ‘CAST’ al tipo de datos definido para la tabla!:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.LETTERS_ENG (ID_C, C_TEXTO, ID_C_SP)
                         VALUES ('C','Letter C',NULL(CHAR(1)));

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

¿Desde cuándo es necesario hacer un ‘CAST’ para insertar un ‘NULL’, sea cual sea el tipo?  (El texto de error indicaba algún problema en el formato, lo que suele ser habitual en los problemas de la transformación de tipos) Y además sólo ocurre en el caso de que la FK haya sido definida con la opción WITH CHECK OPTION –batch RI-.

Esto para mí tiene todo el aspecto de un ‘bug‘…

Saludos.

Carlos.

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: