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

unique constraint oracle

Question

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

Aquí hay un ejemplo de escenario . 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 declaració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 unicidad para que permita múltiples valores NULL , mientras se comprueba la singularidad de los datos reales?

Answer 1

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 NULL. Necesita establecer un valor predeterminado de NEWID ().

Actualice los valores existentes a NEWID () donde NULL antes de crear la restricción UNIQUE.

Answer 2

Como se dijo anteriormente, SQL Server no implementa el estándar ANSI cuando se trata de UNIQUE CONSTRAINT . Hay un ticket en Microsoft Connect para esto desde 2007. Como se sugiere here y here las mejores opciones a partir 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])
)

Answer 3

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 en la tabla.

Puedes hacerlo con un disparador INSTEAD OF :

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

Answer 4

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

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

Todas las actualizaciones e inserciones no nulas han fallado con el siguiente error:

La ACTUALIZACIÓN falló porque las siguientes opciones de SET tienen configuraciones incorrectas: ''ARITHABORT''.

Encontré esto en MSDN

SET ARITHABORT debe estar ENCENDIDO cuando está creando o cambiando índices en columnas computadas o vistas indizadas. Si SET ARITHABORT está DESACTIVADO, fallarán las sentencias CREAR, ACTUALIZAR, INSERTAR y BORRAR en tablas con índices en columnas calculadas o vistas indizadas.

Así que para que esto funcione correctamente lo hice

Haga clic con el botón derecho en [Base de datos] -> Propiedades -> Opciones -> Otras opciones -> Misceláneas -> Anulación aritmética habilitada -> verdadero

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

ALTER DATABASE "DBNAME" SET ARITHABORT ON

pero no he probado esto

Answer 5

Es posible crear una restricción única en una vista indexada agrupada

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)

Answer 6

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

Sin embargo, en el mundo de Microsoft SQL Server, se permite un solo 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 aplicar la restricción.

Answer 7

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

    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

Answer 8

Para las personas que utilizan Microsoft SQL Server Manager y desean crear un índice exclusivo pero que pueda ser objeto de nulos, puede crear su índice único como lo haría normalmente en sus Propiedades de índice para su nuevo índice, seleccione "Filtro" en el panel de la izquierda y luego ingrese Su filtro (que es su cláusula donde). Debería leer algo como esto:

([YourColumnName] IS NOT NULL)

Esto funciona con MSSQL 2012

Answer 9

Puede crear un desencadenador INSTEAD OF para verificar condiciones específicas y errores 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

Answer 10

Se puede hacer en el diseñador también.

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

Answer 11

Tal vez considere un disparador " INSTEAD OF " y haga la comprobación usted mismo? Con un índice no agrupado (no único) en la columna para habilitar la búsqueda.

Answer 12

este código si realiza un registro con textBox y usa insertar y ur textBox está vacío y u haga clic en el botón enviar.

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

Answer 13

SQL Server 2008 y hasta

Simplemente filtre un índice único:

CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName
ON dbo.Party(SamAccountName)
WHERE SamAccountName IS NOT NULL;

En versiones inferiores, una vista materializada todavía no es necesaria

Para SQL Server 2005 y versiones anteriores, puede hacerlo sin una vista. Acabo de agregar una restricción única como la que solicitas a una de mis tablas. Dado que quiero unicidad en la columna SamAccountName , pero quiero permitir varios NULL, usé 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 real sea NULA. 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 entender 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 convirtiera en 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 hace que, de manera implícita, el resultado de cada expresión se guarde en el disco con los otros datos de la tabla, lo que SÍ necesita espacio de disco adicional.

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

¡En SQL Server 2008 y superiores, definitivamente use la solución filtrada en su lugar si es posible!

Controversia

Tenga en cuenta que algunos profesionales de la base de datos verán esto como un caso de "valores nulos sustitutos", que definitivamente tienen problemas (principalmente debido a los problemas que surgen al tratar de determinar cuándo algo es un valor real o un valor sustituto de los datos faltantes ; también puede haber problemas). con el número de valores sustitutos no NULOS que se multiplican como locos).

Sin embargo, creo que este caso es diferente. La columna calculada que estoy agregando nunca se utilizará para determinar nada. No tiene ningún significado de sí mismo y no codifica información que no se haya encontrado por separado en otras columnas definidas correctamente. Nunca debe ser seleccionado o utilizado.

Entonces, mi historia es que este no es un NULL sustituto, ¡y me atengo a él! Como en realidad no queremos que el valor que no es NULL para ningún otro propósito que no sea engañar al índice UNIQUE para que ignore los NULL, nuestro caso de uso no presenta 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 indizada en su lugar, pero conlleva 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 quitar la vista o modificar la vista para que no esté enlazada con el 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 haber el desafío de garantizar que la restricción única que utiliza Coalesce no provoque colisiones. En ese caso, hay algunas opciones. Uno podría ser usar un número negativo, para colocar los "NULOS 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 la tabla Issue (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 los 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 NULL.

Answer 14

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];