¿ Claves naturales o artificiales ?.

Carlos Sacristán

Aquí se expone un resumen de una conversación muy intersante mantenida en las news sobre las ventajas e inconvenientes del uso de las claves naturales y las claves artificiales. Carlos ha ido dando forma a todo lo hablado allí.

Tu revista sobre .NET

Diseño de bases de datos con SQL Server ¿ Claves naturales o artificiales ?

Pregunta ésta difícil de dar una contestación sencilla, puesto que muchas veces influye más el gusto del modelador que aspectos técnicos. Sin embargo, en este pequeño artículo vamos a intentar llegar a una conclusión, o al menos, exponer los pros y los contras de escoger una u otra opción. Creo que puede ser interesante porque no se van a tener en cuenta los puntos de vista de profesionales muy renombrados, MVP's la mayoría de ellos, pero sobre todo (y lo más importante), amigos míos ;-)

Veréis que nos centraremos en los tipos de datos IDENTITY, por ser el ejemplo más claro de clave artificial, aunque no el único.

Empecemos con la polémica...

Bueno, comencemos por el principio. No es descabellado pensar que las claves naturales fueron las primeras que aparecieron, por ser las más lógicas, más "naturales" (permitidme el juego de palabras), por lo que leer la opinión de un "tal" Joe Celko no estaría mal, ¿verdad?. Aunque está en inglés, creo que se entiende lo suficientemente bien como para no tener que traducirlo (y así no me ponéis en un compromiso ;-) ). Ahí va:

**********************************

The IDENTITY column is one of these mistakes.

1) It is not part of the SQL-92 Standard and it is highly proprietary to the Sybase family. It is not portable -- not quite the same thing as proprietary, since you can often translate one SQL dialect into another with a simple replacement (i.e. the % operator becomes the MOD () function). So your code will not move over to a new database.

2) IDENTITY looks like a datatype, but it is not. Create a table with one column in it and make it an IDENTITY column. Insert a number into the table and see what happens. Try to set it to NULL. If you cannot insert, update and delete all the columns, then this is not a table!

3) IDENTITY looks like a constraint, but it is not. Try to create a table with two IDENTITY columns and it fails. If you cannot add it to a column, then it is not a constraint. It is possible to write a a set of constraints that prohibit data from ever being put in the table (their predicate is always FALSE). It is possible to write a a set of constraints that allow anything in the table (their predicate is always TRUE). But no constraint can prohibit the creation of the table itself -- that is a meta-constraint.

4) It is not relational. Consider this statement on a table, Foo, which has an IDENTITY column. Assume the query returns more than one row.

INSERT INTO Foo (x)
SELECT a FROM Bar;

You will get a result like this:
IDENTITY X
============
1 'a'
2 'b'
3 'c'

but if the query changed an index or was put on the physical disk data page differently, you might have gotten:

IDENTITY X
============
1 'b'
2 'c'
3 'a'

Explain why one result is the logically correct choice for an identifier and all other choices are not, without any reference to the physical implementation. You cannot.

Instead of treating the query as a set, you are doing 1950's sequential processing using the underlying sequential file system the Sybase family started with.

5) If you have designed your tables correctly, they will have a meaningful primary key derived from the nature of the entity they model. The IDENTITY column should be a redundant key. The reason IDENTITY columns are popular as keys is that they are easy to declare. This is also the same reason that people build non-normalized databases and put pennies in fuse boxes -- easy is not right.

6) It is a bitch to do calculations on IDENTITY column values. Well, it was hard to do direct math on the sequential position of a record in a 1950's punch card system and that it what the IDENTITY is mimicking.

7) There is no check digit in an IDENTITY columns value, so you have no way of verifying it if you use it as a key.

8) If you use IDENTITY as a key, the values tend to cluster on physical data pages because they are sequential. The result is that if the most recent rows are the most likely to be accessed, there will be locking contention for control of those physical data pages. What you really wanted in a key is some spread of the rows over physical storage to avoid having every user trying to get to the same page at the same time.

9) The actual implementation of the IDENTITY column has been problematic since Version 7.0. You can look up threads in the news groups to get assorted tales of woe.

There are other ways of getting a unique identifier for a table. The most portable method for getting a new identifier number which is not in the set is something like this:

INSERT INTO Foobar (keycol, a, b, c...)
VALUES(COALESCE((SELECT MAX(keycol) FROM Foobar) +1, 0), aa, bb, cc, ...);

