SQL Management Studio插入语句冲突
我正在为我的老板创建一个开发数据库。我有创建所有表的 SQL 脚本，但现在我有大量数据需要导入。数据库中共有 5 个表，它们的 ID 彼此相关，如下所示：
注意：我不允许共享数据库/表名称等信息
Table1
+-- Table2
+-- Table3
+-- Table4
+-- Table5
这意味着表 5 code> 有一个指向 Table 4 的 ID 的字段，依此类推。
所以我正在做一堆这样的插入：
INSERT INTO [dbo].[Table2] (Table1_ID, AnotherField) VALUES (159268, 408659)
但它会为每个插入抛出此异常：
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TABLE2_TABLE1". The conflict occurred in database "--removed by author--", table "dbo.TABLE1", column 'TABLE1_ID'.
编辑： 所以问题是我需要先将数据插入Table1中。所以我的老板找到了一些足以满足表1的数据...所以问题解决了:)
I'm working on creating a development database for my boss. I have the SQL script that creates all the tables, but now I have loads of data to import. There are 5 tables total in the database, and their ID's are related to eachother like so:
Note: I'm not allowed to share info like database/table names
Table1
+-- Table2
+-- Table3
+-- Table4
+-- Table5
Meaning that Table 5 has a field that points to a Table 4's ID, and so on.
So I'm doing a bunch of inserts like this:
INSERT INTO [dbo].[Table2] (Table1_ID, AnotherField) VALUES (159268, 408659)
But it throws this exception for each one:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TABLE2_TABLE1". The conflict occurred in database "--removed by author--", table "dbo.TABLE1", column 'TABLE1_ID'.
EDIT:
So the problem was that I needed to insert the data in Table1 first. So my Boss found some data that will suffice for Table1... So Problem solved :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
没有什么可添加的——错误消息非常清楚。
您正在尝试将 ID 值插入到 Table2 中，但该 ID 值在 Table1 中不存在。因此出现错误消息。
您需要先将数据插入表 1，然后再插入表 2
编辑：
如果表 1 没有数据，则数据库约束考虑不周。您必须从 Table2 中删除约束。然后您必须向他们询问 Table1 的数据。
There isn't really much to add - the error message is pretty clear.
You are trying to insert an ID value in to Table2 however the that ID value doesn't exist in Table1. Hence the error message.
You need to insert the data in to Table1 before you insert in to Table2
EDIT:
If there is no data for Table1 then the database constraints are poorly thought out. You will have to remove the constraint from Table2.You will have to ask them for the data for Table1 then.
Table1中的所有记录，然后导入Table2等。编辑：
好吧，如果您没有
Table1数据，您所能做的就是删除引用Table1的所有外键约束（可能来自Table2）。或者，您可以尝试生成“假”Table1数据，考虑Table2上可用的所有不同Table1ID (aaarrgh)。Table1, thenTable2, etc.EDIT:
Well, if you don't have
Table1data all you can do is remove all foreign-key constraints that referenceTable1(probably fromTable2). Or you could try generating "fake"Table1data, considering all distinctTable1IDs available onTable2(aaarrgh).您可以暂时禁用外键约束，执行插入，然后使用
CHECK和NOCHECK重新启用它。WITH NOCHECK CONSTRAINT禁用约束并允许通常违反约束的插入。WITH CHECK CONSTRAINT重新启用约束。下面是关于如何执行此操作的详细说明。
You can temporarily disable the foreign key constraint, do the insert, and then re-enable it using
CHECKandNOCHECK.WITH NOCHECK CONSTRAINTdisables the constraint and allows an insert that would normally violate the constraint.WITH CHECK CONSTRAINTre-enables the constraint.Here's a detailed explanation of how to do this.