Few days ago my colleague showed me strange behavior of SQL Server's 2008 RTM MERGE statement. Although this is corrected in SP1 1 or more probably in CU1 (there is bug solved in CU1 connected to this - imo), I do believe its worth to be mentioned.

If you try to update parent column (column referenced by foreign key) even with the same value it fails with message "The MERGE statement conflicted with the REFERENCE constraint" when index used for FK relationship is NONCLUSTERED. It does not matter if this index is primary key, unique constraint or non-filtered unique key (those are options you can use for FK). Moreover, it does not matter if there is (or there's not) some other clustered index. It also does not matter if child table has indexes, clustered or non-clustered.

How to reproduce:

• Create parent table with nonclustered index (using either primary key, unique constraint or unique index syntax) over some column.
• Create child table referencing above mentioned column.
• Insert values in both tables. One row per table is enough (child table should have not null reverencing column)
• Execute Merge statement on parent table updating referenced column to the same value.

Example:

-- Create tables
Create Table tparent ( cCode varchar(2) not null);
Create table tchild ( cCode varchar(2) not null);

-- Create non-clustered PK on parent table
Alter Table tparent
add Constraint PK_tparent
Primary Key NONCLUSTERED (cCode asc);

-- Create Foreign key
Alter Table tchild
add Constraint fk_tchild_tparent
Foreign Key (cCode)
References tparent(cCode);

-- Create Foreign key
Insert Into tparent(cCode) values ('AA');
Insert Into tchild(cCode) values ('AA');

-- MERGE
Merge tparent as trg
Using (Select 'AA') as src(cCode)
on src.cCode=trg.cCode
When Matched
Then Update Set cCode=trg.cCode;


-- RESULT
Msg 547, Level 16, State 0, Line 21
The MERGE statement conflicted with the REFERENCE constraint "fk_tchild_tparent".
The conflict occurred in database "tempdb", table "dbo.tchild", column 'cCode'.
The statement has been terminated.

UPDATE:

Simmilar issue was found by Alexander Kuznetsov in "Trusted" Foreign Keys Allow Orphans, Reject Valid Child Rows for SQL Server 2008 R2.