The scalar subquery expression returns the current high value for the key column, and then increments it. If there is no maximum value (i.e. this is the first row to be inserted), then it returns zero. Using this basic idea, you can replace the increment with a different constant or a random number generator. You can also add code to create a check digit.

Another method is to hash the columns that make up a compound key so that you have single short column that can be reconstructed if you need to verify it.

**********************************

Uuuuuf… creo que le gustan, ¿verdad?. Bueno, veamos, ¿qué es lo que podemos sacar en claro de esta larga explicación?.

• en primer lugar, es obvio que un IDENTITY no es muy portable que digamos, al menos no directamente. Sin duda es un problema grave que nos podemos encontrar si a alguien se le ocurre la locura de migrar de SQL Server a Oracle (pobrecito él)
• también nos encontramos con el problema de que, aunque podemos definir una columna como de este tipo de datos, luego en realidad no podemos manejarla como tal. Resulta paradójico, y en ciertos casos puede resultar problemático
• la facilidad de uso de estos datos y su prácticamente nulo mantenimiento ha hecho que se hayan extendido hasta el punto de ser usados erróneamente, dando lugar a malos diseños de tablas por no querer pensar más las cosas
• no son imprescindibles, ni mucho menos. Podemos conseguir su misma funcionalidad y aumentarla con muy poco código, y sabremos en todo momento qué es lo que está pasando

Bien, entonces, si parece que no dan más que problemas, ¿quién los quiere usar después de esto?. Tranquilos, tranquilos, que no todo va por este lado; vamos poquito a poco a ir replicando todas estas pegas, a ver hasta dónde llegamos.

La primera réplica está clara: no hay que ser tan dogmático. Si nos atuviésemos siempre a lo que los libros dicen, seríamos puros autómatas sin ninguna imaginación y, por tanto, no lograríamos soluciones nuevas que pueden facilitarnos la vida en ciertos casos. Lo estándar no puede llegar hasta los problemas con los que nos encontramos diariamente por ser éstos muy concretos (lógico, son nuestros y de nadie más), así que nada impide que nos salgamos de lo que predica, en este caso, SQL-92, y que nuestra solución sea mejor. ¿O es que a alguien se le ha olvidado lo bien que viene la denormalización en ciertas situaciones?.

Bueno, parece que no todo son inconvenientes sobre los IDENTITY. ¿Volvemos a utilizarlos o aún es demasiado pronto?. Por si alguien se ha convencido simplemente con el párrafo anterior, veamos qué ocurre con estos tipos de datos en las transacciones con un ejemplo:

/* Código con IDENTITY */
DROP TABLE Test
GO

CREATE TABLE Test(
TestID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TestName varchar(20) NOT NULL
)
GO

INSERT TEST VALUES ('Uno')
INSERT TEST VALUES ('Dos')
INSERT TEST VALUES ('Tres')
GO

-- Revisar los Valores
SELECT * FROM TEST
GO

-- Revisar el IDENTITY
DBCC CHECKIDENT(Test)

-- Hagamos una transacción fallida
BEGIN TRAN
INSERT TEST VALUES ('Cuatr')
-- OOPS NOS EQUIVOCAMOS, VOLVAMOS ATRÁS CORRIENDO!!
ROLLBACK TRAN
GO

-- Menos mal que todo volvió como estaba antes. Hagámoslo ahora correctamente.
INSERT TEST VALUES ('Cuatro')
SELECT * FROM TEST
-- ¿Pero qué ha pasado aquí? OOPS NO HAY ROLLBACK EN LOS IDENTITY!!!

DBCC CHECKIDENT(Test)

/* Código sin IDENTITY */
DROP TABLE TEST
GO

CREATE TABLE Test(
TestID INT --IDENTITY(1,1) NOT NULL PRIMARY KEY,
TestName varchar(20) NOT NULL)
GO

INSERT TEST SELECT COALESCE(MAX(TESTID),0)+1, 'Uno' FROM TEST
INSERT TEST SELECT COALESCE(MAX(TESTID),0)+1, 'Dos' FROM TEST
INSERT TEST SELECT COALESCE(MAX(TESTID),0)+1, 'Tres' FROM TEST
GO

SELECT * FROM TEST
GO

-- Vamos a ver qué pasa ahora si nos equivocamos
BEGIN TRAN
INSERT TEST SELECT COALESCE(MAX(TESTID),0)+1, 'Cuatr' FROM TEST

-- Ya no sé si fiarme… ¿o sí?
ROLLBACK TRAN
GO

