El mejor método para generar IDs en tablas de movimiento

[darkamerico]

Saludos amigos,
En un entorno multiusuario, garantizar que las IDs (PKs) son únicas es parte fundamental de cualquier sistema bien estructurado.
Actualmente, por ejemplo yo hago lo siguiente:

Código Delphi [-]
for i := 1 to StrToInt(txtNroPagosTDR.Text) do
              begin
                idPago:=clsFx.CalcIDNewPago;
                try
                  dm.q_INSPagoIni.Close;
                  dm.q_INSPagoIni.Params[0].AsLargeInt:=idPago;
                  dm.q_INSPagoIni.Params[1].AsLargeInt:=IdObjeto;
                  dm.q_INSPagoIni.Params[2].AsInteger:=i;   // NroPago
                  dm.q_INSPagoIni.Params[3].AsString:='0';  // Estado Pendiente
                  dm.q_INSPagoIni.Params[4].AsSmallInt:=1;  // Tipo Cronograma Principal
                  dm.q_INSPagoIni.Params[5].AsFloat:=montoCuota;
                  dm.q_INSPagoIni.ExecSQL;
                  savedOk:=True;
                except
                  savedOk:=False;
                end;
                if not savedOk then
                begin
                  ShowMessage('Error: No se pudo Insertar el Registro de Pago - Cuota Simple');
                  exit;
                end;
              end;

Función:

Código Delphi [-]
function TFunciones.CalcIDNewPago: LongInt;
begin
  dm.q_CalcIDNewPago.Close;
  dm.q_CalcIDNewPago.Open;
  result:=dm.q_CalcIDNewPago.Fields[0].AsLargeInt;
end;

Consulta q_CalcIDNewPago:

Código SQL [-]
if((select top 1 id_pago from gestion.pago) is null) select 1;
else select max(id_pago)+1 from gestion.pago

sin embargo, no estoy seguro que sea la mejor forma de hacerlo.

Cualquier aporte será bienvenido.

Atte,
Américo

[mamcx]

Estas duplicando, de forma mas ineficiente*, lo que hace una bd. Usa:

• Llave natural si importa que sea único desde el punto de vista de la logica de negocios
• Autoinc si solo importa que sea único desde el punto de vista de la BD
• UUID si solo importa que sea único desde el punto de vista global

Lo que es dificil es garantizar que sea perfectamente incremental sin 'huecos' pero eso es otro asunto.

* p.d: Y adicionalmente este codigo trae un bug incorporado!

[darkamerico]

Empezado por mamcx

Estas duplicando, de forma mas ineficiente*, lo que hace una bd. Usa:

• Llave natural si importa que sea único desde el punto de vista de la logica de negocios
• Autoinc si solo importa que sea único desde el punto de vista de la BD
• UUID si solo importa que sea único desde el punto de vista global

Lo que es dificil es garantizar que sea perfectamente incremental sin 'huecos' pero eso es otro asunto.

* p.d: Y adicionalmente este codigo trae un bug incorporado!

Te agradecería que me indiques el bug

[[duilioisola]]

En firebird existen los generadores.
Los generadores siempre devuelven números diferentes (garantizado por la base de datos).
Los generadores siempre se incrementan, a pesar de que la transacción falle (Rollback).

Yo suelo dejar que la base de datos lo genere y me lo devuelva.
Con esta sentencia y el generador en el trigger:

Código SQL [-]
CREATE TRIGGER CABECERA_BI0 ...
AS
BEGIN
   new.id = GEN_ID(CONTADOR_CABECERA, 1);
   ...
END

INSERT INTO CABECERA (CLIENTE, FECHA, ...)
VALUES (:CLIENTE, :FECHA, ...)
RETURNING ID

Cuando por alguna razón no puedo utilizarlo de esta manera, pido un número al generador y luego lo utilizo en el insert

Código Delphi [-]
function TDMMain.ContadorGen(NomGen: string): integer;
begin
  Result := 0;
  if (Trim(NomGen) > '') then
  begin
     with THYFIBQuery.Create(nil) do
     begin
        try
           Close;
           DataBase := Self.DataBase;
           SQL.Text := 'SELECT GEN_ID(' + NomGen + ', 1) FROM RDB$DATABASE';
           ExecQuery;
           Result := Fields[0].AsInteger;
           Close;
           FreeHandle;
        finally
           Free;
        end;
     end;
  end;
end;


// Luego inserto llamando al procedimiento para el valor del parámetro ID
INSERT INTO CABECERA (ID, CLIENTE, FECHA, ...)
VALUES (:ID, :CLIENTE, :FECHA, ...)

[[duilioisola]]

En tu caso, básicamente haces un SELECT MAX(ID).

Si justo al mismo tiempo dos usuarios piden el MAX(ID) les devolverá el mismo número.
Luego al hacer el INSERT el primero logrará hacerlo y el segundo dará error porque estará insertando un duplicado.

Particularmente esta forma de obtener un ID es muy ineficiente en Firebird.
A pesar de tener índices por ese campo.
Tiene que ver con la multi versión de registros.
En otros motores no sabría decirte.

[mamcx]

Otro bug: Insertas 10 registros, el id va en 10. Ahora:


Borras el ultimo
Borras todos
Borras el primero

etc.


Un id debe ser global(en su contexto), único y *no repetible*

[marco3k]

Creo que la mejor forma de generar un id es con campo autonumerico o autoincrementable. Ahí simplemente el motor de bd genera el siguiente número sin necesidad de averiguar cual es el mayor número de un campo de tabla.