Tablas SET y MULTISET en Teradata.

El modelo relacional -basado en la teoría de conjuntos- sobre el que están construídos los sistemas gestores de bases de datos relacionales (SGBDR’s) impide que en una misma tabla haya dos filas iguales (esto es un corolario de la primera forma normal). Es por ello que en la la mayoría de dichos SGBDR’s se impone el uso de una clave primaria (Primary Key) por tabla.

Teradata no funciona de esa manera: las PK’s son consideradas conceptos de diseño y no de implementación, y lo más parecido a ellas son los (Unique) Primary Indexes, que tienen más funciones de distribución (en los AMP’s) y rendimiento que de identidad.
No obstante el estándar ANSI sí que permite que en una tabla en la que no haya definidas restricciones de unicidad (PK’s, UK’s, Unique Indexes…) existan filas idénticas -aun a costa de romper la 1FN-.

Teradata incluye en la sintaxis de definición de tabla (DDL) la característica de SET o MULTISET. Si una tabla se define MULTISET, Teradata no efectuará un chequeo de unicidad de fila y permitirá que contenga filas idénticas (sin tener en cuenta otras posibles restricciones de unicidad mediante índices únicos). Como ya hemos dicho, MULTISET es el estándar ANSI.
Por otro lado, si una tabla se define SET Teradata sí efectuará un chequeo de unicidad de fila y no permitirá la existencia de filas idénticas. Este es el modo ‘por defecto’ de Teradata.

Pero las cosas son un poco más sutiles de lo que cabría suponer. La gestión de la aparición de errores puede sorpreder un poco:

 Teradata BTEQ 08.02.03.03 for WIN32.
 Copyright 1984-2006, NCR Corporation. ALL RIGHTS RESERVED.
 Enter your logon or BTEQ command:
.LOGON MY_TD/MY_DB

.LOGON MY_TD/MY_DB
Password:

 *** Logon successfully completed.
 *** Teradata Database Release is V2R.06.02.01.18
 *** Teradata Database Version is 06.02.01.17
 *** Transaction Semantics are BTET.
 *** Character Set Name is 'ASCII'.

 *** Total elapsed time was 3 seconds.

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

CREATE TABLE MY_DB.PRUEBA01 (COL01 INTEGER NOT NULL, COL02 INTEGER)
PRIMARY INDEX (COL01);

CREATE TABLE MY_DB.PRUEBA01 (COL01 INTEGER NOT NULL, COL02 INTEGER)
PRIMARY INDEX (COL01);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (1,1);

INSERT MY_DB.PRUEBA01 VALUES (1,1);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (1,2);

INSERT MY_DB.PRUEBA01 VALUES (1,2);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (1,3);

INSERT MY_DB.PRUEBA01 VALUES (1,3);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (2,1);

INSERT MY_DB.PRUEBA01 VALUES (2,1);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (2,2);

INSERT MY_DB.PRUEBA01 VALUES (2,2);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA01 VALUES (3,1);

INSERT MY_DB.PRUEBA01 VALUES (3,1);

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

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

SELECT * FROM MY_DB.PRUEBA01;

SELECT * FROM MY_DB.PRUEBA01;

 *** Query completed. 6 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

      COL01        COL02
-----------  -----------
          2            1
          3            1
          1            1
          2            2
          1            2
          1            3

Hasta ahora lo único que hemos hecho es crear una tabla con datos que nos servirán de ‘fuente’. Las particularidades de SET y MULTISET las vamos a ver ahora.

Creamos cuatro tablas, con todas las variaciones posibles de SET, MULTISET y UNIQUE/NOT UNIQUE PRIMARY INDEX

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE MULTISET TABLE MY_DB.PRUEBA02_MS_UPI (COL01 INTEGER NOT NULL)
UNIQUE PRIMARY INDEX (COL01);

CREATE MULTISET TABLE MY_DB.PRUEBA02_MS_UPI (COL01 INTEGER NOT NULL)
UNIQUE PRIMARY INDEX (COL01);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE MY_DB.PRUEBA03_S_UPI (COL01 INTEGER NOT NULL)
UNIQUE PRIMARY INDEX (COL01);

