使用外键删除SQL表中的记录 [英] DELETE a record in SQL table with foreign key

问题描述

我的SQL Server中有两个表。

一个是ApplicationUser，另一个是UserApprovals。

这两个表与UserApprooval.UserId中的外键相关联ApplicationUser.UserId。

现在我需要删除一条记录;我开始从ApplicationUser开始，我不知道这是否正确，因为输入错误：

 [DELETE语句与REFERENCE约束冲突FK_UserApprovals_ApplicationUser发生冲突在数据库ekkerossDB表中dbo UserApprovals列UserID 
该语句已被终止] \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ >我尝试了什么： 
 
 
 
我认为正确的方法是先删除UserApprovals，然后再删除ApplicationUser（如果有必要的话）。
 
有没有人可以帮助我？
解决方案
 
是的，你应该删除任何引用 ApplicationUser 首先，即具有外键的任何行必须在删除该主键的行之前... 如果 参照完整性 [ ^ ]已打开。
 
 
 
您可以让SQL完成大部分工作使用级联删除 [ ^ ] 
 
 
 
不要忘记MSDN文档创建外键关系| Microsoft Docs  [ ^ ] 
 
 
如果你不能正常工作，你可以用它作为最后的手段：
 
 
  - 在DB级禁用约束
 EXEC sp_MSForEachTable'ALTER TABLE？ NOCHECK CONSTRAINT ALL'
 GO 
 DELETE FROM ApplicationUser 
 GO 
  - 在数据库级别启用约束
 EXEC sp_MSForEachTable'ALTER TABLE？检查约束所有'
 GO 
显示的示例用于SQL Server Management Studio。
 
 
I have two Tables in my SQL Server.

One is ApplicationUser and the other is UserApprovals.

These two Tables are related with Foreign key from UserApprooval.UserId to ApplicationUser.UserId.

Now I need to DELETE a record; and I start to do it from ApplicationUser, I don't know if that is right because trow an error:

[ The DELETE statement conflicted with the REFERENCE constraint  FK_UserApprovals_ApplicationUser   The conflict occurred in database  ekkerossDB  table  dbo UserApprovals  column UserID 
The statement has been terminated  ]\r\n UpdateApplicationRejectedUser




What I have tried:



I think the right is to DELETE first the UserApprovals and next the ApplicationUser (if that is necessary.

Did some one can assist me on that??
解决方案
Yes, you should delete anything that refers back to the ApplicationUser first i.e. any row that has a foreign key must go before the row with that primary key is removed... if referential integrity[^] is switched on.



You can get SQL to do much of the work for you by using Cascade Delete[^]



And don't forget the MSDN documentation Create Foreign Key Relationships | Microsoft Docs[^]

If you can't get it working in a normal way, you can use this as a last resort:

-- Disable Constraints at DB level
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
DELETE FROM ApplicationUser
GO
-- Enable Constraints at DB level
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
The example shown is for use in SQL Server Management Studio.


                        
这篇关于使用外键删除SQL表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！