Bei Microsoft anmelden
Melden Sie sich an, oder erstellen Sie ein Konto.
Hallo,
Wählen Sie ein anderes Konto aus.
Sie haben mehrere Konten.
Wählen Sie das Konto aus, mit dem Sie sich anmelden möchten.
Englisch
Dieser Artikel ist leider nicht in Ihrer Sprache verfügbar.

Bug #: 50003167 (SQL Hotfix)

For more information about the master list of builds that were released after SQL was released, click the following article number to view the article in the Microsoft Knowledge Base:

957826 Where you can find more information about the SQL Server 2008 builds that were released after SQL Server 2008 and the SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2

Symptoms

In Microsoft SQL Server 2008, a foreign key constraint may not be enforced when the following conditions are true:

  • A MERGE statement is issued.

  • The target column of the update has a nonclustered unique index.

Consider the following scenario. The statement updates a unique column that is named Column1 of a table that is named Table1. Table1 is referenced by a foreign key constraint from a table that is named Table2.

The result is that rows in Table1 are changed when they should not have been. Additionally, Table2 will have rows that have dangling references to Table1.

This problem occurs for this scenario when the following conditions are true:

  • The referenced Column1 column in Table1 is not part of the clustering key of Table1.

  • Only one possible value can be assigned to the Column1 column. For example, one of the following scenarios occurs:

    • The merge source is a single row of data. For example, the merge source is from one of the following select statements:

      • select <ConstantValues>
      • select <Parameters>

      Note This scenario is the most likely scenario.

    • The merge source is actually a single row of data. For example, the merge source is from one of the following select statements:

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

        Note <TableName>.<ColumnName> is known by the query optimizer to be a unique value.

      • select top 1 <ColumnName> from <TableName>
    • The join between the merge source and the merge target has a predicate that guarantees that a single row will be updated.

    • The update clause sets the Column1 column to a constant value, regardless of the merge source.

  • The On Update Cascade option is not enabled on the foreign key constraint in Table2.

Note We recommend that you apply this hotfix if you use the MERGE statement to update columns that have nonclustered unique indexes that are referenced by foreign key constraints.

Resolution

The fix for this issue was first released in Cumulative Update 1. For more information about how to obtain this cumulative update package for SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:

956717 Cumulative update package 1 for SQL Server 2008

Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

956909 The SQL Server 2008 builds that were released after SQL Server 2008 was released

Workaround

The hotfix package eliminates the problem. If you use the MERGE statement in the scenario that is described in the "Symptoms" section and if you choose not to apply the hotfix, follow these steps to eliminate this problem:

  1. Rewrite the MERGE statement so that the values for the merge source are in a table, temp table, or table variable instead of being in-lined in the query.

  2. Use trace flag 8790. This trace flag forces the optimizer to use a kind of plan that is called a wide update plan. Wide update plans do not have the problem. This step carries performance risks for all DML statements. Therefore, you should avoid using this step unless it is impossible to change the application.

The following Transact-SQL script shows one way to change your script to resolve this problem if you cannot apply this hotfix.

For example, you have a script that resembles the following:

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

Change the script so that it resembles the following:

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

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information

For more information about what files are changed, and for information about any prerequisites to apply the cumulative update package that contains the hotfix that is described in this Microsoft Knowledge Base article, click the following article number to view the article in the Microsoft Knowledge Base:

956717 Cumulative update package 1 for SQL Server 2008

References

For more information about the list of builds that are available after the release of SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:

956909 The SQL Server 2008 builds that were released after SQL Server 2008 was released



For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems



For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:

822499New naming schema for Microsoft SQL Server software update packages

For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates

References

For more information about nonclustered indexes in SQL Server 2008, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn.microsoft.com/en-us/library/ms179325(SQL.100).aspx

Benötigen Sie weitere Hilfe?

Möchten Sie weitere Optionen?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

War diese Information hilfreich?

Was hat Ihre Erfahrung beeinflusst?
Wenn Sie auf "Absenden" klicken, wird Ihr Feedback zur Verbesserung von Produkten und Diensten von Microsoft verwendet. Ihr IT-Administrator kann diese Daten sammeln. Datenschutzbestimmungen.

Vielen Dank für Ihr Feedback!

×