Unique Constraints – Aplicações, Alternativas e um lapso “justificável” do SQL Server

Bom Dia Pessoal,

Continuo muito enrolado e com dificuldades de aparecer nos fóruns, mas tento contribuir como posso. No caso dos fóruns do MSDN e do TechNet, tenho entrado, mas apenas com o intuito de moderar, pois, está impossível conseguir responder (as responsabilidades não deixam…). Um dia desse vi uma questão bastante interessante intitulada "Como criar um index para não duplicar". Em um artigo "Mitos do SQL Server – Será que a opção IGNORE_DUP_KEY permite entradas duplicadas na chave primária e índices únicos ?" demonstrei que uma Unique Constraint cria inevitavelmente um índice unique para garantir fisicamente a unicidade. A grande questão é como tratar a presença de valores nulos e essa unicidade. Vejamos um exemplo prático.

Uma situação de negócio

Suponha que seja necessário modelar um sistema de vendas de produtos a consumidores finais. Inevitavelmente irá aparecer alguma entidade "Cliente" nesse modelo. Supondo ainda que os atributos relevantes consistam em Nome, CPF, RG, Renda e Classe, e que os atributos CPF e RG consistam em identificadores únicos, existem decisões a serem tomadas sobre qual dessas colunas irá constituir a chave primária. Pode-se adotar o uso de chaves artificiais adicionando-se um outro identificador como IDCliente, CodCliente, etc ou utilizar-se as chaves naturais como o próprio CPF ou o RG. Não irei discorrer nesse artigo o que é melhor (se utilizar chaves artificiais ou naturais), mas independente da escolha, os atributos CPF e RG continuam sendo únicos e no máximo um deles poderá compor a chave primária.

Na elaboração lógica e (ou) conceitual de um modelo de dados, quando todos os identificadores únicos são levantandos, eles são ditos chaves candidatas, ou seja, todas são candidatas em potencial para se tornar a chave primária. Após a escolha de uma chave candidata para exercer o papel de chave primária, as demais chaves candidatas são ditas chaves secundárias (ou chaves alternativas), pois, embora não sejam chaves primárias podem servir para identificação do registro assim como a chave primária faz. A principal diferença é que a chave primária, além de identificar registros como as chaves secundárias o fazem, ela também será necessária para viabilizar os relacionamentos.

Adotando a escolha de uma chave artificial, temos a seguinte implementação física:

— Cria a tabela de Clientes
CREATE TABLE Clientes (
    ClienteID INT NOT NULL, ClienteNome VARCHAR(50) NOT NULL,
    ClienteRG VARCHAR(15) NOT NULL, ClienteCPF CHAR(11) NOT NULL)

— Adicionar o ClienteID como chave primária
ALTER TABLE Clientes ADD CONSTRAINT PKCliente PRIMARY KEY (ClienteID)

Uma boa prática de administração de dados é garantir que as chaves secundárias tenham uma constraint unique para garantir a unicidade. Embora o CPF e o RG não sejam a chave primária da tabela, não é interessante que eles se repitam. Para garantir essa integridade, é adicionada logo a seguir as constraints unique nessas colunas.

— Adiciona constraints unique para restringir as chaves secundárias
ALTER TABLE Clientes ADD CONSTRAINT UQClienteRG UNIQUE (ClienteRG)
ALTER TABLE Clientes ADD CONSTRAINT UQClienteCPF UNIQUE (ClienteCPF)

Dessa forma, mesmo que a chave primária seja única, não será tolerada a repetição de CPFs e RGs conferindo a essas colunas as características físicas de unicidade. Isso é particularmente importante quando chaves artificiais são escolhidas como chaves primárias. O script abaixo mostra a efetividade dessas constraints.

— Tenta efetuar alguns cadastros
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,‘9845423’,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,‘557023’,‘82013423920’)

