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

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>

2. ENERATED BY DEFAULT AS IDENTITY

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

3. 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.