Momento POG: Unique Index com valores nulos em Banco de Dados
Leonel Fraga de Oliveira 22/08/2012 21:41

Olá, caro leitor do NM Tech! Finalmente achei algo que é interessante para compartilhar aqui.

Imagine a seguinte situação: você tem um sistema cuja chave “forte” em um cadastro de pessoas é o CPF e ele deve ser único em todo o cadastro e o mesmo deve aceitar valores nulos.

A minha primeira ideia com este tipo de implementação é criar um “unique index” no campo CPF do banco de dados, ou seja, não impor mais essa carga para a aplicação fazer.

Beleza, a sugestão é ótima, mas...

O primeiro CPF nulo é inserido com sucesso, daí vem um segundo CPF e PAU: erro de violação do unique index que foi criado, afinal, somente pode haver UM valor distinto do campo em toda a tabela (e embora NULL seja um estado, neste caso consideraremos como um valor, e é assim que o Unique Index trabalha).

Mas graças a POG, este dilema poderá ser facilmente resolvido. Vamos adotar a seguinte tabela como exemplo:

CREATE TABLE [dbo].[Cadastro](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Nome] [varchar](100)
	[Foto] [image] NULL,
	[CPF] [varchar](20)
)

Certo... ela tem uma chave primária sequencial e automática, e o campo CPF é um varchar de 20 caracteres e que aceita nulos, e precisamos criar um índice que permita apenas um ÚNICO CPF em toda a tabela, exceto se ele for nulo.

O primeiro passo para resolver o problema, é criar um campo calculado com a seguinte declaração:

alter table cadastro add UNIQUE_CPF as (case when CPF is null then cast(id as varchar(20)) else CPF end)

O que esse bicho faz: se o CPF for nulo, ele retorna o valor da nossa chave primária automática, fazendo o devido CAST para varchar(20); caso contrário, retorna o CPF propriamente dito.

Agora vamos ao pulo do gato: Crie um UNIQUE INDEX para este campo calculado e “voilá!”: caso haja a tentativa de cadastrar um CPF que já existe, o sistema acusa erro. Caso o valor deste campo seja NULL, o campo calculado irá retornar a nossa PK, que NUNCA irá ser repetida, e portanto, não violará o índice.

CREATE UNIQUE NONCLUSTERED INDEX [IDX_UNQ_CPF] ON [dbo].[Cadastro] 
(
	[UNIQUE_CPF] ASC
)

[Fonte: SQL Server Code Book]

Simples e bem útil, não é? :)

Leonel Fraga de Oliveira Leonel Fraga de Oliveira é formado em Processamento de Dados na Faculdade de Tecnologia de São Paulo (FATEC-SP - 2002) e anteriormente em Técnico em Eletrônica, pela ETE Professor Aprígio Gonzaga (lá em 1999).
Atualmente trabalha como Analista de Sistemas na Prefeitura Municipal de São Caetano do Sul - SP
Tem como hobbies DJing (também trabalha como DJ freelancer) e ciclismo, além da manutenção dos sites NeoMatrix Light e NeoMatrix Tech.
Gosta de música eletrônica, tecnologia, cinema (super fã de Jornada nas Estrelas), gastronomia e outras coisas mais.


Compartilhe nas redes sociais

   

Deixe seu comentário

comments powered by Disqus