Menú de navegaciónMenú
Categorías

La mejor forma de Aprender Programación online y en español www.campusmvp.es

?id=963e0f04-230d-4d3f-8d22-35d51200d441

Auto-numéricos: cómo obtener el último insertado en Oracle, MySQL y SQL Server

El diseño de las bases de datos relacionales se basa en la existencia de tablas y de relaciones entre ellas (aunque como hemos dicho en más de una ocasión en este blog, el nombre "Relacional" otorgado a este tipo de sistemas no viene de estas relaciones, sino de las tablas en sí).

¿Qué es un auto-numérico?

Cuando se diseña una base de datos, para relacionar dos tablas entre sí debemos tener un campo común entre ambas, que es el que nos facilita la manera de relacionarlas. Por ejemplo, en la archiconocida base de datos Northwind las tablas que relacionan los pedidos (Orders) con las líneas de pedido (Order Details) y con los clientes (Customers), y finalmente las líneas de pedido con los productos a los que se refieren, es esta (pulsa para aumentar):

Autonumericos-Relaciones-Northwind

Como vemos, la tabla de pedidos se relaciona con la de líneas de pedido a través de la clave OrderID. Éstas se relacionan con los productos mediante una clave llamada ProductID, y finalmente cada pedido se relaciona con los datos de su cliente mediante una clave en la tabla de clientes llamada CustomerID.

Al campo que se usa para una relación en la tabla principal se le llama clave primaria, y en la tabla relacionada se le llama clave externa o foránea. Más detalles aquí.

Bien, este campo puede ser cualquier valor que sepamos que es único en la tabla primaria de la relación. Por ello pueden ser desde textos que sepamos que van a ser únicos a datos de tipo GUID que son números largos aleatorios que siempre podemos asegurar que son únicos.

Sin embargo, la manera más habitual de relacionar dos tablas es a través de un campo de tipo numérico que el propio motor de bases de datos se encarga de generar por nosotros: son los valores auto-numéricos conocidos también como numéricos auto-incrementales.

Un campo de tipo auto-numérico se inicia normalmente en el 1 con el primer registro, y cada vez que se mete un registro nuevo el motor de bases de datos aumenta en uno su valor, siendo los nuevos registros el 2, 3, 4 y así sucesivamente.

Lo bueno de hacerlo así es que el propio motor de base de datos se encarga de crearlo y nos libera del problema que supondría gestionarlo nosotros mismos. Conseguirlo es mucho más complicado de lo que parece. En un entorno de mucha carga de trabajo, en el que se están introduciendo nuevos registros a cada momento crear ese auto-numérico no es nada fácil pues tendremos que asegurarnos de que no se asigna el mismo a más de un registro, de que van siempre en orden y sin huecos, de que no se producen bloqueos al intentar introducir dos registros a la vez... Y el motor de bases de datos nos ahorra todo este trabajo, ¡Genial!

Cómo averiguamos el auto-numérico actual

Lo anterior es estupendo, pero tiene un problema. Por ejemplo, considera las tablas anteriores de pedidos y supón que estás creando un nuevo pedido que lleva a su vez varias líneas de pedido y se asocia, claro está, a un cliente. Lo primero que debes introducir es el pedido en sí. Éste tendrá asociado un identificador auto-numérico en su campo OrderID que necesitamos averiguar antes de poder introducir las líneas de pedido, puesto que éstas se relacionan con el pedido precisamente a través de este campo.

¿Cómo podemos conseguirlo?

Bueno, lo primero que cabría pensar es que es muy sencillo puesto que basta con hacer una consulta que nos devuelva el valor máximo de ese campo en la tabla, el cual será el correspondiente al último pedido insertado ¿no?.

Esta técnica no es adecuada. El problema es que si lo intentas en tu equipo de pruebas te funcionará bien, pero en la realidad no te servirá. El motivo es que en entornos con muchos usuarios simultáneos, entre que tú introduces tu pedido en la tabla y haces la consulta para ver el ID máximo, otro usuario puede haber introducir otro pedido en la misma tabla que ya tendrá un ID más alto. Si simplemente averiguas el máximo corres el riesgo (casi seguro) de que tus líneas de pedido y tu cliente queden asociados a un pedido que no les corresponde. ¿Ves el problema?

"Bueno", pensarás, "lo que puedo hacer es bloquear la tabla durante un instante mientras calculo ese máximo, y liberarla acto seguido". Podrías hacerlo, pero en un sistema con bastantes usuarios acabarás por crear bloqueos entre ellos disminuyendo el rendimiento. Mala idea...

Entonces ¿cómo podemos hacer?

Siendo este un problema tan común, los principales sistemas gestores de bases de datos relacionales ofrecen sus métodos respectivos para obtener el valor del auto-numérico actual en el mismo momento de la inserción del registro. Veámoslo.

SQL Server

SELECT SCOPE_IDENTITY()

Esta instrucción devuelve el último valor de identidad insertado en una columna de identidad en el mismo ámbito que la instrucción actual.  Un ámbito es un procedimiento almacenado, un desencadenador, una función o una consulta o lote de consultas. Por lo tanto si en la misma instrucción lanzamos un INSERT y esta función obtendremos inmediatamente el valor del auto-numérico generado por la base de datos. Lo verás usado constantemente.

MySQL

SELECT LAST_INSERT_ID()

En el caso de MySQL esta instrucción devuelve el último auto-numérico que se haya generado en la misma conexión que estemos usando actualmente. Mientras no compartamos conexiones (algo poco probable en cualquier caso) no tendríamos problema para obtenerlo con confianza.

