seleccionar - sql server check for null

El valor de campo debe ser único a menos que sea NULL

Question

Estoy usando SQL Server 2005.

Tengo un campo que debe contener un valor único o un valor NULO. Creo que debería aplicar esto con CHECK CONSTRAINT o con un TRIGGER for INSERT, UPDATE .

¿Hay alguna ventaja en usar una restricción aquí sobre un disparador (o viceversa)? ¿A qué se parece semejante restricción / disparador?

¿O hay otra opción más apropiada que no he considerado?

Answer 1

¿Hay una clave principal en esta tabla, tal vez una columna de Identidad? Puede crear una clave única compuesta por el campo en el que está aplicando la singularidad, en combinación con la clave principal.

Hay una discusión sobre este tipo de problema aquí: http://blog.sqlauthority.com/2008/09/07/sql-server-explanation-about-usage-of-unique-index-and-unique-constraint/

FYI - SQL Server 2008 introduce índices filtrados que le permitirían abordar esto de manera diferente.

Answer 2

Aquí hay una manera alternativa de hacerlo con una restricción. Para hacer cumplir esta restricción, necesitará una función que cuente el número de ocurrencias del valor del campo. En su restricción, simplemente asegúrese de que este máximo sea 1.

Restricción:

   field is null or dbo.fn_count_maximum_of_field(field) < 2

EDITAR No recuerdo ahora, y tampoco puedo verificarlo, si la verificación de restricciones se realiza antes de la inserción / actualización o después. Creo que después de que la inserción / actualización se retrotrae a la falla. Si resulta que estoy equivocado, los 2 anteriores deberían ser un 1.

La función de tabla devuelve un int y usa la siguiente selección para derivarlo

   declare @retVal int

   select @retVal = max(occurrences)
   from ( 
        select field, count(*) as occurrences
        from dbo.tbl
        where field = @field
        group by field
   ) tmp

Esto debería ser razonablemente rápido si su columna tiene un índice (no único).

Answer 3

En Oracle, una clave única permitirá múltiples NULLs.

En SQL Server 2005, un buen enfoque es hacer las inserciones a través de una vista y deshabilitar las inserciones directas en la tabla.

Aquí hay un código de muestra.

Answer 4

Por lo general, un desencadenante le permitirá proporcionar un mensaje más detallado y explicativo que una restricción de verificación, así que los he usado para evitar el juego "qué columna era mala" en la depuración.

Answer 5

Una restricción es mucho más ligera que un disparador, aunque una restricción única es efectivamente un índice.

Sin embargo, solo se le permite un NULL en una restricción / índice único. Por lo tanto, deberá usar un disparador para detectar duplicados.

Se ha solicitado a MS que ignore NULLS , pero SQL 2008 ha filtrado los índices (como mencioné mientras escribo esto)

Answer 6

Creo una vista con un índice que ignora los nulos a través de la cláusula where ... es decir, si inserta nulo en la tabla a la vista no le importa, pero si inserta un valor no nulo, la vista aplicará la restricción.

create view dbo.UniqueAssetTag with schemabinding
as
select asset_tag
from dbo.equipment
where asset_tag is not null

GO

create unique clustered index ix_UniqueAssetTag
on UniqueAssetTag(asset_tag)

GO

Así que ahora mi tabla de equipos tiene una columna asset_tag que permite múltiples nulos pero solo valores únicos no nulos.

Nota: Si usa mssql 2000, necesitará " SET ARITHABORT ON " justo antes de realizar cualquier inserción, actualización o eliminación en la tabla. Bastante seguro de que esto no es necesario en mssql 2005 en adelante.

Answer 7

Puede lograr esto creando una columna calculada y poniendo el índice único en esa columna.

ALTER TABLE MYTABLE 
ADD COL2 AS (CASE WHEN COL1 IS NULL THEN CAST(ID AS NVARCHAR(255)) ELSE COL1 END)

CREATE UNIQUE INDEX UQ_COL2 ON MYTABLE (COL2)   

Esto supone que ID es el PK de su tabla y COL1 es la columna "única o nula".

La columna calculada (COL2) usará el valor de PK si su columna "única" es nula.

Todavía existe la posibilidad de colisiones entre la columna ID y COL1 en el siguiente ejemplo:

ID     COL1    COL2
1     [NULL]    1
2        1      1

Para evitar esto, normalmente creo otra columna calculada que almacena si el valor en COL2 proviene de la columna ID o la columna COL1:

 ALTER TABLE MYTABLE 
 ADD COL3 AS (CASE WHEN COL1 IS NULL THEN 1 ELSE 0 END)

El índice debe cambiarse a:

CREATE UNIQUE INDEX UQ_COL2 ON MYTABLE (COL2, COL3)   

Ahora el índice está en las dos columnas calculadas COL2 y COL3, por lo que no hay problema:

ID     COL1    COL2   COL3
1     [NULL]    1       1
2        1      1       0