Oracle Database 12c: Columna identidad o Columna auto-incrementable

Por Ronald Vargas Quesada
Publicado en Noviembre 2015

Hasta la versión 11gR2, no era posible definir a nivel de una tabla, una columna que fuera posible de auto-incrementarse automáticamente, como si lo hacían otros motores de base de datos.
Sin embargo, a partir de la versión 12c, podemos utilizar un nuevo atributo a nivel de campo o columna de la tabla, conocido como identidad a nivel de columna ( Identity Column ).

La implementación de “identidad a nivel de columna” se lleva a cabo mediante la utilización de la cláusula “GENERATED” en la definición de la tabla, utilizando la siguiente sintaxis:

GENERATED 
[ 
ALWAYS | BY DEFAULT [ ON NULL ] 
] 
AS IDENTITY [ ( identity_options ) ]

Como parte de la nueva cláusula, existen 3 posibles opciones;

  • GENERATED ALWAYS AS IDENTITY [(IDENTITY_OPTIONS)]
  • GENERATED BY DEFAULT AS IDENTITY [(IDENTITY_OPTIONS)]
  • GENERATED BY DEFAULT ON NULL AS IDENTITY [(IDENTITY_OPTIONS)]
  1. GENERATED ALWAYS AS IDENTITY
  2. Vamos a utilizar el usuario de demo HR y vamos a crear una tabla utilizando la primera opción disponible para la cláusula GENERATED.

    Para este caso vamos a utilizar una versión del motor de base de datos Oracle Database 12c R1 Standard Edition 2.

    SQL> set linesize  120
    SQL> connect  hr/hr@pdb1
    SQL> select * from  v$version;
    
    BANNER                                                                           
    ---------------------------------------------------------------- 
    Oracle Database 12c  Standard Edition Release 12.1.0.2.0 - 64bit Production       
    PL/SQL Release  12.1.0.2.0 - Production                                           
    CORE    12.1.0.2.0      Production                                               
    TNS for Linux:  Version 12.1.0.2.0 - Production                                   
    NLSRTL Version  12.1.0.2.0 - Production                                           

     

    Vamos a crear la siguiente tabla de pruebas, agregando a la primera columna el atributo de identidad. Adicionalmente, vamos a ampliar la sintaxis agregando una de las posibles opciones que tenemos a nivel de atributo, que me permite determinar el comportamiento del incremento de la columna, como si fuera una secuencia.

    { START WITH ( integer | LIMIT VALUE ) 
    | INCREMENT  BY integer 
    | ( MAXVALUE  integer | NOMAXVALUE ) 
    | ( MINVALUE  integer | NOMINVALUE ) 
    | ( CYCLE |  NOCYCLE ) 
    | ( CACHE  integer | NOCACHE ) 
    | ( ORDER | NOORDER ) }... 


    En este caso, la tabla “test_table_incremental” contará con 2 columnas. La primera de ellas, va a ser definida como una “IDENTIDAD” y tendrá como características un nivel de precisión de 10 enteros, partiendo de un valor inicial de 1, con un autoincremento por igual de 1 y aprovisionando 20 valores secuenciales, para proveer rendimiento a la hora de solicitar el valor requerido.

    SQL> CREATE TABLE  TEST_TABLE_INCREMENTAL
    ("ID" NUMBER(10,0) GENERATED  ALWAYS AS IDENTITY MINVALUE 1 
    MAXVALUE 9999999999999999999999999999
    INCREMENT BY 1 START WITH 1 CACHE 20  NOORDER  NOCYCLE  NOT NULL ENABLE,
    "NAME" VARCHAR2(15)
     )
      /
    
    Table created.
    


    Como puedes observar a continuación, el atributo de “Identidad” agrega una política de restricción de no nulo a la columna afectada por el atributo.

    SQL> desc  TEST_TABLE_INCREMENTAL
    
    Name                                 Null?     Type
    --------------------------------     --------  -------
    ID                                   NOT NULL  NUMBER(10)
    NAME                                           VARCHAR2(15)


    Hagamos una prueba de funcionalidad de la identidad. Para ello vamos a insertar todos los nombres de los empleados de la tabla “employees”.

    SQL> insert into  test_table_incremental(name) select first_name from employees;
    
    107 rows created.
    
    SQL> commit;
    
    Commit complete.

    Veamos el resultado a continuación:

    SQL> select * from  test_table_incremental;
    
    ID  NAME
    --- ---------------
      1 Ellen
      2 Sundar
      3 Mozhe
      4 David
      5 Hermann
      6 Shelli
      7 Amit
      8 Elizabeth
      9 Sarah
     10 David
     11 Laura
     12 Harrison
     13 Alexis
     14 Anthony
     15 Gerald
     16 Nanette
     17 John
     18 Kelly
     19 Karen
     20 Curtis
     21 Lex
     22 Julia
     23 Jennifer
     24 Louise
     25 Bruce
     26 Alberto
     27 Britney
     28 Daniel
     29 Pat
     30 Kevin
     31 Jean
     32 Tayler
     33 Adam
     …
    100 Jose Manuel
    101 Peter
    102 Clara
    103 Shanta
    104 Alana
    105 Matthew
    106 Jennifer
    107 Eleni
    
    107 rows selected.
    
    SQL>

    Si usted intenta ingresar manualmente un valor en la tabla, aun cuando corresponda al consecutivo de la columna, el motor de base de datos le devuelve un error, indicando que no es posible insertar una columna que es generada automáticamente por una identidad.

    SQL> insert into  test_table_incremental values(108,'RONALD');
    insert into  test_table_incremental values(108,'RONALD')
     *
    ERROR at line 1:
    ORA-32795: cannot  insert into a generated always identity column


    Vamos a ver cómo funciona la integridad del atributo a nivel de la tabla. Vamos a borrar el último registro insertado en la tabla.

    SQL> delete  test_table_incremental where id=107;
    
    1 row deleted.
    

    Ahora vamos a insertar solo el campo nombre en la tabla, para ver si mantiene la secuencia de valor.

    SQL> insert  into  test_table_incremental(name)  values('RONALD');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.


    Consultamos los registros en la tabla. Para ellos vamos a utilizar la nueva característica de paginación de registros de Oracle Database 12c y nos vamos a brincar los primeros 106 registros de la tabla. Como borramos el registro con el ID 107, el DML que realizamos previamente, debería asignar el valor 108 al nuevo registro. Como resultado, obtenemos que efectivamente se mantiene la integridad del consecutivo creado.

    SQL> select * from  test_table_incremental offset 106 rows;
    
          ID NAME
    -------- ---------------
         108 RONALD
    
    SQL> delete  test_table_incremental where id=108;
    
    1 row deleted.

     

    Una vez mas.

    SQL> insert  into  test_table_incremental(name)  values('RONALD');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
     
     
    SQL> select * from test_table_incremental offset  106 rows;
    
           ID NAME
    --------- ---------------
          109 RONALD
    
    SQL> commit;
    
    Commit complete.
    
    SQL>  insert into   test_table_incremental(name) values('RONALD');
    
    1 row created.
    
    SQL> select * from  test_table_incremental offset 106 rows;
    
         ID NAME
    ------- ---------------
        109 RONALD
        110 RONALD


    Una instrucción no concluida satisfactoriamente, no va a alterar el valor de la secuencia de inclusión en el valor de la columna ID de la tabla.

    SQL> insert into test_table_incremental  values(111,'RONALD');
    insert into  test_table_incremental values(108,'RONALD')
     *
    ERROR at line 1:
    ORA-32795: cannot  insert into a generated always identity column
    
    SQL> insert  into  test_table_incremental(name)  values('RONALD');
    
    1 row created.
    
    SQL> select * from  test_table_incremental offset 106 rows;
    
          ID NAME
    -------- ---------------
         109 RONALD
         110 RONALD
         111 RONALD
    
    SQL>

  3. GENERATED BY DEFAULT AS IDENTITY
  4. Cuando se utiliza la opción “DEFAULT” en la cláusula “GENERATED”, permite asignarle de facto, el valor inmediatamente disponible de la secuencia, en caso que de manera explícita, no se le indique el valor del campo, que contiene el atributo de IDENTIDAD.

    SQL> create table  identidad_defacto (
      id number(20)  GENERATED BY DEFAULT AS IDENTITY,
      name varchar(20)
      )
      /
    
    Table created.
    

    Al visualizar la descripción de la tabla, podemos ver que se mantiene consistente la creación de una política de restricción sobre el campo ID de no nulo.

    SQL> desc identidad_defacto
    
    Name                              Null?     Type
    --------------------------------- --------  -------------
    ID                                NOT NULL  NUMBER(20)
    NAME                                        VARCHAR2(20)

     

    Ejemplo:

    Primero insertamos un valor en la tabla sin definir explícitamente el valor del campo ID. Por tanto, utilizaremos el valor asignado por la entidad.

    SQL> insert into identidad_defacto(name) values('RONALD');
    
    1 row created.
    
    SQL> select * from identidad_defacto;
    
            ID NAME
    ---------- --------------------
             1 RONALD
     

    Ahora en el siguiente ejemplo, vamos a ingresar otro registro, pero esta vez, de manera explícita vamos a proveer el valor del campo.

    SQL> insert into  identidad_defacto values(100,'JAZMIN');
    
    1 row created.
    
    SQL>  select * from identidad_defacto;
    
            ID NAME
    ---------- --------------------
             1 RONALD
           100 JAZMIN

    Veamos el comportamiento al ingresar luego un registro adicional, esta vez sin establecer explícitamente el valor de ID.

    SQL> insert into  identidad_defacto(name) values('DAVID');
    
    1 row created.
    
    SQL>  select * from identidad_defacto;
    
             ID NAME
     ---------- --------------------
              1 RONALD
            100 JAZMIN
              2 DAVID
    
    

  5. GENERATED BY DEFAULT ON NULL AS IDENTITY

