¿Claves primarias naturales o subrogadas?

¿Clave primaria natural (generalmente una candidata compuesta, de ahí nace parte del debate) o subrogada? Cada vez que en un proyecto me he enfrentado al diseño de una base de datos he encontrado posturas encontradas entre los miembros del equipo. En el post de hoy vamos a ver en qué consiste cada solución, sus pros y contras y qué nos puede hacer decidir por uno u otro tipo de clave.

Recordemos que una Clave primaria (PK) es un conjunto de campos que identifica de forma única un registro de una tabla. Puede ser un solo campo o varios. El debate se produce cuando tenemos que escoger qué campos formarán la clave primaria de la tabla. Podemos utilizar una clave natural o una subrogada.

Clave natural vs. subrogada

Una clave natural (candidata) está formada por atributos de la entidad en el dominio. Dicho de una forma más sencilla, por atributos que la gente utiliza, como el número de factura o un DNI. En cambio, una clave subrogada no tiene un significado de negocio. Puede ser un campo auto-numérico (p.ej. IDENTITY en SQL Server), un GUID, un contador almacenado en otra tabla, etc. Idealmente, el sistema de base de datos debería ser el encargado de generar estas claves.

Vamos a utilizar un ejemplo para ver las diferencias entre los dos tipos de clave. Modelaremos las tablas necesarias para representar una entidad factura y sus líneas de factura. La factura tiene un número de factura y un importe, mientras que las líneas de factura tienen un número de línea, una descripción, una cantidad y un precio unitario.

Ejemplo con claves naturales

En este caso disponemos de una clave candidata (NumeroFactura), que vamos a utilizar como clave primaria. Los scripts de generación y el esquema quedan así:

-- Factura
CREATE TABLE [dbo].[Factura](
	[NumeroFactura] [int] NOT NULL,
	[ImporteTotal] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_Factura] PRIMARY KEY CLUSTERED
