为什么有时在合并命令中会出现错误“尝试将不可为 NULL 的列的值设置为 NULL"? [英] why is there the error `Attempting to set a non-NULL-able column's value to NULL` on merge command sometimes?

问题描述

我使用 merge 语句插入新数据或删除数据.我使用 temp table 作为 source 和 table 作为 target.

目标表与另一个表(表A)有一个foreign key.

有时在执行 (when matching then delete 语句) 时会遇到以下错误.

错误:试图将不可为 NULL 的列的值设置为 NULL.

如果我评论这一行，查询将完美运行.

此外，如果我删除Material table 和table A 之间的外键，查询也会完美运行.

我已应用此修补程序，但我还没有提到问题.

SQL Server 版本:Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)

**目标表(材料)** **表 A**PatientIdRef (ForeignKey) PatientId(Primary Key)VisitNumberRef(Foreign Key) VisitNumber(Primary Key)单价名称Unit_Price_Ins家族........创建表#Temp(patinetId [varchar](50) 非空，[Visit_Number] [smallint] NULL,[Unit_Price] [float] NULL,[Unit_Price_Ins] [float] NULL，...........)合并材料作为目标使用(选择 patinetId ,Visit_Number,Unit_Price,Unit_Price_Ins来自#Temp) 作为来源在(source.patientid=target.patientid 整理 arabic_ci_as 和 source.visit_number=target.visit_number)匹配后删除当不匹配时插入(patinetIdref ,Visit_Numberref,Unit_Price,Unit_Price_Ins )值(patinetId ,Visit_Number,Unit_Price,Unit_Price_Ins)

解决方案

由于您在目标表具有多个外键 (FK) 约束的情况下遇到此问题，很可能是由于此错误:FIX:当您在SQL Server 2008、SQL Server 2008 R2 或 SQL Server 2012

您正在运行 SQL Server 2008 R2 SP1 版本 10.50.2550

此问题已在 SQL Server 2008 R2 SP1 版本 10.50.2822 及更高版本(累积更新 8)中修复.

从这里下载:SQL Server 2008 R2 Service Pack 1 的累积更新包 8>

或者最好下载适用于 SQL Server 2008 R2 SP1 的最新累积更新:SQL 累积更新包 13服务器 2008 R2 SP1

<小时>

您提到您已经为此应用了 SQL Server 修补程序之一，但我建议您通过执行 SELECT @@VERSION 并验证版本号来仔细检查它是否已正确安装10.50.2822 或更高版本.

I use merge statement for inserting new data or deleting data. I use a temp table as source and a table as target.

the target table has a foreign key with another table(Table A).

I encounter following error sometimes when (when matched then delete statement) is executed.

error:
    Attempting to set a non-NULL-able column's value to NULL.

If I comment this line the query is run perfectly.

also If i remove the foreign key between Material table and table A query is run perfectly too.

I have applied this Hotfix but I have mentioned problem yet.

SQL Server version:Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)

**Target Tbale (Material)**                **Table A**

PatientIdRef (ForeignKey)             PatientId(Primary Key)
VisitNumberRef(Foreign Key)           VisitNumber(Primary Key)
Unit_Price                            Name
Unit_Price_Ins                        family
....
....                            

create tabl #Temp 
(
  patinetId [varchar](50) NOT NULL,
  [Visit_Number] [smallint] NULL,
  [Unit_Price] [float] NULL,
  [Unit_Price_Ins] [float] NULL,
  ......
  .....
)
merge materials as target
using(select patinetId ,Visit_Number,Unit_Price,Unit_Price_Ins
from #Temp
) as source
on (source.patientid=target.patientid collate arabic_ci_as and source.visit_number=target.visit_number)
when matched then delete
when not matched then insert
(patinetIdref ,Visit_Numberref,Unit_Price,Unit_Price_Ins )
values(patinetId ,Visit_Number,Unit_Price,Unit_Price_Ins)

解决方案

Since you are experiencing this problem in a case where the target table has more than one foreign key (FK) constraint, it's most probably due to this bug: FIX: "Attempting to set a non-NULL-able column's value to NULL" error message when you use a MERGE statement in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012

You are running SQL Server 2008 R2 SP1 version 10.50.2550

This is fixed in SQL Server 2008 R2 SP1 version 10.50.2822 and later (Cumulative Update 8).

Download from here: Cumulative update package 8 for SQL Server 2008 R2 Service Pack 1

Or preferably, download the latest Cumulative Update available for SQL Server 2008 R2 SP1: Cumulative update package 13 for SQL Server 2008 R2 SP1

---

You mention that you have already applied one of the SQL Server hotfixes for this, but I suggest you double check that it is installed properly by executing SELECT @@VERSION and verifying that the version number is 10.50.2822 or later.

这篇关于为什么有时在合并命令中会出现错误“尝试将不可为 NULL 的列的值设置为 NULL"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！