(1 row(s) affected)

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint ‘UQClienteCPF’. Cannot insert duplicate key in object ‘dbo.Clientes’.
The statement has been terminated.

Como era de se esperar, mesmo não havendo duplicidade de chaves primárias, o fato do CPF estar repetido impede o cadastro do terceiro registro. Nada mais justo, pois, se o CPF é uma chave secundária e identifica o registro de forma única ele também não pode ser duplicado. Enquanto as chaves secundárias forem obrigatórias, a constraint unique serve perfeitamente aos propósitos de identificar registros de forma secundária, bem como garantir a qualidade dos dados impedindo que chaves secundárias se repitam.

O dilema da Unique Constraint para campos não obrigatórios

Alguns problemas e contradições surgem quando colunas não obrigatórias utilizam Unique Constraints. O exemplo abaixo demonstra uma dessas possibilidades.

— Exclui os registros
DELETE FROM Clientes

— Altera as colunas para permitir valores nulos
ALTER TABLE Clientes ALTER COLUMN ClienteRG VARCHAR(15) NULL
ALTER TABLE Clientes ALTER COLUMN ClienteCPF CHAR(11) NULL

— Adiciona três clientes
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,NULL,NULL)

(1 row(s) affected)

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint ‘UQClienteRG’. Cannot insert duplicate key in object ‘dbo.Clientes’.
The statement has been terminated.

Embora não tenha existido nenhuma "repetição" de fato, a presença de dois valores nulos no campo RG fez com que o terceiro registro fosse rejeitado. No primeiro exemplo, a repetição do CPF "82013423920" justifica uma violação, mas será que a presença de um registro nulo deveria sinalizar uma rejeição ? Para o SQL Server parece que sim, mas para o ORACLE parece que não. Antes que algum xiita comece um bombardeio de críticas ao SQL Server, vejamos alguns pontos de vista.

Razões para bloquear registros nulos repetidos

Se considerarmos que a coluna CPF e a coluna RG correspondem a uma chave secundária, ou seja, tem capacidades de identificar registros como únicos então faz de fato sentido deixar um valor nulo apenas. Afinal dado um determinado CPF é esperado retornar apenas um único registro e se o valor nulo puder se cadastrado várias vezes estaremos violando essa característica da chave secundária e ela perde sua capacidade de identificar registros de forma única. Esse é o comportamento do SQL Server em relação a constraint Unique.

Razões para não bloquear registros nulos repetidos

Se considerarmos a lógica baseada em três valores (verdadeiro, falso ou nulo) temos algumas premissas básicas:

A B A = B ?
Verdadeiro Verdadeiro Verdadeiro
Verdadeiro Falso Falso
Verdadeiro Nulo Nulo
Falso Verdadeiro Falso
Falso Falso Falso
Falso Nulo Nulo
Nulo Verdadeiro Nulo
Nulo Falso Nulo
Nulo Nulo Nulo

Se essa lógica for completamente aplicada ao raciocínio das chaves secundárias, não podemos admitir que um valor nulo seja exatamento igual a outro valor nulo. Toda vez que algo conhecido (NOT NULL) é comparado com algo desconhecido (NULL) o resultado é desconhecido. Para tornar isso implementável, admite-se que se uma comparação é desconhecida ela não pode ser considerada como verdade e portanto convenciona-se que ela é falsa. É por essa razão que em qualquer banco de dados a comparação de valores nulos com outros valores nulos é avaliada como falsa por padrão.

Se a comparação entre um valor nulo e outro valor nulo é avaliada como falsa, então o comportamento esperado (e em conformidade com o comitê ANSI) é de que quando houver valores conhecidos (NOT NULL), as repetições não são permitidas e quando houver valores desconhecidos (NULL) as repetições são permitidas. Esse é o comportamento do ORACLE em relação a constraint Unique e que na minha opinião parece mais coerente. Entretanto existem algumas outras considerações a serem feitas.

A indexação e a Nulabilidade