-- Qué nos mostrará... ¿
SELECT * FROM TEST
INSERT TEST SELECT COALESCE(MAX(TESTID),0)+1, 'Cuatro' FROM TEST
SELECT * FROM TEST

-- AHORA SÍ!!!!

¡¡Vaya!! Resulta que ahora no me puedo fiar de los IDENTITY porque no quieren saber nada de las transacciones. Problema gordo éste, no hay duda: los valores se pierden y no se pueden recuperar. Está claro entonces: no hay que usarlos, me puedo encontrar con problemas gordos y que además no tienen solución. Los desecho totalmente... ¿seguro?.

Pongámonos ahora del otro lado. ¿Os acordáis cuando decía que las facilidades que nos brindaban este tipo de datos podían dar lugar a usos erróneos?, pues el código anterior es un claro ejemplo de ello. Es decir, dicho campo sólo está para simplificar, y su valor no me debería importar mucho, salvo para hacer uniones entre tablas. Veamos: si mi clave principal natural es empresa+serie+factura, estos datos los mantengo, y hago que sean únicos, y además añado un campo IDENTITY. El usuario nunca verá este campo IDENTITY, pero yo lo utilizaré internamente para hacer JOIN con él y así unir esta tabla con otras. En ese caso poco me importa ese número o que me queden huecos al no hacer ROLLBACK de él. De este modo obtenemos dos puntos a favor: la simplicidad en las uniones y lo compacta que es la clave primaria de la tabla (no es lo mismo 20 caracteres que los 4 bytes que ocupa un entero en SQL Server)

Una página de índice con una clave de 10 bytes podrá almacenar, simplificando, unas 300 entradas por página (8000/(10+14)), mientras que un índice con una clave de 4 bytes podría almacenar, simplificando de nuevo, unas 400 entradas por página (8000/(4+14)). Esto representa una diferencia considerable, que permitiría en algunas operaciones una diferencia de rendimiento apreciable.

Otro caso distinto es la ejecución de JOIN, en cuyo caso el procesador debe realizar una serie de comparaciones de valores extraídos de los campos de enlace en ambas tablas. Con procesadores de 32 bits, cualquier valor mayor de 4 bytes representa bien una comparación externa al procesador, bien una operación que requiere más de un ciclo de CPU, lo cual representa en cualquier caso una merma de rendimiento. Sin embargo, en este caso, como en el de claves foráneas, los valores almacenados en esos campos son completamente irrelevantes para el usuario, y van a ser utilizados solamente por SQL Server para validar entradas y para ejecutar enlaces, por lo que un campo IDENTITY podría ser perfectamente válido.

