10.10.07
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
Ricardo said,
May 23, 2009 at 5:14 pm
Por qué usuas secuencias??..
Miguel said,
May 23, 2009 at 5:50 pm
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.
Ricardo said,
May 26, 2009 at 4:14 pm
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.
Miguel said,
May 26, 2009 at 7:21 pm
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.
Mayra said,
November 19, 2009 at 7:26 pm
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 ?
Miguel said,
November 24, 2009 at 11:49 pm
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.