Respuesta

SEQUENCE (por ejemplo, en PostgreSQL y Oracle) es un objeto especial de base de datos que almacena el valor actual y permite obtener números únicos para inserciones (generalmente, claves). IDENTITY es una propiedad de campo que asigna automáticamente el siguiente valor en orden en la inserción (autoincremento de tabla en SQL Server, MySQL, PostgreSQL).

Diferencias clave:

SEQUENCE es un objeto fuera de la tabla, accesible desde diferentes lugares, se puede obtener el siguiente valor sin inserción (nextval / currval). Se utiliza para diferentes tablas, puede haber varias SEQUENCE en una tabla.
IDENTITY es parte de la definición de la columna, se incrementa automáticamente solo al insertar una fila.

Ejemplo:

-- PostgreSQL SEQUENCE
CREATE SEQUENCE order_seq;
INSERT INTO orders(id, name) VALUES (nextval('order_seq'), 'First Order');

-- PostgreSQL IDENTITY
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username TEXT
);
INSERT INTO users(username) VALUES ('ivan');  -- id se asignará automáticamente

Problemas y matices:

Si se insertan id manualmente, es posible que haya "huecos" o colisiones.
Al eliminar filas, los valores no se reutilizan, lo que puede causar una ruptura en el rango de claves.
En algunas bases de datos (por ejemplo, MySQL), al revertir una transacción, el autoincremento puede "saltar hacia adelante", dejando el valor sin cambiar.
SEQUENCE se puede usar en varias tablas, pero esto complica el mantenimiento de la unicidad.

Pregunta trampa

¿Qué sucede si dos consultas solicitan al mismo tiempo nextval() de un SEQUENCE? ¿Es posible obtener valores no únicos?

Respuesta: No, SEQUENCE garantiza unicidad. Cada consulta recibe un valor único, incluso con solicitudes simultáneas. Sin embargo, el orden puede no coincidir con el orden de inserción debido al paralelismo.

Ejemplo:

-- El primer hilo obtiene nextval=100, el segundo a los pocos momentos — nextval=101.
-- Incluso si luego el primero lo revierte, el valor 100 ya se utiliza.

Historia #1

Después de mover la base de datos de MySQL a PostgreSQL, se olvidaron de las diferencias entre SEQUENCE e IDENTITY: intentaron insertar manualmente los id, lo que provocó un error de "clave duplicada" al generar automáticamente. Se corrigió trasladando completamente la gestión de claves a SEQUENCE.

Historia #2

Al eliminar parcialmente filas de una tabla con autoincremento, una vez fue necesario "llenar" el rango de id. Debido a la recreación incontrolada de nuevas filas, aparecieron ambigüedades: las claves se volvieron inconsistentes con las reglas de lógica de negocio, y se tuvo que pasar a una SEQUENCE separada para el control.

Historia #3

Debido a la falta de control sobre SEQUENCE en un gran sistema OLTP, el número de pedido superó 2,147,483,647 y causó un fallo crítico en la API de integración, ya que no estaba preparado para valores bigint. La consulta se volvió a implementar con límites de control para SEQUENCE.

¿Cómo funcionan y en qué se diferencian fundamentalmente la generación y el manejo de secuencias (SEQUENCE/IDENTITY) en la creación automática de claves únicas en SQL? ¿Cuáles son los problemas potenciales que pueden surgir?

Respuesta

Pregunta trampa