Mitos do SQL Server – Será que a opção IGNORE_DUP_KEY permite entradas duplicadas na chave primária e índices únicos ?

Boa Noite Pessoal,

Seguindo tempestivas semanas de trabalho, algumas aulas sendo ministradas, provas de certificação marcadas e diversas outras pendências está realmente complicado manter a postagem semanal além de comparecer nos fóruns e até responder os e-mails e dúvidas de SQL Server (estou tentando colocar tudo em dia). Ontem apresentei uma aula sobre índices e notei que em uma das transparências aparecia a opção Ignore_Dup_Key. A idéia era meramente citar a opção, mas esse nome leva a interpretações equivocadas. Para não perder o hábito, resolvi discorrer um pouco mais sobre essa opção que ao contrário do que parece não permite a entrada de registros duplicados em uma chave primária, uma constraint unique ou um índice unique.

A relação entre constraints de unicidade e índices

Existem duas constraints relacionadas a unicidade (ou como alguns dizem, a integridade de entidade). A primeira é a chave primária e a segunda é a unique constraint. Normalmente a primeira opção é mais conhecida, pois, além de possibilitar unicidade é obrigatória para permitir relacionamentos. A segunda possui essas características embora não seja destinada a esse fim. Ela normalmente é utilizada para garantir a unicidade das chaves secundárias. Tanto a chave primária quanto a unique constraint são critérios lógicos e não físicos. Isso significa que uma chave primária ou uma unique constraint nada tem a ver com a unicidade dos registros diretamente. Para que essa unicidade possa ser garantida do ponto de vista físico, a criação dessas estruturas lógicas faz com que fisicamente índices sejam implementados. Ex:

— Cria uma tabela
CREATE TABLE Clientes (
    ID INT NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    CPF CHAR(11) NOT NULL,

CONSTRAINT PK_Clientes PRIMARY KEY (ID),
CONSTRAINT UQ_CPF UNIQUE (CPF))

— Verifica os índices existentes
EXEC sp_helpindex ‘Clientes’

— Verifica as constraints existentes
EXEC sp_helpconstraint ‘Clientes’

Como pode ser observado, a criação de uma chave primária e uma constraint unique provocou a criação de dois índices unique (mesmo que não de forma explícita). A criação desses índices é a implementação física que possibilita que restrições lógicas como a chave primária e a unique constraint possam ser garantidas. Não importa se a constraint é uma chave primária ou uma unique constraint, pois, fisicamente é criado um índice único. A ligação entre as constraints e os índices é indissiociável. Não é possível excluir os índices se as constraints existirem. A exclusão das constraints provoca a exclusão dos índices.

Os efeitos da opção IGNORE_DUP_KEY

O script abaixo remove as constraints previamente criadas e adiciona a propriedade IGNORE_DUP_KEY nas constraints. Essas propriedades não são pertinentes as constraints em si, mas são aplicáveis aos índices que serão criados por conta das constraints.

— Retira as Constraints da tabela de clientes
ALTER TABLE Clientes DROP CONSTRAINT PK_Clientes
ALTER TABLE Clientes DROP CONSTRAINT UQ_CPF

— Adiciona as constraints com a opção IGNORE_DUP_KEY
ALTER TABLE Clientes ADD CONSTRAINT PK_Clientes
    PRIMARY KEY (ID) WITH (IGNORE_DUP_KEY = ON)

ALTER TABLE Clientes ADD CONSTRAINT UQ_CPF
    UNIQUE (CPF) WITH (IGNORE_DUP_KEY = ON)

Agora que a chave primária foi criada com a opção de ignorar registros repetidos, resta checar se isso é semelhante a permitir registros duplicados.

— Insere um cliente com o ID 1 e com o ID 2
INSERT INTO Clientes VALUES (1, ‘Rodrigo Borges Freitas’, ‘70036580923’)
INSERT INTO Clientes VALUES (2, ‘Iara Mendes Oliveira’, ‘20136789102’)