Oracle

Lo de Oracle es un poco más complicado. Para empezar, y aunque parezca increíble, Oracle no tenía campos auto-incrementales hasta la aparición de su versión 12c en Junio de 2013. Antes de eso había que ingeniárselas usando Secuencias y disparadores para poder conseguir lo mismo.

Por ejemplo en Oracle antes de su versión 12c, crearíamos una tabla con un campo identificador con un código similar a este:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
	....
);

Es decir, como un campo numérico entero normal. Luego debíamos definir una secuencia:

CREATE SEQUENCE Orders_seq MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1;

Y un disparador (trigger) previo a la inserción que cada vez que se inserte un registro en la tabla de pedidos recuperaría el valor siguiente de la secuencia y lo metería en el campo adecuado del registro. Algo como esto:

CREATE OR REPLACE TRIGGER orders_bit
BEFORE INSERT ON Orders
FOR EACH ROW
BEGIN
  SELECT Orders_seq.NEXTVAL
  INTO   :new.id
   FROM   dual;
END;

Finalmente, junto con la inserción de datos se debía incluir una instrucción RETURNING que devolviese el identificador como resultado de la instrucción. Algo así:

INSERT INTO Orders (CustomerID, EmployeeID, OrderDate....)
VALUES (1, 1...)
RETURNING OrderID INTO :last_insert_id

¡Un rollo!

En la última versión 12c lo han mejorado un poco porque nos ahorran la primera parte. En lugar de tener que declarar una secuencia y un disparador podemos declarar directamente el campo como auto-incremental, así:

CREATE TABLE Orders
  (OrderID NUMBER GENERATED ALWAYS AS IDENTITY, ... );

Ahora, eso sí, para obtener el valor debemos hacer exactamente lo mismo que antes en la instrucción de inserción, es decir, usar RETURNING INTO para obtener el valor, como acabamos de ver.

Fecha de publicación:
campusMVP campusMVP es la mejor forma de aprender a programar online y en español. En nuestros cursos solamente encontrarás contenidos propios de alta calidad (teoría+vídeos+prácticas) creados y tutelados por los principales expertos del sector. Nosotros vamos mucho más allá de una simple colección de vídeos colgados en Internet porque nuestro principal objetivo es que tú aprendas. Ver todos los posts de campusMVP
Archivado en: Acceso a Datos

¿Te ha gustado este post?
Pues espera a ver nuestro boletín mensual...

Suscríbete a la newsletter

La mejor formación online para desarrolladores como tú

Comentarios (3) -

Juan Camilo Rojas
Juan Camilo Rojas

Hola, felicitaciones! Excelente artículo!

Me llamó la atención el proceso tan tedioso que toca realizar en las versiones anteriores de Oracle, justamente eso es lo que sucede en los proyectos de mi empresa con la versión 11g.

Me parece bastante acertado por parte de Oracle que hayan simplificado la forma de crear autoincrementales, pero al ser la versión 12c, me resulta muy complicado hacer el upgrade, ya que en la empresa donde trabajo no tenemos acceso a esa versión y tocaría actualizarla en los servidores de todos los clientes que usan nuestra aplicación, un proceso que puede ser traumático si no se hace una evaluación exhaustiva de las implicaciones que tiene ese cambio.

Por lo demás, me gustó muchísimo el articulo por la manera sencilla y estructurada de desarrollar el tema, lo cual permite ir al punto pero entendiendo la teoría que hay detrás.

Un saludo!

Responder

Hola. Esta muy bueno el artículo.
Me gustaría, si es posible, en el caso de SQL Server, que mencionen cuál es la diferencia entre SCOPE_IDENTITY Y @@IDENTITY y en que caso se debería usar uno u otro o a que problema y manera de solucionarlo nos enfrentamos. Muchas gracias.

Responder

José Manuel Alarcón
José Manuel Alarcón

Hola Alexis:

En SQL Server hay al menos 3 formas de obtener el último ID insertado, pero no son equivalentes:

- @@Identity: devuelve el último ID en la misma conexión
- Scope_identity(): devuelve el último ID creado en la misma conexión y el mismo contexto (de ahí su nombre). En este caso el contexto se refiere a la consulta o procedimiento almacenado actual.
- ident_current(nombre)(): devuelve el último Id de la tabla que le indiquemos (el que haya en ese momento)

En la mayor parte de los casos @@identity y scope_identity() se comportan igual, pero no siempre es así. Por ejemplo, si tienes un trigger (disparador) en tu tabla y lanzas una consulta de inserción que genera un nuevo registro y por lo tanto un nuevo ID, si el trigger a su vez genera otro registro en otra tabla (y otro ID en ésta), en esta situación @@identity te devolverá el ID del registro que ha insertado el disparador, no el ID de la tabla que has actualizado (lo cual no es lo que quieres casi nunca), ya que te devuelve el último que se haya generado en esa conexión. Sin embargo scope_identity() te devuelve el que esperabas (el de tu tabla) ya que siempre devuelve el del contexto, que en este caso es tu consulta de inserción.

Este es el motivo de que normalmente se recomiende el uso de scope_identity().

Espero que te sirva.

Saludos!

Responder

Agregar comentario

Los datos anteriores se utilizarán exclusivamente para permitirte hacer el comentario y, si lo seleccionas, notificarte de nuevos comentarios en este artículo, pero no se procesarán ni se utilizarán para ningún otro propósito. Lee nuestra política de privacidad.