Oracle 12c - Columna auto-incremental

Introducción:

He trabajado desde almacenar información en archivos con lenguajes como Pascal, Turbo C y C++, luego algo que en su momento me gustó mucho trabajar con FoxPro aunque es otra historia. Posteriormente trabaje con SQL Server, y en este último he podido apreciar que era muy fácil aplicar y hasta muy común las llaves primarias como campos numéricos de modo que sean auto incrementables, por ejemplo, en SQL Server se utiliza IDENTITY. Luego conocí a Oracle en su versión 10g R1/10g R2 y posteriormente el 11g R1/11gR2, hasta la versión 11g R2 la forma de trabajar con campos auto incrementables era un tanto atípico, puesto que se debería crear la tabla con un campo de tipo numérico, determinar su constraint como “Primary Key”, crear un “Sequence” y por último generar un disparador (Trigger) que permita cargar el número generado por el “Sequence” con la finalidad de dar ese valor al campo numérico destinado para tal fin. Jeje un tanto medio trucho aparentemente, pero era la manera en que trabaje con esas versiones y si hubo otras las desconozco, a cambio de estas tareas adicionales sin embargo como desarrollador se siente que tiene el control, al menos para mí algo muy valorable, lo que no da SQL Server pues uno desconoce cómo trabaja por detrás.

Y particularmente en la versión Oracle 12c se tiene la definición para columnas que pueden ser generados como IDENTITY, el cual es gestionado por el gestor y evitando trabajar como se lo hacía con versiones previas.

Oracle, hasta la versión 11gR2:

La forma de generar una clave primaria y que sea auto incrementable hasta la versión 11g R2, era de la siguiente forma:

-- Creación de la table “OCUPACION_JCC2”.

CREATE TABLE TPAR_OCUPACION_JCC2

(

  ID_OCUPACION       NUMBER(19)                 NOT NULL,

  NOMBRE             VARCHAR2(150 BYTE)         NOT NULL,

  DESCRIPCION        VARCHAR2(150 BYTE),

  USUARIO   VARCHAR2(50 BYTE)          DEFAULT USER                  NOT NULL,

  FECHA     TIMESTAMP(6)               DEFAULT CURRENT_TIMESTAMP     NOT NULL,

  MAQUINA  VARCHAR2(50 BYTE)          DEFAULT SYS_CONTEXT('USERENV','IP_ADDRESS') NOT NULL

);

-- Aplicando un alter table, para determiner el CONSTRAINT=”PK_IDENTIDAD_JCC2” como clave primaria.

ALTER TABLE TPAR_OCUPACION_JCC2 ADD (

  CONSTRAINT PK_IDENTIDAD_JCC2 PRIMARY KEY (ID_OCUPACION));

-- Creación del sequence (secuencial).

CREATE SEQUENCE SEQ_OCUPACION_JCC2 START WITH 1;

-- Definición del trigger que disparará antes de realizer la inserción.

CREATE OR REPLACE TRIGGER TRIG_OCUPACION_JCC2

    BEFORE INSERT ON TPAR_OCUPACION_JCC2

    FOR EACH ROW

BEGIN

  SELECT SEQ_OCUPACION_JCC2.NEXTVAL

  INTO   :new.ID_OCUPACION

  FROM   dual;

END;

Oracle 12c

En esta versión apareció una nueva forma de definir campos auto incrementales, que pudieran ser aplicables a campos PK (clave primaria), pero se debe tomar en cuenta varios factores que se detallan a continuación:

Sintaxis:

COLUMN NAME

GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ]

AS IDENTITY [ ( identity_options ) ]

Donde:

ALWAY:                Define que no es necesario denotar el campo explícitamente en la sentencia INSERT, y si se lo coloca dándole un valor incluso válido, arrojará un error: “ORA-32795: cannot insert into a generated always identity column”

BY DEFAULT:      Se debe definir esta propiedad si en la sentencia INSERT se lo pretende colocar explícitamente, aceptará el valor otorgado, sin embargo, si se da un valor nulo saldrá un error debido a que todo campo definido como IDENTITY es NOT NULL.

BY DEFAULT ON NULL:   Se debe definir esta propiedad si puede darse el caso de otorgar valor nulo, se activará la propiedad específica ON NULL desencadenando la generación por defecto de un nuevo valor auto incrementado a través del SEQUENCE destinado.

identity_options:            Es, básicamente, la sintaxis para generador de secuencias - mismas que las opciones CREATE SEQUENCE.

CREATE SEQUENCE sequence_name

  MINVALUE value

  MAXVALUE value

  START WITH value

  INCREMENT BY value

  CACHE value;

Casos:

Se estudiará los siguientes casos a), b) y c) y esencialmente el objetivo es conocer el mecanismo que emplea el gestor para su gestión de campos IDENTITY, pensados para el tipo de caso particular que pudiera darse.

a)      ALWAYS

a.1) Crear la tabla:

DROP TABLE PRUEBA CASCADE CONSTRAINTS;

CREATE TABLE PRUEBA

(

  ID        NUMBER(19)      GENERATED ALWAYS AS IDENTITY    PRIMARY KEY,

  NOMBRE    VARCHAR2(50)

);

a.2) Insertaremos un registro, tratando de ingresar un valor explícito al campo ID definido como ALWAYS:

INSERT INTO PRUEBA(ID, NOMBRE) VALUES(10, 'JHIMY DANIEL');

Da el error: [Error] Execution (18: 20): ORA-32795: cannot insert into a generated always identity column

