Claves de negocio opcionales y únicas en SQL Server

Una cuenta puede existir antes de recibir su identificador externo. Muchas cuentas pueden carecer de él, pero dos del mismo tenant no deben compartir un valor conocido. Una restricción UNIQUE nullable convencional no expresa automáticamente esa regla.

Definir qué filas participan

Para una sola columna nullable, un índice único ordinario de SQL Server permite una entrada NULL. En una clave compuesta, la unicidad se evalúa sobre la combinación completa. Ningún caso significa ignorar todas las filas cuyo campo opcional falta. Un índice único filtrado permite expresar esa participación.

El ejemplo exige unicidad de ExternalId dentro de TenantId solo cuando el identificador externo no es NULL.

CREATE TABLE #Accounts
(
    AccountId int NOT NULL PRIMARY KEY,
    TenantId int NOT NULL,
    ExternalId nvarchar(100) NULL
);
CREATE UNIQUE INDEX UX_Accounts_External
ON #Accounts (TenantId, ExternalId)
WHERE ExternalId IS NOT NULL;

INSERT #Accounts VALUES
(1, 10, NULL), (2, 10, NULL),
(3, 10, N'ABC'), (4, 20, N'ABC');

BEGIN TRY
    INSERT #Accounts VALUES (5, 10, N'ABC');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS DuplicateError;
END CATCH;

SELECT AccountId, TenantId, ExternalId
FROM #Accounts ORDER BY AccountId;
DROP TABLE #Accounts;

Se aceptan las dos ausencias del tenant 10. ABC puede existir en los tenants 10 y 20 porque el tenant forma parte de la clave. El segundo ABC del tenant 10 falla y quedan cuatro filas. El filtro elige participantes y las columnas clave determinan las colisiones.

Mantén una clave primaria estable como AccountId para las relaciones. Un identificador externo puede llegar tarde, cambiar o retirarse. Obligar a las tablas dependientes a seguir ese ciclo complica el modelo. Un índice único filtrado tampoco proporciona un destino general de clave externa para filas fuera del filtro.

Hacer explícito el significado de igualdad

NULL, cadena vacía y cadena de espacios no son estados equivalentes salvo que la aplicación lo establezca. Si vacío significa ausente, normalízalo expresamente antes de almacenar y exige la representación aceptada. De lo contrario, una cadena vacía participa como valor real y puede colisionar.

La igualdad de cadenas sigue la collation de la columna y las reglas de SQL Server. Sensibilidad a mayúsculas y acentos puede cambiar qué valores coinciden. Los espacios finales también pueden compararse como iguales en comparaciones ordinarias. El contrato debe seguir al sistema externo, no un valor predeterminado accidental.

Si el proveedor distingue valores que tu collation considera iguales, no los fusionas silenciosamente aplicando minúsculas o recortes. Si el negocio sí considera equivalentes varias representaciones, normaliza igual en API, importaciones y scripts. Una columna normalizada almacenada puede hacer visible la regla, siempre que mantengas su consistencia.

Antes de crear el índice sobre datos existentes, agrupa valores no NULL por la clave completa deseada y revisa duplicados. Utiliza exactamente la semántica de comparación prevista. Decide quién conserva la identidad y qué ocurre con las dependencias. Eliminar cuentas arbitrariamente no es mantenimiento de índices.

Resolver asignaciones simultáneas en la base

Un SELECT previo sirve para mensajes amigables, pero no garantiza unicidad. Dos solicitudes pueden comprobar ausencia antes de insertar cualquiera de ellas. El índice es el árbitro final. Captura la violación relevante de clave duplicada y conviértela en un conflicto comprensible.

Actualizar de NULL a una referencia conocida sigue la misma regla que insertar. Cambiar TenantId también. Prueba ambas transiciones porque suelen tener rutas distintas en la aplicación. Incluye dos sesiones asignando el mismo valor a la vez, además de duplicados secuenciales.

Si las cuentas eliminadas lógicamente liberan el identificador, incorpora esa política al filtro. Define entonces la restauración: una cuenta antigua puede entrar en conflicto con un propietario nuevo. Si nunca debe reutilizarse, conserva unicidad también para las cuentas archivadas.

Considera finalmente el índice como regla de integridad y estructura con mantenimiento. Su creación puede fallar por duplicados y requiere planificación en tablas grandes. Mantén coherentes las opciones SET necesarias para índices filtrados. Documenta la regla de negocio junto a la migración para que futuros cambios conserven el límite correcto.

Referencias técnicas: Microsoft Learn: Unique indexes · Microsoft Learn: Filtered indexes.