如何写支票以避免消息"INSERT statement conflicted with the FOREIGN KEY constraint"？

Question

我阅读并理解了以下问的问题：插入语句与外国密钥约束冲突。我确实明白了，但是，在这种情况下，我需要将大约1 GB的记录插入桌子中，其中一些记录与外键相抵触。查询看起来像这样：

IF NOT EXISTS (SELECT * FROM [dbo].[tbl_R_TaskHistory] WHERE [TaskID] = 
10000529)
BEGIN insert into [dbo].[tbl_History] 
([TaskID],[UserID],[ActD],[RequestD],[No],[SignID],[Completed]) 
values (10000529,'A0000187',NULL,5738366,0,NULL,CAST(N'2011-03-16 
04:53:37.210' AS DateTime)) END 

requestID上的冲突ocurs，所以我认为必须有一种进行检查以避免错误消息的方法。我的观点是，我希望我的查询检查requestID是否没有外键约束，它不会插入此记录并移至下一个记录。

Answer 1

如果您的查询仅包含一行，则可以像这样展开检查：

IF NOT EXISTS (SELECT * FROM [dbo].[tbl_R_TaskHistory] WHERE [TaskID] = 10000529) AND EXISTS(SELECT 1 FROM [dbo].[...referencing table...] WHERE [RequestD] = 5738366)
BEGIN 
    insert into [dbo].[tbl_History] ([TaskID],[UserID],[ActD],[RequestD],[No],[SignID],[Completed]) 
    values (10000529,'A0000187',NULL,5738366,0,NULL,CAST(N'2011-03-16 04:53:37.210' AS DateTime));
END 

无论如何，如果您同时插入许多行，并且出于性能考虑，则最好将值存储在缓冲区表中。这样的东西：

insert into #tbl_History ([TaskID],[UserID],[ActD],[RequestD],[No],[SignID],[Completed]) 
values (10000529,'A0000187',NULL,5738366,0,NULL,CAST(N'2011-03-16 04:53:37.210' AS DateTime)) 
      ,(...)
      ,(...)
      ,(...)

然后，只需对您的引用表执行内部加入：

insert into [dbo].[tbl_History] ([TaskID],[UserID],[ActD],[RequestD],[No],[SignID],[Completed]) 
SELECT [TaskID],[UserID],[ActD],[RequestD],[No],[SignID],[Completed]
FROM #tbl_History A
INNER JOIN [dbo].[...referencing table...] B
    ON A.[RequestD] = B.[RequestD];

Answer 2

此语法也有效

declare @a int = 5;
declare @b int = 18;
insert into sample (a, b) 
select @a, @b  
where not exists (select 1 from sample where b = @b)
  and     exists (select 1 from student where iden = @a)

---

这避免了创建#TEMP

insert into sample (a, b) 
select a, b 
from ( values (5,19)
            , (5,30)
            , (5,31)
            , (5,32)
            , (7,41)
            , (7,42)
     ) v(a,b)  
where not exists (select 1 from sample where b = v.b)
  and     exists (select 1 from student where iden = v.a)