Por que uma restrição UNIQUE permite apenas um NULL?

Por que funciona desta forma? Porque lá atrás, alguém tomou uma decisão de design sem saber ou se importar com o que o padrão diz (afinal, temos todos os tipos de comportamentos estranhos com NULLs e podemos coagir comportamentos diferentes à vontade). Essa decisão ditou que, neste caso, NULL = NULL.

Não foi uma decisão muito inteligente. O que eles deveriam ter feito é ter o comportamento padrão aderido ao padrão ANSI, e se eles realmente quisessem esse comportamento peculiar, permiti-lo através de uma opção DDL como WITH CONSIDER_NULLS_EQUALou WITH ALLOW_ONLY_ONE_NULL.

Claro, a retrospectiva é 20/20.

E agora temos uma solução alternativa, mesmo que não seja a mais limpa ou intuitiva.

Você pode obter o comportamento ANSI adequado no SQL Server 2008 e superior criando um índice exclusivo e filtrado.

CREATE UNIQUE INDEX foo ON dbo.bar(key) WHERE key IS NOT NULL;

Isso permite mais de um NULLvalor porque essas linhas são completamente deixadas de fora da verificação de duplicatas. Como um bônus adicional, isso acabaria sendo um índice menor do que aquele que consistia em toda a tabela se vários NULLs fossem permitidos (especialmente quando não é a única coluna no índice, tem INCLUDEcolunas, etc). No entanto, convém estar ciente de algumas das outras limitações dos índices filtrados:

• Como os índices filtrados podem ser um recurso mais poderoso

Correto. A implementação de uma restrição ou índice exclusivo no sql server permite um e apenas um NULL. Também correto que isso tecnicamente não se encaixa na definição de NULL, mas é uma daquelas coisas que eles fizeram para torná-lo mais útil, mesmo que não seja "tecnicamente" correto. Observe que uma PRIMARY KEY (também um índice exclusivo) não permite NULLs (é claro).

Primeiro - pare de usar a frase "valor nulo", isso apenas o levará ao erro. Em vez disso, use a frase "marcador nulo" - um marcador em uma coluna indicando que o valor real nessa coluna está ausente ou inaplicável (mas observe que o marcador não diz qual dessas opções é realmente o caso¹).

Agora, imagine o seguinte (onde o banco de dados não tem conhecimento completo da situação modelada).

Situation          Database

ID   Code          ID   Code
--   -----         --   -----
1    A             1    A
2    B             2    (null)
3    C             3    C
4    B             4    (null)

A regra de integridade que estamos modelando é "o Código deve ser único". A situação do mundo real viola isso, portanto, o banco de dados não deve permitir que os itens 2 e 4 estejam na tabela ao mesmo tempo.

A abordagem mais segura e menos flexível seria proibir marcadores nulos no campo Código, portanto, não há possibilidade de dados inconsistentes. A abordagem mais flexível seria permitir vários marcadores nulos e se preocupar com a exclusividade quando os valores são inseridos.

Os programadores da Sybase optaram pela abordagem um tanto segura e não muito flexível de permitir apenas um marcador nulo na tabela - algo que os comentaristas têm reclamado desde então. A Microsoft continuou esse comportamento, acho que para compatibilidade com versões anteriores.

¹ Tenho certeza de que li em algum lugar que Codd considerou implementar dois marcadores nulos - um para desconhecido, outro para inaplicável - mas o rejeitou, mas não consigo encontrar a referência. Estou me lembrando corretamente?

PS Minha citação favorita sobre null: Louis Davidson, "Professional SQL Server 2000 Database Design", Wrox Press, 2001, página 52. "Resumido em uma única frase: NULL é mau."

Isso pode não ser tecnicamente preciso, mas filosoficamente me ajuda a dormir à noite...

Como vários outros disseram ou aludiram, se você pensar em NULL como desconhecido, não poderá determinar se um valor NULL é de fato igual a outro valor NULL. Pensando dessa forma, a expressão NULL == NULL deve resultar em NULL, significando desconhecido.

Uma restrição Unique precisaria de um valor definitivo para a comparação dos valores da coluna. Em outras palavras, ao comparar um único valor de coluna com qualquer outro valor de coluna usando o operador de igualdade, ele deve ser avaliado como false para ser válido. Desconhecido não é realmente falso, embora muitas vezes seja tratado como falso. Dois valores NULL podem ser iguais ou não... simplesmente não pode ser determinado definitivamente.

Ajuda pensar em uma restrição única como valores restritivos que podem ser determinados como distintos um do outro. O que quero dizer com isso é se você executar um SELECT parecido com isto:

SELECT * from dbo.table1 WHERE ColumnWithUniqueContraint="some value"

A maioria das pessoas esperaria um resultado, dado que há uma restrição única. Se você permitisse vários valores NULL em ColumnWithUniqueConstraint, seria impossível selecionar uma única linha distinta da tabela usando NULL como o valor comparado.

Dado isso, acredito que, independentemente de ser implementado ou não com precisão em relação à definição de NULL, é definitivamente muito mais prático na maioria das situações do que permitir vários valores NULL.

Um dos principais objetivos de uma UNIQUErestrição é evitar registros duplicados. Se for necessário ter uma tabela na qual possa haver vários registros em que um valor é "desconhecido", mas dois registros não podem ter o mesmo valor "conhecido", os valores desconhecidos devem receber identificadores únicos artificiais antes de serem adicionado à mesa.

Existem alguns casos raros em que uma coluna que tem uma UNIQUErestrição e contém um único valor nulo; por exemplo, se uma tabela contiver um mapeamento entre valores de coluna e descrições de texto localizadas, uma linha para NULLpossibilitaria definir a descrição que deveria aparecer quando essa coluna em alguma outra tabela fosse NULL. O comportamento de NULLpermite esse caso de uso.

Caso contrário, não vejo base para um banco de dados com uma UNIQUErestrição em qualquer coluna para permitir a existência de muitos registros idênticos, mas não vejo como evitar isso, permitindo vários registros cujos valores de chave não sejam distinguíveis. Declarar que NULLnão é igual a si mesmo não tornará os NULLvalores distinguíveis uns dos outros.