Merge into throws foreign key conflict

Question

This is part of my SQL statement:

MERGE INTO Scyk.dbo.Threads AS d
USING (SELECT Id,Question,QuestionTitle, Answer, Category FROM Homework.dbo.Zadanes) AS s
ON 1 = 2 
   WHEN NOT MATCHED THEN
      INSERT (CurrentValue, NumberOfPosts, TotalValue, Views, Title, DateCreated, LastPostAuthor, LastPostDateTime, IsDeleted, Restricted, RestrictWrite, Sticked, CategoryName, AuthorUserName)
      VALUES (0, 2, 0, 0, s.QuestionTitle, CURRENT_TIMESTAMP, 'zadania', DATEADD(second, 1, CURRENT_TIMESTAMP), 0, 0, 0, 0, s.Category, 'zadania')

This is the error that I get:

The MERGE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Threads_dbo.Categories_CategoryName". The conflict occurred in database "Scyk", table "dbo.Categories", column 'Name'.

So the first thing I did, is that I checked for foreign key integrity:

SELECT Distinct(Category) FROM [Homework].[dbo].[Zadanes]
where not exists
(select distinct(Name) from Scyk.dbo.Categories)

But this returned nothing, and that means that foreign keys are good. So what is going on here?

Answer 1

What is going on is that your query says

Select any categories from Homework.dbo.Zadanes but only if there are no rows in Scyk.dbo.Categories.

Presumably there are rows in the latter table and therefore your query returned nothing.

Try

SELECT z.Category 
  FROM [Homework].[dbo].[Zadanes] z LEFT OUTER JOIN
       [Scyk].[dbo].[Categories] c
    ON z.Category = c.Name
 WHERE c.Name IS NULL
 GROUP BY z.Category

Also, unless you're using the OUTPUT clause to remap a bunch of IDs at once, there is no reason to use MERGE in place of INSERT.

Answer 2

You should just stop using MERGE, IMHO, and just use a normal INSERT. What is MERGE buying you here?

There are at least 15 bugs that remain unfixed in some or all supported versions of SQL Server:

• Use Caution with SQL Server's MERGE Statement
• So, you want to use MERGE, eh?

It sounds like this is the one causing your issue, and it has been closed as Won't Fix. The last one may resolve your issue (get the fix from KB #956718), but somehow I doubt it. Even if it does, then you may be affected by one of the other 14 listed there (or another that has yet to be discovered). Sadly, this is a case of releasing a half-finished feature with minimal testing and then ignoring it for 3+ releases.

If you want to check the foreign key, your query should probably look like this:

SELECT Distinct(Category) FROM [Homework].[dbo].[Zadanes] AS z
where not exists
(select 1 from Scyk.dbo.Categories AS c WHERE c.name = z.Category);

Though it's odd that you have a foreign key on a name rather than, say, a CategoryID. What happens when the name changes? Do you have cascade options set? How much space are you wasting by repeating 'Electronics' or longer category names in every row in Zadanes?