DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 956718 - Last Review: September 19, 2008 - Revision: 4.0

Hotfix Download Available
View and request hotfix downloads
 
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  (http://support.microsoft.com/kb/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  (http://support.microsoft.com/kb/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  (http://support.microsoft.com/kb/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  (http://support.microsoft.com/kb/956717/LN/ ) 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  (http://support.microsoft.com/kb/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  (http://support.microsoft.com/kb/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:
822499  (http://support.microsoft.com/kb/822499/ ) New 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  (http://support.microsoft.com/kb/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 (http://msdn.microsoft.com/en-us/library/ms179325(SQL.100).aspx)

APPLIES TO
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
Keywords: 
kbautohotfix kbhotfixserver kbqfe kbpubtypekc kbfix KB956718
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support