(
	[NumeroFactura] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- LineaFactura
CREATE TABLE [dbo].[LineaFactura](
	[NumeroFactura] [int] NOT NULL,
	[LineaFactura] [int] NOT NULL,
	[Descripcion] [nvarchar](50) NOT NULL,
	[Cantidad] [decimal](18, 2) NOT NULL,
	[PrecioUnitario] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_LineaFactura] PRIMARY KEY CLUSTERED
(
	[NumeroFactura] ASC,
	[LineaFactura] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- FK a Factura
ALTER TABLE [dbo].[LineaFactura]  WITH CHECK ADD  CONSTRAINT [FK_LineaFactura_Factura] FOREIGN KEY([NumeroFactura])
REFERENCES [dbo].[Factura] ([NumeroFactura])
GO
ALTER TABLE [dbo].[LineaFactura] CHECK CONSTRAINT [FK_LineaFactura_Factura]
GO

Ejemplo con claves subrogadas

Si preferimos utilizar claves subrogadas, hemos de modificar los scripts anteriores para

  • Agregar el campo correspondiente para la clave subrogada (en este caso, un campo identity) y establecerlo como clave primaria.
  • Agregar índices únicos en ambas tablas, que protejan contra duplicados en la clave candidata.

El script queda así:

-- Factura
CREATE TABLE [dbo].[Factura](
	[IdFactura] [int] IDENTITY(1,1) NOT NULL, -- Clave subrogada
	[NumeroFactura] [int] NOT NULL,
	[ImporteTotal] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_Factura] PRIMARY KEY CLUSTERED
(
	[IdFactura] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Indice unico NumeroFactura
CREATE UNIQUE NONCLUSTERED INDEX [IX_Factura] ON [dbo].[Factura]
(
	[NumeroFactura] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

--LineaFactura
CREATE TABLE [dbo].[LineaFactura](
	[IdLineaFactura] [int] IDENTITY(1,1) NOT NULL, -- Clave subrogada
	[IdFactura] [int] NOT NULL,
	[LineaFactura] [int] NOT NULL,
	[Descripcion] [nvarchar](50) NOT NULL,
	[Cantidad] [decimal](18, 2) NOT NULL,
	[PrecioUnitario] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_LineaFactura] PRIMARY KEY CLUSTERED
(
	[IdLineaFactura] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- FK a Factura
ALTER TABLE [dbo].[LineaFactura]  WITH CHECK ADD  CONSTRAINT [FK_LineaFactura_Factura] FOREIGN KEY([IdFactura])
REFERENCES [dbo].[Factura] ([IdFactura])
GO
ALTER TABLE [dbo].[LineaFactura] CHECK CONSTRAINT [FK_LineaFactura_Factura]
GO

-- Undice unico IdFactura, LineaFactura
CREATE UNIQUE NONCLUSTERED INDEX [IX_LineaFactura] ON [dbo].[LineaFactura]
(
	[IdFactura] ASC,
	[LineaFactura] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Ventajas e inconvenientes

Como vamos a ver, las ventajas e inconvenientes de ambas soluciones son en su mayoría complementarias. Empecemos con las Claves naturales:

  • Ventajas
    • No requieren un índice único adicional.
    • Se pueden utilizar para búsquedas.
    • Fáciles de entender y seguir.
    • No requieren joins al hacer consultas en las tablas relacionadas. En el ejemplo, puedes buscar por NumeroFactura en la tabla de líneas sin necesidad de join con la tabla de facturas.
  • Inconvenientes
    • Durante el ciclo de desarrollo puede ser necesario modificar la PK de la entidad, con la correspondiente propagación del cambio a las tablas que la referencien.
    • Si se utilizan cadenas, los joins son más lentos en comparación con tipos numéricos. Al utilizar más espacio, también caben menos datos en cada página de índice.
    • Puede producirse contención si generamos las claves en nuestra aplicación, según cómo lo implementemos. Piensa en varios hilos generando claves para las inserciones y los mecanismos de locking necesarios para no generar duplicados.

El primer inconveniente es el más importante: imagina que ahora nos fusionamos con otra empresa, y hay que asimilar sus facturas. Si añadimos un campo Empresa a la FK de la tabla Factura, también habremos de modificar la FK de la tabla LineaFactura, así como de todas las tablas que la referenciasen.

El segundo inconveniente es relativo, puesto que en la alternativa vamos a necesitar igualmente índices por la clave candidata para búsquedas e integridad, lo que también nos acabará afectando😐 Lo que seguro es importante siempre es tener buenas claves candidatas, sea cual sea la aproximación que escojamos :P En cuanto al tercer inconveniente, depende mucho de cada situación porque, por ejemplo, también podríamos tener claves que formen parte del dominio y se puedan generar por parte del sistema (por ejemplo, un GUID).

Veamos ahora los pros y contras de las Claves subrogadas que coinciden casi uno a uno con los anteriores, dándoles la vuelta:

  • Ventajas
    • La lógica de negocio no está en las claves. Te proteges ante cambios.
    • Son pequeñas. Enteros o GUIDs ocupan muy poco. Joins más rápidas.
    • No hay contención puesto que los mecanismos de generación secuencial o de GUIDs son rapidísimos y los provee el sistema.
    • Uniformidad. Se pueden programar tareas de mantenimiento sobre tablas que asumen un esquema de PK común.
  • Inconvenientes
    • Siempre se requiere una join al buscar en las tablas hijas/relacionadas.
    • Normalización. Se requiere un índice único adicional sobre la clave candidata (natural), y ojo con olvidárselo, porque podríamos tener datos duplicados en el dominio, que serían correctos en la BBDD.
    • Si el mecanismo de generación de claves lo controla la BBDD, hay que tener en cuenta el soporte para distintas bases de datos, que no funcionan igual.

He intentado mostrar los problemas que me parecen más relevantes o con los que me he encontrado más a menudo. En algunos de los enlaces que he dejado a lo largo del post se mencionan y argumentan más problemas y ventajas, así como en las páginas de Wikipedia correspondientes [Surrogate Key] [Natural Key]. Los enlaces al final de los artículos de Wikipedia también son muy interesantes.

Modelado de relaciones

Hasta ahora nos hemos centrado en las tablas que definen entidades y vemos que la decisión entre uno y otro tipo de clave no es obvia, ni hay reglas de aplicación general. Dependiendo de la situación deberemos valorar sus ventajas e inconvenientes y actuar en consecuencia. Pero tenemos otro tipo de tablas: aquellas que relacionan entidades.

Agreguemos a nuestro ejemplo de las facturas una tabla de pedidos, y una relación M:N entre ellas que nos indica qué pedidos han originado qué facturas (posiblemente no sea el mejor ejemplo, pero ya nos sirve :P). Para crear la tabla Pedido hacemos:

CREATE TABLE [dbo].[Pedido](
[IdPedido] [int] IDENTITY(1,1) NOT NULL,
[NumeroPedido] [int] NOT NULL,
CONSTRAINT [PK_Pedido] PRIMARY KEY CLUSTERED
(
[IdPedido] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Pedido] ON [dbo].[Pedido]
(
[NumeroPedido] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Nota: He tomado la aproximación con claves subrogadas para el ejemplo de las relaciones. El razonamiento es similar si hubiera utilizado claves naturales en las tablas de Factura y Pedido, con una salvedad que comento más adelante.

Tenemos una buena clave candidata para esta tabla de relación:  IdFactura+IdPedido. ¿La utilizamos, o utilizamos una clave subrogada? Comparemos las soluciones. Utilizando la clave candidata la tabla queda así:

CREATE TABLE [dbo].[PedidoFacturaV1](
	[IdFactura] [int] NOT NULL,
	[IdPedido] [int] NOT NULL,
 CONSTRAINT [PK_PedidoFacturaV1] PRIMARY KEY CLUSTERED
(
	[IdFactura] ASC,
	[IdPedido] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- FK a Factura
ALTER TABLE [dbo].[PedidoFacturaV1]  WITH CHECK ADD  CONSTRAINT [FK_PedidoFacturaV1_Factura] FOREIGN KEY([IdFactura])
REFERENCES [dbo].[Factura] ([IdFactura])
GO
ALTER TABLE [dbo].[PedidoFacturaV1] CHECK CONSTRAINT [FK_PedidoFacturaV1_Factura]
GO

-- FK a Pedido
ALTER TABLE [dbo].[PedidoFacturaV1]  WITH CHECK ADD  CONSTRAINT [FK_PedidoFacturaV1_Pedido] FOREIGN KEY([IdPedido])
REFERENCES [dbo].[Pedido] ([IdPedido])
GO
ALTER TABLE [dbo].[PedidoFacturaV1] CHECK CONSTRAINT [FK_PedidoFacturaV1_Pedido]
GO

Si utilizamos una clave subrogada, el script de generación de la tabla quedaría así:

CREATE TABLE [dbo].[PedidoFacturaV2](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[IdPedido] [int] NOT NULL,
	[IdFactura] [int] NOT NULL,
 CONSTRAINT [PK_PedidoFactura] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Indice unico sobre IdFactura,IdPedido
CREATE UNIQUE NONCLUSTERED INDEX [IX_PedidoFactura] ON [dbo].[PedidoFacturaV2]
(
	[IdFactura] ASC,
	[IdPedido] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

-- FK a Factura
ALTER TABLE [dbo].[PedidoFacturaV2]  WITH CHECK ADD  CONSTRAINT [FK_PedidoFactura_Factura] FOREIGN KEY([IdFactura])
REFERENCES [dbo].[Factura] ([IdFactura])
GO
ALTER TABLE [dbo].[PedidoFacturaV2] CHECK CONSTRAINT [FK_PedidoFactura_Factura]
GO

-- FK a Pedido
ALTER TABLE [dbo].[PedidoFacturaV2]  WITH CHECK ADD  CONSTRAINT [FK_PedidoFactura_Pedido] FOREIGN KEY([IdPedido])
REFERENCES [dbo].[Pedido] ([IdPedido])
GO
ALTER TABLE [dbo].[PedidoFacturaV2] CHECK CONSTRAINT [FK_PedidoFactura_Pedido]
GO

Vemos que la segunda opción -claves subrogadas- es más compleja ya que necesitamos un campo autonumérico nuevo y un índice único adicional (sobre los dos campos FK, ya que si no podríamos tener duplicados). Sin embargo, a diferencia del caso del modelado de entidades, utilizar una clave subrogada en este caso no nos aporta ninguna ventaja.

When it comes to defining your entities, I understand that using an identity or GUID or whatever you like instead of real-world data has advantages. It is when we relate entities that we should consider using those existing primary key columns from our entity tables (however you had defined them) to construct an intelligent and logical and accurate primary key for our entity relation table to avoid the need to create extra, additional identity columns and unique constraints.

¿Y si hubiéramos utilizado claves naturales? Pues en ese caso, el razonamiento es el mismo, pero fíjate en que, como pasaba con la tabla LineaFactura, al agregar un campo Empresa a la PK de Factura deberíamos modificar PedidoFactura. Eso no sería necesario si en Factura utilizamos una clave subrogada.

Id required antipattern

En el recomendable libro SQL Antipatterns: Avoiding the Pitfalls of Database Programming se menciona el uso de claves subrogadas Id como un antipatrón. Lo puedes ver en esta presentación -basada en el libro-, a partir de la diapositiva 57:

Sin embargo, creo que la crítica no se hace tanto al hecho de utilizar claves subrogadas en general, si no a su uso indiscriminado, en tablas de relación (que como hemos visto antes, no nos da ninguna ventaja) y especialmente al hecho de llamar a esos campos Id, a secas, en vez de utilizar nombres con más sentido (como IdFactura), lo cual en bases de datos grandes con consultas complejas puede ser muy poco mantenible.

En resumen

Sólo en el caso de tablas que simplemente relacionan entidades la elección parece decantarse hacia el uso de claves naturales. Para cualquier otra situación, tendremos que estudiar en qué medida nos afectan los pros y contras de cada solución caso por caso. La principal elección sería: Simplicidad en el diseño de la BBDD y las consultas vs. resistencia a cambios en las claves primarias y una posible mejora de rendimiento.

Dicho esto, desde un punto de vista ágil y de desarrollo iterativo, en un nuevo proyecto siempre optaría por utilizar al principio claves naturales -por ser más sencillas y conectadas al negocio- y, al ir avanzando el proyecto, volver a valorar si por complejidad merece la pena pasar a utilizar claves subrogadas. Puede que incuso tablas que al principio simplemente relacionaban entidades evolucionen en entidades, ya sea por nuevos requerimientos o porque nuestra comprensión del dominio ha aumentado. Este principio se puede ver alterado por factores como preferencias personales o experiencias previas del equipo, las capacidades de nuestro motor de BBDD y/o ORM, la fase del desarrollo en que nos encontremos, que los tipos de datos de nuestras candidatas no sean los más óptimos (texto), etc.

Espero que os resulte útil la recopilación de puntos de vista y enlaces, y mucha suerte con vuestros diseños de BBDD. Hasta la próxima!

Esta entrada fue publicada en Dev y etiquetada , , . Guarda el enlace permanente.

8 respuestas a ¿Claves primarias naturales o subrogadas?

  1. Alberto Glez dijo:

    Saludos, muy buen artículo, sólo creo tener una pequeña aportación. Recientemente me ví involucrado en un proyecto de base de datos para el cual adopté el esqumea de claves subrogadas. Originalmente se trataba de una bdd centralizada, pero mis problemas comenzaron cuando el proyecto cambió y el sistema tenía que distribuirse, haciendo que la bdd se distribuyera en diferentes servidores y luego (por otro proceso) tuviera que ser concentrada de nuevo. Los ID’s generados automáticamente son una pesadilla para manejarlos en este escenario que les comento.

  2. armentaisai dijo:

    Reblogged this on csharplifestyle.

  3. jlbarros dijo:

    Muy buen artículo. Aunque creo que es un mal consejo plantear el uso de claves naturales como la primera opción. Hicimos precisamente eso en un proyecto y fue traumático. Las claves deben decidirse adecuadamente desde el principio porque todo el desarrollo posterior dependerá de cómo estén definidas esas claves. Si te da por cambiarlas después, entonces, lo más probable es que tengas que cambiar gran parte de tu desarrollo. En realidad, mi experiencia me dice que es mucho mejor utilizar claves subrogadas. Te dan total independencia con respecto a los cambios del negocio y mantienen la estabilidad del proyecto. Recordemos que una clave no es parte del negocio. Las claves son simplemente artilugios técnicos necesarios para el diseño de la aplicación. En el caso del anterior comentario, creo que vale la pena resaltar que una clave subrogada debe garantizar que es única. Por ello, no suelo usar campos autoincrementales para claves subrogadas. En su lugar utilizo algoritmos que generan cadenas únicas para cada registro.

    • Coincido, por lo que creo que o no me entendiste o no me expliqué bien. La conclusión que intentaba apuntar es: claves subrogadas para la entidad, claves naturales en las tablas de relaciones. Como bien dices, la clave es un artefacto, pero la relación entre dos entidades sí es una parte del negocio.
      Por otra parte, algo que sí añadiría al artículo hoy es una recomendación sobre campos autonuméricos/entity, que parece que Alberto ha sufrido: no los uses NUNCA. Los Guids son tus amigos🙂
      Un saludo!

      • Alphaville99 dijo:

        Pero por que la recomendación sobre claves autonumericas, que no se los utilice NUNCA. Yo he diseñado ciertas tablas con claves autonumericas y no he tenido ningún problema, o es que te refieres que no se utilicen NUNCA en las tablas relaciones solamente y que se recomienda usarlas (claves autonumericas) en las tablas entidad.

        Otra pregunta que es mas recomendable: usar claves autonumericas o guid’s.

        Un saludo!

  4. jlbarros dijo:

    Muy buena tu aclaración y ahora sí estamos de acuerdo en todo. Por otra parte, te agradezco la recomendación del libro SQL Antipatterns: Avoiding the Pitfalls of Database Programming. Lo compré vía internet y toda la tarde he estado leyéndolo y de verdad es excelente. Me resolvió algunas dudas que tenía con un proyecto nuevo que estoy realizando.

  5. Carlos Castro dijo:

    Excelente, muchas gracias

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s