No artigo "Mitos do SQL Server – Será que a opção IGNORE_DUP_KEY permite entradas duplicadas na chave primária e índices únicos ?" demonstrei a relação entre constraints unique e a indexação. Essa relação não é exclusiva do SQL Server. Outros SGBDs também a utilizam. Ter um índice na coluna da constraint unique é uma estratégia básica de garantir fisicamente a unicidade e permitir um mecanismo de consulta mais eficiente para impedir que a unicidade seja violada. É justamente nesse ponto que reside a explicação do porque o Oracle e o SQL Server divergem no comportamento em relação às constraints unique.

Por padrão, nos SGBDs o valor nulo significa ausente e por isso alguns deles não incluem entradas nulas na árvore de índices. Isso pode parecer mais eficiente por um lado, pois a árvore será mais compacta, mas em contrapartida irá limitar sensivelmente o desempenho de consultas que façam pesquisas em um coluna que aceite valores nulos. Se por um lado o ORACLE parece mais coerente com a lógica de três valores para constraints unique, por outro lado ele não será capaz de utilizar um índice para a consulta abaixo:

SELECT ClienteID, ClienteNome, ClienteRG, ClienteCPF
FROM Clientes WHERE ClienteRG IS NULL

Considerando a presença de valores nulos repetidos e a característica opcional do CPF, se esse tipo de pesquisa for feito com freqüência, haverá uma queda sensível no desempenho por parte do Oracle por exemplo (ainda que em coerência com a lógica de três valores). Normalmente para anular esse efeito, é comum colocar um valor padrão como XXX, mas nesse caso, essa alternativa não faz sentido, pois a repetição de XXX seria vetada já que se trata de um valor conhecido.

O SQL Server em contrapartida é capaz de adicionar entradas nulas na árvore de indexação (independente da constraint unique estar ou não presente) e caso lhe venha a ser solicitado a consulta citada, ele poderá utilizar o índice de forma mais eficiente.

As diferenças estruturais na forma como o ORACLE e o SQL Server trabalham a indexação é a grande responsável pelos seus comportamentos em relação a constraint unique já que indiretamente essa depende de um índice. É por isso que por um lado o SQL Server pode ter índices mais eficientes mas por outro pode apresentar um comportamento equivocado em relação a lógica de três valores. Ainda assim, o ORACLE pode levar uma certa vantagem, pois, é necessário antes de mais nada garantir a consistência dos dados antes de seu desempenho nas consultas. Apenas por curiosidade, se esse exemplo fosse utilizado em DB2 não funcionaria, pois, ele não permite a criação de constraints unique em colunas que não sejam NOT NULL.

Solução Alternativa 1 – Revisão da modelagem

No próprio post no fórum do MSDN, foi solicitado a revisão da modelagem de forma a eliminar esse problema. Concordo que a utilização de técnicas de modelagem baseadas em especialização podem eliminar a questão da nulabilidade. Uma implementação alternativa poderia ser feito com os scripts abaixo:

— Não execute esse script. Ele é apenas educacional
— Cria tabelas adicionais (relacionamentos não inclusos)

CREATE TABLE ClientesRG (
    ClienteID INT NOT NULL,
    ClienteRG VARCHAR(15) NOT NULL)

CREATE TABLE ClientesCPF (
    ClienteID INT NOT NULL,
    ClienteCPF CHAR(11) NOT NULL)

Nesse caso, sempre que um cliente tivesse um RG, o ID desse cliente e seu RG seriam cadastrados e o mesmo raciocínio valeria para o CPF. Embora do ponto de vista lógico seja uma boa alternativa, do ponto de vista físico essa alternativa provocaria diversos problemas com a necessidade frequente de vários joins o que possivelmente a inviabilizaria dependendo do volume de registros e da quantidade de chaves secundárias. Se aparecem outras chaves alternativas como o PIS ou NIS, seriam mais duas implementações. Acredito que a modelagem pode resolver muitos problemas, mas em minha opinião esse não é um deles. O uso da modelagem seria muito bom caso houvesse dependências funcionais que violassem a 3FN por conta das dependências transitivas, ou seja, a presença de atributos que sejam determinados por outros atributos que não os participantes da chave primária. Nesse caso, o uso de tabelas auxiliares seria muito bem vindo. Ex:

— Não executar esse script. Ele é apenas educacional
— Cria uma tabela auxiliar para armazenar o RG

CREATE TABLE ClientesRG (
    ClienteID INT NOT NULL,
    ClienteRG VARCHAR(15) NOT NULL,
    OrgaoExpedidor VARCHAR(7) NOT NULL,
    DataExpedicao SMALLDATETIME NOT NULL)

Nessa implementação, se um cliente não possuir RG, automaticamente as demais colunas serão nulas e portanto pode não fazer sentido armazenar todas essas colunas em uma tabela a parte. No caso de apenas o atributo RG, talvez a criação de uma tabela a parte não seja interessante pelas razões já expostas.

Solução Alternativa 2 – O uso de triggers

Normalmente, quando a chave secundária é representada por uma coluna opcional (NULL), o SQL Server não irá obedecer corretamente a lógica de três valores. Nessas situações, é muito comum que o implementador recorra à utilização de uma trigger. Ex:

— Cria uma trigger para checar a repetição
CREATE TRIGGER trgCadastraCliente ON Clientes
INSTEAD OF INSERT
AS
BEGIN

    — Informa os erros
    IF EXISTS (SELECT * FROM Clientes As C
        INNER JOIN INSERTED As I ON C.ClienteRG = I.ClienteRG)
    BEGIN
        RAISERROR(‘Há RGs duplicados nessa operação’,16,1)
        ROLLBACK
        RETURN
    END

    IF EXISTS (SELECT * FROM Clientes As C
        INNER JOIN INSERTED As I ON C.ClienteCPF = I.ClienteCPF)
    BEGIN
        RAISERROR(‘Há CPFs duplicados nessa operação’,16,1)
        ROLLBACK
        RETURN
    END

    — Cadastra todos os registros
    INSERT INTO Clientes
    SELECT * FROM INSERTED

END
GO

— Exclui os registros
DELETE FROM Clientes

— Efetua alguns cadastros (Há repetição de nulos no RG e no CPF)
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,NULL,NULL)
INSERT INTO Clientes VALUES (4,‘Luíza’,‘785123’,NULL)

— Verifica as inserções
SELECT ClienteID, ClienteNome, ClienteRG, ClienteCPF
FROM Clientes

