Eliminar el atributo ‘IDENTITY’ de una columna preservando su contenido.

Las columnas IDENTITY son un mecanismo que sirve para generar claves subrogadas en Teradata. Es un método análogo -aunque completamente diferente- a las secuencias (sequences) en Oracle.

Debido a la naturaleza paralela de la arquitectura de Teradata, un elemento como una secuencia -que serializa por definición- es prácticamente impensable.

En cambio, el funcionamiento de las columnas IDENTITY es ‘AMP-local’ (y en consecuencia, paralelo) y funciona reservando un ‘buffer‘ de números para cada AMP, que se irán usando en las operaciones correspondientes. Esto es un punto importante: una secuencia es, claro, “secuencial”: y dará valores sucesivos para operaciones (generalmente INSERTs) sucesivas. Una columna IDENTITY garantiza valores únicos, pero no necesariamente sucesivos (es más, muy probablemente no lo serán).

Otra diferencia importante es que, mientras las secuencias Oracle son objetos independientes, las IDENTITY columns se circunscriben a la tabla donde fueron definidas.

Hay bastantes cosas incómodas respecto a las IDENTITY columns. Una de ellas, y no la menos importante, es la imposibilidad de ejecutar limpiamente un “Backup/Copy/Restore” con arcmain de una tabla que contenga una de ellas.

Así que: ¿Qué pasa si queremos eliminar la parte IDENTITY de una columna de una tabla? Hay sitios donde se recomienda agregar una columna a la tabla con el mismo tipo de la columna IDENTITY, hacer un UPDATE con los datos de ésta y luego eliminarla. No está mal, pero si la columna IDENTITY es el PRIMARY INDEX estamos jodidos. También se puede recurrir al viejo truco de CREATE TABLE + INSERT…SELECT + RENAME TABLE, pero asi estamos creando una tabla nueva (Nuevo object ID en la base de datos, GRANTS y estadísticas perdidos sin remisión…)

¿Habrá alguna forma más sencilla? Veamos.

 BTEQ -- Enter your SQL request or BTEQ command: 
CREATE MULTISET TABLE PRUEBA_ID(
   ID_N INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , 
   C_TXT VARCHAR(25));


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


 BTEQ -- Enter your SQL request or BTEQ command: 
INSERT INTO PRUEBA_ID VALUES (NULL, 'UNO');


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


 BTEQ -- Enter your SQL request or BTEQ command: 
INSERT INTO PRUEBA_ID VALUES(NULL, 'DOS');


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


 BTEQ -- Enter your SQL request or BTEQ command: 
SELECT * FROM PRUEBA_ID;


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

       ID_N  C_TXT
-----------  -------------------------
          1  UNO
          2  DOS

Aquí tenemos nuestra tabla de prueba con su columna IDENTITY que además es el PRIMARY INDEX.

¿Qué pasa si intentamos quitar el atributo IDENTITY redefiniendo la columna sin él?

 BTEQ -- Enter your SQL request or BTEQ command: 
ALTER TABLE PRUEBA_ID ADD ID_N INTEGER NOT NULL;


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


 BTEQ -- Enter your SQL request or BTEQ command: 
INSERT INTO PRUEBA_ID VALUES(NULL, 'DOS');


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


 BTEQ -- Enter your SQL request or BTEQ command: 
SELECT * FROM PRUEBA_ID;


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

       ID_N  C_TXT
-----------  -------------------------
      10001  DOS
          1  UNO
          2  DOS

Pues que no hemos hecho nada.

¿Y si intentamos simplemente modificar el atributo de ‘ALWAYS’ a ‘BY DEFAULT’?

 BTEQ -- Enter your SQL request or BTEQ command: 
ALTER TABLE PRUEBA_ID ADD ID_N INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1);


ALTER TABLE PRUEBA_ID ADD ID_N INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1);
                                                                                                               $
 *** Failure 3706 Syntax error: Cannot add new Identity Column option.
                Statement# 1, Info =112 
 *** Total elapsed time was 1 second.

Agua. No se puede modificar.

Afortunadamente hay una opción enterrada en las profundidades de los manuales de documentación de Teradata:

BTEQ -- Enter your SQL request or BTEQ command: 
ALTER TABLE PRUEBA_ID DROP ID_N IDENTITY;                      


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

Ahora, si intentamos un INSERT con NULL:

BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO PRUEBA_ID VALUES(NULL, 'DOS');

*** Failure 3811 Column 'ID_N' is NOT NULL. Give the column a value.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

Y la columna se comporta como una columna más:

BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO PRUEBA_ID VALUES(3,'TRES');

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

BTEQ -- Enter your SQL request or BTEQ command:
SELECT * FROM PRUEBA_ID ;

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

ID_N C_TXT
----------- -------------------------
10001 DOS
3 TRES
1 UNO
2 DOS

Por último, vemos el DDL de la tabla, que lo confirma:

BTEQ -- Enter your SQL request or BTEQ command:
SHOW TABLE PRUEBA_ID ;

*** Text of DDL statement returned.
*** Total elapsed time was 1 second.

----------------------------------------------------------------------------------------------------
CREATE MULTISET TABLE CARLOS.PRUEBA_ID ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ID_N INTEGER NOT NULL,
C_TXT VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( ID_N );

BTEQ -- Enter your SQL request or BTEQ command:

En resumen, el atributo IDENTITY ha desaparecido, aunque la columna y su contenido permanecen.

Eso sí: no hay marcha atrás. Una vez eliminado el atributo IDENTITY no se puede volver a agregar a la columna con un ALTER:

 
BTEQ -- Enter your SQL request or BTEQ command:
ALTER TABLE CARLOS.PRUEBA_ID
ADD ID_N INTEGER NOT NULL 
      GENERATED ALWAYS AS IDENTITY (NO CYCLE START WITH 10);               

ADD ID_N INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (NO CYCLE START WITH 10);
                                                                      
 *** Failure 3706 Syntax error: Cannot add new Identity Column option.
                Statement# 1, Info =116
 *** Total elapsed time was 1 second.

No obstante, hemos conseguido eliminar el atributo IDENTITY manteniendo la tabla ‘intacta’, con sus GRANTs y estadísticas.

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: