auto_increment en Oracle

Las personas que han usado MySQL como motor de base de datos deben saber que el auto_increment es muy útil al momento de generar ids en las tablas que nos sirvan como llaves primarias. En MySQL, al momento de crear la tabla normalmente ejecutaríamos el siguiente script:

 CREATE TABLE animals (
     id INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );

Con lo que crearemos una tabla con 2 atributos llamados id y name, donde id actuará como la llave primaria de la tabla, garantizando que no podremos insertar 2 o más registros con el mismo id. Trás de esto, el auto_increment, hace que cada que el nuevo registro que insertemos, tenga el siguiente valor numérico que le corresponde (si hemos insertado 10 registros, el siguiente que insertemos tendrá valor 11).

Cuál es el problema? el auto_increment no es SQL estandar, por lo que no todos los motores de bases de datos trabajan con este. En estos días estoy portando una base de datos de MySQL a Oracle, y aunque existen diversas herramientas que facilitan la tarea, no la llevan a cabo por completo.

Lo primero que se me ocurrió para remplazar el auto_increment fue ampliar los scripts de PHP para que generara el id con el que se debía insertar el nuevo registro (algo como usar un select count(*) y sumarle 1).

Luego buscando por ahí, encontré que en oracle podía crear secuencias(más adelante explico como se crean), que basicamente remplaza de manera no completa al auto_increment, porque me genera una secuencia numérica, pero aún así al momento de hacer la inserción del registro, tendría que usar <secuencia>.nextval para generar el id, lo que me pondría a modificar de nuevo todos los scripts.

Seguí navengando por ahí, hasta que encontré una solución. Creando un Trigger (o "disparador", como bien decide traducirlo el iSQL Plus), podemos hacer que la inserción del id sea automática, tal y como se haría con un auto_increment en MySQL. A continuación describo el proceso.

Primero creamos nuestra tabla:

 CREATE TABLE animals (
     id NUMBER(10) NOT NULL,
     name VARCHAR2(30) NOT NULL
 );
 ALTER TABLE table_name
    add CONSTRAINT constraint_name PRIMARY KEY (id);

Luego creamos la secuencia, en este caso le decimos que se llama s_animals, que comienza en 1, y que se va incrementando de a 1:

 CREATE SEQUENCE s_animals
   STARTS WITH 1
   INCREMENT BY 1;

Por último, creamos el TRIGGER:

 CREATE OR REPLACE TRIGGER increment_animals
   BEFORE INSERT
     ON animals
     REFERENCING NEW AS NEW
     FOR EACH ROW
   BEGIN
     SELECT s_animals.nextval INTO :NEW.ID FROM dual;
   END;

De esta manera, podemos hacer los insert "incompletos" (al no tener que especificar el id del registro, tal y como lo haríamos en MySQL), así:

 INSERT INTO animals (name) VALUES ('Lion');
 INSERT INTO animals (name) VALUES ('Cat');
 INSERT INTO animals (name) VALUES ('Dog');

Obtendremos:

| ID | NAME |
+----+------+
| 1  | Lion |
+----+------+
| 2  | Cat  |
+----+------+
| 3  | Dog  |
+----+------+

Espero haber sido lo suficientemente claro :P