¿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 😛 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:

Ver este documento en Scribd

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!