La última de las opciones de la cláusula GENERATED, es definir la identidad como un valor de facto en caso de que el valor acompañado a la instrucción de inserción, contenga un valor no definido de forma explícita para el campo con el atributo de IDENTIDAD.

Ejemplo: Vamos como trabaja esto:

SQL> create table  identidad_defacto_nulos (
id  number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY,
name  varchar(15)
 )
  /

Table  created.

SQL>  insert into identidad_defacto_nulos values(NULL, 'RONALD');

1 row  created.

SQL>  insert into identidad_defacto_nulos(name) values ('FERNANDO');

1 row  created.

SQL>  insert into identidad_defacto_nulos values(300,'SOFIA');

1 row  created.

SQL> select * from  identidad_defacto_nulos;

        ID NAME
---------- ---------------
         1 RONALD
         2 FERNANDO
       300 SOFIA

Conclusión

Ya no tienes que dar tantas vueltas para similar el comportamiento del atributo de AUTO_INCREMENT existente en la cláusula CREATE TABLE de MySQL. Ahora lo tienes en Oracle, con más opciones de configuración y con controles adicionales de facto, como la creación de la política de restricción NOT NULL.

IDENTITY COLUMN una nueva característica del motor de base de datos Oracle 12c que te facilitará tu vida.

 


Publicado por Ronald Vargas Quesada. Consultor, Profesor Universitario, Oracle Academy Instructor y Conferencista Internacional. Ronald tiene más de 25 años de experiencia en Oracle DBA. Él reside en Costa Rica y es responsable de la creación de las Centroamericano Oracle Usuarios Grupos y responsable de introducir OTN Tour a los países centroamericanos. Ronald es un participante activo en OracleMania, Comunidad Oracle Hispana y LAOUC y disfruta ayudando a sus compañeros para encontrar soluciones y respuestas en Oracle Technology. Ronald es también el director de tecnología para la LAOUC. Ronald es actualmente instructor de Oracle University y fue reconocido como uno de los 15 mejores instructores LAD por Oracle University.

Este artículo ha sido revisado por el equipo de productos Oracle y se encuentra en cumplimiento de las normas y prácticas para el uso de los productos Oracle.