¿Cómo creo una restricción única que también permita nulos?

Quiero tener una restricción única en una columna que voy a poblar con GUID. Sin embargo, mis datos contienen valores nulos para estas columnas. ¿Cómo creo la restricción que permite múltiples valores nulos?

Aquí hay un escenario de ejemplo . Considera este esquema:

CREATE TABLE People ( Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY, Name NVARCHAR(250) NOT NULL, LibraryCardId UNIQUEIDENTIFIER NULL, CONSTRAINT UQ_People_LibraryCardId UNIQUE (LibraryCardId) ) 

Luego vea este código para lo que estoy tratando de lograr:

 -- This works fine: INSERT INTO People (Name, LibraryCardId) VALUES ('John Doe', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA'); -- This also works fine, obviously: INSERT INTO People (Name, LibraryCardId) VALUES ('Marie Doe', 'BBBBBBBB-BBBB-BBBB-BBBB-BBBBBBBBBBBB'); -- This would *correctly* fail: --INSERT INTO People (Name, LibraryCardId) --VALUES ('John Doe the Second', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA'); -- This works fine this one first time: INSERT INTO People (Name, LibraryCardId) VALUES ('Richard Roe', NULL); -- THE PROBLEM: This fails even though I'd like to be able to do this: INSERT INTO People (Name, LibraryCardId) VALUES ('Marcus Roe', NULL); 

La instrucción final falla con un mensaje:

Violación de la restricción UNIQUE KEY ‘UQ_People_LibraryCardId’. No se puede insertar una clave duplicada en el objeto ‘dbo.People’.

¿Cómo puedo cambiar mi esquema y / o restricción de exclusividad para que permita múltiples valores NULL , mientras sigo revisando la exclusividad de los datos reales?

SQL Server 2008 +

Puede crear un índice único que acepte múltiples NULL con una cláusula WHERE . 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.

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 UNIQUE debe rechazar valores duplicados que no sean NULL, pero acepte múltiples valores NULL.

Sin embargo, en el mundo de SQL Server de Microsoft, se permite un único NULL pero varios NULL no son …

En SQL Server 2008 , puede definir un índice único filtrado basado en un predicado que excluye NULLs:

 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 aplicar la restricción.

SQL Server 2008 y versiones posteriores

Solo filtra 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 anteriores, puede hacerlo sin una vista. Acabo de agregar una restricción única como la que está pidiendo a una de mis tablas. Dado que deseo exclusividad en la columna SamAccountName , pero quiero permitir varios 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á única en toda la tabla cuando la columna única deseada sea NULL. En este caso, PartyID es una columna de identidad y ser numérico nunca coincidirá con ningún SamAccountName , por lo 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 volviera no numérico algún día y pudiera coincidir con un SamAccountName , ahora no importará.

Tenga en cuenta que la presencia de un índice que incluye la columna calculada ocasiona implícitamente que cada resultado de expresión se guarde en el disco con los demás datos de la tabla, lo que SÍ requiere espacio adicional en el disco.

Tenga en cuenta que si no desea un índice, puede seguir ahorrando CPU haciendo que la expresión se precalcula en el disco agregando la palabra clave PERSISTED al final de la definición de la expresión de la columna.

¡En SQL Server 2008 en adelante, definitivamente use la solución filtrada 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 para tratar de determinar cuándo algo es un valor real o un valor sustituto para los datos faltantes , también puede haber problemas con el número de valores indirectos 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 en sí mismo y no codifica ninguna información que no se encuentre ya por separado en otras columnas definidas correctamente. Nunca debe ser seleccionado o usado.

¡Entonces, mi historia es que esto no es un NULO sustituto, y me estoy apegando a eso! Como en realidad no queremos el valor no NULL para ningún fin que no sea engañar al índice UNIQUE para que ignore NULLs, nuestro caso de uso no tiene ninguno de los problemas que surgen con la creación NULL sustituta normal.

Dicho todo esto, no tengo ningún problema con el uso de una vista indexada en su lugar, pero trae algunos problemas, como el requisito de utilizar SCHEMABINDING . Diviértete agregando una nueva columna a tu tabla base (al menos tendrás que soltar el índice, y luego soltar la vista o modificar la vista para no estar enlazado al esquema). Consulte la lista completa (larga) de requisitos para crear una vista indizada 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 única que utiliza Coalesce no Coalesce colisiones. En ese caso, hay algunas opciones. Uno podría ser usar un número negativo, para poner los “NULL sustitutos” solo en el rango negativo, y los “valores reales” solo en el rango positivo. Alternativamente, el siguiente patrón podría ser utilizado. En el Issue tabla (donde IssueID es la PRIMARY KEY ), puede haber o no un TicketID , pero si lo hay, 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, la restricción UNIQUE estará en valores (123, NULL). Si IssueID 2 no tiene ticket, estará activado (NULL, 2). Algunos pensamientos mostrarán que esta restricción no se puede duplicar para ninguna fila en la tabla, y aún permite múltiples valores NULL.

Para las personas que usan el Administrador de Servidor Microsoft SQL y desean crear un índice Único pero Nullable, pueden crear su índice único como lo harían normalmente en las Propiedades del Índice para su nuevo índice, seleccionar “Filtro” desde el panel izquierdo, luego ingresar su filtro (que es su cláusula Where) Debería leer algo como esto:

 ([YourColumnName] IS NOT NULL) 

Esto funciona con MSSQL 2012

