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
Con el objetivo de probar la sintaxis descrita, se creará la tabla Project2 con las siguientes características para la columna Project_Id:
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,
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;
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.
Comentarios