— Tenta inserir registros com o CPF e o RG repetidos (valores conhecidos)
INSERT INTO Clientes VALUES (5,‘Juliana’,‘14538543’,‘82013423920’)
INSERT INTO Clientes VALUES (6,‘Joyce’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (7,‘Marta’,‘14538543’,NULL)

Msg 50000, Level 16, State 1, Procedure trgCadastraCliente, Line 11
Há RGs duplicados nessa operação
Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

Através da trigger, será feita uma checagem se o RG ou o CPF já estão cadastrados na tabela. O uso do EXISTS irá descartar registros com RG ou CPF nulos e portanto já descarta automaticamente registros cujo o RG ou o CPF sejam nulos dispensando-os da validação. Caso exista algum RG ou CPF preenchido, a transação é revertida e os registros não são cadastros. É necessário utilizar o EXISTS em oposição à captura de valores diretamente da tabela INSERTED. A trigger da forma que foi criada está preparada para INSERTs de conjuntos (INSERT com SELECT) e a captura direta dos valores da INSERTED com variáveis não trata essa situação. Se registros com RG e CPFs duplicados (desde que esses RGs e CPFs não sejam nulos) aparecerem, a trigger lançará uma exceção rejeitando-os.

Para que a trigger ganhe eficiência, é recomendável a criação de índices sobre as colunas consultadas (no caso o RG e o CPF). Ex:

— Cria índices sobre o RG e o CPF
CREATE INDEX IXClienteRG ON Clientes (ClienteRG)
CREATE INDEX IXClienteCPF ON Clientes (ClienteCPF)

— Elimina os objetos utilizados nessa solução
DROP TRIGGER trgCadastraCliente
DROP INDEX Clientes.IXClienteRG
DROP INDEX Clientes.IXClienteCPF

O uso de triggers é interessante para permitir que o SQL Server trabalhe "corretamente" com a lógica de três valores "emulando" unique constraints. O problema reside no desempenho. Já é sabido que triggers são péssimas escolhas quando o assunto é desempenho. Os índices podem ajudar, mas essa não seria a minha primeira escolha a menos que uma trigger já estivesse presente e a lógica de unicidade apenas fosse adicionada a essa trigger.

Solução Alternativa 3 – O uso de colunas calculadas

O SQL Server tem a capacidade de indexar colunas calculadas. Pressupondo que a chave primária nunca irá se repetir, é possível fazer uma combinação entre a chave primária e o RG para criar uma coluna de unicidade para o RG e utilizar a mesma lógica para o CPF. Ex:

— Cria duas colunas calculadas
ALTER TABLE Clientes ADD UCClienteRG As IsNull(ClienteRG,ClienteID)
ALTER TABLE Clientes ADD UCClienteCPF As IsNull(ClienteCPF,ClienteID)

— Cria dois índices únicos para as colunas recém criadas
CREATE UNIQUE INDEX IXUCClienteRG ON Clientes (UCClienteRG)
CREATE UNIQUE INDEX IXUCClienteCPF ON Clientes (UCClienteCPF)

— Exclui os registros
DELETE FROM Clientes

— Efetua alguns cadastros (Há repetição de nulos no RG e no CPF)
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,NULL,NULL)
INSERT INTO Clientes VALUES (4,‘Luíza’,‘785123’,NULL)

— Verifica os registros nas colunas calculadas
SELECT ClienteID, ClienteRG, ClienteCPF, UCClienteRG, UCClienteCPF FROM Clientes

Podemos visualizar o resultado da consulta conforme a tabela abaixo:

Cliente ClienteRG ClienteCPF UCClienteRG UCClienteCPF
1 14538543 75712230466 14538543 75712230466
2 NULL 82013423920 2 82013423920
3 NULL NULL 3 3
4 785123 NULL 785123 4

Como a chave primária nunca irá se repetir e ela não pode ser nula, é possível utilizar as colunas calculadas de forma que se o RG ou o CPF forem nulos, utiliza-se a chave primária. Se eles não forem nulos, utiliza-se os próprios. Isso irá permitir a repetição de nulos, mas irá impedir a repetição de registros não nulos.

