MERGE与FOREIGN KEY约束冲突 [英] MERGE conflict with the FOREIGN KEY constraint

问题描述

我有此更新脚本，其中将新数据加载到数据库中或更新现有数据.但是我得到了这些错误.

I've this updatescript in which I load new data into my db or update existing data. But I get these errors.

The MERGE statement conflicted with the FOREIGN KEY constraint
"FK_JW_MaterialData2PL_cMat_MaterialText". 
 The conflict occurred in database "TEST", table "dbo.JW_MaterialData", column 'camosGUID'.

和

The MERGE statement conflicted with the FOREIGN KEY constraint
"FK_JW_MaterialData_cMat_Material". The conflict occurred in 
database "TEST", table "dbo.cMat_Material", column 'camosGUID'.

这是相关的陈述.但是，当然还有更多的MERGE语句和更多的表.

This are the statements which are relevant. But of course there are a lot more MERGE statements with a lot more tables.

MERGE INTO [dbo].[JW_MaterialData2PL] ON camosGUID
WHEN MATCHED THEN UPDATE SET 
WHEN NOT MATCHED THEN INSERT 

MERGE INTO [dbo].[cMat_Material] ON camosGUID
WHEN MATCHED THEN UPDATE SET 
WHEN NOT MATCHED THEN INSERT 

MERGE INTO [dbo].[JW_MaterialData] ON camosGUID
WHEN MATCHED THEN UPDATE SET 
WHEN NOT MATCHED THEN INSERT 

FK_JW_MaterialData2PL_cMat_MaterialText和FK_JW_MaterialData_cMat_Material设置为ON UPDATE CASCADE和ON DELETE CASCADE.

也许这是我放入MERGE语句的顺序的问题吗?还是您知道可能是什么原因?

Maybe is this a problem of the order I put the MERGE statements? Or do you know what could be the reason?

另一个问题:当我再次运行脚本时，没有错误.那么只运行两次scipt可以吗，否则将来可能会引起问题吗?

And another question: When I run the script again there are no errors. So is it OK to just run the scipt twice or could this cause problems in the future?

推荐答案

有必要通过查看表的依存关系以正确的顺序放置MERGE语句.

It was necessary to put the MERGE statements in the right order by looking at the dependencies of the tables.

1. cMat_Material
2. JW_MaterialData
3. JW_MaterialData2PL

1. cMat_Material
2. JW_MaterialData
3. JW_MaterialData2PL

这篇关于MERGE与FOREIGN KEY约束冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！