Como eu crio uma restrição única que também permite nulos?

Eu quero ter uma restrição exclusiva em uma coluna que eu vou preencher com GUIDs. No entanto, meus dados contêm valores nulos para essas colunas. Como faço para criar a restrição que permite vários valores nulos?

Aqui está um cenário de exemplo . Considere 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) ) 

Então veja este código para o que estou tentando alcançar:

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

A declaração final falha com uma mensagem:

Violação da restrição UNIQUE KEY ‘UQ_People_LibraryCardId’. Não é possível inserir chave duplicada no object ‘dbo.People’.

Como posso alterar minha restrição de esquema e / ou exclusividade para permitir múltiplos valores NULL , enquanto ainda verifico a exclusividade dos dados reais?

SQL Server 2008 +

Você pode criar um índice exclusivo que aceite vários NULLs com uma cláusula WHERE . Veja a resposta abaixo .

Antes do SQL Server 2008

Você não pode criar uma restrição UNIQUE e permitir NULLs. Você precisa definir um valor padrão de NEWID ().

Atualize os valores existentes para NEWID (), onde NULL antes de criar a restrição UNIQUE.

O que você está procurando é, de fato, parte dos padrões ANSI SQL: 92, SQL: 1999 e SQL: 2003, isto é, uma restrição UNIQUE não deve permitir valores duplicados não-NULL, mas aceitar múltiplos valores NULL.

No mundo Microsoft do SQL Server, no entanto, um único NULL é permitido, mas vários NULLs não são …

No SQL Server 2008 , você pode definir um índice filtrado exclusivo com base em um predicado que exclua NULLs:

 CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull ON YourTable(yourcolumn) WHERE yourcolumn IS NOT NULL; 

Em versões anteriores, você pode recorrer a VIEWS com um predicado NOT NULL para impor a restrição.

SQL Server 2008 e até

Basta filtrar um índice exclusivo:

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

Em versões inferiores, uma visão materializada ainda não é necessária

Para o SQL Server 2005 e versões anteriores, você pode fazer isso sem uma exibição. Acabei de adicionar uma restrição exclusiva, como você está pedindo para uma das minhas tabelas. Considerando que desejo exclusividade na coluna SamAccountName , mas desejo permitir vários NULLs, usei uma coluna materializada em vez de uma visualização 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) 

Você simplesmente tem que colocar algo na coluna computada que será garantido como único em toda a tabela quando a coluna exclusiva desejada for NULL. Nesse caso, PartyID é uma coluna de identidade e ser numérico nunca corresponderá a qualquer SamAccountName , então funcionou para mim. Você pode tentar seu próprio método – certifique-se de entender o domínio de seus dados para que não haja possibilidade de interseção com dados reais. Isso pode ser tão simples quanto prefixar um caractere de diferenciador como este:

 Coalesce('n' + SamAccountName, 'p' + Convert(varchar(11), PartyID)) 

Mesmo que o PartyID se PartyID não numérico algum dia e possa coincidir com um SamAccountName , agora isso não importará.

Observe que a presença de um índice incluindo a coluna computada implicitamente faz com que cada resultado da expressão seja salvo em disco com os outros dados na tabela, o que requer espaço em disco adicional.

Observe que, se você não quiser um índice, ainda poderá salvar a CPU, tornando a expressão pré-calculada em disco, adicionando a palavra-chave PERSISTED ao final da definição da expressão de coluna.

No SQL Server 2008 e superior, definitivamente use a solução filtrada, se possível!

Controvérsia

Observe que alguns profissionais de database verão isso como um caso de “NULLs substitutos”, que definitivamente têm problemas (principalmente devido a problemas em tentar determinar quando algo é um valor real ou um valor substituto para dados ausentes ; também pode haver problemas com o número de valores substitutos não-NULL multiplicando-se como um louco).

No entanto, acredito que este caso seja diferente. A coluna computada que estou adicionando nunca será usada para determinar nada. Não tem significado próprio e não codifica informações que ainda não tenham sido encontradas separadamente em outras colunas definidas corretamente. Nunca deve ser selecionado ou usado.

Então, minha história é que isso não é um substituto NULL, e eu estou aderindo a isso! Como na verdade não queremos o valor não-NULL para qualquer propósito além de enganar o índice UNIQUE para ignorar NULLs, nosso caso de uso não tem nenhum dos problemas que surgem com a criação NULL substituta normal.