— Tenta inserir registros com o CPF e o RG repetidos (valores conhecidos)
INSERT INTO Clientes VALUES (5,‘Juliana’,‘14538543’,‘82013423920’)
INSERT INTO Clientes VALUES (6,‘Joyce’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (7,‘Marta’,‘14538543’,NULL)

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUCClienteRG’.

The statement has been terminated.
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUCClienteCPF’.

The statement has been terminated.
Msg 2601, Level 14, State 1, Line 3
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUCClienteRG’.

The statement has been terminated.

Como esses registros tentam inserir valores conhecidos de RG e CPF (NOT NULL) que já estão previamente cadastrados, um erro é retornado impedindo o cadastro.

Essa solução já é bem mais eficiente que as anteriores. A criação de índices sobre as colunas ClienteRG e ClienteCPF já era esperada, pois, dada a caraterística dessas colunas serem chaves secundárias é bem provável que sejam ótimas candidatas a indexação. Expandir a indexação para colunas calculadas não impõe overheads significativos e além de auxiliar a pesquisa de RG e CPF, também garante a unicidade.

As desvantagens sobre essa implementação residem no fato de que cada chave secundária significa em uma coluna a mais na tabela. Mesmo a coluna sendo calculada, o fato dela ser indexada fará com que ela exista fisicamente incorrendo em mais espaço. Há um problema adicional no caso de sobreposição de valores. Se existir uma chave primária 70035478765 e um CPF 70035478765 por exemplo, haverá problemas em relação a unicidade da coluna UCClienteCPF. Um pouco de criatividade pode facilmente superar essa limitação. Caso a chave primária tenha tipos diferentes das colunas supostamente únicas é recomendável utilizar um CAST para manter a tipagem correta.

O script abaixo retira as mudanças dessa implementação.

— Retira os índices
DROP INDEX Clientes.IXUCClienteRG
DROP INDEX Clientes.IXUCClienteCPF

— Elimina as colunas
ALTER TABLE Clientes DROP COLUMN UCClienteRG
ALTER TABLE Clientes DROP COLUMN UCClienteCPF

Solução Alternativa 4 – O uso de Views Indexadas

Essa alternativa é de excelente custo-benefício. Ela consiste basicamente em adotar um comportamento semelhante ao ORACLE, ou seja, impedir a repetição de valores conhecidos, permitir a repetição de valores desconhecidos, mas abrir mão das pesquisas com base em valores desconhecidos (mesmo que um índice esteja disponível). Vejamos como fazer isso:

— Cria uma View Indexada para impedir RGs nulos
CREATE VIEW vRG WITH SCHEMABINDING
AS SELECT ClienteRG FROM dbo.Clientes
WHERE ClienteRG IS NOT NULL

— Cria um índice sobre a View
CREATE UNIQUE CLUSTERED INDEX IXUCClienteRG ON vRG (ClienteRG)

— Cria uma View Indexada para impedir CPFs nulos
CREATE VIEW vCPF WITH SCHEMABINDING
AS SELECT ClienteCPF FROM dbo.Clientes
WHERE ClienteCPF IS NOT NULL

— Cria um índice sobre a View
CREATE UNIQUE CLUSTERED INDEX IXUCClienteCPF ON vCPF (ClienteCPF)

— Exclui os registros
DELETE FROM Clientes

— Efetua alguns cadastros (Há repetição de nulos no RG e no CPF)
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,NULL,NULL)
INSERT INTO Clientes VALUES (4,‘Luíza’,‘785123’,NULL)

