Columnas Identidad

En versiones previas a Oracle 12c, los valores de una columna clave primaria se generaban mediante una secuencia que produce un valor único y para que el proceso sea automático se requería adicionalmente de un disparador.  A partir de Oracle Database 12c se puede prescindir de las secuencias empleando columnas de identidad, una columna de identidad  genera un valor numérico secuencial y único al estilo de gestores como MySQL y PostgreSQL.

Para crear una columna de identidad se emplea la sintaxis básica siguiente:

             nombre_columna tipo GENERATED BY DEFAULT AS IDENTITY

El siguiente ejemplo crea la tabla Projects en el esquema HR:

   create table HR.Projects(

     project_id number(6)  GENERATED BY DEFAULT AS IDENTITY,

     project_name varchar2(60) not null,

     constraint Projects_PK Primary key( project_id)

   ); 

Probar la columna insertando datos y omitiendo la columna identidad porque esta genera su valor por defecto:

    insert into HR.Projects(project_name)

  values('Proyecto A1');

  insert into HR.Projects(project_name)

  values('Proyecto A2');

Consultar datos de Projects:

   select project_id,project_name

   from HR.Projects;

Como puede apreciarse en la imagen, la columan identidad genera automáticamente los valores secuenciales 1 y 2 para cada fila insertada. 

Para poder cambiar el valor inicial, establecer el  valor de incremento e indicar valores mínimo y máximo se puede aplicar la sintaxis siguiente:

  nombre_columna tipo GENERATED BY DEFAULT AS IDENTITY

             MINVALUE valor_mínimo

             MAXVALUE valor_máximo
             INCREMENT BY valor_incremento

             START WITH valor_inicial 

             NOORDER  NOCYCLE  NOT NULL ENABLE

Con el objetivo de probar la sintaxis descrita, se creará la tabla Project2 con las siguientes características para la columna Project_Id:

•   Valor Mínimo:100
•   Valor Máximo:99999999
•   Incremento: 10
•   Iniciar con:100

create table HR.Projects2(

     project_id number(6) GENERATED BY DEFAULT AS IDENTITY
                          MINVALUE 100
                          MAXVALUE 99999999
                          INCREMENT BY 10
                          START WITH 100,

     project_name varchar2(60) not null,

     constraint Projects_PK Primary key( project_id)

   ); 

Table HR.PROJECTS2 creado.

Insertar datos en la nueva tabla:

 insert into HR.Projects2(project_name)
 values('Proyecto A1A');

 insert into HR.Projects2(project_name)
 values('Proyecto A2A');

Consultar la tabla Projects2 para comprobar resultados:

 SELECT *
 FROM PROJECTS2; 

  

Conclusión

Las columnas de identidad constituyen un mecanismo natural y ágil para generar valores únicos y secuenciales para los campos de clave primaria, evitando crear objetos extra como  secuencias y disparadores.

Hasta una próxima ocasión.

Saludos.