Como crear y modificar un campo autoincremental en Oracle

Muchas veces nos encontramos con la necesidad de crear un campo autoincremental en una tabla de nuestra base de datos. Típicamente suele usarse para generar una clave primaria para dicha tabla. Pues bien, la primera vez que nos enfrentamos con esta situación trabajando con Oracle, nos encontraremos con un grave problema que nos puede hacer perder mucho tiempo, puesto que, aunque parezca mentira, no existe ningún tipo de campo autoincremental en Oracle.

La solución es bastante sencilla (aunque no por ello deja de ser incómodo no disponer de un campo de este tipo directamente). Oracle dispone de un tipo de objeto denominado secuencia (SEQUENCE). Una secuencia tiene un valor inicial, un valor máximo y un valor de secuencia que incrementará cada vez que hagamos una llamada a la secuencia.

La estructura para crear una secuencia es la siguiente:

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value / NOCACHE;

Donde:

  • MINVALUE es el valor mínimo que tendrá la secuencia (usualmente 0 o 1)
  • MAXVALUE es el valor máximo que tendrá la secuencia (se puede obviar y por defecto se le asignará 999999999999999999999999999)
  • START WITH es el valor con el que empezará la secuencia
  • INCREMENT BY es el valor con el que se incrementará la secuencia (usualmente 1, pero podríamos usar 2 para generar solo valores pares por ejemplo [siempre y cuando hubiesemos puesto el campo anterior a 0 o 2 😉 ] )
  • CACHE / NOCACHE el uso de CACHE permite indicar cuantos valores queremos que sean guardados en memoria para una acceso más rápido. El inconveniente que tiene es que en caso de una caida del sistema, los valores generados por la secuencia se pierden y quedarian “huecos”.

Una vez creada la secuencia la usaremos llamando a sequence_name.nextval. Por ejemplo:

INSERT INTO mi_tabla (tabla_id, tabla_campo1) VALUES (mi_secuencia.nextval, ‘valor1’);

También podremos acceder al valor de la secuencia en una consulta haciendo la siguiente llamada (hay que tener en cuenta que esta llamada también nos incrementará el contador de la secuencia):

SELECT sequence_name.nextval FROM dual;

Por último veremos como podemos modificar el valor de la secuencia para asignarle uno arbitráriamente. Primero modificaremos la secuencia indicándole que a partir de este momento cada vez que sea llamada incremente su contador en tantas posiciones como la diferencia entre el valor que queremos asignarle (p.ej. si nuestra secuencia tiene valor 327 y queremos asignarle el 450, deberemos incrementar en 123).

ALTER SEQUENCE seq_name INCREMENT BY 123;

Luego haremos una llamada a la secuencia para que se incremente con el nuevo valor:

SELECT seq_name.nextval FROM dual;

Y por último volveremos a alterar la secuencia para que vuelva a incrementar igual que antes (en nuestro ejemplo de uno en uno)

ALTER SEQUENCE seq_name INCREMENT BY 1;

También existe la posibilidad de generar campos autoincrementales creando un trigger que sea llamado antes de la inserción y se encargue de calcular el siguiente valor libre. O haciendo una consulta previa del primer valor libre del campo. Pero son técnicas más engorrosas y que pueden dar lugar a errores y problemas con más facilidad de la expuesta aquí.