INSERT 语句与 FOREIGN KEY 约束冲突 - SQL Server

INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server

提问by SmartestVEGA

I am getting the following error. Could you please help me?

我收到以下错误。请你帮助我好吗？

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sup_Item_Sup_Item_Cat". The conflict occurred in database "dev_bo", table "dbo.Sup_Item_Cat". The statement has been terminated.

消息 547，级别 16，状态 0，第 1 行
INSERT 语句与 FOREIGN KEY 约束“FK_Sup_Item_Sup_Item_Cat”冲突。冲突发生在数据库“dev_bo”、表“dbo.Sup_Item_Cat”中。该语句已终止。

Code:

代码：

insert into sup_item (supplier_id, sup_item_id, name, sup_item_cat_id, 
                      status_code, last_modified_user_id, last_modified_timestamp, client_id)   
values (10162425, 10, 'jaiso', '123123',
        'a', '12', '2010-12-12', '1062425')

The last column client_idis causing the error. I tried to put the value which already exists in the dbo.Sup_Item_Catinto the column, corresponding to the sup_item.. but no joy :-(

最后一列client_id导致错误。我试图将已经存在的值dbo.Sup_Item_Cat放入列中，对应于 sup_item .. 但没有喜悦:-(

回答by Mike M.

In your table dbo.Sup_Item_Cat, it has a foreign key reference to another table. The way a FK works is it cannot have a value in that column that is not also in the primary key column of the referenced table.

在您的表中dbo.Sup_Item_Cat，它有一个对另一个表的外键引用。FK 的工作方式是它不能在该列中具有不在引用表的主键列中的值。

If you have SQL Server Management Studio, open it up and sp_help'dbo.Sup_Item_Cat'. See which column that FK is on, and which column of which table it references. You're inserting some bad data.

如果您有 SQL Server Management Studio，请打开它并使用sp_help“ dbo.Sup_Item_Cat”。查看 FK 在哪一列，以及它引用哪个表的哪一列。您正在插入一些错误数据。

Let me know if you need anything explained better!

如果您需要更好的解释，请告诉我！

回答by plasmasnakeneo

I had this issue myself, regarding the error message that is received trying to populate a foreign key field. I ended up on this page in hopes of finding the answer. The checked answer on this page is indeed the correct one, unfortunately I feel that the answer is a bit incomplete for people not as familiar with SQL. I am fairly apt at writing code but SQL queries are new to me as well as building database tables.

我自己也遇到过这个问题，关于在尝试填充外键字段时收到的错误消息。我最终来到了这个页面，希望能找到答案。这个页面上的检查答案确实是正确的，不幸的是我觉得对于不熟悉SQL的人来说答案有点不完整。我相当擅长编写代码，但 SQL 查询对我以及构建数据库表来说都是新的。

Despite the checked answer being correct:

尽管检查的答案是正确的：

Mike M wrote-

"The way a FK works is it cannot have a value in that column that is not also in the primary key column of the referenced table."

迈克 M 写道-

“FK 的工作方式是它不能在该列中具有不在引用表的主键列中的值。”

What is missing from this answer is simply;

这个答案所缺少的很简单；

You must build the table containing the Primary Key first.

您必须首先构建包含主键的表。

Another way to say it is;

另一种说法是；

You must Insert Data into the parent table, containing the Primary Key, before attempting to insert data into the child table containing the Foreign Key.

在尝试将数据插入包含外键的子表之前，您必须将数据插入包含主键的父表中。

In short, many of the tutorials seem to be glazing over this fact so that if you were to try on your own and didn't realize there was an order of operations, then you would get this error. Naturally after adding the primary key data, your foreign key data in the child table must conform to the primary key field in the parent table, otherwise, you will still get this error.

简而言之，许多教程似乎都对这一事实视若无睹，因此如果您自己尝试并且没有意识到存在操作顺序，那么您就会得到这个错误。自然添加主键数据后，你子表中的外键数据必须与父表中的主键字段一致，否则还是会报这个错误。

If anyone read down this far. I hope this helped make the checked answer more clear. I know there are some of you who may feel that this sort of thing is pretty straight-forward and that opening a book would have answered this question before it was posted, but the truth is that not everyone learns in the same way.

如果有人读到这里。我希望这有助于使检查的答案更加清晰。我知道你们中有些人可能会觉得这种事情很简单，打开一本书就会在发布之前回答这个问题，但事实是，并不是每个人都以同样的方式学习。

回答by Matthew Smith

You are trying to insert a record with a value in the foreign key column that doesn't exist in the foreign table.

您正在尝试在外键列中插入一条具有外键列中不存在于外表中的值的记录。

For example: If you have Books and Authors tables where Books has a foreign key constraint on the Authors table and you try to insert a book record for which there is no author record.

例如：如果您有 Books 和 Authors 表，其中 Books 在 Authors 表上有一个外键约束，并且您尝试插入没有作者记录的书籍记录。

回答by Justin Niessner

You'll need to post your statement for more clarification. But...

您需要发布您的声明以获得更多说明。但...

That error means that the table you are inserting data into has a foreign key relationship with another table. Before data can be inserted, the value in the foreign key field mustexist in the other table first.

该错误意味着您要向其中插入数据的表与另一个表具有外键关系。在插入数据之前，外键字段中的值必须先存在于另一个表中。

回答by John Waclawski

Something I found was that all the fields have to match EXACTLY.

我发现所有字段都必须完全匹配。

For example, sending 'cat dog' is not the same as sending 'catdog'.

例如，发送“cat dog”与发送“catdog”不同。

What I did to troubleshoot this was to script out the FK code from the table I was inserting data into, take note of the "Foreign Key" that had the constraints (in my case there were 2) and make sure those 2 fields values matched EXACTLY as they were in the table that was throwing the FK Constraint error.

我为解决此问题所做的工作是从我插入数据的表中编写 FK 代码，记下具有约束的“外键”（在我的情况下有 2 个）并确保这 2 个字段值匹配正如他们在抛出 FK 约束错误的表中一样。

Once I fixed the 2 fields giving my problems, life was good!

一旦我修复了 2 个字段给我的问题，生活就很好了！

If you need a better explanation, let me know.

如果您需要更好的解释，请告诉我。

回答by Cobusve

The problem is not with client_id from what I can see. It looks more like the problem is with the 4th column, sup_item_cat_id

从我所见，问题不在于 client_id。看起来问题更像是第 4 列 sup_item_cat_id

I would run

我会跑

sp_helpconstraint sup_item

and pay attention to the constraint_keys column returned for the foreign key FK_Sup_Item_Sup_Item_Cat to confirm which column is the actual problem, but I am pretty sure it is not the one you are trying to fix. Besides '123123' looks suspect as well.

并注意为外键 FK_Sup_Item_Sup_Item_Cat 返回的constraint_keys 列以确认哪一列是实际问题，但我很确定这不是您要修复的问题。除了“123123”看起来也很可疑。

回答by Donnie

It means exactly what it says. You're trying to insert a value into a column that has a FK constraint on it that doesn't match any values in the lookup table.

这正是它所说的意思。您正在尝试将一个值插入到一个列中，该列上有一个 FK 约束，该约束与查找表中的任何值都不匹配。

回答by dotnet

1. run sp_helpconstraint
2. pay ATTENTION to the constraint_keys column returned for the foreign key

1. 运行 sp_helpconstraint
2. 注意为外键返回的constraint_keys 列

回答by user1424678

Double check the fields in the relationship the foreign key is defined for. SQL Server Management Studio may not have had the fields you wanted selected when you defined the relationship. This has burned me in the past.

仔细检查定义外键的关系中的字段。在定义关系时，SQL Server Management Studio 可能没有您想要选择的字段。过去这让我很生气。

回答by sathish

Parent table data missing causes the problem. In your problem non availability of data in "dbo.Sup_Item_Cat" causes the problem

父表数据丢失导致问题。在您的问题中，“dbo.Sup_Item_Cat”中的数据不可用导致了问题