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):
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: