Malas Prácticas: Obtener la secuencia de una Clave Primaria con un Select Max()…

Hace no demasiado inaguré la sección de Buenas Prácticas, sección en la que ya se han añadido algunas entradas con recomendaciones al desarrollador.
Hoy dándole vueltas a una nueva buena práctica para añadir (y después de haber oí­do una barbaridad por la oficina), he pensado que tal vez serí­a interesante hablar también de las cosas que no hay que hacer bajo ningíºn caso, de ahí­ surge esta nueva sección llamada “Malas Prácticas”.
Como primera mala práctica vamos a hablar de una forma que se utiliza para obtener la secuencia de la primary key de una tabla. Insisto es una mala práctica, la cual no recomiendo para nada usar, más adelante se explicará por qué. 
Como siempre, lo describo con un ejemplo, que es la forma en la que creo yo que todo queda más claro.
Pongamos el caso de que tenemos una tabla llamada COCHE que consta de dos campos, COCH_CODIGO Y COCH_NOMBRE. COCH_CODIGO es la clave primaria de la tabla, y es de tipo numérico. COCH_NOMBRE es un texto de tamaño 50 caracteres. 
Un requerimiento de nuestra aplicación exige que al dar de alta un nuevo coche en nuestra base de datos, asociemos el código del coche al usuario que ha insertado el coche (imaginemos que la tabla USUARIO contiene un campo llamado COCH_CODIGO donde se guarda la referencia al coche del usuario).
Una MALA PRíCTICA es intentar resolver este problema de la siguiente manera (utilizo pseudocódigo)
/* Obtenemos el código del usuario */
codigoUsuario = obtieneCodigoUsuarioActual();
/* Obtenemos la Id del íºltimo coche que añadimos */
int ultimaId = devuelvePrimerRegistro(“SELECT MAX(COCH_CODIGO) +1 FROM COCHE”);
/* Insertamos el registro */
ejecutarSQL(INSERT INTO COCHE (COCH_CODIGO, COCH_NOMBRE) VALUES (ultimaId, ‘Coche Fantástico’));
/* Actualizamos los datos del usuario */
ejecutarSQL(UPDATE USUARIO SET COCH_CODIGO = ultimaId where USUA_CODIGO = codigoUsuario);
¿Dónde está aquí­ el problema? …
¿Que ocurrirí­a si dos usuarios al mismo tiempo fueran a dar de alta un nuevo coche? Fijaros en la secuencia
1) El primero harí­a el SELECT MAX y obtendrí­a el código 23.
2) Antes de que el primero haga el INSERT en la tabla COCHE, el segundo harí­a el SELECT MAX y obtendrí­a también 23
3) El primero hacer el INSERT, todo va bien.
4) El segundo hacer el INSERT… y PUM, CONSTRAINT ERROR, Clave Primaria duplicada.
¿Soluciones? Afortunadamente existen unas cuantas.
1) Secuencias ORACLE
Si usáis como base de datos ORACLE estáis de suerte, ya que aquí­ existe el concepto de secuencia. Puedes crearte una secuencia para cada tabla, y antes de realizar el insert pedir el siguiente níºmero de dicha secuencia.
int secuencia = devuelveEscalar(“SELECT SEQ_COCHE.NEXTVAL”);
ejecutarSQL(INSERT INTO COCHE (COCH_CODIGO, COCH_NOMBRE) VALUES (secuencia, ‘Coche Fantástico’));
ejecutarSQL(UPDATE USUARIO SET COCH_CODIGO = secuencia where USUA_CODIGO = codigoUsuario);
Cuando el segundo usuario que ataque al código concurrentemente pida la secuencia, nunca será la que ha tenido el primero, por lo que nunca saltará la constraint de la foreign key.
2) Secuencias a media
Si trabajáis por ejemplo en SQL Server no tenéis secuencias. Pero eso no quiere decir que no podáis currároslas vosotros. Por ejemplo, podrí­ais crear una tabla llamada SECUENCIA, donde tengas campos por cada una de las tablas de vuestra base de datos. Los campos serí­an de tipo entero.
Os creais una función que le paséis el nombre de la tabla, y que os busque en la tabla SECUENCIA el campo correspondiente a esa tabla, os lo retorne y actualice el campo en la tabla SECUENCIA.
Realmente con esta forma de trabajar podrí­ais hacer igual que con Oracle, pero currado por vosotros.
3) Procedimientos Almacenados
Los procedimientos almacenados os proveen de herramientas para devolveros los datos producidos en un INSERT. Por ejemplo en PL/SQL tenéis la instruccion RETURNING, y en Transact/SQL tenéis el comando @@IDENTITY que te devuelve la íºltima identidad obtenida para la conexión activa.
Saludos a todos.
Miguel

9 thoughts on “Malas Prácticas: Obtener la secuencia de una Clave Primaria con un Select Max()…”

  1. Hola Ricardo,
    En Oracle no existen los campos autonuméricos, y la herramienta que proporciona el sistema para lograr un funcionamiento similar (aunque más potente bajo mi punto de vista) son las secuencias.
    En cuanto a cómo emular un sistema de secuencias con SQL Server, se muestra en el artí­culo íºnicamente como curiosidad. SQL Server cuenta con campos autonuméricos, por lo que cuentas con esta alternativa para tus desarrollos.
    Saludos.
    Miguel.

  2. Hola Miguel, gracias por la respuesta, lo que sucede es que no entiendo la necesidad de usar secuencias, pues me parecen fastidiosas de controlar, ademas de que solo funcionan en oracle, creo que se pueden prescindir totalmente de ellas.

  3. Hola Ricardo,
    Tal como te comentaba, en Oracle no tenemos otra si queremos simular el uso de los autonumericos.
    En cuanto a emular este sistema en otros Gestores de Bases de Datos que cuenten con autonumericos, tal vez a priori no tenga sentido, ya que para algo esta el autonumerico, aunque todo dependera del problema que debas resolver. Tal vez necesitas algo mas potente que un autonumerico, en ese caso emular secuencias te daria esa flexibilidad.
    Saludos.
    Miguel.

  4. Hola:
    Soy una desarrolladora newby y he utilizado Oracle asi como SqlServer, me parece muy interesante este artí­culo porque aunque SqlServer cuenta con autonumerí­cos para el identity yo tengo un caso especial.
    En una de mis tablas tengo un campo Folio, y otro IdDepartamento, el campo Folio debe ser consecutivo dentro del Departamento, pero no consecutivo necesariamente al registro anterior a el, si no al registro anterior del MISMO DEPARTAMENTO que el, se me complica explicarlo pero un pequeño ejemplo:
    Tenemos los siguientes registros…
    Departamento = 3, Folio = 3001
    Departamento = 4, Folio = 4001
    Departamento = 4, Folio = 4002
    Departamento = 4. Folio = 4003
    Departamento = 3, Folio = 3002
    Creo que para cubrir esta necesidad si me hacen falta las secuencias, ¿o de que manera lo podria hacer en SqlServer ?

  5. Hola Mayra,
    Me temo que vas a necesitar un contador asociado a cada uno de los departamentos que vayas creando; efectivamente asociar una secuencia a cada departamento podrí­a ser tu solución.
    Eso sí­, ten en cuenta que si los níºmeros de folio siempre han de ser consecutivos, sin huecos, el obtener la secuencia para crear un nuestro registro tendrás que meterlo en una transacción, porque si la operación falla, perderás la secuencia y podrí­as tener registros asociados a departamentos con níºmeros de folio no consecutivos.
    Un saludo.
    Miguel.

  6. Estas son mis 2 preguntas.
    No encuentro la opcion de autonumerico en esta version de sql server.
    con respecto a la segunda me están pidiendo, ejemplo, el código de las agencias el cual tiene que se tiene que agarra de otra tabla llamada consecutivo, el consecutivo para las agencias es “AG-” además se tiene que incrementar autonumericamente cuendo cree una agencia. tendria que quedar así­. en la tabla de la base de datos.
    ID_agencia Nombre
    AG-1 Agencia Sur
    AG-2 Agencia norte
    AG-3 Agencia este
    AG-4 Agencia oeste
    ,etc..
    alguna sugerencia?

  7. picachu, solo se puedeo aplicar la propiedad de autonumerico a los campos tipo numéricos, no a los alfanumericos, podrías hacer 2 campos, 1 numérico y al obtenerlo concatenarlo con el alfanumerico, o hacerlo por codigo y solo tener 1 numérico.
    El SQL MAX pienso que sería muy raro aunque no imposible que concurrieran 2 usuarios dando clic en el mismo minuto, segundo, milisegundo, etc, etc, aunque no deja de ser una mala practica pero no por esos motivos, me voy mas por el lado de que si un ID ha sido eliminado y no hubieron buenas relaciones foraneas, se perderá la integridad al revivir un ID muerto.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.