KB956718 - CORREÇÃO: Uma instrução MERGE pode não impor uma restrição de chave externa quando a instrução atualiza uma coluna de chave exclusiva que não faz parte de uma chave de clustering e há uma única linha como a fonte de atualização no SQL Server 2008

Aplica-se A
SQL Server 2008

Erro #: 50003167 (Hotfix do SQL)

Para obter mais informações sobre a lista mestre de compilações lançadas após o lançamento do SQL, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:

957826 Onde pode encontrar mais informações sobre as compilações do SQL Server 2008 lançadas após o SQL Server 2008 e as compilações do SQL Server 2005 lançadas após o SQL Server Service Pack 2 de 2005

Sintomas

No Microsoft SQL Server 2008, uma restrição de chave estrangeira pode não ser imposta quando as seguintes condições forem verdadeiras:

  • É emitida uma instrução MERGE.
  • A coluna de destino da atualização tem um índice exclusivo não agrupado.

Tenha em conta o seguinte cenário. A instrução atualiza uma coluna exclusiva denominada Coluna1 de uma tabela com o nome Tabela1. A Tabela1 é referenciada por uma restrição de chave externa de uma tabela designada Tabela2.

O resultado é que as linhas na Tabela1 são alteradas onde não deveriam ter sido. Além disso, a Tabela2 terá linhas com referências pendentes à Tabela1.

Esse problema ocorre para esse cenário quando as seguintes condições são verdadeiras:

  • A coluna Coluna1 referenciada na Tabela1 não faz parte da chave de agrupamento da Tabela1.

  • Só pode ser atribuído um valor possível à coluna Column1 . Por exemplo, ocorre um dos seguintes cenários:

    • A origem de intercalação é uma única linha de dados. Por exemplo, a origem de intercalação é de uma das seguintes instruções select:

      • select <ConstantValues>

      • select <Parameters>

      Observação Este cenário é o mais provável.

    • A origem de intercalação é, na verdade, uma única linha de dados. Por exemplo, a origem de intercalação é de uma das seguintes instruções select:

      • select <ColumnName> from <TableName> where <TableName>.<ColumnName> = 1

        Observação <TableName>.<ColumnName> é conhecido pelo otimizador de consultas como um valor exclusivo.

      • select top 1 <ColumnName> from <TableName>

    • A associação entre a origem de intercalação e o destino de intercalação tem um predicado que garante que será atualizada uma única linha.

    • A cláusula update define a coluna Column1 para um valor constante, independentemente da origem de intercalação.

  • A opção Em cascata ao atualizar não está habilitada na restrição de chave estrangeira na Tabela2.

Observação Recomendamos que você aplique esse hotfix se você usar a instrução MERGE para atualizar colunas que têm índices exclusivos não clusterizados que são referenciados por restrições de chave externa.

Resolução

A correção para este problema foi lançada pela primeira vez na Atualização Cumulativa 1. Para obter mais informações sobre como obter esse pacote de atualização cumulativa para o SQL Server 2008, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:

956717 Pacote de atualizações cumulativas 1 para SQL Server 2008

Observação Como as compilações são cumulativas, cada nova versão de correção contém todos os hotfixes e todas as correções de segurança que foram incluídas com o lançamento de correção anterior SQL Server 2008. Recomendamos que você considere aplicar o lançamento de correção mais recente que contém esse hotfix. Para mais informações, clique no número de artigo que se segue para ver o artigo na Base de Dados de Conhecimento Microsoft:

956909 As compilações SQL Server 2008 lançadas após o lançamento do SQL Server 2008

Solução

O pacote de hotfix elimina o problema. Se utilizar a instrução MERGE no cenário descrito na secção "Sintomas" e se optar por não aplicar a correcção, siga estes passos para eliminar este problema:

  1. Reescreva a instrução MERGE para que os valores para a origem de intercalação estejam numa tabela, tabela temporária ou variável de tabela, em vez de estarem incrustados na consulta.

  2. Utilize o sinalizador de rastreio 8790. Esse sinalizador de rastreamento força o otimizador a usar um tipo de plano que é chamado de plano de atualização ampla. Planos de atualização amplos não têm o problema. Este passo acarreta riscos de desempenho para todas as instruções DML. Portanto, você deve evitar usar esta etapa, a menos que seja impossível alterar o aplicativo.

O script Transact-SQL a seguir mostra uma maneira de alterar o script para resolver esse problema se você não puder aplicar esse hotfix.

Por exemplo, tem um script semelhante ao seguinte:

use tempdb;

drop table sale, product;
create table product(pno int not null primary key, name char(30), pAlternateKey char(6) not null unique);
create table sale(sno int not null primary key, pAlternateKey char(6) not null references product(pAlternateKey));
insert product values(1, 'Office Chair', 'ochair');
insert sale values(1, 'ochair')

-- No violation of foreign key constraint is detected. However, one should be.
merge into product
using (select 'Office Chair2' as name, 1 as pno, 'oxx' as pAlternateKey) as src
on product.pno = src.pno
when matched then
   update set product.pAlternateKey = src.pAlternateKey, 
              product.name = src.name
when not matched then
   insert values(src.pno, src.name, src.pAlternateKey);

Altere o script para que se assemelhe ao seguinte:

insert product values(1, 'Office Chair', 'ochair');
insert sale values(1, 'ochair')
-- A foreign key constraint violation is detected, and the update fails.
declare @source table 
   (name nchar(30), pno int, pAlternateKey nchar(30));
insert into @source values('Office Chair2',1,'oxx');

merge into product
using @source as src
on product.pno = src.pno
when matched then
   update set product.pAlternateKey = src.pAlternateKey, 
              product.name = src.name
when not matched then
   insert values(src.pno, src.name, src.pAlternateKey);

Estado

A Microsoft confirmou que se trata de um problema nos produtos Microsoft listados na secção "Aplica-se a".

Mais Informações

Para obter mais informações sobre quais arquivos são alterados e para obter informações sobre quaisquer pré-requisitos para aplicar o pacote de atualização cumulativa que contém o hotfix descrito neste artigo da Base de dados de Conhecimento da Microsoft, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:

956717 Pacote de atualizações cumulativas 1 para SQL Server 2008

Referências

Para obter mais informações sobre a lista de compilações disponíveis após o lançamento do SQL Server 2008, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:

956909 As compilações SQL Server 2008 lançadas após o lançamento do SQL Server 2008

Para obter mais informações sobre o modelo de manutenção incremental para SQL Server, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:

935897 Um modelo de manutenção incremental está disponível na equipe de SQL Server para fornecer hotfixes para problemas relatados

Para obter mais informações sobre o esquema de nomenclatura para atualizações SQL Server, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:

822499 Novo esquema de nomenclatura para pacotes de atualização de software do Microsoft SQL Server

Para obter mais informações sobre a terminologia de atualização de software, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:

824684 Descrição da terminologia padrão utilizada para descrever as atualizações de software da Microsoft

Índices não agrupados no SQL Server 2008

Para obter mais informações sobre índices não clusterizados no SQL Server 2008, visite o seguinte site da Microsoft Developer Network (MSDN):

https://msdn.microsoft.com/library/ms179325(sql.100).aspx