CREATE SET TABLE MY_DB.PRUEBA03_S_UPI (COL01 INTEGER NOT NULL)
UNIQUE PRIMARY INDEX (COL01);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE MULTISET TABLE MY_DB.PRUEBA04_MS_NUPI (COL01 INTEGER NOT NULL)
PRIMARY INDEX (COL01);

CREATE MULTISET TABLE MY_DB.PRUEBA04_MS_NUPI (COL01 INTEGER NOT NULL)
PRIMARY INDEX (COL01);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE MY_DB.PRUEBA05_S_NUPI (COL01 INTEGER NOT NULL)
PRIMARY INDEX (COL01);

CREATE SET TABLE MY_DB.PRUEBA05_S_NUPI (COL01 INTEGER NOT NULL)
PRIMARY INDEX (COL01);

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

Ahora nos disponemos a insertar en ellas el contenido de la primera columna de nuestra tabla ‘fuente’, que contiene datos iguales.

El comportamiento ante la inserción en la primera tabla (MULTISET / UNIQUE PRIMARY INDEX) es el esperado:

INSERT MY_DB.PRUEBA02_MS_UPI SELECT COL01 FROM MY_DB.PRUEBA01;

INSERT MY_DB.PRUEBA02_MS_UPI SELECT COL01 FROM MY_DB.PRUEBA01;
 *** Failure 2801 Duplicate unique prime key error in MY_DB.PRUEBA02_MS_UPI.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

En efecto: falla con un error 2801 ‘Duplicate unique prime key error in MY_DB.PRUEBA02_MS_UPI‘.

Pero veamos que pasa con la siguiente tabla (SET / UNIQUE PRIMARY INDEX):

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA03_S_UPI SELECT COL01 FROM MY_DB.PRUEBA01;

INSERT MY_DB.PRUEBA03_S_UPI SELECT COL01 FROM MY_DB.PRUEBA01;

 *** Insert completed. 3 rows added.
 *** Total elapsed time was 1 second.

¡No ha habido error! Pero se han insertado 3 filas, no las 6 de la tabla fuente. La verificación de unicidad (SET TABLE) ha eliminado silenciosamente los duplicados antes de la inserción, por lo que la unicidad del índice no ha disparado el error como en el caso anterior.

El siguiente caso (MULTISET / NOT UNIQUE PRIMARY INDEX) es trivial: insertará todas las filas.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA04_MS_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

INSERT MY_DB.PRUEBA04_MS_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

 *** Insert completed. 6 rows added.
 *** Total elapsed time was 1 second.

Y, por último, el caso SET / NOT UNIQUE PRIMARY INDEX, análogo al segundo: sin error, pero insertando sólo las filas distintas (3):

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA05_S_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

INSERT MY_DB.PRUEBA05_S_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

 *** Insert completed. 3 rows added.
 *** Total elapsed time was 1 second.

Un par de cosas más: si volvemos a ejecutar el último INSERT sobre la tabla SET no aparecerá ningún error, pero no insertará ninguna fila (‘No rows added’):

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA05_S_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

INSERT MY_DB.PRUEBA05_S_NUPI SELECT COL01 FROM MY_DB.PRUEBA01;

 *** Insert completed. No rows added.
 *** Total elapsed time was 1 second.

dado que todas existen ya en la tabla destino.

Segundo: Esto no funciona con un INSERT…VALUES, que SÍ lanzará un error de duplicados en una tabla SET:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT MY_DB.PRUEBA05_S_NUPI VALUES (1);

INSERT MY_DB.PRUEBA05_S_NUPI VALUES (1);
 *** Failure 2802 Duplicate row error in MY_DB.PRUEBA05_S_NUPI.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

Así que realmente SÍ existe una gran diferencia entre SET y MULTISET, incluso si se definen índices únicos. SET hace que los INSERT…SELECT sólo inserten filas que no existan previamente en la tabla destino. NO lanzan ningún error de duplicidad, pero el número de filas insertadas puede ser menor que los que hay en la tabla fuente (e incluso ser cero).

Saludos.

Carlos.