Tudo o que disse, eu não tenho nenhum problema com o uso de uma exibição indexada em vez disso – mas traz alguns problemas com ela, como o requisito de usar SCHEMABINDING . Divirta-se adicionando uma nova coluna à sua tabela base (você terá, no mínimo, que descartar o índice e, em seguida, descartar a exibição ou alterar a exibição para não estar vinculado ao esquema). Veja a lista completa (longa) de requisitos para criar uma exibição indexada no SQL Server (2005) (também versões posteriores), (2000) .

Atualizar

Se sua coluna for numérica, pode haver o desafio de garantir que a restrição exclusiva usando Coalesce não resulte em colisões. Nesse caso, existem algumas opções. Pode-se usar um número negativo, para colocar os “NULLs substitutos” somente no intervalo negativo, e os “valores reais” apenas no intervalo positivo. Como alternativa, o seguinte padrão pode ser usado. Na tabela Issue (onde IssueID é a PRIMARY KEY ), pode ou não haver um TicketID , mas se houver, deve ser exclusivo.

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

Se IssueID 1 tiver o ticket 123, a restrição UNIQUE estará nos valores (123, NULL). Se o IssueID 2 não tiver ticket, ele estará ativado (NULL, 2). Alguns pensamentos mostram que essa restrição não pode ser duplicada para nenhuma linha da tabela e ainda permite vários NULLs.

Para as pessoas que estão usando o Microsoft SQL Server Manager e desejam criar um índice Exclusivo, mas Anulável, você pode criar seu índice exclusivo como faria normalmente em suas propriedades de índice para seu novo índice, selecione “Filtrar” no painel esquerdo e digite seu filtro (que é sua cláusula where). Deve ler algo assim:

 ([YourColumnName] IS NOT NULL) 

Isso funciona com o MSSQL 2012

Quando apliquei o índice exclusivo abaixo:

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

todas as atualizações e inserções não nulas falharam com o erro abaixo:

UPDATE falhou porque as seguintes opções SET possuem configurações incorretas: ‘ARITHABORT’.

Eu encontrei isso no MSDN

SET ARITHABORT deve estar ON quando você estiver criando ou alterando índices em colunas computadas ou exibições indexadas. Se SET ARITHABORT estiver OFF, as instruções CREATE, UPDATE, INSERT e DELETE em tabelas com índices em colunas computadas ou exibições indexadas falharão.

Então, para que isso funcione corretamente eu fiz isso

Clique com o botão direito do mouse em [Banco de dados] -> Propriedades -> Opções -> Outras Opções -> Perdidas -> Aborto Aritmético Ativado -> verdadeiro

Eu acredito que é possível definir essa opção no código usando

 ALTER DATABASE "DBNAME" SET ARITHABORT ON 

mas eu não testei isso

Crie uma visualização que selecione somente colunas não NULL e crie o UNIQUE INDEX na exibição:

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

Observe que você precisará executar os INSERT e UPDATE na exibição em vez de na tabela.

Você pode fazer isso com um gatilho INSTEAD OF :

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

É possível criar uma restrição exclusiva em uma Exibição Indexada em Cluster

Você pode criar a Vista assim:

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

e a restrição única como esta:

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

Isso pode ser feito no designer também

Clique com o botão direito do mouse em Índice> Propriedades para obter esta janela

capturar

Talvez considere um gatilho ” INSTEAD OF ” e faça o teste você mesmo? Com um índice não-clusterizado (não exclusivo) na coluna para ativar a pesquisa.

Como afirmado anteriormente, o SQL Server não implementa o padrão ANSI quando se trata de UNIQUE CONSTRAINT . Há um ticket no Microsoft Connect para isso desde 2007. Como sugerido, aqui e aqui as melhores opções a partir de hoje são usar um índice filtrado como indicado em outra resposta ou em uma coluna computada, por exemplo:

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

Você pode criar um gatilho INSTEAD OF para verificar condições e erros específicos, se forem atendidos. Criar um índice pode ser caro em tabelas maiores.

Aqui está um exemplo:

 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 

Você não pode fazer isso com uma restrição UNIQUE , mas você pode fazer isso em um gatilho.

  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 se vc fizer um formulário de cadastro com textBox e usar insert e ur textBox estiver vazio e vc clicar no botão submit.

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