Como parece que estamos cogiendo carrerilla, vamos a seguir defendiendo a los IDENTIY, o más en general a las claves artificiales, porque vamos a recordar que en SQL Server también tenemos los tipos de datos UNIQUEIDENTIFIER (GUID's). Y es que un valor de cualquiera de estos tipos de datos no cambia nunca, justamente porque nos es irrelevante (no como un código de oficina o de artículo de nuestra empresa, que puede que a alguien se le ocurra la "magnífica" idea de modificarlo). Esto es importante en la concurrencia del bloqueo optimista. Veámoslo con un ejemplo. Supongamos que tenemos una la tabla con una clave que pueda cambiar:

Clave Campo1 Campo2
C1 V11 V12
C2 V21 V22

El usuario A y el usuario B leen ambos el registro C1:

SELECT * FROM Tabla

El usuario A cambia el valor de la clave:

UPDATE Tabla SET Clave = C0 WHERE Clave =C1

Entonces el usuario B intenta cambiar el valor del campo1:

UPDATE Tabla SET Campo1 = V00 WHERE Clave = C1.

¿Qué ocurre?, simplemente no puede actualizar el registro porque no existe; peor aún, no puede refrescar el registro porque el usuario B no tiene nada que lo identifique. Con las claves artificiales esto no pasa, ya que las claves artificiales no cambian. No es un inconveniente que no se puedan cambiar, es una ventaja. Si ejecutas una instrucción como ésta:

UPDATE Tabla SET Campo1 = Valor1 WHERE Id = ValorId

Siendo Id la clave artificial, puedes estar seguro de que si la instrucción no actualiza ningún registro es porque el registro se ha eliminado, no quedando la posibilidad de que se haya modificado la clave primaria ya que es artificial.

Está claro que el punto anterior es una ventaja, y eso sin contar que el pequeño tamaño de un dato de tipo entero no es comparable con una PK formada por varios campos alfanuméricos. Son dos razones prácticamente no discutibles desde este punto de vista. ¿Entonces nos quedamos con los IDENTITY?; bueno, puede ser un error desechar una clave natural simplemente por su tamaño (10, 100 ó 200 bytes). Sí, sí, leer atentamente lo siguiente y os sorprenderéis:

Supuestos:

En alguna tabla (la llamaremos 'ArticulosProducidos') se requiere que estas tablas estén relacionadas y juntas producen una llave primaria. Asumamos que esa tabla tiene 5 Millones de filas. Por ultimo todas las tablas requieren 90 bytes en otras columnas.

Esta tabla 'ArticulosProducidos', se ve así en índices agrupados:

Es decir, nos ahorramos un 80% en el índice agrupado y un 33% en el número de páginas requeridas para leer una fila, y como son sólo referencias a otras tablas no tienes que crear un índice UNIQUE (que sería mortal porque consumiría ¡¡728 Mb!!). Pero eso no es lo mejor: si revisamos las páginas de datos se reducen sustancialmente, ya que ahora pasan de consumir 1 Gb a sólo 578 Kb: un ahorro del ¡¡¡30%!!!.

Si en tu empresa o a tu cliente, se les ocurre contratar a Joe Celko para validar tu diseño de BD, y sale con el cuento de ANSI, teoría de BD, etc. puedes rápidamente sacar tus cálculos y decirle a tu jefe o cliente:

• "Sí, efectivamente decidí romper XXXXX para lograr un incremento del 100% en el desempeño, ahora si usted quiere bajar la velocidad a la mitad, puedo volverla a normalizar."

Todos sabemos la respuesta...

Pero no cantes victoria, ahora contratan a Emilio Boucau, para que analice desempeño, y Emilio sale con que la consulta mas frecuente es la 'ArticulosProducidos' filtrados por Artículo, Envase y Cartón y cuando se hace dicha consulta (por llaves naturales que es lo que conoce el usuario) escribiste:

SELECT ...
FROM ArticulosProduccion AS AP JOIN Articulos AS A ON AP.ArticuloID=A.ArticuloID JOIN Envases AS E ON AP.EnvaseID=E.EnvaseID JOIN Cartones as C ON AP.CartonID=C.CartonID
WHERE A.Articulo=@Articulo AND E.Envase=@Envase AND C.Carton=@Carton

Con lo cual se produce una búsqueda en cada uno de los índices de dichas tablas para luego buscar en la tabla de 'ArticulosProducidos', produciendo una lectura de 8 páginas (3 Tablas x 2 Niveles + 2 del índice sobre la llave Artificial), mientras que si hubieras mantenido el esquema relacional puro sólo hubieras tenido que leer 3 páginas haciendo que el plan de ejecución de tu consulta se vea horroroso.

Pero aún así tienes las de ganar porque vuelves a sacar tus números y dices:

• "Es correcto, pero los índices de dichas tablas ocupan 3.6 Mb y por la frecuencia que se usan normalmente están en memoria RAM, mientras que si uso una llave Primaria Natural ocupa 15 Mb, esto sin contar con la diferencia de espacio en los datos que produce un ahorro de 30%, si usted quiere bajar la velocidad en un ...!."

¡¡¡Felicidades!!! Tienes un diseño a prueba Joe y Emilio. Pero creo que igual te van a echar porque si contratan a Emilio y a Joe para que valide tus diseños es porque están buscando una excusa :)

Conclusiones

Ah, pero... ¿hay conclusiones después de todo esto?. Bueno, creo que hay una cosa clara: es un tema polémico, ¿verdad? ;-)

No, hablando en serio, lo que podemos decir es que, como en muchos otros temas de las bases de datos, no existe una receta mágica ni algo que se pueda aplicar en todos los casos y que sea la mejor solución. Y es que estamos hablando de diseño, y ya sabemos todos lo que eso significa: muchas horas de análisis y de leer y leer los requisitos del usuario.

Concretando en nuestro tema, no se puede rechazar ninguna de las dos opciones tajantemente porque, tal y como hemos visto en este (largo) artículo, tienen ventajas e inconvenientes. Como hemos dicho antes, habrá que analizar la situación en la que nos encontremos cuando nos enfrentemos a este problema decidir por una u otra.

La idea de este artículo ha sido intentar tener más claro lo que supone usar las claves naturales o las claves artificiales a partir de los comentarios de grandes profesionales de SQL Server. Espero haberlo conseguido...

Para cualquier tipo de sugerencia, colaboración o comunicación, diríjanse a

webmaster@helpdna.net