我得到了错误" DELETE语句冲突与基准约束" [英] I got error "The DELETE statement conflicted with the REFERENCE constraint"

问题描述

我试图截断与外键的表，得到了这个消息：

I tried to truncate a table with foreign keys and got the message:

无法截断表，因为它是被外键约束引用。

我读了很多文学的有关问题，并认为我找到了解决方案，通过删除

I read a lot of literature about the problem and thought that I found the solution by using delete

DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)

这是我仍然有一个错误信息：

By I still got an error message:

DELETE语句冲突与基准约束。

当我试图删除与Microsoft Management Studio中，执行之前的查询

When I am trying to delete with Microsoft Management Studio and execute the previous query

DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)

它并没有给它正确工作的错误。我想从一个表中删除所有的信息和添加新的进去，但我不想放弃，并创建外键。

it didn't give an error it worked properly. I want to delete all information from a table and add new into it, but I don't want to drop and create foreign keys.

推荐答案

该错误意味着你必须在其他表中的数据引用您要删除的数据。

The error means that you have data in other tables that references the data you are trying to delete.

您将需要或者删除并重新创建约束或删除数据表明外键引用。

You would need to either drop and recreate the constraints or delete the data that the Foreign Key references.

假设你有下面的表格

dbo.Students
(
StudentId
StudentName
StudentTypeId
)


dbo.StudentTypes
(
StudentTypeId
StudentType
)

假设一个外键约束在 StudentTypes 的 StudentTypeId 列之间存在和 StudentTypeId 列在学生

Suppose a Foreign Key constraint exists between the StudentTypeId column in StudentTypes and the StudentTypeId column in Students

如果您尝试删除 StudentTypes 将出现在学生的 StudentTypeId 列错误引用数据中的 。StudentTypes 表

If you try to delete all the data in StudentTypes an error will occur as the StudentTypeId column in Students reference the data in the StudentTypes table.

编辑：

删除和 TRUNCATE 基本上做同样的事情。唯一的区别是， TRUNCATE 不保存到日志文件中的变化。你也不能使用 TRUNCATE

DELETE and TRUNCATE essentially do the same thing. The only difference is that TRUNCATE does not save the changes in to the Log file. Also you can't use a WHERE clause with TRUNCATE

，为什么你可以在SSMS运行这一点，但不是通过你的应用程序。我真的不能看到这种情况出现。该FK约束仍然会抛出一个错误，无论交易发源在那里。

AS to why you can run this in SSMS but not via your Application. I really can't see this happening. The FK constraint would still throw an error regardless of where the transaction originated from.

这篇关于我得到了错误" DELETE语句冲突与基准约束"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！