— Insere um novo cliente com o ID 1 (já existente)
INSERT INTO Clientes VALUES (1, ‘Natasha Gomes Siqueira’, ‘32205631997’)

O conjunto de comandos retorna a seguintes sequência de mensagens no SQL Server Management Studio:

(1 row(s) affected)

(1 row(s) affected)
Duplicate key was ignored.

(0 row(s) affected)

Os dois primeiros INSERTs foram contemplados na tabela, mas já o terceiro INSERT foi simplesmente “ignorado” e por isso a mensagem “(0 row(s) affected)”. Impedir a duplicação de chaves primárias é um requisito inviolável para permitir relacionamentos, pois, se chaves primárias pudessem ser duplicadas e houvesse dois registros com o ID igual a 1, qual seria o sentido das tabelas que tivessem uma FK apontando para esse ID ? Seria ilógico.

A unique constraint não é utilizada em relacionamentos e sobre certos aspectos menos restritiva. Será que a opção IGNORE_DUP_KEY possibilitaria a duplicação ?

— Insere um novo cliente com o ID 3, porém com CPF repetido
INSERT INTO Clientes VALUES (3, ‘Natasha Gomes Siqueira’, ‘70036580923’)

Após a execução desse cadastro, a mensagem é a mesma que a anterior. O título “(0 row(s) affected)” e uma instrução SELECT podem confirmar que o terceiro registro não foi inserido. Nada mais lógico, pois, as restrições não se devem às particularidades de cada constraint já que ambas criam um índice único e rejeitam registros repetidos da mesma forma embora não retornem nenhuma mensagem de erro. Será que o mesmo se mantêm para inserções em conjunto e não em linha ?

— Cria uma tabela com a mesma estrutura porém sem restrições
CREATE TABLE ClientesTmp (
    ID INT NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    CPF CHAR(11) NOT NULL)

— Insere quatro registros
INSERT INTO ClientesTmp VALUES (1,‘Bruno Morais Jr.’,‘45233310738’)
INSERT INTO ClientesTmp VALUES (3,‘Carla Martins Praxedes’,‘20136789102’)
INSERT INTO ClientesTmp VALUES (4,‘Eugênio Silva Batista’,‘58017439501’)
INSERT INTO ClientesTmp VALUES (5,‘Clístenes Jorge Moura’,‘58017439501’)

— Tenta inserir todos os registros de ClientesTMP em Clientes
— A cláusula OUTPUT mostra o registro inserido

INSERT INTO Clientes (ID, Nome, CPF)
OUTPUT INSERTED.*
SELECT ID, Nome, CPF FROM ClientesTmp

Embora a tabela ClientesTmp tivesse 4 registros, apenas um registro foi inserido na tabela Clientes que conforme mostrado pela cláusula OUTPUT foi o ID 4. Os demais foram simplesmente ignorados. Não é difícil descobrir o porquê. O ID 1 já existia na tabela Clientes. O ID 3 possui um CPF que já está sendo utilizado pelo ID 2 na tabela Clientes e o ID 4 e 5 possuem o mesmo CPF. No momento em que o ID 4 foi inserido, o ID 5 passa a representar um CPF em repetição e por isso foi desconsiderado. Em outras palavras, o uso da opção IGNORE_DUP_KEY faz com que todos os registros sejam inseridos, descartando-se apenas os duplicados. Alguns podem achar que esse já era o comportamento padrão mesmo sem a opção IGNORE_DUP_KEY. Será mesmo ?

— Retira as Constraints da tabela de clientes
ALTER TABLE Clientes DROP CONSTRAINT PK_Clientes
ALTER TABLE Clientes DROP CONSTRAINT UQ_CPF

— Adiciona as constraints sem a opção IGNORE_DUP_KEY
ALTER TABLE Clientes ADD CONSTRAINT PK_Clientes PRIMARY KEY (ID)
ALTER TABLE Clientes ADD CONSTRAINT UQ_CPF UNIQUE (CPF)