a.3) Insertaremos un registro sin definir explícitamente el campo ID.

INSERT INTO PRUEBA(NOMBRE) VALUES('JHIMY DANIEL');

Arroja el mensaje correcto: 1 row created.

Si ejecutamos un SELECT, podremos apreciar el registro insertado:

SELECT * FROM PRUEBA;

b)      BY DEFAULT

b.1) Crear la tabla:

DROP TABLE PRUEBA CASCADE CONSTRAINTS;

CREATE TABLE PRUEBA

(

  ID        NUMBER(19)      GENERATED BY DEFAULT AS IDENTITY    PRIMARY KEY,

  NOMBRE    VARCHAR2(50)

);

b.2) Insertaremos un registro, tratando de ingresar un valor explícito al campo ID definido como BY DEFAULT:

INSERT INTO PRUEBA(ID, NOMBRE) VALUES(10, 'JHIMY DANIEL');

Arroja el mensaje correcto: 1 row created.

Si ejecutamos un SELECT, podremos apreciar el registro insertado:

SELECT * FROM PRUEBA;

b.3) Insertaremos un registro sin definir explícitamente el campo ID.

INSERT INTO PRUEBA(NOMBRE) VALUES('JHIMY DANIEL');

Arroja el mensaje correcto: 1 row created.

Si ejecutamos un SELECT, podremos apreciar el registro insertado:

SELECT * FROM PRUEBA;

Como se puede apreciar el valor incremental que le corresponde es el que le otorgó.

b.4) Insertaremos un registro con valor NULL en el campo ID.

INSERT INTO PRUEBA(ID, NOMBRE) VALUES(NULL, 'JHIMY DANIEL');

Arroja el mensaje de error: ORA-01400: cannot insert NULL into ("IDENTIDAD"."PRUEBA"."ID")

c)       BY DEFAULT ON NULL

c.1) Crear la tabla:

DROP TABLE PRUEBA CASCADE CONSTRAINTS;

CREATE TABLE PRUEBA

(

  ID        NUMBER(19) GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,

  NOMBRE    VARCHAR2(50)

);

c.2) Insertaremos un registro, tratando de ingresar un valor explícito al campo ID definido como BY DEFAULT ON NULL:

INSERT INTO PRUEBA(ID, NOMBRE) VALUES(10, 'JHIMY DANIEL');

Arroja el mensaje correcto: 1 row created.

Si ejecutamos un SELECT, podremos apreciar el registro insertado:

SELECT * FROM PRUEBA;

c.3) Insertaremos un registro sin definir explícitamente el campo ID.

INSERT INTO PRUEBA(NOMBRE) VALUES('JHIMY DANIEL');

Arroja el mensaje correcto: 1 row created.

Si ejecutamos un SELECT, podremos apreciar el registro insertado:

SELECT * FROM PRUEBA;

Como se puede apreciar el valor incremental que le corresponde es el que le otorgó.

c.4) Insertaremos un registro con valor NULL en el campo ID.

INSERT INTO PRUEBA(ID, NOMBRE) VALUES(NULL, 'JHIMY DANIEL');

Arroja el mensaje correcto: 1 row created.

Si ejecutamos un SELECT, podremos apreciar el registro insertado:

SELECT * FROM PRUEBA;

Se puede apreciar que se ha insertado correctamente, y el valor otorgado es el 2, siendo que en el anterior ejercicio le asigno el valor de 1.

identity_options:

Se debe considerar que tiene las mismas propiedades de una sentencia “CREATE SEQUENCE”, y nos da lugar a aplicar, por ejemplo:

Muchas veces por tema de migración u otros necesitaremos modificar el inicio de la secuencia, refiriéndome a la secuencia automáticamente generada al definir un campo como IDENTITY.

Trataremos de cambiar un SEQUENCE autogenerado:

ALTER SEQUENCE ISEQ$$_94523

        MINVALUE 1

        MAXVALUE 999999999999999999999999999

        START WITH 10

        INCREMENT BY 1

        CACHE 20;

El mensaje que nos arrojará: ORA-02283: cannot alter starting sequence number

Siendo que no nos permite modificar el inicio de la secuencia, se lo debe realizar de la siguiente forma, considerando que deseamos iniciar en 100:

ALTER TABLE PRUEBA MODIFY (ID   GENERATED BY DEFAULT AS IDENTITY START WITH 100 NOCACHE);       

Conclusión:

El modo en que se debe definir el tipo de IDENTITY es según el caso particular que se pueda dar, puedo citar como ejemplo los siguientes:

a)       Tomando en cuenta al tipo IDENTITY “BY DEFAULT ON NULL” es aplicable y da lugar, por ejemplo: En casos de migración de datos cuando de un conjunto de datos de una estructura predefinida, producto de la normalización da lugar a una o más entidades, en la cual la consolidación de un ETL no podrá determinar en algunos casos el valor del ID siendo el valor NULL por defecto.

b)      Si se diera el caso de que previamente existe migración de datos y estamos obligados a poder modificar el inicio de la secuencia, se debe considerar las propiedades de “identity_options” como se puede apreciar en líneas arriba, debido a que Oracle no nos permitirá realizar un ALTER sobre la secuencia autogenerada.

La gestión de campos auto incrementales en Oracle 12c tiene ventajas sustanciales, siendo el gestor el encargado de gestionar este tipo tareas que en otras versiones predecesoras no se las podía considerar triviales y si había que tratarlas con determinado cuidado.