sql - una - Permitir nulo en columna única

restricciones check sql server ejemplos

Question

Creé la siguiente tabla:

CREATE TABLE MMCompany (
   CompanyUniqueID BIGSERIAL PRIMARY KEY NOT NULL, 
   Name VARCHAR (150) NOT NULL,
   PhoneNumber VARCHAR(20) NOT NULL UNIQUE, 
   Email VARCHAR(75) UNIQUE,
   CompanyLogo BYTEA
 );

La columna de correo electrónico es única y causa un "error" en mi escenario ya que solo puede haber un registro con nulo. Intento obtener registros de empresas sin el mismo correo electrónico pero, al mismo tiempo, les permito a las empresas no tener correo electrónico.

¿Cómo puedo lograr eso?

Answer 1

Único y nulo no se lleva bien, dado que nulo no está definido por definición; no se puede saber si dos valores nulos son el mismo desconocido.

En este sentido, su restricción única actual en el correo electrónico es lo correcto y debería funcionar tal como está.

En caso de que alguna vez necesite hacerlo de otra manera, un índice parcial funciona:

create unique index on MMCompany((email is null)) where (email is null);

Otro enfoque es definir un disparador de restricción. Algo como:

create function email_chk() returns trigger as $$
begin
  if exists (
    select 1 from mmcompany where email is null and companyuniqueid <> new.id
  ) then
    raise ''dup null found'';
  end if;
  return null;
end;
$$ language plpgsql;

create constraint trigger after insert or update on mmcompany
for each row when (new.email is null)
execute procedure email_chk();

Answer 2

Coloque la columna de correo electrónico de la tabla. Póngalo en una nueva tabla donde NO puede ser NULO y ÚNICO:

CREATE TABLE CompanyEmail
 (
    CompanyUniqueID INT NOT NULL PRIMARY KEY
       REFERENCES MMCompany (CompanyUniqueID),
    Email VARCHAR(75) NOT NULL UNIQUE
 );

Evite las restricciones ÚNICAS anulables.

Answer 3

Esto es un malentendido.
La restricción UNIQUE hace exactamente lo que quieres. Múltiples valores NULL pueden coexistir en una columna definida UNIQUE .

Citando el manual sobre restricciones ÚNICAS :

En general, una restricción única se infringe cuando hay más de una fila en la tabla donde los valores de todas las columnas incluidas en la restricción son iguales. Sin embargo, dos valores nulos no se consideran iguales en esta comparación. Esto significa que incluso en presencia de una restricción única, es posible almacenar filas duplicadas que contengan un valor nulo en al menos una de las columnas restringidas. Este comportamiento cumple con el estándar SQL, pero hemos escuchado que otras bases de datos SQL pueden no seguir esta regla. Por lo tanto, tenga cuidado al desarrollar aplicaciones destinadas a ser portátiles.

Negrita énfasis mío.

Tenga en cuenta que los tipos de caracteres permiten una cadena vacía ( '''' ), que no es un valor NULL y desencadenaría una violación única al igual que cualquier otro valor no nulo cuando se ingrese en más de una fila.

Answer 4

No hay tal problema en Postgres

En la respuesta correcta de Erwin Brandstetter, explica que debería estar viendo el comportamiento que desea (múltiples NULL permitidos en una restricción Única). Debería ver este comportamiento en Postgres en particular, así como en cualquier base de datos compatible con SQL estándar en general.

Solución alternativa para otras bases de datos

Sin embargo, el documento de Postgres advierte sobre la portabilidad porque se sabe que algunas bases de datos infringen esta característica. Para un sistema no conforme, sugiero que se reemplace el uso de un valor NULO en dichos campos con un valor falso. El valor falso sería una cadena como "unknown_" más algún valor arbitrario que es casi seguro que será único. Ese valor arbitrario podría ser algo así como la fecha actual más un número aleatorio.

UUID

Pero, en lugar de desplegar su propio valor arbitrario, genere un UUID . El UUID versión 1 original es de hecho una combinación de la fecha y hora actual, un número aleatorio y la dirección MAC virtualmente única de la computadora.

Un UUID presentado como una cadena hexadecimal con formato canónico usando guiones se ve así:

93e6f268-5c2d-4c63-9d9c-40e6ac034f88

Entonces mi sugerencia es combinar una cadena arbitraria como "unknown_" más un UUID, para que se vea así:

unknown_93e6f268-5c2d-4c63-9d9c-40e6ac034f88

Por lo tanto, mi sugerencia para las bases de datos no compatibles es generar dicho valor y usarlo en lugar de NULL, usarlo cuando aún no tenga un valor conocido en esa columna para una fila en particular. En lugar de escribir consultas que buscan filas que tienen (o no tienen) un valor NULL en esa columna, escribir consultas que busquen filas que tienen (o no tienen) un valor que comienza con la cadena arbitraria, "desconocido_" en este ejemplo. Cada fila satisfaría la restricción de tener un valor único.

De hecho, asignaría este valor "desconocido" + UUID como valor predeterminado para esa columna.

También podría agregar una restricción NOT NULL a esta columna.

Generando valores de UUID

Postgres tiene soporte integrado para el tipo de datos de UUID, pero eso es irrelevante en esta respuesta aquí. Lo que necesita es generar un valor de UUID.

Para generar UUID necesita una extensión (complemento) que agregue esta capacidad a Postgres. La mayoría de los instaladores de Postgres incluyen dicha extensión. Esta extensión se llama uuid-ossp . Por lo general, la extensión no está activada por defecto. Para hacerlo en versiones recientes de Postgres, use el comando CREATE EXTENSION . Para obtener instrucciones, consulte la publicación de mi blog sobre la instalación en Postgres 9.1 y posterior o sobre mi otra publicación en Postgres 9.0 y anteriores . Tanto la forma de instalación nueva como la antigua es fácil, siempre que la extensión / complemento se haya compilado y empaquetado con la instalación de Postgres.

Resumen

Permítanme aclarar que para Postgres solo, no hay necesidad de esta solución porque Postgres cumple con el estándar SQL. Pero si:

• Le preocupa la portabilidad de su código a algún otro sistema de base de datos no conforme, o
• Necesita intercambiar datos con un sistema de base de datos no conforme, o
• Usted está de acuerdo con el Dr. Chris Date en que NULL es obra del diablo y debe evitarse

... entonces una solución como esta es necesaria.

Answer 5

Algunas bases de datos no permiten múltiples valores nulos, por ejemplo, la documentación de SQL Server establece que "múltiples valores nulos se consideran duplicados". En las bases de datos que no permiten las restricciones ÚNICAS anulables, puede probar esto (de la respuesta de GuidoG a otra pregunta):

CREATE UNIQUE NONCLUSTERED INDEX IDX_Email
ON MMCompany (Email)
WHERE Email IS NOT NULL;

Answer 6

Y en caso de que esté generando sus tablas de base de datos utilizando EF Code First, edite su método de clase de migración como el siguiente para aplicar su restricción UNIQUE KEY para ignorar NULL.

migrationBuilder.Sql(@"CREATE UNIQUE NONCLUSTERED INDEX[IX_Employees_TaskId] ON[dbo].[Employees]([TaskId] ASC)
                                WHERE [TaskId] IS NOT NULL"
                                );

Y luego podría probar su Restricción Única iniciando sesión en su Base de Datos a través de SQL Server Management Studio o algo similar. Como en este caso Employee Table acepta felizmente 2 valores NULL en TaskId aunque es una columna ÚNICA.