— Exclui o ID 4 da tabela Clientes para tentar carregá-lo novamente
DELETE FROM Clientes WHERE ID = 4

— Tenta inserir todos os registros de ClientesTMP em Clientes
INSERT INTO Clientes (ID, Nome, CPF)
SELECT ID, Nome, CPF FROM ClientesTmp

Dessa vez o efeito não foi exatamente o mesmo. Embora o ID 4 fosse o único que pudesse ser inserido em Clientes a partir de ClientesTmp, o fato de haver outros rejeitos rejeitáveis fez com que toda a operação fosse abortada.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_Clientes’. Cannot insert duplicate key in object ‘dbo.Clientes’.
The statement has been terminated.

O teste da unique constraint sequer foi realizado, pois, o primeiro registro (ID 1) já provoca uma violação da chave primária PK_Clientes.

Após todos esses testes está mais que comprovodo que a opção IGNORE_DUP_KEY ignora os registros duplicados, mas isso em hipótese nenhuma significa que as duplicações são permitidas apenas porque essa opção foi especificada. A única coisa que essa opção faz é ignorar as duplicações descartando-as sem levantar uma exceção. Existem momentos em que isso é desejável, mas também existem outros que não. O fato é que o SQL Server, assim como qualquer banco de dados relacional sério não permitirá repetições em chaves primárias, constraints uniques, ou melhor dizendo em índices unique. Se alguém dissemina essa informação, está apenas disseminando mais um desses mitos não comprovados que vimos por aí.

[ ]s,

Gustavo

2 Respostas para “Mitos do SQL Server – Será que a opção IGNORE_DUP_KEY permite entradas duplicadas na chave primária e índices únicos ?

  1. grande Gustavo… tudo bem?ótimo artigo… só uma observação… você comentou que a chave primária era obrigatória para relacionamentos… mas na verdade, no sql server é possível criar relacionamentos utilizando tambem colunas que tenham uma Unique Constraint… isso pode ser útil em tabelas MUITO pequenas, que por exemplo possuem apenas 1 pagina de dados… nesse caso o custo de um table scan seria baixissimo, já que sempre iria retornar 1 pagina com todos os dados.. e o overhead de manter um indice seria desnecessario… lógico… a gente sempre tem que avaliar aonde isso tras um ganho ou perda de performance… mas de qualquer forma… vale o teste para confirmar isso: CREATE DATABASE TesteGOUSE TestegoCREATE TABLE t1(id int IDENTITY(1,1), col1 VARCHAR(10))GOALTER TABLE t1 ADD CONSTRAINT UQ_ID UNIQUE(id)goCREATE TABLE t2(id INT IDENTITY(1,1), col1 VARCHAR(10), idT1 INT)go– como temos uma unique constraint ele vai permitir criar a FK…ALTER TABLE t2 ADD CONSTRAINT FK_t1_t2 FOREIGN KEY(id) REFERENCES t1(id)GOINSERT INTO t1 values(\’valor 1\’)go– esse vai funcionarINSERT INTO t2 VALUES(\’valor 1\’, 1)go– nao funciona por causa da FKINSERT INTO t2 VALUES(\’valor 2\’, 2)abraço! Felipe Ferreirahttp://weblogs.asp.net/felipeferreira

  2. Olá Felipe,Tudo bem ?De fato, como fisicamente a Unique Key e a Primary Key são objetos muito parecidos, é permitido fazer relacionamentos com a Unique Key (embora do ponto de vista lógico isso não seja recomendável).Ainda assim, tanto Unique Constraints como Primary Keys produzem índices e não é possível escapar do Overhead. Não é facultativo criar ou não um índice quando se utiliza um PK ou uma Unique Constraint. No seu caso, o comando sp_helpindex \’T1\’ demonstra a presença de um índice (mesmo que a unicidade seja garantida com uma Unique Constraint ao invés de uma Primary Key)[ ]s,Gustavo

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