多值语句中的 SQL Server 外键冲突? [英] SQL Server foreign key conflict in a multi values statement?

问题描述

我有一个 SQL Server INSERT 语句，如下所示:

I have a SQL Server INSERT sentence like below:

insert into foo( num, a, b ) values 
(1, a, b), 
(2, a, b), 
...
(9999, a, b);

但是存在INSERT 语句与 FOREIGN KEY 约束冲突"错误.问题是 SQL Server 没有告诉我究竟哪个值或与问题所在的行有关.如何快速找到错误值?

But there is a "INSERT statement conflicted with the FOREIGN KEY constraint" error. The problem is that SQL Server does not tell me which exactly is the value or line with the problem. How can I quickly find what the value with the error?

推荐答案

您可以将其转换为 SELECT 语句以查找不匹配的行.

You can turn this into a SELECT statement to find the rows that do not match.

假设列 a 引用表 other，以下将显示其他表中不存在的值:

Assuming that the column a references the table other the following will show the values that are not present in the other table:

select *
from (
  values 
   (1, a, b), 
   (2, a, b), 
    ...
   (9999, a, b)
) t(num,a,b)
where not exists (select 1 
                  from other o 
                  where o.id = t.a);

<小时>

select .. from ( values ) 需要 SQL Server 2012 或更高版本 - 但是当您使用该标签时，您应该能够使用它.

---

The select .. from ( values ) requires SQL Server 2012 or newer - but as you have used that tag, you should be able to use that.

这篇关于多值语句中的 SQL Server 外键冲突?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！