Solución:
Lo que está buscando es de hecho parte de los estándares ANSI SQL: 92, SQL: 1999 y SQL: 2003, es decir, una restricción ÚNICA debe prohibir valores duplicados que no sean NULL pero aceptar múltiples valores NULL.
Sin embargo, en el mundo de Microsoft de SQL Server, se permite un único NULL, pero no se permiten varios NULL …
En SQL Server 2008, puede definir un índice filtrado único basado en un predicado que excluye NULL:
CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;
En versiones anteriores, puede recurrir a VIEWS con un predicado NOT NULL para hacer cumplir la restricción.
SQL Server 2008 +
Puede crear un índice único que acepte varios NULL con un WHERE cláusula. Vea la respuesta a continuación.
Antes de SQL Server 2008
No puede crear una restricción ÚNICA y permitir valores NULL. Necesita establecer un valor predeterminado de NEWID ().
Actualice los valores existentes a NEWID () donde NULL antes de crear la restricción UNIQUE.
SQL Server 2008 y posteriores
Simplemente filtre un índice único:
CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName
ON dbo.Party(SamAccountName)
WHERE SamAccountName IS NOT NULL;
En versiones inferiores, aún no se requiere una vista materializada
Para SQL Server 2005 y versiones anteriores, puede hacerlo sin una vista. Acabo de agregar una restricción única como la que estás pidiendo a una de mis tablas. Dado que quiero unicidad en la columna SamAccountName, pero quiero permitir múltiples NULL, utilicé una columna materializada en lugar de una vista materializada:
ALTER TABLE dbo.Party ADD SamAccountNameUnique
AS (Coalesce(SamAccountName, Convert(varchar(11), PartyID)))
ALTER TABLE dbo.Party ADD CONSTRAINT UQ_Party_SamAccountName
UNIQUE (SamAccountNameUnique)
Simplemente tiene que poner algo en la columna calculada que se garantizará como único en toda la tabla cuando la columna única deseada real sea NULL. En este caso, PartyID es una columna de identidad y ser numérico nunca coincidirá con ninguna SamAccountName, así que funcionó para mí. Puede probar su propio método; asegúrese de comprender el dominio de sus datos para que no haya posibilidad de intersección con datos reales. Eso podría ser tan simple como anteponer un carácter diferenciador como este:
Coalesce('n' + SamAccountName, 'p' + Convert(varchar(11), PartyID))
Incluso si PartyID se convirtió en no numérico algún día y podría coincidir con un SamAccountName, ahora no importará.
Tenga en cuenta que la presencia de un índice que incluye la columna calculada implícitamente hace que el resultado de cada expresión se guarde en el disco con los demás datos de la tabla, lo que SÍ ocupa espacio adicional en el disco.
Tenga en cuenta que si no desea un índice, aún puede ahorrar CPU haciendo que la expresión se calcule previamente en el disco agregando la palabra clave PERSISTED hasta el final de la definición de expresión de columna.
En SQL Server 2008 y versiones posteriores, ¡definitivamente use la solución filtrada en su lugar si es posible!
Controversia
Tenga en cuenta que algunos profesionales de bases de datos verán esto como un caso de “NULL sustitutos”, que definitivamente tienen problemas (principalmente debido a problemas al tratar de determinar cuándo algo es un valor real o un valor sustituto para los datos faltantes; también puede haber problemas con la cantidad de valores sustitutos no NULL que se multiplican como locos).
Sin embargo, creo que este caso es diferente. La columna calculada que estoy agregando nunca se usará para determinar nada. No tiene ningún significado por sí mismo y no codifica información que no se encuentre ya por separado en otras columnas correctamente definidas. Nunca debe seleccionarse ni utilizarse.
Entonces, mi historia es que este no es un NULL sustituto, ¡y me aferro a él! Dado que en realidad no queremos el valor no NULL para ningún otro propósito que no sea engañar al UNIQUE index para ignorar NULL, nuestro caso de uso no tiene ninguno de los problemas que surgen con la creación de NULL sustituto normal.
Dicho todo esto, no tengo ningún problema con usar una vista indexada en su lugar, pero trae algunos problemas, como el requisito de usar SCHEMABINDING. Diviértase agregando una nueva columna a su tabla base (como mínimo tendrá que eliminar el índice y luego eliminar la vista o alterar la vista para que no se limite al esquema). Consulte la lista completa (larga) de requisitos para crear una vista indexada en SQL Server (2005) (también versiones posteriores), (2000).
Actualizar
Si su columna es numérica, puede existir el desafío de garantizar que la restricción de unicidad usando Coalesce no da lugar a colisiones. En ese caso, hay algunas opciones. Una podría ser usar un número negativo, poner los “NULL sustitutos” solo en el rango negativo y los “valores reales” solo en el rango positivo. Alternativamente, se podría usar el siguiente patrón. En mesa Issue (dónde IssueID es el PRIMARY KEY), puede haber o no una TicketID, pero si hay uno, debe ser único.
ALTER TABLE dbo.Issue ADD TicketUnique
AS (CASE WHEN TicketID IS NULL THEN IssueID END);
ALTER TABLE dbo.Issue ADD CONSTRAINT UQ_Issue_Ticket_AllowNull
UNIQUE (TicketID, TicketUnique);
Si IssueID 1 tiene el ticket 123, el UNIQUE la restricción estará en los valores (123, NULL). Si IssueID 2 no tiene ticket, estará activado (NULL, 2). Un poco de pensamiento mostrará que esta restricción no se puede duplicar para ninguna fila de la tabla y aún permite múltiples NULL.