— Tenta inserir registros com o CPF e o RG repetidos (valores conhecidos)
INSERT INTO Clientes VALUES (5,‘Juliana’,‘14538543’,‘82013423920’)
INSERT INTO Clientes VALUES (6,‘Joyce’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (7,‘Marta’,‘14538543’,NULL)

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.vCPF’ with unique index ‘IXUCClienteCPF’.

The statement has been terminated.
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object ‘dbo.vCPF’ with unique index ‘IXUCClienteCPF’.

The statement has been terminated.
Msg 2601, Level 14, State 1, Line 3
Cannot insert duplicate key row in object ‘dbo.vRG’ with unique index ‘IXUCClienteRG’.

The statement has been terminated.

Um dos pré-requisitos para indexar uma view é criar um índice único. O RG e o CPF sempre serão únicos, desde que os valores nulos fiquem ausentes (considerando a não conformidade com a lógica de três valores). Como as views ignoraram os valores nulos, o índice será único e impedirá o cadastros de RGs e CPFs repetidos desde que esses sejam conhecidos (NOT NULL).

O script a seguir elimina as views indexadas e seus índices

DROP VIEW vRG
DROP VIEW vCPF

Solução Alternativa 5 – O uso da cláusula WHERE no índice

Essa alternativa é praticamente igual a alternativa anterior. Penso que a possibilidade de utilizar a cláusula WHERE em um índice no SQL Server 2008 não é um nenhuma grande novidade. É apenas um "atalho" para evitar a criação da View, porém com uma sintaxe menor e mais simples. Enfim, valem as mesmas vantagens e desvantagens. Segue abaixo o script

— Criação de Índices com filtros
CREATE UNIQUE INDEX IXUClienteRG ON Clientes (ClienteRG) WHERE ClienteRG IS NOT NULL
CREATE UNIQUE INDEX IXUClienteCPF ON Clientes (ClienteCPF) WHERE ClienteCPF IS NOT NULL

— Exclui os registros
DELETE FROM Clientes

— Efetua alguns cadastros (Há repetição de nulos no RG e no CPF)
INSERT INTO Clientes VALUES (1,‘Natanael’,‘14538543’,‘75712230466’)
INSERT INTO Clientes VALUES (2,‘Marcelo’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (3,‘Jonas’,NULL,NULL)
INSERT INTO Clientes VALUES (4,‘Luíza’,‘785123’,NULL)

— Tenta inserir registros com o CPF e o RG repetidos (valores conhecidos)
INSERT INTO Clientes VALUES (5,‘Juliana’,‘14538543’,‘82013423920’)
INSERT INTO Clientes VALUES (6,‘Joyce’,NULL,‘82013423920’)
INSERT INTO Clientes VALUES (7,‘Marta’,‘14538543’,NULL)

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUClienteRG’.

The statement has been terminated.
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUClienteCPF’.
The statement has been terminated.
Msg 2601, Level 14, State 1, Line 3
Cannot insert duplicate key row in object ‘dbo.Clientes’ with unique index ‘IXUClienteRG’.
The statement has been terminated.

O uso da cláusula WHERE nos índices elimina os valores nulos. A propriedade unique diz que não deve haver repetições. O conjunto da cláusula com a propriedade faz com que os valores conhecidos (NOT NULL) não possam se repetir, mas em contrapartida não impõe nenhuma regra para os valores desconhecidos (NULL).

Como pode ser observado, o SQL Server considera valores nulos como iguais na utilização de constraints unique em oposição a lógica de três valores. A justificativa reside no fato de que a implementação física dessa constraint é baseada em índices e como o SQL Server tem a habilidade de utilizar o índice para valores nulos, o efeito colateral é o comportamento adverso nas unique constraints. Isso não significa que não seja possível manter a unicidade e ignorar os nulos em uma coluna. Há várias alternativas para isso. Só é preciso procurar a implementação correta.

[ ]s,

Gustavo

4 Respostas para “Unique Constraints – Aplicações, Alternativas e um lapso “justificável” do SQL Server

  1. Olá Gustavo,Seus artigos estão cada dia melhores; você já pensou em escrever para a SQL Magazine?Att.Sérgio

  2. Olá Sérgio,Obrigado. É bom saber que o blog está sendo útil e de qualidade. Ultimamente tenho postado algumas "bíblias", mas focando-me nas dúvidas bem típicas do cotidiano.Eu já pensei em escrever para a SQL Magazine sim, mas ainda não pintou o convite. Dessas revistas profissionais, apenas a mundo .NET me convidou e acabei escrevendo dois artigos para eles. Vou ficar no aguardo, certamente que se aparecer a oportunidade, vou publicar um desses artigos para eles com certeza.Abs,

  3. Excelente post! Se eu tivesse visto ele antes de publicar meu post com certeza não o teria escrito. :-)Eu também já tinha visto o uso de views indexadas, mas devo confessar que não sei se usaria essa abordagem ao invés de filtered indexes, considerando as restrições e set options, além da questão dos bookmark lookups que podemos utilizar. Mas que em termos de funcionamento e armazenamento elas estão na mesma ordem, concordo e assino embaixo.[]sLuti

  4. Gostei Bastante! Me foi muito util

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s