Cuando apliqué el índice único a continuación:

 CREATE UNIQUE NONCLUSTERED INDEX idx_badgeid_notnull ON employee(badgeid) WHERE badgeid IS NOT NULL; 

cada actualización no nula e inserción fallaron con el siguiente error:

La actualización falló porque las siguientes opciones SET tienen configuraciones incorrectas: ‘ARITHABORT’.

Encontré esto en MSDN

SET ARITHABORT debe estar activado cuando está creando o cambiando índices en columnas calculadas o vistas indizadas. Si SET ARITHABORT está desactivado, las instrucciones CREATE, UPDATE, INSERT y DELETE en tablas con índices en columnas calculadas o vistas indexadas fallarán.

Entonces, para que esto funcione correctamente, hice esto

Haga clic derecho en [Base de datos] -> Propiedades -> Opciones -> Otras opciones -> Varios -> Abortar aritmética habilitado -> verdadero

Creo que es posible configurar esta opción en código usando

 ALTER DATABASE "DBNAME" SET ARITHABORT ON 

pero no he probado esto

Cree una vista que seleccione solo columnas que no sean NULL y cree el UNIQUE INDEX en la vista:

 CREATE VIEW myview AS SELECT * FROM mytable WHERE mycolumn IS NOT NULL CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn) 

Tenga en cuenta que deberá realizar INSERT y UPDATE en la vista en lugar de la tabla.

Puedes hacerlo con un gatillo INSTEAD OF :

 CREATE TRIGGER trg_mytable_insert ON mytable INSTEAD OF INSERT AS BEGIN INSERT INTO myview SELECT * FROM inserted END 

Es posible crear una restricción única en una vista indizada en clúster

Puedes crear la vista así:

 CREATE VIEW dbo.VIEW_OfYourTable WITH SCHEMABINDING AS SELECT YourUniqueColumnWithNullValues FROM dbo.YourTable WHERE YourUniqueColumnWithNullValues IS NOT NULL; 

y la restricción única como esta:

 CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_OFYOURTABLE ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues) 

Se puede hacer en el diseñador también

Haga clic derecho en el índice> Propiedades para obtener esta ventana

capturar

¿Tal vez considerar un activador ” INSTEAD OF ” y hacer el control usted mismo? Con un índice no agrupado (no exclusivo) en la columna para habilitar la búsqueda.

Como se indicó anteriormente, SQL Server no implementa el estándar ANSI cuando se trata de UNIQUE CONSTRAINT . Hay un boleto en Microsoft Connect para esto desde 2007. Como se sugirió aquí y aquí, las mejores opciones a día de hoy son usar un índice filtrado como se indica en otra respuesta o en una columna calculada, por ejemplo:

 CREATE TABLE [Orders] ( [OrderId] INT IDENTITY(1,1) NOT NULL, [TrackingId] varchar(11) NULL, ... [ComputedUniqueTrackingId] AS ( CASE WHEN [TrackingId] IS NULL THEN '#' + cast([OrderId] as varchar(12)) ELSE [TrackingId_Unique] END ), CONSTRAINT [UQ_TrackingId] UNIQUE ([ComputedUniqueTrackingId]) ) 

Puede crear un disparador INSTEAD OF para verificar condiciones específicas y un error si se cumplen. Crear un índice puede ser costoso en tablas más grandes.

Aquí hay un ejemplo:

 CREATE TRIGGER PONY.trg_pony_unique_name ON PONY.tbl_pony INSTEAD OF INSERT, UPDATE AS BEGIN IF EXISTS( SELECT TOP (1) 1 FROM inserted i GROUP BY i.pony_name HAVING COUNT(1) > 1 ) OR EXISTS( SELECT TOP (1) 1 FROM PONY.tbl_pony t INNER JOIN inserted i ON i.pony_name = t.pony_name ) THROW 911911, 'A pony must have a name as unique as s/he is. --PAS', 16; ELSE INSERT INTO PONY.tbl_pony (pony_name, stable_id, pet_human_id) SELECT pony_name, stable_id, pet_human_id FROM inserted END 

No puede hacer esto con una restricción UNIQUE , pero puede hacerlo en un desencadenador.

  CREATE TRIGGER [dbo].[OnInsertMyTableTrigger] ON [dbo].[MyTable] INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; DECLARE @Column1 INT; DECLARE @Column2 INT; -- allow nulls on this column SELECT @Column1=Column1, @Column2=Column2 FROM inserted; -- Check if an existing record already exists, if not allow the insert. IF NOT EXISTS(SELECT * FROM dbo.MyTable WHERE Column1=@Column1 AND Column2=@Column2 @Column2 IS NOT NULL) BEGIN INSERT INTO dbo.MyTable (Column1, Column2) SELECT @Column2, @Column2; END ELSE BEGIN RAISERROR('The unique constraint applies on Column1 %d, AND Column2 %d, unless Column2 is NULL.', 16, 1, @Column1, @Column2); ROLLBACK TRANSACTION; END END 
 CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME] ON [dbo].[Employee]([Username] ASC) WHERE ([Username] IS NOT NULL) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0) ON [PRIMARY]; 

este código si haces un formulario de registro con textBox y usas insert y tu textBox está vacío y haces clic en el botón de enviar.

 CREATE UNIQUE NONCLUSTERED INDEX [IX_tableName_Column] ON [dbo].[tableName]([columnName] ASC) WHERE [columnName] !=`''`;